= 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.