Incident Review: PatroniLongRunningTransation Site Wide Degredation

Key Information

Metric Value
Customers Affected All users accessing the website and background jobs
Requests Affected All requets
Incident Severity severity2
Start Time 2024-10-08 10:02:00 UTC
End Time 2024-10-08 10:19:00 UTC
Total Duration 17 minutes
Link to Incident Issue #18677 (closed)

Summary

A part of a customer project export we tried to run a DELETE query that took 20minutes+ to execute. This resulted into apdex drop in ServicePatroni.

Screenshot_2024-10-11_at_11.33.54

source

Since ServicePatroni slowed down, it resulted in a site-wide degradation

Screenshot_2024-10-11_at_11.34.48

source

Luckily we've caught this early enough that nothing paged the on-call, nor do we see a dip into our availability dashboard

Screenshot_2024-10-11_at_11.36.22

source

Details

At 2024-10-08 09:56 an alert fire PatroniLongRunningTransactionDetected but this didn't page the on-call which was a bit confusing at first, and had gone unnoticed for at least 5 minutes.

During a similar time (2024-10-08 10:12 UTC), another incident was created severity2 2024-10-08: Pipelines not completing and Stuck ... (#18676 - closed) by support which diverted focus on that incident, however it did seem related because we identified sidekiq slowdowns which we suspected it was related to this. When we have a long-running query in Postgres this will lead to dead-tuples and performance degradation site-wide, as we see in the rails_primary_sql apdex dropping:

Screenshot_2024-10-11_at_11.33.54

source

We then identified the long-running query following the long-running transaction query which showed that this was coming from the rails console:

-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid            | 16406
datname          | gitlabhq_production
pid              | 2454953
leader_pid       |
usesysid         | 16384
usename          | gitlab
application_name |
client_addr      | 10.217.4.9
client_hostname  |
client_port      | 56590
backend_start    | 2024-10-08 09:22:50.084117+00
xact_start       | 2024-10-08 09:43:40.855693+00
query_start      | 2024-10-08 10:04:37.649471+00
state_change     | 2024-10-08 10:04:37.64952+00
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 2194184182
backend_xmin     |
query_id         | -4585514564375050396
query            | /*application:console,db_config_name:main,console_hostname:console-01-sv-gprd,console_username:xxxx-rails*/ DELETE FROM "events" WHERE "events"."target_id" = xxxxx AND "events"."target_type" = 'Note'
backend_type     | client backend
now              | 2024-10-08 10:04:37.65331+00
query_age        | 00:00:00.003839
xact_age         | 00:20:56.797617

After we identified the person running the query we reached out to them on Slack to see if it was safe to cancel the query, and they did it themselves after the query got canceled (2024-10-08 10:13) we saw an improvement in the patroni apdex immediately and took another 6 minutes for everything else to stabilize (this is most likely due to metrics taking a while to recover).

The query started at 2024-10-08 09:43:40.855693+00 and was killed at 2024-10-08 10:13:40.855693+00 as we can see in the marginalia sampler below:

Screenshot_2024-10-11_at_13.40.00

source

This means that the query ran for 19 minutes (2024-10-18 10:02 - 2024-10-18 09:43) before it affected the rest database and the rest of the services.

Outcomes/Corrective Actions

  1. Alert PatroniLongRunningTransactionDetected Not... (production-engineering#25883 - closed)
  2. Unbounded Transaction Duration Inside Rails (production-engineering#25884)

Learning Opportunities

What went well?

  1. It was super simple to find the long-running query, runbook attached to the alert was super clear: https://gitlab.com/gitlab-com/runbooks/blob/master/docs/patroni/alerts/PatroniLongRunningTransactionsDetected.md
  2. Dashboard attached to alert was super clear as well: https://dashboards.gitlab.net/d/alerts-long_running_transactions/alerts3a-long-running-transactions?from=1728380700000&to=1728385200000&var-environment=gprd&orgId=1

What was difficult?

  1. The only thing difficult was that we had 2 incidents triggered at the same time from different people, they seemed related but later found out it wasn't

Review Guidelines

This review should be completed by the team which owns the service causing the alert. That team has the most context around what caused the problem and what information will be needed for an effective fix. The EOC or IMOC may create this issue, but unless they are also on the service owning team, they should assign someone from that team as the DRI.

For the person opening the Incident Review

  • Set the title to Incident Review: (Incident issue name)
  • Assign a Service::* label (most likely matching the one on the incident issue)
  • Set a Severity::* label which matches the incident
  • In the Key Information section, make sure to include a link to the incident issue
  • Find and Assign a DRI from the team which owns the service (check their slack channel or assign the team's manager) The DRI for the incident review is the issue assignee.
  • Announce the incident review in the incident channel on Slack.
:mega: @here An incident review issue was created for this incident with <USER> assigned as the DRI.
If you have any review feedback please add it to <ISSUE_LINK>.

For the assigned DRI

  • Fill in the remaining fields in the Key Information section, using the incident issue as a reference. Feel free to ask the EOC or other folks involved if anything is difficult to find.
  • If there are metrics showing Customers Affected or Requests Affected, link those metrics in those fields
  • Create a few short sentences in the Summary section summarizing what happened (TL;DR)
  • Use the description section to write a few paragraphs explaining what happened
  • Link any corrective actions and describe any other actions or outcomes from the incident
  • Consider the implications for self-managed and Dedicated instances. For example, do any bug fixes need to be backported?
  • Add any appropriate labels based on the incident issue and discussions
  • Once discussion wraps up in the comments, summarize any takeaways in the details section
  • Close the review before the due date
Edited by Steve Xuereb