Cases10 cases

6 to 10 of 10

1. IL-0582 Jobs piled up in Kanmu's PostgreSQL-based qg job queue on Aurora as dequeue slowed and dead tuples kept growing

   Kanmu2024

   Symptoms

   A Datadog alert fired because a large number of unprocessed jobs had piled up in Kanmu's job queue and were not being worked off. The jobs were the batch enqueued at about the same time every day, in the usual volume, but the workers took far longer than normal to dequeue and process them.

   Throughput degradationHigh latency

   Trigger

   A heavy ad hoc analysis query from the BI tool Redash ran for a long time on the writer instance, because the Redash data source pointed at the cluster endpoint.

   Root cause

   Operator error

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Change with a delayed effectUnreviewed defaults

   Detection

   A Datadog alert on unprocessed jobs piling up in the job queue.

   Remediation

   • Changed the Redash data source to the reader endpoint
   • Ended the long-running Redash query with pg_terminate_backend
   • Ran VACUUM on que_jobs by hand again, which cleaned up the dead tuples

   Action items

   • Make Redash unable to connect to the writer instance even if the writer endpoint is set, by mapping its host name to a TEST-NET-1 address in the Redash container's /etc/hosts
   • Set statement_timeout on PostgreSQL users so that long queries stop automatically
   • Consider improving qg's dequeue algorithm or adopting a job queue such as river that uses SELECT FOR UPDATE SKIP LOCKED

   Lessons

   A long-running query on the same database instance, even one unrelated to que_jobs, can keep VACUUM from removing que_jobs dead tuples, so the table keeps growing and the job queue slows down. qg's design with advisory locks and a recursive CTE is sensitive to dead tuples.

   View case
2. IL-0511 MIT xPro stopped responding after a configuration deploy as pgbouncer logins to RDS failed with 'wrong password type'

   MIT Open Learning2023

   Symptoms

   On 30 October 2023 at 3:51PM, six minutes after a configuration update was deployed to the xPro production Heroku stack, Pingdom alerted the DevOps on-call engineer, who confirmed at 3:52PM that the site was not responding.

   Service outageCredentials unavailable

   Trigger

   A configuration update applied through Salt at 3:45PM found that the Vault lease of the database credentials was about to expire and could not be renewed, so Vault generated new credentials.

   Root cause

   Configuration change

   Component

   DatabaseSecrets store

   Contributing factor

   Unreviewed defaultsChange with a delayed effectConfiguration not in code

   Detection

   Pingdom alerted the DevOps on-call engineer at 3:51PM, and the alert went through OpsGenie; Pingdom closed it automatically at 4:25PM.

   Remediation

   • Created a new database parameter group and applied it to the running RDS instance
   • Cleared the cached credentials and re-applied the Heroku configuration through Salt

   Action items

   • Move the management of xPro resources, including the RDS instance, into Pulumi, where AWS default parameter groups are not used

   Lessons

   Almost none of the configuration for this stack, the RDS instance in particular, was managed as code, and that was one of the main reasons the fix took long. Under Pulumi the instance would not have used an AWS default parameter group, and the fix would have been a change to an attached group.

   View case
3. IL-0386 After a move to Google Cloud, Postgres connection timeouts and memcached i/o timeouts spiked on GKE nodes

   incident.io2023

   Symptoms

   Shortly after incident.io moved its infrastructure fully into Google Cloud, the application saw a larger-than-normal number of Postgres connection timeouts, and new database connections per second were in the hundreds, spiking up and down. At the same time, the memcached instance used for transient data and faster API responses had sporadic connection timeouts.

   Intermittent timeoutsPacket loss

   Trigger

   After the move to Google Cloud, the application opened hundreds of new Postgres connections per second, and an accidental database join made it send thousands of duplicate calls to an external third party in parallel.

   Root cause

   Capacity exhaustion

   Component

   CNI pluginDatabaseIn-memory cache

   Contributing factor

   Unreviewed defaultsOverlapping failures

   Detection

   An incident was declared when the number of Postgres connection timeouts rose above normal. The remaining cache timeouts were traced through the logs of the Kubernetes node that served an affected request, the request's trace and the node's metrics.

   Remediation

   • Doubled the maximum Postgres connection lifespan from 15m to 30m
   • Made the Postgres connection pools static so they always keep N connections open
   • Raised the memcache client's MaxIdleConns from 2 to 20
   • Moved temporarily from Google's hosted memcached to a memcached inside the Kubernetes cluster
   • Shipped the fix for the accidental database join that made thousands of duplicate external calls

   Action items

   • Set more sensible concurrency limits for outbound network calls

   Lessons

   Network timeouts are not always local to one request or pod: when they appear, the whole activity of the node, including other pods on it, needs to be checked. Staying within a provider's rate limits does not keep outbound calls from affecting a node, so outbound network calls need more sensible concurrency limits.

   View case
4. IL-0431 During Mackerel's move to AWS, PostgreSQL failed over to a replica that had stopped replicating, losing an hour of data

   Hatena2017

   Symptoms

   On 7 August 2017, while checking the application after the maintenance that moved Mackerel from an on-premises data center to AWS, the team found that data updated during a specific time window was missing. Everything written to the PostgreSQL database between 14:30 and 15:30 JST was gone.

   Data lossReplication lagNetwork unreachable

   Trigger

   One hour after the application was switched to the newly promoted primary db001, db001 lost network connectivity and the database failed over to the standby db002.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase failover manager

   Contributing factor

   Overlapping failuresUnreviewed defaultsUnrepresentative stagingAutomation amplification

   Detection

   The team found the missing data while checking how the application behaved after the migration work.

   Remediation

   • Tried to restore the lost updates by hand from the table data left on db001 and gave up
   • Tried point-in-time recovery from the base backup and WAL and gave up
   • Built a new replica db003 with db002 as primary to restore redundancy
   • Kept db001 in maintenance state for investigation
   • Watched db002 for some time to confirm the problem did not recur, then completed the migration

   Action items

   • Change the PostgreSQL replica creation script to set recovery_target_timeline = 'latest' in recovery.conf by default
   • Add a check to a server configuration test tool such as Serverspec that recovery.conf contains recovery_target_timeline = 'latest'

   Lessons

   A setting needed only for a rare layout, such as a grandchild replica during a migration, was applied by hand through a script option and was forgotten. Because recovery_target_timeline = 'latest' is harmless on ordinary standbys too, Hatena now sets it by default whenever a replica is created. The staging rehearsal used the same layout and steps but had the setting right, so it could not catch the mistake. Hatena notes that a similar layout may be used again for a future PostgreSQL upgrade or a move to another region.

   View case
5. IL-0218 GitLab.com's primary PostgreSQL data was wiped by mistake and, with no working backup, the site was down about 18 hours

   GitLab2017

   Symptoms

   From around 19:00 UTC on 31 January 2017, load on GitLab.com's PostgreSQL database rose, which GitLab suspected was spam, and many users could not post comments on issues and merge requests. Around 23:00 UTC the replication of the PostgreSQL secondary fell behind and then failed, because WAL segments it still needed had already been removed from the primary.

   Database overloadReplication lagData lossService outage

   Trigger

   While trying to restore replication to the secondary, an engineer ran the removal of the PostgreSQL data directory on the primary, db1.cluster.gitlab.com, believing it was the secondary.

   Root cause

   Operator error

   Component

   DatabaseDatabase replicationDatabase backups and snapshotsCloud block storage

   Contributing factor

   Untested backupSingle point of failureNo runbookPages not deliveredUnverified assumptionUnreviewed defaults

   Detection

   The engineer who ran the deletion on the primary noticed the mistake and stopped it a second or two later.

   Remediation

   • Raised max_wal_senders from 3 to 32 and lowered max_connections from 8000 to 2000 so PostgreSQL would restart
   • Stopped the deletion on the primary a second or two after noticing it
   • Restored the database from the LVM snapshot taken about 6 hours before the outage by copying the PostgreSQL data directory from staging
   • Restored the webhooks from a separate database built from the snapshot and imported them as a SQL dump
   • Incremented all database sequences by 100,000 so IDs used before the outage could not be reused
   • Re-enabled GitLab.com gradually
   • Rebuilt projects created after 17:20 UTC in basic form from their Git repositories on the NFS cluster

   Action items

   • Change the shell prompt (PS1) on all hosts to show more clearly which host and environment is in use
   • Monitor backups with Prometheus
   • Set PostgreSQL's max_connections to a sane value
   • Investigate point-in-time recovery and continuous archiving for PostgreSQL
   • Take LVM snapshots of the production databases every hour
   • Take Azure disk snapshots of the production databases
   • Move staging to the ARM environment
   • Recover the production replicas
   • Test recovery of PostgreSQL database backups automatically
   • Improve the PostgreSQL replication documentation and runbooks
   • Investigate pgbarman for PostgreSQL backups
   • Investigate WAL-E for database backup and realtime replication
   • Build a streaming database restore
   • Assign an owner for data durability
   • Set up multiple secondaries and balance load among them with pgpool-II
   • Provide a tool for developers to run and revert Rails migrations on staging
   • Build owned recovery procedures for the rest of the GitLab.com infrastructure, not only the database
   • Publish a backup monitoring dashboard and add more backup data to it
   • Improve the abuse reporting and response system so removal for spam does not hard delete
   • Redact engineers' names in future public incident documents

   Lessons

   GitLab split the incident into two problems: GitLab.com went down because the primary's data directory was removed by mistake during a manual, undocumented replication rebuild, and recovery took over 18 hours because the backups had failed silently, disk snapshots were not enabled and the only copy sat on slow staging disks. The backup procedure was never tested because no one owned it. GitLab chose to focus on fast, regularly practised recovery and on making it obvious which host an engineer is on, rather than on blocking commands, since a safer rm only protects against one of many ways to lose data.

   View case

By root cause5