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-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 cause2