= Time Based Sllow Data DB =

We want to see what happens if we write all FACT slow data into a databases. If this works out nicely, we want to try next to allow people to access and analyse slowdata less painfully than using the aux files directly. We are not sure yet, if it is possible to provide easier access, without hiding to much from the users. Often simplicity comes at a price and we try to reduce this price, while gaining as much simplicity as possible.

== Table design ==

First we want to try, and see what happens if we directly translate the aux file bin table extension format into MySQL tables. No relations will be created at this point.
Already this simple step, is not trivially possible, since arrays cannot be stored triviall in an RDMS. So our first approach would be to translate an array "stuff" of size 100 into 100 columns named "stuff_00" to "stuff_99". 

Of course when reading the data back from the DB, the information, that the data has been an array before will be lost. (Well, not if we provide some helpful code, that is able to 'recreate' the array-like data structure just by 'looking' at the column names, but this is not included in the first approach).


Here is an overview over most of our aux files, sorted by size. 

|| name                                    || delta t [s]  ||  stddev [s] || rows   ||  width [bytes]    ||    size = rows*width [bytes]||
||BIAS_CONTROL_DAC                         ||     0.568 ||     6.419 ||    137359 ||      1676 || 230213684 ||
||BIAS_CONTROL_VOLTAGE                     ||     0.568 ||     6.419 ||    137359 ||      1676 || 230213684 ||
||FEEDBACK_CALIBRATED_CURRENTS             ||     1.230 ||     9.689 ||     63269 ||      3372 || 213343068 ||
||FAD_CONTROL_EVENT_DATA                   ||     5.386 ||    15.066 ||      7050 ||     23052 || 162516600 ||
||BIAS_CONTROL_CURRENT                     ||     0.686 ||     7.046 ||    113823 ||       844 ||  96066612 ||
||FTM_CONTROL_DYNAMIC_DATA                 ||     1.415 ||     1.239 ||     61082 ||      1086 ||  66335052 ||
||FTM_CONTROL_TRIGGER_RATES                ||     1.415 ||     1.239 ||     61082 ||       844 ||  51553208 ||
||FAD_CONTROL_STATISTICS1                  ||     1.000 ||     0.003 ||     86431 ||       428 ||  36992468 ||
||FSC_CONTROL_BIAS_TEMP                    ||    16.722 ||     4.993 ||      5168 ||      1304 ||   6739072 ||
||FTM_CONTROL_COUNTER                      ||     0.647 ||     1.043 ||    133532 ||        36 ||   4807152 ||
||FAD_CONTROL_TEMPERATURE                  ||     5.392 ||    15.020 ||      7043 ||       654 ||   4606122 ||
||FTM_CONTROL_STATIC_DATA                  ||    15.309 ||    69.539 ||      5063 ||       752 ||   3807376 ||
||DATA_LOGGER_STATS                        ||     1.035 ||     0.004 ||     83494 ||        44 ||   3673736 ||
||FAD_CONTROL_STATS                        ||     1.048 ||     0.006 ||     82476 ||        44 ||   3628944 ||
||DRIVE_CONTROL_TRACKING_POSITION          ||     1.068 ||     0.555 ||     33809 ||        76 ||   2569484 ||
||DRIVE_CONTROL_POINTING_POSITION          ||     1.054 ||     0.111 ||     81983 ||        28 ||   2295524 ||
||FAD_CONTROL_STATUS                       ||     1.996 ||    24.535 ||     18960 ||        96 ||   1820160 ||
||FAD_CONTROL_RUN_NUMBER                   ||     3.992 ||    34.784 ||      9480 ||       180 ||   1706400 ||
||FAD_CONTROL_TRIGGER_COUNTER              ||     1.081 ||     6.753 ||     35122 ||        44 ||   1545368 ||
||FSC_CONTROL_TEMPERATURE                  ||    16.722 ||     4.993 ||      5168 ||       252 ||   1302336 ||
||DRIVE_CONTROL_STATUS                     ||     1.054 ||     0.111 ||     81983 ||        15 ||   1229745 ||
||FAD_CONTROL_REFERENCE_CLOCK              ||     5.392 ||    15.020 ||      7043 ||       174 ||   1225482 ||
||FAD_CONTROL_EVENTS                       ||     1.094 ||     6.809 ||     34703 ||        28 ||    971684 ||
||BIAS_CONTROL_MESSAGE                     ||     1.132 ||    10.282 ||     70635 ||        12 ||    847620 ||
||FSC_CONTROL_CURRENT                      ||    16.722 ||     4.993 ||      5168 ||       136 ||    702848 ||
||FSC_CONTROL_VOLTAGE                      ||    16.722 ||     4.993 ||      5168 ||       136 ||    702848 ||
||BIAS_CONTROL_STATE                       ||     1.698 ||    14.192 ||     47076 ||        12 ||    564912 ||
||SQM_CONTROL_DATA                         ||     5.000 ||     0.001 ||     17286 ||        32 ||    553152 ||
||LID_CONTROL_DATA                         ||     5.002 ||     0.202 ||     17278 ||        32 ||    552896 ||
||FAD_CONTROL_DAC                          ||    44.923 ||   442.293 ||       800 ||       684 ||    547200 ||
||FAD_CONTROL_CONNECTIONS                  ||     3.893 ||    34.286 ||      9722 ||        53 ||    515266 ||
||RATE_SCAN_DATA                           ||   102.766 ||   956.443 ||       573 ||       836 ||    479028 ||
||RATE_SCAN_MESSAGE                        ||     1.769 ||   125.806 ||     33225 ||        12 ||    398700 ||
||PWR_CONTROL_DATA                         ||     5.137 ||     0.399 ||     16826 ||        20 ||    336520 ||
||FEEDBACK_MESSAGE                         ||     3.339 ||    19.504 ||     23943 ||        12 ||    287316 ||
||MAGIC_WEATHER_DATA                       ||    19.767 ||     7.996 ||      4372 ||        42 ||    183624 ||
||FSC_CONTROL_HUMIDITY                     ||    16.722 ||     4.993 ||      5168 ||        32 ||    165376 ||
||AGILENT_CONTROL_24V_DATA                 ||    15.000 ||     0.033 ||      5762 ||        28 ||    161336 ||
||AGILENT_CONTROL_50V_DATA                 ||    15.000 ||     0.032 ||      5762 ||        28 ||    161336 ||
||AGILENT_CONTROL_80V_DATA                 ||    15.000 ||     0.072 ||      5762 ||        28 ||    161336 ||
||GPS_CONTROL_NEMA                         ||    60.000 ||     0.001 ||      1441 ||        40 ||     57640 ||
||MAGIC_WEATHER_MESSAGE                    ||    19.748 ||     7.038 ||      4376 ||        12 ||     52512 ||
||MCP_CONFIGURATION                        ||    22.903 ||    75.766 ||      1659 ||        28 ||     46452 ||
||FAD_CONTROL_MESSAGE                      ||    11.919 ||    55.860 ||      3187 ||        12 ||     38244 ||
||MCP_MESSAGE                              ||    12.409 ||    56.926 ||      3061 ||        12 ||     36732 ||
||TEMPERATURE_DATA                         ||    60.407 ||     4.864 ||      1431 ||        24 ||     34344 ||
||FTM_CONTROL_MESSAGE                      ||    29.959 ||   538.642 ||      2614 ||        12 ||     31368 ||
||FEEDBACK_CALIBRATION_STEPS               ||  7977.991 || 15955.981 ||         6 ||      5008 ||     30048 ||
||MCP_STATE                                ||    22.847 ||    75.683 ||      1663 ||        12 ||     19956 ||
||TNG_WEATHER_DATA                         ||   300.010 ||     0.156 ||       288 ||        68 ||     19584 ||
||RATE_CONTROL_MESSAGE                     ||    50.982 ||  1019.175 ||      1521 ||        12 ||     18252 ||
||TEMPERATURE_MESSAGE                      ||    59.288 ||     6.809 ||      1458 ||        12 ||     17496 ||
||FAD_CONTROL_STATE                        ||    26.647 ||    81.170 ||      1426 ||        12 ||     17112 ||
||FTM_CONTROL_STATE                        ||    62.956 ||   782.737 ||      1234 ||        12 ||     14808 ||
||FEEDBACK_CALIBRATION                     || 39889.953 ||     0.000 ||         2 ||      6668 ||     13336 ||
||FAD_CONTROL_START_RUN                    ||    84.382 ||   127.959 ||       451 ||        28 ||     12628 ||
||DIM_CONTROL_MESSAGE                      ||    44.932 ||   111.104 ||       913 ||        12 ||     10956 ||
||FAD_CONTROL_RUNS                         ||    80.277 ||   124.719 ||       474 ||        20 ||      9480 ||
||RATE_CONTROL_STATE                       ||   108.382 ||  1483.901 ||       716 ||        12 ||      8592 ||
||FSC_CONTROL_MESSAGE                      ||   127.224 ||    18.792 ||       680 ||        12 ||      8160 ||
||FEEDBACK_CALIBRATION_R8                  || 39889.953 ||     0.000 ||         2 ||      3340 ||      6680 ||
||DATA_LOGGER_STATE                        ||    88.927 ||   131.025 ||       428 ||        12 ||      5136 ||
||DRIVE_CONTROL_SOURCE_POSITION            ||   694.667 ||   722.352 ||        53 ||        91 ||      4823 ||
||RATE_CONTROL_THRESHOLD                   ||   299.184 ||   165.609 ||       121 ||        30 ||      3630 ||
||DRIVE_CONTROL_MESSAGE                    ||   381.581 ||  2995.895 ||       221 ||        12 ||      2652 ||
||DATA_LOGGER_NUM_SUBS                     ||   608.098 ||  3594.266 ||       121 ||        20 ||      2420 ||
||TIME_CHECK_MESSAGE                       ||   447.689 ||   441.846 ||       192 ||        12 ||      2304 ||
||FEEDBACK_STATE                           ||   451.599 ||  2810.061 ||       178 ||        12 ||      2136 ||
||DATA_LOGGER_FILENAME_NIGHTLY             ||   608.098 ||  3594.266 ||       121 ||        16 ||      1936 ||
||FAD_CONTROL_DRS_RUNS                     ||   651.135 || 10020.114 ||        56 ||        32 ||      1792 ||
||TIME_CHECK_OFFSET                        ||   900.005 ||     0.192 ||        96 ||        16 ||      1536 ||
||DRIVE_CONTROL_STATE                      ||   307.644 ||   595.365 ||       127 ||        12 ||      1524 ||
||MAGIC_LIDAR_DATA                         ||   612.450 ||    53.593 ||        21 ||        36 ||       756 ||
||FAD_CONTROL_FILE_FORMAT                  ||   948.925 ||  1698.665 ||        39 ||        14 ||       546 ||
||FAD_CONTROL_REGION_OF_INTEREST           ||  1976.848 ||  1879.630 ||        20 ||        16 ||       320 ||
||MAGIC_LIDAR_MESSAGE                      ||   612.822 ||    52.446 ||        21 ||        12 ||       252 ||
||FAD_CONTROL_INCOMPLETE                   ||     0.330 ||     0.919 ||        11 ||        20 ||       220 ||
||TEMPERATURE_STATE                        ||  2014.740 ||  3929.100 ||        18 ||        12 ||       216 ||
||TNG_WEATHER_DUST                         ||  7200.273 ||     1.052 ||        12 ||        16 ||       192 ||
||RATE_SCAN_STATE                          ||  4199.063 ||  7709.202 ||        15 ||        12 ||       180 ||
||TNG_WEATHER_MESSAGE                      ||  7200.041 ||     0.049 ||        12 ||        12 ||       144 ||
||RATE_SCAN_PROCESS_DATA                   || 14632.373 || 15636.454 ||         5 ||        24 ||       120 ||
||DIM_CONTROL_STATE                        ||  6773.531 || 14085.157 ||         7 ||        12 ||        84 ||
||EVENT_SERVER_MESSAGE                     || 10905.531 || 10767.661 ||         5 ||        12 ||        60 ||
||LID_CONTROL_MESSAGE                      ||  9297.873 || 15467.975 ||         5 ||        12 ||        60 ||
||LID_CONTROL_STATE                        || 12040.861 || 16997.522 ||         4 ||        12 ||        48 ||
||MAGIC_WEATHER_STATE                      ||  3122.660 ||  4404.884 ||         4 ||        12 ||        48 ||
||EVENT_SERVER_STATE                       || 43622.122 ||     0.000 ||         2 ||        12 ||        24 ||
||GCN_MESSAGE                              ||     6.311 ||     0.000 ||         2 ||        12 ||        24 ||
||GCN_STATE                                ||     6.311 ||     0.000 ||         2 ||        12 ||        24 ||
||PWR_CONTROL_MESSAGE                      || 40274.024 ||     0.000 ||         2 ||        12 ||        24 ||
||PWR_CONTROL_STATE                        || 40274.024 ||     0.000 ||         2 ||        12 ||        24 ||


So if we simply stick to this table deisgn, we can estimate, that for out longest table (the one with the most rows -- 137k per night) we might end with about 0.5 giga lines after 10 years.
I think, this is still no problem for an InnoDB table index, so in principle, I don't see a problem in creating one table per file, for the entire lifetime of the experiment.

=== Table width ===
Since MySQL tables cannot be wider that 64kb , out widest fits tables might create problems, if we don't take care about column sizes:
These are our widest tables:

|| name                                    || delta t [s]  ||  stddev [s] || rows   ||  width [bytes]    ||    size = rows*width [bytes]||
||FAD_CONTROL_EVENT_DATA                   ||     5.386 ||    15.066 ||      7050 ||     23052 || 162516600 ||
||FEEDBACK_CALIBRATION                     || 39889.953 ||     0.000 ||         2 ||      6668 ||     13336 ||
||FEEDBACK_CALIBRATION_STEPS               ||  7977.991 || 15955.981 ||         6 ||      5008 ||     30048 ||
||FEEDBACK_CALIBRATED_CURRENTS             ||     1.230 ||     9.689 ||     63269 ||      3372 || 213343068 ||
||FEEDBACK_CALIBRATION_R8                  || 39889.953 ||     0.000 ||         2 ||      3340 ||      6680 ||
||BIAS_CONTROL_DAC                         ||     0.568 ||     6.419 ||    137359 ||      1676 || 230213684 ||
||BIAS_CONTROL_VOLTAGE                     ||     0.568 ||     6.419 ||    137359 ||      1676 || 230213684 ||
||FSC_CONTROL_BIAS_TEMP                    ||    16.722 ||     4.993 ||      5168 ||      1304 ||   6739072 ||
||FTM_CONTROL_DYNAMIC_DATA                 ||     1.415 ||     1.239 ||     61082 ||      1086 ||  66335052 ||
||BIAS_CONTROL_CURRENT                     ||     0.686 ||     7.046 ||    113823 ||       844 ||  96066612 ||
||FTM_CONTROL_TRIGGER_RATES                ||     1.415 ||     1.239 ||     61082 ||       844 ||  51553208 ||

=== arrays --> columns, Number of columns ===
I found on SO that there is a limit on the total number of columns in a table at 3398 columns.
Since storage of arrays is not suported natively by MySQL we decided to create a large number of columns fo each array.

This limit is crossed by FAD_CONTROL_EVENT_DATA, which ends up having 5762 columns. So we omit this aux file for the time being.

=== omitted FITS files ===
the following slow data files were omitted during table creation:

    * 'CLIENT_LIST' # ???
    * 'CHAT_MESSAGE'  # ???
    * 'FAD_CONTROL_START_RUN' # ???
    * 'FAD_CONTROL_EVENT_DATA' # too many columns
    * 'FEEDBACK_CALIBRATION' # omitted because it generates a strange error, that might be connected to a column name, that is the same as an internal innoDB table name     or so... strange 
    * 'GPS_CONTROL_NEMA', # omitted, because of column name 'Time' and 'time', maybe my mysql installation is case insensitive .. :-| stupid me.



= First experiences with slow data insertion =

In about 8h I only managed to insert 262MB of data. While neither the python process, that was reading the data, nor the mysqld, that was writing the data comsumed 100% CPU load at any time. Not sure why this is the case?
Mybe the stuff was io blocked.

--> python script is 40...500 times faster when not inserting the data into the DB, but just reading... so input side is not io blocked.

apparently one can improve the situation a bit by using bulk inserta, but one must also stay below the maximum paket size

== Mongo ==

without bulk creation. 1day slow data (20130701) = 227MB in 356seconcs into the DB. (roughly 3 CPUs at ~60% maybe)
So we write into this DB also with only ~0.6MB/sec, but its worked right away ... no tweaking necessary.

filesize of DB --> 1.0GB  
So an increase of a factor of ~5 is observed.
That means for our 2TB of slow data, we would need already 10TB diskspace.

And loading 2TB, i.e. (2 years of slow data) into the DB would take roughly 5 weeks.