Cases11 cases

8 to 11 of 11

1. IL-0326 A retention clean-up job filled the disk of MailPace's managed Postgres, which stayed offline for just over two hours

   MailPace2023

   Symptoms

   At 13:36 BST on Friday August 11th 2023, minutes after MailPace reran its data clean-up job with more memory, notifications arrived that all database transactions were failing across multiple services. The managed Postgres database had gone offline, did not come back when restarted, and reported that it was out of space.

   Node disk fullService outageWrites rejected

   Trigger

   The daily retention clean-up job, which had been failing with killed, was rerun with its memory raised from 1gb to 8gb, and its large destroy_all deletions filled the database disk.

   Root cause

   Capacity exhaustion

   Component

   Database

   Contributing factor

   Inefficient queryMonitoring blind spotNo headroom

   Detection

   Notifications that all database transactions were failing across multiple services arrived as soon as the outage began; the provider was automatically notified at 99% storage. MailPace's two status pages did not detect the outage.

   Remediation

   • Contacted the managed database provider's support
   • Notified users on Twitter and Mastodon
   • Prepared an offsite backup from six hours earlier on a new database instance as a fallback
   • The provider migrated the database to a larger instance
   • Removed the old data in production by hand, day by day, with an updated query

   Action items

   • Delete old data in batches of 50 records with in_batches in the clean-up job
   • Run the clean-up job four times a day instead of once
   • Stay on a much larger database instance with room to grow
   • Add cron monitoring to all cron jobs
   • Point the status pages at a /health endpoint that checks database access and returns a 500 error on failure
   • Investigate a hot-replica database for automated failover

   Lessons

   Several causes came together: a clean-up job that failed silently, a naive deletion that grew huge once data had piled up, a database plan without headroom for growth, and status pages that missed the outage, presumably because https://app.mailpace.com still answered 200 while the database was down. MailPace also advises that critical services such as email sending be set up with more than one provider.

   View case
2. IL-0302 Peak load on GitHub's mysql1 MySQL cluster maxed out its database proxy's connections and stopped writes in March 2022

   GitHub2022

   Symptoms

   On March 16, 2022 at 14:09 UTC, write operations across GitHub stopped working, including git operations, webhooks, pull requests, API requests, issues, GitHub Packages, GitHub Codespaces, GitHub Actions and GitHub Pages. Under peak-hour load, the database proxy in front of the shared mysql1 MySQL database had reached its maximum number of connections.

   Writes rejectedDatabase overloadHigh error rateFailover failure

   Trigger

   Increased load on mysql1 during peak hours, combined with poor query performance in specific circumstances, pushed the database proxy to its maximum number of connections.

   Root cause

   Capacity exhaustion

   Component

   DatabaseDatabase proxy and connection pooler

   Contributing factor

   Inefficient queryChange during peak hoursHarmful manual actionExcessive blast radius

   Remediation

   • Failed over mysql1 to a healthy replica on March 16
   • Reset the application connections to the new primary on March 17
   • Added an index that fixed the main query performance problem
   • Performed a primary failover on March 22
   • Performed a primary failover on March 23
   • Throttled webhook traffic to reduce load

   Action items

   • Keep throttling webhook traffic at peak load times
   • Audit the load patterns of mysql1 during peak hours and make performance fixes based on the audits
   • Move traffic to other databases to reduce load and speed up failover
   • Review change management, particularly monitoring and changes during high load in production
   • Shard the databases and scale up hardware

   View case
3. IL-0212 Many Slack users could not connect because a Vitess keyspace for channel membership was overloaded by cache-miss queries

   Slack2022

   Symptoms

   Just after 6 a.m. Pacific Time on 22 February 2022, user tickets about problems connecting to Slack, problems reported by internal users, and pages and alerts to several engineering teams all arrived at almost the same moment. Many users could not connect: the Slack client could not finish booting, the step that fetches channel lists, preferences and recent conversations, and showed an error page.

   Database overloadHigh error rateHigh latency

   Trigger

   A percentage-based rollout step upgraded the Consul agent on 25% of the fleet, followed by a slow sequential restart of the agents. Each restart on a Memcached node removed it from the Consul service catalog, so Mcrib, the cache-tier control plane, replaced it with an empty spare or flushed it when it rejoined, and the cache hit rate dropped as Slack reached its daily peak traffic.

   Root cause

   Capacity exhaustion

   Component

   DatabaseIn-memory cacheService discovery

   Contributing factor

   Dependence on a warm cacheInefficient queryAutomation amplificationRetry stormChange during peak hours

   Detection

   User tickets about connection problems, reports from internal users and pages and alerts to several engineering teams arrived almost at the same time, just after 6 a.m. Pacific Time.

   Remediation

   • Paused the Consul agent restart operation
   • Throttled client boot requests to reduce load on the database tier
   • Cut the client boot rate limit again after a too-large increase and then raised it in smaller steps
   • Changed the GDM membership query to read from Vitess only the data missing from Memcached
   • Let the query read from replicas as well as Vitess primaries
   • Raised the client boot rate limit slowly back to normal traffic levels as the caches refilled

   Action items

   • Change the process for Consul rollouts so upgrades do not trigger this failure mode again
   • Change the Mcrib control loop so a brief network outage or partition of cache nodes does not set off the same cascading failure
   • Make the GDM membership query read from a table sharded by channel
   • Check the other cache-fronted database queries for high-volume scatter queries
   • Explore longer-term ways to make the caching tier more resilient

   Lessons

   Slack describes performance bottlenecks, a cascading failure, dependence on a warm cache and a change as the trigger. Once the system had passed the tipping point, lower cache hit rates caused high database load that in turn kept the cache from filling, and a system in that state stays there until an outside intervention, a large cut in load or an increase in capacity, changes it. Mcrib contributed not by being wrong but by being faster than the old Consul lock scheme at replacing Memcached nodes, which increased churn in the cache tier. Client retries still added load despite backoff and jitter. A brief network partition of the cache nodes could set off the same failure, and the interaction between Consul and the caching architecture is now more widely understood.

   View case
4. 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

By root cause3