Database table sizes - Githubissues

CamDavidsonPilon commented 3 years ago

As of current date, after running the Pioreactor for a few months in a small cluster, here's a snapshot of how large the Sqlite3 artifacts are on disk (in mb) (some of these tables aren't around anymore):

             od_reading_statistics   0.004096
    sqlite_autoindex_experiments_1   0.004096
        led_automation_settings_ix   0.004096
     dosing_automation_settings_ix   0.004096
           led_automation_settings   0.004096
                     sqlite_master   0.012288
        dosing_automation_settings   0.020480
                    stirring_rates   0.020480
temperature_automation_settings_ix   0.024576
                    experiments_ix   0.024576
                       experiments   0.028672
             alt_media_fraction_ix   0.073728
   temperature_automation_settings   0.086016
                      config_files   0.122880
                alt_media_fraction   0.180224
                  dosing_events_ix   0.217088
                       pid_logs_ix   0.335872
                     dosing_events   0.692224
                          pid_logs   1.441792
           temperature_readings_ix   1.949696
              temperature_readings   4.399104
                           logs_ix  21.004288
           od_readings_filtered_ix  22.425600
              od_readings_filtered  45.903872
          kalman_filter_outputs_ix  50.630656
                   growth_rates_ix  50.630656
                              logs  91.009024
                      growth_rates 105.705472
                od_readings_raw_ix 158.986240
                     led_events_ix 164.225024
                   od_readings_raw 367.345664
                        led_events 450.797568
             kalman_filter_outputs 638.550016

How can we optimize this? What's redundant?

[x] led_events data is very redundant - this was only holding the IR LED blinking on and off over experiments.
[x] Probably remove the index on led_events - we don't use it in the UI
[x] Remove the index kalman_filter_outputs_ix
[x] kalman_filter_outputs redundancy is storing arrays of floats as strings. That's silly. We could truncate the precision of the floats:
```
[0.9817443680619926, 1.007342729613004, 4.877678040174688e-05, 0.0]
```
to
```
[0.9817, 1.00734, 4.87e-05, 0.0]
```

CamDavidsonPilon commented 2 years ago

More recent snapshot of a different cluster:

kalman_filter_outputs|76.603392
od_readings_raw|25.706496
od_readings_filtered|23.781376
growth_rates|23.781376
od_readings_raw_ix|9.568256
od_readings_filtered_ix|9.424896
growth_rates_ix|9.424896
logs|6.418432
stirring_rates|3.223552
ir_led_intensities|2.179072
logs_ix|1.212416
temperature_readings|0.499712
temperature_readings_ix|0.200704
config_files|0.14336
temperature_automation_settings|0.02048
sqlite_schema|0.012288
temperature_automation_events|0.004096
sqlite_autoindex_pioreactor_unit_labels_1|0.004096
sqlite_autoindex_experiments_1|0.004096
pioreactor_unit_labels|0.004096
od_blanks|0.004096
led_change_events|0.004096
led_automation_settings|0.004096
led_automation_events|0.004096
experiments_ix|0.004096
experiments|0.004096
dosing_events|0.004096
dosing_automation_settings|0.004096
dosing_automation_events|0.004096
alt_media_fractions_ix|0.004096
alt_media_fractions|0.004096

CamDavidsonPilon commented 2 years ago

After a month of experiments with new KF table (no json)

19                      kalman_filter_outputs  204.320768
10                            od_readings_raw  156.975104
12                       od_readings_filtered  140.292096
22                               growth_rates  140.292096
9                          od_readings_raw_ix   58.769408
21                            growth_rates_ix   57.184256
11                    od_readings_filtered_ix   57.184256
20                         ir_led_intensities   38.772736
15                                       logs   37.752832
4                              stirring_rates   19.386368
14                                    logs_ix    6.844416
1                        temperature_readings    3.186688
0                     temperature_readings_ix    1.261568
28                               config_files    0.540672
2             temperature_automation_settings    0.053248
5                               sqlite_schema    0.016384
24                                experiments    0.012288
23                             experiments_ix    0.012288
13                                  od_blanks    0.012288
6   sqlite_autoindex_pioreactor_unit_labels_1    0.004096
18                      led_automation_events    0.004096
3               temperature_automation_events    0.004096
8                      pioreactor_unit_labels    0.004096
17                    led_automation_settings    0.004096
16                          led_change_events    0.004096
25                              dosing_events    0.004096
26                 dosing_automation_settings    0.004096
27                   dosing_automation_events    0.004096
7              sqlite_autoindex_experiments_1    0.004096
29                     alt_media_fractions_ix    0.004096
30                        alt_media_fractions    0.004096

CamDavidsonPilon commented 1 month ago

A more recent snapshot:

The total size of the sqlite db was 4gb

sqlite> SELECT name ,SUM(pgsize)/1024 table_size  FROM "dbstat" GROUP BY name ORDER BY table_size desc;
name                                              table_size
------------------------------------------------  ----------
kalman_filter_outputs                             719720
pioreactor_unit_activity_data                     718384
pioreactor_unit_activity_data_ix                  512444
od_readings                                       403496
od_readings_ix                                    367964
od_readings_filtered                              349172
growth_rates                                      349172
growth_rates_ix                                   334264
od_readings_filtered_ix                           332876
logs                                              283080
pwm_dcs                                           244448
stirring_rates                                    127032
logs_ix                                           88896
ir_led_intensities                                47752
temperature_automation_events                     45192
temperature_readings                              14756
dosing_events                                     13804
temperature_readings_ix                           8192
alt_media_fractions                               5948
dosing_automation_events                          3396
alt_media_fractions_ix                            3000
config_files_histories                            200
dosing_automation_settings                        48
temperature_automation_settings                   36
sqlite_schema                                     20
calibrations                                      20
workers                                           4
sqlite_autoindex_workers_1                        4
sqlite_autoindex_pioreactor_unit_labels_2         4
sqlite_autoindex_pioreactor_unit_labels_1         4
sqlite_autoindex_experiments_1                    4
sqlite_autoindex_experiment_worker_assignments_1  4
sqlite_autoindex_calibrations_1                   4
pioreactor_unit_labels                            4
od_blanks                                         4
led_change_events                                 4
led_automation_settings                           4
led_automation_events                             4
experiments_ix                                    4
experiments                                       4
experiment_worker_assignments                     4
experiment_profile_runs                           4
config_files_histories_ix                         4
calibrations_ix                                   4

Pioreactor / pioreactor

Database table sizes #196