Pioreactor / Pioreactor/pioreactor

Database table sizes

Open
#196 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

performance sql / database
Dominant language
Python
Stars
149
Forks
18
PR merge metrics
No merged PRs in 30d

Description

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?

  • led_events data is very redundant - this was only holding the IR LED blinking on and off over experiments.
  • Probably remove the index on led_events - we don't use it in the UI
  • Remove the index kalman_filter_outputs_ix
  • 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]

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

No files or tests are named. Start by locating the SQLite schema and the tables listed in the issue, then measure current table and index sizes; done means the remaining redundant storage is reduced and the database behavior is validated without breaking existing use.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlite
Domain
databases
Issue type
Refactor
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.