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