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.
With Bulk create …
Filling 3 years of slow data into the DB, should only take about 3 days now. That's feasible, I think.
We skipped a couple of slow data services from inserting into the DB, because we didn't understand, what they mean. Currently as a test, we inserted everything (apart from the services we skipped) from 01.04.2014 until 04.01.2015 into the DB. So it's not a full year. The size of the DB is about 400GB. Currently we are running on a 500GB (actually only 450GB) SSD.
Today I am moving the data to a 1.5TB normal disk and try to fill in everything from 2012 until now. I will go backwards in time ... so starting with data from 2015. And see how far I can go down.
The Filler
Currently the filler works on aux-files that were copied from La Palma to ETH. It does not care about what data has already been filled into the DB, but tries to fill it again (which fails, because of the "Time" key constraint) ... this is not very efficient.
In the future the filler should a) be a constantly running thing, like the data logger, which fills the aux-DB in real time. But there should also be some way of "see if something is missing and re-fill it from the aux-file"-thing. In case the real time filler looses connection to the DB and stops writing to it.
This is the list of services, we currently fill into the DB:
AGILENT_CONTROL_24V_DATA AGILENT_CONTROL_50V_DATA AGILENT_CONTROL_80V_DATA BIAS_CONTROL_CURRENT BIAS_CONTROL_DAC BIAS_CONTROL_STATE BIAS_CONTROL_VOLTAGE DRIVE_CONTROL_POINTING_POSITION DRIVE_CONTROL_SOURCE_POSITION DRIVE_CONTROL_STATE DRIVE_CONTROL_STATUS DRIVE_CONTROL_TRACKING_POSITION FAD_CONTROL_CONNECTIONS FAD_CONTROL_DAC FAD_CONTROL_DNA FAD_CONTROL_DRS_RUNS FAD_CONTROL_EVENTS FAD_CONTROL_FILE_FORMAT FAD_CONTROL_FIRMWARE_VERSION FAD_CONTROL_INCOMPLETE FAD_CONTROL_PRESCALER FAD_CONTROL_REFERENCE_CLOCK FAD_CONTROL_REGION_OF_INTEREST FAD_CONTROL_RUN_NUMBER FAD_CONTROL_RUNS FAD_CONTROL_START_RUN FAD_CONTROL_STATE FAD_CONTROL_STATISTICS1 FAD_CONTROL_STATS FAD_CONTROL_STATUS FAD_CONTROL_TEMPERATURE FAD_CONTROL_TRIGGER_COUNTER FEEDBACK_CALIBRATED_CURRENTS FEEDBACK_CALIBRATION FEEDBACK_CALIBRATION_R8 FEEDBACK_CALIBRATION_STEPS FEEDBACK_STATE FSC_CONTROL_BIAS_TEMP FSC_CONTROL_CURRENT FSC_CONTROL_HUMIDITY FSC_CONTROL_TEMPERATURE FSC_CONTROL_VOLTAGE FTM_CONTROL_COUNTER FTM_CONTROL_DYNAMIC_DATA FTM_CONTROL_FTU_LIST FTM_CONTROL_STATE FTM_CONTROL_STATIC_DATA FTM_CONTROL_TRIGGER_RATES GPS_CONTROL_NEMA LID_CONTROL_DATA LID_CONTROL_STATE MAGIC_WEATHER_DATA MAGIC_WEATHER_STATE MCP_CONFIGURATION MCP_STATE PWR_CONTROL_DATA PWR_CONTROL_STATE RATE_CONTROL_STATE RATE_CONTROL_THRESHOLD RATE_SCAN_DATA RATE_SCAN_PROCESS_DATA RATE_SCAN_STATE SQM_CONTROL_DATA TEMPERATURE_DATA TIME_CHECK_OFFSET TNG_WEATHER_DATA TNG_WEATHER_DUST TNG_WEATHER_STATE
