[gprd] Manually kill a migration to end the transaction for database process ID 82178
Production
Change
Change Summary
[gprd]
Manually kill a migration to end the transaction for database process ID 82178
.
This process is running this statement in a transaction in the production database: DROP INDEX CONCURRENTLY "ci_job_artifacts_expire_at_unlocked_idx"
That process is taking a very long time and there is no way to know when it will complete.
It is blocking deployments. (For more details, see: #6821 (closed))
The change steps for mitigating this situation involve the following production changes (adapted from #6821 (comment 910467418)).
- Cancel the deployment pipeline job which is running the migration: https://ops.gitlab.net/gitlab-com/gl-infra/deployer/-/jobs/6801872
- Check the status of the
82178
statement:DROP INDEX CONCURRENTLY "ci_job_artifacts_expire_at_unlocked_idx"
- If the process is still running, then forcibly
cancel
it. - If the process is still running, then forcible
terminate
it.
Originating production incident issue: #6821 (closed)
Change Details
- Services Impacted - ServicePostgres
- Change Technician - @nnelson
- Change Reviewer - @bshah11
-
Time tracking -
20 minutes
-
Downtime Component -
No downtime
Detailed steps for the change
Pre-Change Steps - steps to be completed before execution of the change
Estimated Time to Complete - 5 minutes
-
Set label changein-progress on this issue -
Check the list of long-running transactions in the production database. ssh patroni-v12-05-db-gprd.c.gitlab-production.internal sudo gitlab-psql
SELECT pid, application_name, client_addr, state, age(clock_timestamp(), query_start) as duration, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state != 'idle' AND age(clock_timestamp(), query_start) > '00:01:00' ORDER BY age(clock_timestamp(), query_start) DESC;
-
Verify that pid
82178
(DROP INDEX CONCURRENTLY "ci_job_artifacts_expire_at_unlocked_idx"
) is in the list. -
Navigate to the pipeline job page: https://ops.gitlab.net/gitlab-com/gl-infra/deployer/-/jobs/6801872 -
Double-check to confirm that the transaction is still active by checking the prometheus metrics and confirming that it resembles:
query | seconds |
---|---|
{application="web", environment="gprd", fqdn="patroni-v12-05-db-gprd.c.gitlab-production.internal", shard="default", stage="main", type="patroni"} |
10802.588974 |
Change Steps - steps to take to execute the change
Estimated Time to Complete - 10 minutes
-
Cancel the job: https://ops.gitlab.net/gitlab-com/gl-infra/deployer/-/jobs/6801872 -
Check the status of the postgres process: ssh patroni-v12-05-db-gprd.c.gitlab-production.internal sudo gitlab-psql
SELECT pid, application_name, client_addr, state, age(clock_timestamp(), query_start), query from pg_stat_activity where pid = 82178;
-
If the process state
is stillactive
, then manually cancel it.select pg_cancel_backend(82178);
-
Check the status of the postgres process: SELECT pid, application_name, client_addr, state, age(clock_timestamp(), query_start), query from pg_stat_activity where pid = 82178;
-
Wait for 3 minutes, checking the status intermittently, to give the backend an opportunity to cancel the process. -
If the process state
remainsactive
, then manually terminate it.select pg_terminate_backend(82178);
-
Set label changecomplete on this issue
Post-Change Steps - steps to take to verify the change
Estimated Time to Complete - 2 minutes
-
Check the status of the postgres process: SELECT pid, application_name, client_addr, state, age(clock_timestamp(), query_start), query from pg_stat_activity where pid = 82178;
-
Verify that the results are empty, because the process no longer exists.
Rollback
Rollback steps - steps to be taken in the event of a need to rollback this change
Estimated Time to Complete - 0 minutes
-
No roll back required nor possible -- the statement will have been terminated.
Monitoring
Key metrics to observe
- Metric:
pg_stat_activity_marginalia_sampler_max_tx_age_in_seconds
- Location: https://prometheus-db.gprd.gitlab.net
- What changes to this metric should prompt a rollback:
No rollback is possible
- What changes to this metric should constitute a success:
The metric should disappear from the chart linked 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. -
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.
Edited by Nels Nelson