Cases2 cases

1 to 2 of 2

1. IL-0451 Setting indexes INVISIBLE on Aurora MySQL 3.10 spiked CPU and ReadIOPS on all three readers but not on the writer

   Daangn2026

   Symptoms

   Daangn's DB team set unused indexes of one table to INVISIBLE, one at a time, on an Aurora MySQL 3.10.x cluster with one writer and three readers.

   Cache hit ratio drop

   Trigger

   The DB team ran ALTER TABLE store_reviews ALTER INDEX ... INVISIBLE for unused indexes, one at a time, as the first step before dropping them.

   Root cause

   Software defect

   Component

   DatabaseSchema migration

   Contributing factor

   Unverified assumption

   Detection

   CloudWatch showed reader CPU and ReadIOPS spikes that lined up one to one with the three ALTER INDEX INVISIBLE statements.

   Action items

   • Upgrade to the next Aurora MySQL LTS release that contains the fix
   • Until then, run index DDL in low-traffic hours
   • Combine changes to several indexes into one ALTER statement so the reader buffer pools are invalidated once instead of N times

   Lessons

   On Aurora MySQL from 3.08.0 up to before 3.14, making an index INVISIBLE is not free: the writer sees nothing, but every reader loses its cached pages of the whole table, and the larger and busier the table, the bigger the CPU and ReadIOPS spike while the cache warms again. Until the fixed version is in use, index DDL should be treated as an operation that empties the reader buffer pools. When looking for unused indexes, the usage statistics must be read on every node, because an index unused on the writer may be used heavily on one reader. Querying information_schema.INNODB_BUFFER_PAGE scans the whole buffer pool and takes seconds and adds load on large instances, so it is not a query to run routinely in production.

   View case
2. IL-0385 GitHub MySQL read replicas hit a semaphore deadlock during a schema migration's table rename and kept crash-recovering

   GitHub2021

   Symptoms

   On 27 November 2021 at 20:40 UTC, a significant part of GitHub's MySQL read replicas entered a semaphore deadlock while a schema migration on a large MySQL table ran its final step, the rename that moves the updated table into place. Core GitHub services, including GitHub Actions, API Requests, Codespaces, Git Operations, Issues, GitHub Packages, GitHub Pages, Pull Requests and Webhooks, became degraded.

   Lock contention or deadlockDatabase overload

   Trigger

   The final rename step of a schema migration on a large MySQL table. Schema migrations are a common task at GitHub and often take weeks to complete.

   Root cause

   Software defect

   Component

   DatabaseSchema migration

   Contributing factor

   No headroomNo staged rollout

   Remediation

   • Promoted every healthy internal replica into the production path to add read capacity
   • Removed production traffic from the broken replicas until each had processed the table rename
   • Moved the recovered replicas back into production to restore capacity

   Action items

   • Continue functional partitioning so migrations can run in canary mode on a single shard
   • Update internal procedures to increase how much each cluster is over-provisioned
   • Pause schema migrations until it is known how to safeguard against this failure
   • Keep investigating the specific failure scenario
   • Test the migration tooling and classify ways to improve it for this kind of scenario

   View case

By root cause1