Cases21 cases

19 to 21 of 21

1. IL-0486 Figma's PostgreSQL 9 database on RDS slowed under high write IOPS and lock contention until the site was unavailable

   Figma2020

   Symptoms

   On January 22, 2020 at 9:45 AM PST, Figma's database metrics showed rising write IOPS and lock contention, while database CPU was lower than normal. An automated alert followed at 10:00 AM, and by 11:42 AM enough API requests had queued up behind the database that Figma became unavailable for some users.

   Lock contention or deadlockDatabase overloadService outage

   Trigger

   An automatic ANALYZE on one of the tables in the query finished at exactly the moment the incident began. The new statistics pushed the planner's row estimate for an inner subquery past 200 rows, and the plan for the whole query switched to an estimate of over 20 million rows.

   Root cause

   Software defect

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Overlapping failuresHarmful manual action

   Detection

   Database metrics showed higher write IOPS and lock contention at 9:45 AM PST, and an automated alert at 10:00 AM started the investigation.

   Remediation

   • Cancelled several inessential queries and increased the allocated IOPS
   • Cancelled autovacuum statements running on the largest tables
   • Changed autovacuum_freeze_max_age and restarted the database to stop aggressive autovacuuming
   • Took the service down for emergency maintenance and upgraded the database to PostgreSQL 11

   Action items

   • Revisit transaction timeouts and limits so long-running and expensive queries are terminated
   • Improve metrics, monitoring and database settings for autovacuuming, locking and query performance
   • Audit queries for unnecessary or excessive joins and subqueries that could trip up the query planner
   • Bring the performance tools of the upgraded PostgreSQL into the incident response plans
   • Audit the structure and access patterns of data stored in PostgreSQL and consider moving some of it to datastores suited to those patterns
   • Improve in-app messaging about unexpected maintenance windows
   • Move towards online database upgrades that do not need downtime
   • Let users keep working on already open files safely while offline, even after closing the browser

   Lessons

   Having upgraded staging to PostgreSQL 11, tested it for months and written a production upgrade plan let the team upgrade under poor conditions with confidence. A routine statistics refresh can flip a query to a catastrophic plan, and the same query's plan can differ widely over time, so comparing plans from snapshots taken at different dates exposed the cause.

   View case
2. IL-0457 The HA HDFS NameNode of a log-collection Hadoop cluster failed over around 09:00 after its ZKFC health check timed out

   LINE2019

   Symptoms

   The NameNode of an HA HDFS cluster run by LINE Data Labs occasionally failed over at a particular time of day, around 09:00. At the failover the ZooKeeper Failover Controller (ZKFC) logged that its NameNode health check had timed out after 60 seconds: WARN ha.HealthMonitor (HealthMonitor.java:doHealthChecks(211)) - Transport-level exception trying to monitor health of NameNode at xxxx/xxx.xxx.xxx.xxx:xxxx: java.net.SocketTimeoutException: 60000 millis timeout while waiting for channel to be ready for read.

   Unnecessary failoverLock contention or deadlockIntermittent timeouts

   Trigger

   Around 09:00 the NameNode began deleting the files under the .Trash checkpoint (TrashPolicyDefault#deleteCheckpoint), and deleting one directory held the FSNamesystem write lock for about 36 seconds.

   Root cause

   Capacity exhaustion

   Component

   Shared network filesystemDatabase failover manager

   Contributing factor

   Unreviewed defaults

   Remediation

   • Changed fs.trash.interval from 4320 to 1440 so files under .Trash are deleted daily instead of every 3 days
   • Configured a dedicated Service RPC port for the ZKFC and DataNode health checks

   View case
3. IL-0391 GoCardless API requests timed out for about 15 seconds while a Postgres migration waited for an AccessExclusive lock

   GoCardless2015

   Symptoms

   Just after a planned Postgres schema migration started, GoCardless received alerts that API requests were timing out. The API was down for about 15 seconds, until the migration went through and the API came back.

   Lock contention or deadlockService outage

   Trigger

   A deploy with a schema migration that renamed the empty invoicing tables and dropped and recreated their foreign key constraints, which referenced some of the most heavily used tables.

   Root cause

   Faulty deployment

   Component

   DatabaseSchema migration

   Contributing factor

   Unverified assumption

   Detection

   Alerts about API requests timing out began just after the schema migration started.

   Lessons

   Avoiding renames of tables in use, table rewrites under an exclusive lock and expensive work while holding one was not enough for GoCardless: a short DDL statement can still cause downtime by waiting in the lock queue behind a long-running query. GoCardless recommends removing long-running queries and transactions from the application and running analytics on an asynchronously updated replica, setting log_min_duration_statement and log_lock_waits to find such queries, setting lock_timeout in migration scripts so a deploy aborts rather than taking the application down, splitting schema changes so locks are held for shorter times, and keeping Postgres up to date. Developers with a console on the production database can cause the same downtime by leaving a transaction open.

   View case

By root cause8