Cases2 cases

1 to 2 of 2

1. IL-0456 Inflearn went down six times during a January 2022 free-course event as slow queries piled up on Aurora PostgreSQL

   Inflab2022

   Symptoms

   During a 100% discount event that made 125 courses free, Inflearn received 5 to 20 times its normal traffic, and on four days, 3, 4, 7 and 17 January 2022, the service became unusable around 9 a.m. and 10 p.m. There were six outages in all.

   Service outageDatabase overloadHigh latency

   Trigger

   A 100% discount event on 125 courses, from 3 to 17 January and 18 to 24 January 2022, went viral and brought 5 to 20 times the usual traffic.

   Root cause

   Capacity exhaustion

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Inefficient queryMonitoring blind spotExcessive blast radius

   Detection

   The team followed the outages through hourly transaction counts, RDS metrics, monitoring and slow query alerts.

   Remediation

   • Added an index to the interested-companies query on the course page
   • Changed how often the cache data stored in the database was reset and regenerated
   • Moved the read queries that loaded the writer DB to the reader DBs
   • Scaled the database to 8 r5.8xlarge instances, which bought only a few tens of minutes
   • Rewrote slow course queries or added indexes so they use indexes, and switched off features whose queries could not be fixed
   • Removed the large course description column from the select * course queries
   • Split the IDs of the IN clause into batches of 20 run with Promise.all
   • Upgraded PostgreSQL from 10.16 to 11.13
   • Set a 5-second query timeout
   • Raised max connections
   • Ran VACUUM by hand on the course table
   • Moved the large course description column into a separate table

   Action items

   • Change the structure in which one table's failure spreads to the whole service
   • Improve the table and cache structures that are hard to tune
   • Make the SQL code easier to follow
   • Improve code that gives no confidence to change, such as code without types or test code
   • Build a stable batch environment
   • Keep a monolith of reasonable size and separate the domains through which failures spread most

   Lessons

   Because every database bottleneck appeared as slow queries on the reader DBs, watching only the readers hid the root cause on the writer. In PostgreSQL, frequent updates to a table with large columns produce large dead tuples in bulk and slow every query on that table. Before the second event the team could not run a meaningful performance test because the first week showed that almost any page could fail under load. The event exposed high sensitivity to failure in every area, one table's failure spreading to the whole service, hard-to-tune table and cache structures, unintuitive SQL code, code without types or tests, no stable batch environment, and many hidden failure points not yet found.

   View case
2. IL-0211 A Mandrill PostgreSQL shard shut down at transaction ID wraparound and email sending was impaired for about 40 hours

   Mailchimp2019

   Symptoms

   From 05:35 UTC on 4 February 2019 Mandrill, Mailchimp's transactional email service, sent only 80% of queued emails, because one of its PostgreSQL shards, shard4, stopped accepting writes. When Mandrill engineers opened an investigation at 11:58 UTC, they saw a sharply elevated load on shard4.

   Writes rejectedThroughput degradationLoad imbalanceNode disk full

   Trigger

   At 05:35 UTC on 4 February the transaction ID counter of shard4, the busiest Mandrill PostgreSQL shard, reached its upper limit and PostgreSQL shut the database down to protect against wraparound.

   Root cause

   Capacity exhaustion

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Ignored early signalNo alert on limitSignal buried in noiseRetry stormMonitoring blind spot

   Detection

   The first exception was logged overnight but was buried by unrelated exceptions in the centralized error reporting. Mandrill engineers opened an investigation at 11:58 UTC and, while looking for the source of the rising load on shard4, found the wraparound at 12:56 UTC.

   Remediation

   • Put shard4 into standalone mode and started VACUUM at 14:16 UTC on 4 February
   • Stopped and restarted the vacuum with settings that made it run faster
   • Started a dump and restore of the database without the Search and Url tables while the vacuum ran
   • Replaced storage volumes on the Mandrill app servers whose disks were filling with queued jobs and logs
   • Moved the job-queue tables from shard4 to other live shards
   • Truncated the Search and Url tables on the locked database at 19:12 UTC on 5 February, after which the vacuum finished in about an hour
   • Started sending from the backlog at 21:36 UTC
   • Added alerting for XID wraparound by 22:40 UTC

   Lessons

   Mailchimp named the factors that slowed its response: knowledge of and access to Mandrill systems and monitoring rested with a few people, Mandrill logging needed a locally writable disk, logs of malformed API requests were hard to separate from real application errors, job health was hard to see, and the disk space problem made partial deployments for triage risky. What helped was trained incident commanders with clear roles (incident commanders in 3-hour rotations, scribes, technical liaisons and points of contact between teams), periodic summaries with a list of recent and open decisions, and taking time in a video meeting to reassess, which led to the truncation. Running in the cloud let the team expand storage quickly, and the team was lucky that the largest table holding up the vacuum held data it could delete.

   View case

By root cause1