Optimise the ci_builds table to reduce per-job footprint
Following my evaluation that was done as part of Ensure CI/CD can scale and ci_builds we should focus first on splitting the ci_builds
table.
We can simply shrink the table size with that by 4-5x times (excluding indexes) and introduce a more aggressive cleaning of old records from separate table.
Copying the content:
Currently, the ci_builds
is very extensive:
create_table "ci_builds", force: :cascade do |t|
t.string "status"
t.datetime "finished_at"
t.text "trace"
t.datetime "created_at"
t.datetime "updated_at"
t.datetime "started_at"
t.integer "runner_id"
t.float "coverage"
t.integer "commit_id"
t.text "commands"
t.string "name"
t.text "options"
t.boolean "allow_failure", default: false, null: false
t.string "stage"
t.integer "trigger_request_id"
t.integer "stage_idx"
t.boolean "tag"
t.string "ref"
t.integer "user_id"
t.string "type"
t.string "target_url"
t.string "description"
t.text "artifacts_file"
t.integer "project_id"
t.text "artifacts_metadata"
t.integer "erased_by_id"
t.datetime "erased_at"
t.datetime "artifacts_expire_at"
t.string "environment"
t.integer "artifacts_size", limit: 8
t.string "when"
t.text "yaml_variables"
t.datetime "queued_at"
t.string "token"
t.integer "lock_version"
t.string "coverage_regex"
t.integer "auto_canceled_by_id"
t.boolean "retried"
t.integer "stage_id"
t.integer "artifacts_file_store"
t.integer "artifacts_metadata_store"
t.boolean "protected"
t.integer "failure_reason"
end
It contains a mix of different data, with different long-termvity. For example some of data are required by runner to know exactly what to run, some of data are requried to present the status in the user interface. Storing everything in single place makes the job footprint to be on average 2.5kB of raw data (206GB / 86341522 rows). The impact of that is that every change to this table significantly increases IO requirements as we have to read and write the new data which in the end results in much slower queries. We update rows of this table a number of times: when changing statuses (around 5-6 times) and mostly the data that has to be rewritten could be stored in separate table making DB to be more happy. Having such big footprint also makes it very expensive to fetch data from this table, as we have to maintain big shared buffers with hot data (often accessed).
We could quite easily split this table into smaller tables based on the what kind of rate of data update. We likely need to have constant (only inserted) to represent data that is required to prepare job definition for runner. We likley need some data to be dynamic to hold the current state of job to be quickly accessed. Doing that would be possible without affecting currently running system, as it would be transparent to application how we store the data.
Splitting data also allows us to reduce the per-job footprint to probably around 200 bytes (keeping in database only most crucial data), and be able to recycle the rest after some period. For example, we would make jobs to be retryable no longer than for 1 year or 1 month. After that we would recycle data and with the current storage requirements we would be able to hold 10 times more.
The biggest take-ways:
-
commands
weights 26GB, on average 358 bytes, -
target_url
weights 133MB, on average 73 bytes, -
yaml_variables
weights 15GB, on average 220 bytes, -
token
weights 1.46GB, on average 21 bytes, -
options
weights 28GB, on average 395 bytes, - on average record weights 1059 bytes without indexes,
- it seems that all other data would weight around 90-100 bytes only (seems quite low though).
Some of these items can be moved into a separate table:
commands
,yaml_variables
andoptions
. This alone is 69GB worth of data. This data can be recycled after sometime recycling the data.