Cases10 cases

6 to 10 of 10

1. IL-0447 An every RDS for MySQL instance slowed badly when its gp2 EBS BurstBalance ran out and IOPS fell to the baseline

   every2024

   Symptoms

   The BurstBalance of an RDS instance began to fall rapidly and finally ran out, and the database's performance dropped sharply. The instance ran MySQL 5.7.44 on db.t3.medium with general purpose SSD (gp2) storage of 30GiB.

   Database overloadThroughput degradation

   Trigger

   A complex SELECT query began writing large amounts of data to temporary tables, which raised WriteIOPS and used up the burst credits.

   Root cause

   Capacity exhaustion

   Component

   DatabaseCloud block storage

   Contributing factor

   Inefficient query

   Action items

   • Consider tuning the query by reviewing indexes and rewriting it
   • Consider moving the storage from gp2 to gp3 or increasing its size to raise the baseline IOPS
   • Consider adjusting tmp_table_size and max_heap_table_size
   • Consider moving to a more powerful instance type
   • Consider monitoring BurstBalance with CloudWatch alerts and adding external monitoring such as Pingdom

   Lessons

   A complex query can have a large effect on I/O, so database design and queries need continuous improvement, and query performance needs regular monitoring.

   View case
2. IL-0567 Aurora MySQL behind Cookie Run: OvenBreak hit 100% CPU after a sales-ranking feature launch, and players could not play

   Devsisters2023

   Symptoms

   After further bug-fix deployments and reconnecting users on the launch day of the package sales ranking feature, a database alarm fired and the CPU of the Cookie Run: OvenBreak AWS Aurora MySQL database was at 100%. Database connections then failed, and players could not log in to or play the game.

   Database overloadService outage

   Trigger

   The package sales ranking feature was deployed, and further bug-fix deployments followed that reconnected users; each server update left more servers with empty caches that ran the ranking query against the database.

   Root cause

   Faulty deployment

   Component

   Database

   Contributing factor

   Inefficient queryDependence on a warm cacheUnrepresentative staging

   Detection

   A database alarm fired, and the CPU graph of the Aurora MySQL database showed 100%.

   Remediation

   • Put the game servers into maintenance
   • Added a switch for the package sales ranking feature and turned the feature off, which restored the service
   • Rewrote the ranking query to group products that share a sale start time and to drop the current-time condition
   • Moved the ranking query to a scheduler server that runs it periodically and stores one shared cached result
   • Turned the package sales ranking feature back on after both fixes

   Lessons

   A heavy query combined with per-server caches in a distributed server setup caused a problem nobody expected, so the points likely to cause trouble need to be found in advance. During an outage, restoring the service comes before finding the cause, and a switch that turns a feature off, built in ahead of time, is a safety device that helps run a stable service. The online ALTER itself went well with Percona Toolkit and was used again for later schema changes.

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