When Indexes Become Write Killers: MySQL Order Table Write Performance Collapse Postmortem

Incident Snapshot

Three days before the outage, the team added three secondary indexes to the t_order table using ALGORITHM=INPLACE, LOCK=NONE:

ALTER TABLE t_order ADD INDEX idx_buyer_status (buyer_id, order_status);
ALTER TABLE t_order ADD INDEX idx_seller_created (seller_id, created_at);
ALTER TABLE t_order ADD INDEX idx_status_amount (order_status, pay_amount);

During the evening peak, write P99 latency rose from ~15 ms to ~2.3 s, write TPS fell from ~4800 to ~1100, and connection-pool waits exceeded 200. CPU and IOPS were not saturated, ruling out simple resource exhaustion.

Why the Schema Made Writes Expensive

The table uses an auto-increment BIGINT primary key, so clustered-index inserts are near-sequential. However, the new indexes introduce non-sequential key distributions: buyer_id, seller_id, order_status, and pay_amount values land on random B+Tree pages.

Every INSERT now maintains four secondary indexes instead of one.

Updates to order_status (payment, cancel, ship) require delete+insert in idx_buyer_status and idx_status_amount.

Updates to pay_amount touch idx_status_amount.

A DML-cost matrix shows exactly which indexes each operation touches:

DML                     PRIMARY/uk_order_no   idx_buyer_status   idx_seller_created   idx_status_amount
INSERT new order        Maintain              Maintain           Maintain             Maintain
Update non-index column Clustered row version Usually unchanged  Usually unchanged    Usually unchanged
Update order_status     Clustered row version Delete old + insert new Usually unchanged  Delete old + insert new
Update pay_amount       Clustered row version Usually unchanged  Usually unchanged    Delete old + insert new
DELETE                  Maintain              Maintain           Maintain             Maintain

Because order-status transitions are frequent, the two indexes containing order_status incur continuous write amplification.

Amplification Chain: From Page Splits to User Timeouts

The article illustrates a non-linear degradation loop:

[More secondary index work]
         |
         v
[Longer transaction time]
         |
         v
[More overlap and lock waits]
         |
         v
[Connection pool queue]
         |
         v
[Timeout retries add load]
         |
         +---------------> back to transaction contention

Page splits, Change Buffer merges, redo logging, dirty-page flushing, latches, and locks all participate; no single counter tells the whole story.

First 30 Minutes: Eliminate Obvious Suspects

4.1 Separate Queue Time from SQL Time

Instrument the application to report connection_acquire_time, sql_execution_time, transaction_total_time, plus timeout/retry counts and P50/P95/P99. If acquire time is high but SQL is normal, fix the pool/upstream concurrency first. If SQL time is high, enter MySQL engine diagnostics.

4.2 Check Locks Without Confusing Symptom for Cause

SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits ORDER BY wait_age_secs DESC;
SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

Determine whether blocking stems from long transactions, hot-row updates, batch jobs, or many previously non-conflicting transactions now overlapping because each statement runs slower.

4.3 Measure Redo, Flush, and Buffer-Pool Headroom

Collect per-minute deltas, not snapshots:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_os_log_written', 'Innodb_log_waits',
  'Innodb_buffer_pool_wait_free', 'Innodb_buffer_pool_reads',
  'Innodb_data_fsyncs', 'Innodb_data_pending_fsyncs');

Correlate with checkpoint age, dirty-page ratio, fsync P95/P99, storage latency, replication lag, and history list length. If redo rate and Innodb_log_waits jump together after the index change, the log subsystem may be the direct bottleneck.

Not Every Plausible Index Pays Its Way

5.1 Buyer Order List

Query pattern:

SELECT id, order_no, order_status, pay_amount, created_at
FROM t_order WHERE buyer_id = ? AND order_status = ?
ORDER BY created_at DESC LIMIT 20;

Existing (buyer_id, order_status) cannot satisfy the sort. Candidate: (buyer_id, order_status, created_at); if status filter is weak, also test (buyer_id, created_at). Decide with real-parameter plans and read/write benchmarks, not column-count intuition.

5.2 Seller Order Query

Pattern:

SELECT id, order_no, order_status, pay_amount, created_at
FROM t_order WHERE seller_id = ? AND created_at >= ? AND created_at < ?
ORDER BY created_at DESC LIMIT 100;

(seller_id, created_at)

is a reasonable candidate, but its value depends on peak QPS, result-set size, and SLO. Low-frequency admin queries may not justify permanent write overhead on a high-throughput table.

5.3 Site-Wide Analytics

Pattern:

SELECT order_status, COUNT(*), SUM(pay_amount)
FROM t_order WHERE created_at >= ? AND created_at < ?
GROUP BY order_status;

This OLAP-style scan does not naturally benefit from (order_status, pay_amount). Prefer summary tables, read replicas, or CDC-fed OLAP. If a heterogeneous index is added, define acceptable lag, fallback strategy, backlog alerts, and idempotent replay.

Measuring Read Benefit: Statistics First, Business Confirmation Second

sys.schema_index_statistics

lacks a read_count column; the article uses rows_selected and notes the statistics reset time and business-cycle coverage.

SELECT table_schema, table_name, index_name,
       rows_selected, select_latency,
       rows_inserted, insert_latency,
       rows_updated, update_latency,
       rows_deleted, delete_latency
FROM sys.schema_index_statistics
WHERE table_schema = 'order_db' AND table_name = 't_order'
ORDER BY rows_selected ASC, insert_latency DESC;

During the incident window, idx_status_amount showed rows_selected = 0 while inserts and status updates continued, making it a removal candidate. Before dropping, verify:

Performance Schema not recently reset or instance restarted.

Coverage includes peak, month-end, back-office, support, risk, and ad-hoc tasks.

Business owner explicitly confirms no dependency.

After making the index invisible, key SQL plans, scanned rows, and tail latency show no regression. sys.schema_unused_indexes only surfaces candidates; it must not drive automated deletion.

Page Splits and Change Buffer: Using Two Misunderstood Signals Correctly

7.1 Page Splits Are Instance-Level Trends, Not Index-Level Verdicts

SELECT NAME, SUBSYSTEM, COUNT, STATUS, COMMENT
FROM information_schema.innodb_metrics
WHERE NAME IN (
  'index_page_splits', 'index_page_reorg_attempts',
  'index_page_reorg_successful', 'index_page_merge_attempts',
  'index_page_merge_successful');

If metrics are disabled, enable with SET GLOBAL innodb_monitor_enable = 'module_index'; after assessing overhead. index_page_splits has no table or index name; it is only comparable in an A/B test where a single index changes under identical workload. Normalize by DML that actually modifies index keys:

index_page_splits delta / 10k index-key-affecting DML

Denominator includes INSERT, DELETE, and UPDATEs that change order_status, pay_amount, etc. Normalizing only by INSERT misleads.

7.2 Change Buffer Has Value and Hard Boundaries

SELECT NAME, COUNT, COMMENT
FROM information_schema.innodb_metrics
WHERE NAME LIKE 'ibuf%';

Change Buffer defers random I/O for non-unique secondary indexes not in the buffer pool, but merge still consumes resources later. Crucially, secondary indexes with descending columns do not support Change Buffer . Therefore, avoid defaulting to created_at DESC for ORDER BY created_at DESC; (seller_id, created_at) can usually be reverse-scanned. Only introduce physical DESC after a real plan proves it necessary, and include the write-cost impact in benchmarks.

Judge Read Benefit with EXPLAIN ANALYZE, Not Guesswork

Run on staging or a read replica with production-like parameters:

EXPLAIN ANALYZE
SELECT id, order_no, order_status, pay_amount, created_at
FROM t_order WHERE buyer_id = 10001 AND order_status = 1
ORDER BY created_at DESC LIMIT 20;

Record actual rows scanned/returned, actual time, loops, filesort presence, index usage, and estimated-vs-actual row divergence. EXPLAIN ANALYZE executes the statement; do not run it on large ranges during production peaks.

Simultaneously identify the true resource consumers:

SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_seconds,
       ROUND(AVG_TIMER_WAIT/1000000000, 3) AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'order_db' AND DIGEST_TEXT LIKE '%t_order%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

Index design unit is not a column but a "business-valuable query pattern."

A/B Benchmark: Change One Index at a Time

Prepare four configurations with identical data volume, buffer pool, disk, concurrency, and warm-up:

A = PRIMARY(id) + uk_order_no
B = A + idx_buyer_status
C = B + idx_seller_created
D = C + idx_status_amount

Workload mix derived from production digests and traces (example starting point):

INSERT new order          45%
UPDATE order_status       25%
SELECT by order_no        15%
SELECT buyer list         10%
SELECT seller range        5%

9.1 Minimal sysbench Lua Skeleton

Save as order_workload.lua; adapt table name, state machine, and data distribution. Ensure buyer_id and seller_id are not purely sequential.

function thread_init()
  con = sysbench.sql.driver():connect()
end

function event()
  local buyer_id = sysbench.rand.uniform(1, 1000000)
  local seller_id = sysbench.rand.uniform(1, 100000)
  local amount = sysbench.rand.uniform(1, 100000) / 100

  con:query("BEGIN")
  con:query(string.format([[
    INSERT INTO t_order
      (order_no, buyer_id, seller_id, order_status, pay_amount, created_at, updated_at)
    VALUES (UUID(), %d, %d, 0, %.2f, NOW(3), NOW(3))]], buyer_id, seller_id, amount))
  con:query(string.format([[
    UPDATE t_order SET order_status = 1 WHERE id = LAST_INSERT_ID()]]))
  con:query(string.format([[
    SELECT id, order_no, order_status, pay_amount
    FROM t_order WHERE buyer_id = %d AND order_status = 1
    ORDER BY created_at DESC LIMIT 20]], buyer_id))
  con:query("COMMIT")
end

The UPDATE order_status is intentionally placed in the same transaction to exercise the rewrite cost of both status indexes. Full benchmark should add order-no lookup and seller time-range queries at their production proportions.

9.2 Run Command and Sampling Rules

sysbench order_workload.lua \
  --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=bench \
  --mysql-password='***' --mysql-db=order_db --threads=64 --time=600 --report-interval=5 run

Each group records: TPS, P50/P95/P99, index-key-affecting DML count, index_page_splits delta, redo rate, Innodb_log_waits, buffer pool reads/wait_free, lock waits, Threads_running, and target Top SQL latency with scanned rows.

Decision criterion is business SLO, not a fixed percentage: if D vs. C significantly worsens write tail latency and engine pressure while idx_status_amount has no verifiable read gain, it qualifies for removal; if B or C materially improves high-frequency critical reads without exceeding the write budget, keep them.

Production Removal: Disable Read Path First, Then Release Write Cost

10.1 Invisible Index Is a Read-Path Safety Net, Not a Performance Test

ALTER TABLE t_order ALTER INDEX idx_status_amount INVISIBLE;

Invisible indexes are excluded from optimizer plans by default, but InnoDB still maintains them. The observation window validates "business queries are safe without it," not "deletion improves write throughput." Check for sessions with use_invisible_indexes=ON. Observe at least one full business cycle: peak, admin jobs, cron, month-end, support, risk. Examine key digest plans, scanned rows, and P95/P99—not just aggregate QPS.

10.2 MDL Pre-Check Before DROP

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
       LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'order_db' AND OBJECT_NAME = 't_order';

Confirm no long-running transactions in innodb_trx. MySQL 8.0 drops ordinary secondary indexes in-place with concurrent DML, but MDL is still required at start/end. Rehearse on same version and cloud provider.

SET SESSION lock_wait_timeout = 5;
ALTER TABLE t_order DROP INDEX idx_status_amount,
  ALGORITHM=INPLACE, LOCK=NONE;

10.3 Post-Deletion Verification and Rollback Plan

Compare write P95/P99, TPS, redo rate, lock waits, connection acquire time, Threads_running, page-split delta, and all critical read SLOs in a comparable business window. Keep the recreate statement but do not assume instant recovery:

CREATE INDEX idx_status_amount ON t_order(order_status, pay_amount);

Rebuilding requires its own DDL duration, resource, replication, and MDL risk assessment. Rollback playbook must specify trigger SLO, executor, and off-peak window.

Codify the Lesson into Index Governance

Every new index must be tied to a concrete Top SQL and the review record must include:

SQL, business owner, peak QPS, read SLO
EXPLAIN; EXPLAIN ANALYZE in safe env when needed
Scanned/returned rows and index size estimate
Business proportion of INSERT / DELETE / index-column UPDATE
Unique or DESC? Change Buffer impact
Expected read gain, allowed write P99 increase, observation window, rollback owner

After launch, track actual rows_selected, index size, write P99, redo rate, and page-split delta. Unused indexes follow "candidate list → owner confirmation → invisible → full-cycle observe → off-peak DROP → post-drop verify"; never auto-delete on "30 days unused."

The core transaction table should limit not the index count, but the write cost per business transaction that carries proven read value.

References

MySQL 8.0: InnoDB Change Buffer

MySQL 8.0: InnoDB INFORMATION_SCHEMA Metrics

MySQL 8.0: Online DDL Operations

MySQL 8.0: Invisible Indexes

MySQL 8.0: schema_index_statistics

MySQL 8.0: EXPLAIN ANALYZE