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.