2022-08-02: GSTG Truncate some CI tables on Main and Main Tables on CI to free storage
Production Change
Change Summary
This change is for STAGING.
Since we finished CI decomposition in https://gitlab.com/gitlab-com/gl-infra/production/-/issues/7111 we now have a bunch of big unused tables sitting around in the Main and CI databases that belong in the other databases. Nothing should be reading or writing tables that belong in the other database but due to https://gitlab.com/gitlab-com/gl-infra/capacity-planning/-/issues/81 and probably other reasons we want to free up this storage.
In future we plan to implement a more automated way of truncating all tables in gitlab-org/gitlab#366787 (closed) but for now we want to alleviate some storage pressure. Additionally running these commands in staging and production may also reveal issues we need to be aware of when implementing an automated approach.
For this change request we'll only be truncating ci_builds_metadata
and merge_request_diff_commits
since these are big enough to free up a good amount of storage and have no foreign keys that will prevent them from being truncated on their own.
The following may be useful to diagnose if things are taking too long:
- Check if locks exist for table:
select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'ci_builds_metadata';
- Find queries for locks associated with a table:
select pid, state, usename, query, query_start from pg_stat_activity where pid in (select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 'ci_builds_metadata');
- Cancel a pid:
SELECT pg_cancel_backend(<PID>);
- Terminate a pid:
SELECT pg_terminate_backend(<PID>);
- Find the longest running queries:
select pid, state, usename, query, query_start from pg_stat_activity order by query_start asc limit 10;
Change Details
- Services Impacted - ServicePostgres
-
Change Technician -
@DylanGriffith
- Change Reviewer - @Finotto
- Time tracking - unknown
- Downtime Component - none
Detailed steps for the change
Change Steps - steps to take to execute the change
Estimated Time to Complete (mins) - 60
-
Running during low utilization hours due to large amounts of IO from truncate -
Set label changein-progress /label ~change::in-progress
-
Validate there are definitely no queries for ci_builds_metadata
in the Main database-
Sequential Tuple Reads should be 0 -
Index tuple reads should be 0 -
Slow query logs only shows querys on patroni-ci-
databases
-
-
Connect to the Main Patroni database server -
Note disk usage from df
:Used 495GB
-
Confirm for certain that you are actually connected to the Main Patroni database with the correct write prevention installed. The below should show triggers for INSERT
,UPDATE
,DELETE
to callgitlab_schema_prevent_write
select event_object_table, trigger_name, event_manipulation, action_statement from information_schema.triggers where event_object_table = 'ci_builds_metadata';
-
Confirm there are no locks on this table: select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'ci_builds_metadata';
-
On the Main primary database run the following to truncate ci_builds_metadata
:begin; SET session_replication_role = replica; -- ignore triggers on ci_builds_metadata TRUNCATE TABLE ci_builds_metadata; commit;
-
Note disk usage from df
(should have gone down):Used 490GB
=> reduced 5GB -
Close the Main primary database session -
Validate there are definitely no queries for merge_request_diff_commits
in the CI database-
Sequential Tuple Reads should be 0 -
Index tuple reads should be 0 -
Slow query logs only shows querys on patroni-0X-
databases
-
-
Connect to the CI Patroni database server -
Note disk usage from df
:Used 491GB
-
Confirm for certain that you are actually connected to the CI Patroni database with the correct write prevention installed. The below should show triggers for INSERT
,UPDATE
,DELETE
to callgitlab_schema_prevent_write
select event_object_table, trigger_name, event_manipulation, action_statement from information_schema.triggers where event_object_table = 'merge_request_diff_commits';
-
Confirm there are no locks on this table: select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'merge_request_diff_commits';
-
On the CI primary database run the following to truncate merge_request_diff_commits
:begin; SET session_replication_role = replica; -- ignore triggers on merge_request_diff_commits TRUNCATE TABLE merge_request_diff_commits; commit;
-
Note disk usage from df
(should have gone down):Used 377GB
=> saved 114GB -
Close the CI primary database session -
Set label changecomplete /label ~change::complete
Rollback
Rollback steps - steps to be taken in the event of a need to rollback this change
Estimated Time to Complete (mins) - 15
-
This data was unused and stale so if there is an issue with some service that was reading this data then it's probably best to roll forward and fix whatever just broke -
If there is absolutely no way to roll forward and you really need to bring back the data that was truncated you can theoretically recover this table from any recent snapshot (including the delayed replicas) since these tables had writes blocked and haven't been written to for a month. -
Set label changeaborted /label ~change::aborted
Monitoring
Key metrics to observe
- Metric: Postgresql Overview
- Location: https://dashboards.gitlab.net/d/000000144/postgresql-overview?orgId=1
- What changes to this metric should prompt a rollback: Sudden saturation of any metrics
- Metric: Sentry
- Location: sentry.gitlab.net/
- What changes to this metric should prompt a rollback: New errors that are caused by this missing data
- Metric: Patroni Overview
- Location: https://dashboards.gitlab.net/d/patroni-main/patroni-overview?orgId=1
- What changes to this metric should prompt a rollback: Large increase in errors
Change Reviewer checklist
-
Check if the following applies: - The scheduled day and time of execution of the change is appropriate.
- The change plan is technically accurate.
- The change plan includes estimated timing values based on previous testing.
- The change plan includes a viable rollback plan.
- The specified metrics/monitoring dashboards provide sufficient visibility for the change.
-
Check if the following applies: - The complexity of the plan is appropriate for the corresponding risk of the change. (i.e. the plan contains clear details).
- The change plan includes success measures for all steps/milestones during the execution.
- The change adequately minimizes risk within the environment/service.
- The performance implications of executing the change are well-understood and documented.
- The specified metrics/monitoring dashboards provide sufficient visibility for the change.
- If not, is it possible (or necessary) to make changes to observability platforms for added visibility?
- The change has a primary and secondary SRE with knowledge of the details available during the change window.
- The labels blocks deployments and/or blocks feature-flags are applied as necessary
Change Technician checklist
-
Check if all items below are complete: - The change plan is technically accurate.
- This Change Issue is linked to the appropriate Issue and/or Epic
- Change has been tested in staging and results noted in a comment on this issue.
- A dry-run has been conducted and results noted in a comment on this issue.
- For C1 and C2 change issues, the change event is added to the GitLab Production calendar.
- For C1 and C2 change issues, the SRE on-call has been informed prior to change being rolled out. (In #production channel, mention
@sre-oncall
and this issue and await their acknowledgement.) - For C1 and C2 change issues, the SRE on-call provided approval with the eoc_approved label on the issue.
- For C1 and C2 change issues, the Infrastructure Manager provided approval with the manager_approved label on the issue.
- Release managers have been informed (If needed! Cases include DB change) prior to change being rolled out. (In #production channel, mention
@release-managers
and this issue and await their acknowledgment.) - There are currently no active incidents that are severity1 or severity2
- If the change involves doing maintenance on a database host, an appropriate silence targeting the host(s) should be added for the duration of the change.