Incident Library

IL-0451 Daangn · 2026-10-06 (published)databaseaws

Setting indexes INVISIBLE on Aurora MySQL 3.10 spiked CPU and ReadIOPS on all three readers but not on the writer

In Aurora the instances are separate from the storage: the writer sends only redo logs to the storage layer, which builds the pages, and each reader has its own buffer pool over the same storage, updating cached pages from the redo it receives. One Daangn service ran an Aurora MySQL 3.10.x cluster with one writer and three readers, and one table had more than 20 indexes. To cut write cost and planning cost, the team picked six indexes whose count_read in performance_schema.table_io_waits_summary_by_index_usage had not grown on any of the four nodes over two weekly snapshots, and planned to make them INVISIBLE, watch for a few days and then drop them. Each INVISIBLE statement made all three readers mark almost every cached page of the table as stale, so the next reads went to storage and reader CPU and ReadIOPS jumped while the buffer pool refilled.

The IS_STALE column, added in MySQL 8.0.24, shows pages whose tablespace version no longer matches; community MySQL uses this since 8.0.23 to discard pages of dropped or truncated tablespaces lazily. The team sent the seven-case reproduction to AWS technical support. The Aurora MySQL service team confirmed that index DDL triggered the invalidation of the table's buffer pool pages on readers, that this is unnecessary because index operations are metadata-only and change neither the tablespace nor data pages, and that the behaviour came in with Aurora MySQL 3.08.0 as part of a feature improvement and was applied more widely than intended to DDL such as INDEX INVISIBLE, CREATE INDEX and DROP INDEX. AWS identified it as an engine defect, planned a fix that makes readers refresh only the table definition cache for metadata-only DDL, and recommended running index DDL in low-traffic hours until then.

AWS later said the fix would be in Aurora MySQL 3.14. Versions from 3.08.0 up to before 3.14 are affected, and there is no plan to backport the fix to earlier versions. Because Daangn runs the LTS version 3.10.x and cannot move to 3.14 right away, the team is waiting for the next LTS release that contains the fix.

Severity
–
Scope
All three readers of one service's Aurora MySQL cluster: each index DDL invalidated the table's cached pages, raising ReadIOPS from a few hundred to 3.1K and CPU from 30% to 50-60%
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

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.

In CloudWatch, each of the three ALTER INDEX ... INVISIBLE statements matched one spike on all three readers at once: ReadIOPS rose from a few hundred to 3.1K, CPU from 30% to 50-60%, and BufferCacheHitRatio dropped for a moment. On the writer, ReadIOPS, CPU and BufferCacheHitRatio stayed as usual.

The statements were of the form ALTER TABLE store_reviews ALTER INDEX idx_store_id_score_created_at INVISIBLE. The team had expected no load, because making an index invisible only changes the data dictionary and leaves data and index pages untouched, so a reader's cached pages of the table should stay valid; only DDL that rebuilds the table into a new tablespace should empty them.

The falling hit ratio and rising ReadIOPS suggested that pages were disappearing from the reader buffer pool. Counting the table's pages per index in information_schema.INNODB_BUFFER_PAGE on a reader just before and just after the DDL showed no change (511866 pages before, 511847 after, about 7998 MB). The IS_STALE column of the same view did show it: after ALTER TABLE store_reviews ALTER INDEX idx_user_id_created_at_visibility INVISIBLE, 245625 of 252656 PRIMARY pages (97.2%), 98.2% of the changed index and 94.5% of another index were stale, against 0 before. The writer had no stale pages. The invalidation covered the whole table, data pages and every index, not only the index that changed.

Reproducing seven kinds of DDL on a test table order_logs showed the same pattern. ADD COLUMN with ALGORITHM=INSTANT and ALTER COLUMN ... SET DEFAULT with ALGORITHM=INPLACE left the reader buffer pool untouched. MODIFY COLUMN ... NOT NULL (INPLACE with rebuild) and a column type change with ALGORITHM=COPY moved the table to a new tablespace (98989 to 98990, 98423 to 98424), which empties the cache as expected. CREATE INDEX, DROP INDEX and RENAME INDEX kept the same tablespace but made 99.8% to 100% of the table's cached pages on the reader stale.

Symptom Cache hit ratio drop

Root cause

Root cause Software defect

Component DatabaseSchema migration
Contributing factor Unverified assumption
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.

Detection

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

Remediation

–

Action items

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.

Sources

References

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, Schema migration among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.