Removal of foreign key fk_e4ef9c2f27 on PRD
Production Change
Change Summary
We had a recent incident where the post-migration for removing a foreign key on the ci_builds
table has failed. We couldn't acquire the exclusive lock after several retries.
To mitigate the issue the migration was marked "done" and it was skipped. This change request is for manually removing the foreign key during off-peak hours.
It's important to remove this foreign key because it's responsible for another incident: #6625 (closed)
Change Details
- Services Impacted - Database
- Change Technician - @msmiley @cindy
- Change Reviewer - @pbair, @msmiley
- Time tracking - 15-60 minutes
-
Downtime Component - No hard downtime is expected, but this will cause lock contention during each attempt. We are limiting the duration of that contention by setting
statement_timeout
. If the contention lasts long enough, it could saturate the pgbouncer connection pool, which could lead to a brief burst of slowness and timeouts.
Detailed steps for the change
Pre-Change Steps - steps to be completed before execution of the change
Estimated Time to Complete (3 mins) - Estimated Time to Complete in Minutes
-
Verify that the foreign key is still present
PSQL console:
\d+ ci_runners
You should see the following FK entry:
TABLE "ci_builds" CONSTRAINT "fk_e4ef9c2f27" FOREIGN KEY (runner_id) REFERENCES ci_runners(id) ON DELETE SET NULL NOT VALID
Change Steps - steps to take to execute the change
Estimated Time to Complete (15 mins) - Estimated Time to Complete in Minutes
-
Remove the foreign key
BEGIN;
SET LOCAL statement_timeout='3s'; -- can be increased to 5-8s
LOCK ci_builds, ci_runners IN ACCESS EXCLUSIVE MODE;
ALTER TABLE ci_builds DROP CONSTRAINT fk_e4ef9c2f27;
COMMIT;
If the locks cannot be acquired on the two tables, the statement can fail with a statement timeout.
A ROLLBACK;
can be issued to abort the current transaction, and the process can be retried. You might need to manually adjust the statement_timeout
if the statement fails with statement timeout.
Post-Change Steps - steps to take to verify the change
Estimated Time to Complete (3 mins) - Estimated Time to Complete in Minutes
-
Verify that the foreign key is gone
PSQL console:
\d+ ci_runners
You shouldn't see fk_e4ef9c2f27
.
Rollback
Rollback is not necessary. This FK is a NOT VALID
FK and needs to be replaced with loose foreign keys: gitlab-org/gitlab!83129 (merged)
Monitoring
Key metrics to observe
A few seconds of lock timeout is not too noticeable, we might get a slight APDEX drop.
Summary of infrastructure changes
-
Does this change introduce new compute instances? -
Does this change re-size any existing compute instances? -
Does this change introduce any additional usage of tooling like Elastic Search, CDNs, Cloudflare, etc?
Summary of the above
Change Reviewer checklist
-
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.
-
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.
Change Technician checklist
-
This issue has a criticality label (e.g. C1, C2, C3, C4) and a change-type label (e.g. changeunscheduled, changescheduled) based on the Change Management Criticalities. -
This issue has the change technician as the assignee. -
Pre-Change, Change, Post-Change, and Rollback steps and have been filled out and reviewed. -
This Change Issue is linked to the appropriate Issue and/or Epic -
Necessary approvals have been completed based on the Change Management Workflow. -
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. -
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.) -
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.