MySQL Performance Optimization: Diagnosing Gradual Query Slowdown with EXPLAIN, Composite Indexes & Online DDL Safety

Problem Background: Three Types of "Getting Slower"

The article identifies three common patterns behind gradually degrading query performance:

Data Growth : A query optimized at launch degrades from 200ms to 2s+ a year later with no schema changes.

Usage Drift : New reporting queries run 30s each, 40 times/hour, discovered only during inspection.

Resource Contention : Buffer pool hit rate drops due to new SQL, slowing the entire instance while teams blame each other.

All three share consistent symptoms but layered root causes . Mixing them leads to wasted effort (e.g., tuning parameters when the real fix is a missing index).

Goals

Decompose "getting slower" into three measurable categories: execution slow, wait slow, resource slow — using evidence, not guesswork.

Complete a concrete SQL transformation loop: locate → root cause → fix → verify → rollback.

Institutionalize slow-log and digest alerts to avoid repeating the same long investigation.

Document both "temporarily fixed" and "regression-tested" in delivery.

Environment & Version Assumptions

Version: MySQL 8.4.13 LTS (InnoDB, utf8mb4 + utf8mb4_0900_ai_ci)
Deployment: Self-hosted 1 primary (write) + 1 replica (read, some queries + offset)
Instance: 16C / 64G / NVMe, buffer pool 48G
Database: bizdb, core table orders (~62M rows)
Change method: Privileged account, change window, two-person execution

Orders table schema (simplified):

CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `user_id` bigint unsigned NOT NULL COMMENT 'User ID',
  `status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'Order status',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Amount',
  `merchant_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT 'Merchant ID',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order time',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_merchant_created` (`merchant_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Pre-Checks: Permissions, Parameter Snapshots, Resource Baseline

4.1 Confirm Permissions

Run SHOW GRANTS FOR CURRENT_USER(); to verify you have PROCESS, SYSTEM_VARIABLES_ADMIN (or SUPER), SLOW_QUERY_LOG_ADMIN, CONNECTION_ADMIN (for KILL), and ALTER / INDEX / CREATE on the database.

4.2 Snapshot Key Parameters

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
  'innodb_buffer_pool_size','innodb_redo_log_capacity',
  'long_query_time','slow_query_log','log_timestamps',
  'tmp_table_size','max_heap_table_size','lock_wait_timeout'
);

Output showed slow_query_log=OFF, long_query_time=10s (effectively disabled), log_timestamps=UTC (8-hour offset from local time, changed to SYSTEM).

4.3 Server Resource Baseline

uptime; free -g; df -h /data; iostat -dx 1 5

Results: load average 6.42 on 16 cores (high), memory 59G/63G used (buffer pool 48G resident), NVMe I/O healthy (r_await 0.3ms, w_await 1.8ms, %util 45%). Conclusion: I/O not saturated, memory pressure normal, high load driven by user-space CPU — problem is CPU-bound slow queries .

Initial Diagnosis: Three Types of Slow

Execution slow: SQL scans/joins/sorts amplify          Evidence: EXPLAIN, rows_examined
Wait slow:      Query not bad but queued (locks, etc.) Evidence: PROCESSLIST state, MDL/InnoDB waits
Resource slow:  Computation not slow but mem/IO/CPU    Evidence: hit rate, tmp table to disk, OS wait

5.1 PROCESSLIST Snapshot

SHOW FULL PROCESSLIST;

Output showed multiple COUNT(*) queries stuck in Sending data for hundreds of seconds (execution slow), a 1802-second reporting query (long query), and no Waiting for table metadata lock signals (so not wait-slow yet).

5.2 Three-Type Slow Reference Table

Type        Typical Evidence                    Typical Root Cause              Fix Direction
Execution   No lock wait + large rows + high CPU  Missing index/stats/SQL writing Index, SQL, stats
Wait        Processlist stuck in lock states      MDL/row lock/FK cascade         Kill or reduce density, rewrite SQL
Resource    Buffer hit rate drop / disk IO / CPU  Temp table to disk / swap       Resource config & capacity

Step 1: Slow Log & Digest Metrics

6.1 Slow Log Configuration

Method A (runtime, dynamic):

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.3;
SET GLOBAL log_timestamps = SYSTEM;

Method B (persistent in /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf):

[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
long_query_time = 0.3
log_timestamps = SYSTEM

Verify with SHOW VARIABLES LIKE 'slow_query%'; and SHOW VARIABLES LIKE 'long_query_time';.

Log rotation via logrotate (size 512M, rotate 14, compress, postrotate FLUSH SLOW LOGS). File permissions mysql:mysql 0640. Use mysql_config_editor for passwordless admin access.

Warnings : Do not enable log_queries_not_using_indexes without log_throttle_queries_not_using_indexes (floods log). Keep log file on separate disk or ensure rotation. After investigation, raise long_query_time back to baseline.

6.2 Reading a Slow Log Entry

# Time: 2026-10-20T19:22:14.100123+08:00
# User@Host: rpt_user[rpt_user] @ app-3 [10.2.3.3] Id: 220
# Query_time: 2.561 Lock_time: 0.000 Rows_sent: 1 Rows_examined: 62817821
SELECT COUNT(*) FROM orders WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';

Key readings: Query_time 2.561s vs Lock_time 0.000 → pure execution slow. Rows_examined: 62.8M vs Rows_sent: 1 → 62.8M:1 read amplification, classic missing-index or plan regression.

6.3 Digest Aggregation (performance_schema)

SELECT SCHEMA_NAME, DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
  ROUND(AVG_TIMER_WAIT/1e9, 1) AS avg_ms,
  ROUND(MAX_TIMER_WAIT/1e9, 0) AS max_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'bizdb' AND DIGEST_TEXT LIKE '%orders%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

Results: high-frequency COUNT(*) (1254 calls, 489s total, 390ms avg) and long-tail reporting SUM(amount) (30 calls, 618s total, 20.6s avg). Fix high-frequency first (larger business impact).

Alternative: pt-query-digest (Percona Toolkit) parses slow log file directly.

Reproduction & Evidence Chain: EXPLAIN

7.1 Target Slow SQL

SELECT COUNT(*), SUM(amount)
FROM orders
WHERE user_id = 120071233001
  AND status = 2
  AND created_at >= '2026-09-01';

7.2 EXPLAIN (Plan Only)

EXPLAIN SELECT ...

Output:

+----+-------------+--------+-------+----------------------+-----------+---------+------+----------+-------------+
| id | select_type | table  | type  | key                  | key_len   | ref     | rows | filtered | Extra       |
+----+-------------+--------+-------+----------------------+-----------+---------+------+----------+-------------+
| 1  | SIMPLE      | orders | ALL   | NULL                 | NULL      | NULL    | 62817821 | 0.00 | Using where |
+----+-------------+--------+-------+----------------------+-----------+---------+------+----------+-------------+

Analysis: type=ALL, key=NULL → full table scan, no index used . Extra=Using where (no Using index condition / Using index ) → secondary index not participating, full table read required. Column types match ( user_id is bigint), so no implicit conversion issue.

7.3 EXPLAIN ANALYZE (Actual Execution)

EXPLAIN ANALYZE SELECT ...

Output:

-> Aggregate: count(1), sum(orders.amount) (cost=6.5e6 rows=1) (actual time=2668..2668 rows=1 loops=1)
    -> Filter: ((orders.status = 2) and (orders.created_at >= TIMESTAMP'2026-09-01'))
         (actual time=0.25..2661 rows=42 loops=1)
         -> Table scan on orders (cost=5.9e6 rows=6.28e7) (actual time=0.06..2198 rows=6.28e7 loops=1)

Full scan of 62.8M rows took ~2.2s, filtered down to 42 rows — 1.49M:1 read amplification . All 2.6s spent reading rows that were discarded.

7.4 Optimizer Trace: Why the Index Was Rejected

SET optimizer_trace = "enabled=on";
SELECT ...;
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = "enabled=off";

Trace excerpt:

{
  "considered_execution_plans": [
    {
      "table": "`orders`",
      "best_access_path": {
        "considered_access_paths": [
          { "access_type": "ref", "index": "idx_user_id", "rows": 217400, "cost": 9.2e5, "chosen": false },
          { "access_type": "scan", "rows": 62817821, "cost": 7.6e5, "chosen": true }
        ]
      }
    }
  ]
}

idx_user_id

path had chosen:false because estimated rows (217K) and cost (9.2e5) exceeded full scan cost (7.6e5). This is direct evidence of plan regression due to data growth — the optimizer's cost model flipped as the per-user row count crossed a threshold.

7.5 EXPLAIN FORMAT=TREE (Quick Structure View)

EXPLAIN FORMAT=TREE SELECT ...

Shows same tree without actual timings; useful for quick structural check before deciding to run ANALYZE.

Fix Loop: Add Composite Index, One Shot

8.1 Root Cause with Evidence

Orders table grew to 62M rows; query filters on user_id + status + created_at.

Existing index idx_user_id(user_id) is single-column; optimizer's persistent statistics estimated the per-user index range too large (cost > full scan), so it chose type=ALL.

Mechanism of "getting slower" : per-user data grows continuously → single-column index candidate rows grow → crosses cost threshold → optimizer abandons index for full scan → query slows by an order of magnitude and continues slowing as table grows. The slope comes from data; the fixable point is index structure.

8.2 Index Design (Column Order Rationale)

ALTER TABLE orders
ADD INDEX idx_user_status_created (user_id, status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;

Order rationale: equality columns first ( user_id, status) to lock prefix; range column ( created_at >=) last to leverage index ordering for prefix scan. Wrong order (e.g., created_at in middle) turns later equalities into range-internal filters, retaining read amplification.

8.3 Online DDL Three Safety Barriers

Impact & Risk : ALGORITHM=INPLACE allows concurrent reads/writes, but requires brief MDL-X (exclusive metadata lock) at start. If a long transaction/query holds MDL-S on the table, the ALTER queues behind it, and all subsequent connections queue behind the ALTER — a single "add index" becomes an instant outage.

Barrier 1 — Check long transactions beforehand:

SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec, trx_state, trx_rows_locked
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10
ORDER BY trx_started;

Example output showed a 1841-second running transaction (trx_id 4211). Kill only after verifying session ownership (Id, User, Host) and notifying business; record in ticket.

Barrier 2 — Set session lock_wait_timeout for the ALTER: SET SESSION lock_wait_timeout = 30; If MDL not acquired in 30s, ALTER aborts instead of blocking everyone indefinitely.

Barrier 3 — Rehearse on test/restored replica: Restore production backup to test, run same ALTER, then verify plan with EXPLAIN/EXPLAIN ANALYZE. Never hand-run ALTER on production replica (causes primary-replica schema drift).

8.4 Rehearsal Verification (on test instance after DDL)

EXPLAIN SELECT COUNT(*) FROM orders WHERE user_id = 120071233001 AND status = 2 AND created_at >= '2026-09-01';

Output:

+----+-------------+--------+-------+------------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | key                    | key_len          | ref     | rows | Extra                 |
+----+-------------+--------+-------+------------------------+------------------+---------+------+------+-----------------------+
| 1  | SIMPLE      | orders | range | idx_user_status_created| 9                | NULL    | 42   | Using index condition |
+----+-------------+--------+-------+------------------------+------------------+---------+------+------+-----------------------+

type=range

, rows=42 (matches actual filtered rows), key_len=9 = 8 bytes (bigint unsigned) + 1 byte (tinyint unsigned), both NOT NULL so no extra null byte. Plan correct.

8.5 Production Execution (in change window)

SET SESSION lock_wait_timeout = 30;
ALTER TABLE orders
ADD INDEX idx_user_status_created (user_id, status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;

Monitor with SHOW PROCESSLIST; watching for Waiting for table metadata lock pile-up. Confirm with

SHOW INDEX FROM orders WHERE Key_name = 'idx_user_status_created';

and ANALYZE TABLE orders; (8.0+ persists statistics, optimizer sees new cardinality immediately).

8.6 Verification: Same SQL, New Plan + Real Execution

EXPLAIN ANALYZE SELECT ...

Output:

-> Aggregate: count(1), sum(orders.amount) (cost=0.099 rows=1) (actual time=0.19..0.19 rows=1 loops=1)
    -> Index range scan on orders using idx_user_status_created
         (actual time=0.11..0.13 rows=42 loops=1)

Before/after comparison:

Before : Rows Read = 62,817,821; Actual Time ≈ 2.66 s

After : Rows Read = 42; Actual Time ≈ 0.19 ms

Business-side verification: microservice RT curve dropped to milliseconds, replica caught up (Seconds_Behind_Source = 0).

8.7 Post-Deploy: 24-Hour Observation & Hardening

Observation actions (first 24h):

-- 1. Slow log: digest avg_ms for COUNT(*) template should drop an order of magnitude
SELECT ROUND(AVG_TIMER_WAIT/1e9,1) AS avg_ms, COUNT_STAR
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'bizdb' AND DIGEST_TEXT LIKE '%COUNT(*)%';

-- 2. Resource fallback: CPU, threads_running, IOPS back to baseline
SHOW GLOBAL STATUS LIKE 'Threads_running';

-- 3. Replication: replica replay caught up (index build on replica causes temporary lag)
SHOW REPLICA STATUS\G

Hardening actions:

Archive both EXPLAIN/EXPLAIN ANALYZE, slow log line, trace output in ticket — becomes onboarding reference for "why this index exists".

Runbook update: orders must retain idx_user_status_created; dropping it is an incident. Future composite indexes reviewed with same three-part pattern (equality → equality → range).

Regression metric: track Rows_examined / Rows_sent ratio as slow-SQL health score, long-term archival.

Parameter Layer: Config Examples & Effect Mechanisms

9.1 Buffer Pool Hit Rate (Resource-Slow Evidence)

SELECT 1 - (
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
) / (
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
) AS miss_ratio;

Result: 0.0007 (0.07%, healthy; >1% warrants investigation).

Online resize (8.0+):

SET GLOBAL innodb_buffer_pool_size = 34359738368; -- 32G example
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_resize_status';

Background chunked resize; completion shows completed resizing buffer pool. Rule of thumb: dedicated DB server 50-70% of physical RAM (32-45G for 64G host). Current 48G (75%) slightly high; adjust based on long-term hit-rate trend.

9.2 Redo Log Capacity (8.4 Syntax)

SET GLOBAL innodb_redo_log_capacity = 1073741824; -- 1G
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

Old innodb_log_file_size removed in 8.4. Sizing: cover a write burst window; start 1-2G, cap by disk budget. Monitor disk usage and crash-recovery replay time after change.

9.3 Internal Temporary Tables (TempTable Engine, 8.0+)

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW VARIABLES LIKE 'temptable%';

Output: Created_tmp_tables 1,204,657, Created_tmp_disk_tables 18,320 (1.5% spill, acceptable; >10% tune). temptable_max_ram=1G default, temptable_max_mmap=0 (no mmap spill). Increase if spill high:

SET PERSIST temptable_max_ram = 4294967296; -- 4G (constrained by available RAM)

SET PERSIST

writes to mysqld-auto.cnf, survives restart; requires SYSTEM_VARIABLES_ADMIN. Note : tmp_table_size / max_heap_table_size control explicit ENGINE=MEMORY and MEMORY-engine internal temp tables; 8.0+ default TempTable engine uses temptable_max_ram / temptable_max_mmap. Don't mix the two parameter sets. On RDS, SET PERSIST often blocked; use console parameter groups.

9.4 lock_wait_timeout — DDL Queue Behavior

SET GLOBAL lock_wait_timeout = 1800; -- 30 min global cap (default 1 year = infinite queue)

Session-level override to 30s used during ALTER (Barrier 2).

9.5 Binlog Disk Usage — Another "Getting Slower" Source

SHOW BINARY LOGS;
SELECT @@binlog_expire_logs_seconds;
SET PERSIST binlog_expire_logs_seconds = 1209600; -- 14 days example

Example showed 92 binlog files ≈ 92G. Manual PURGE BINARY LOGS BEFORE '...'; is high-risk: must confirm all replicas have fetched past that position or replication breaks. Prefer auto-expiry via binlog_expire_logs_seconds. Track total binlog size trend and replica lag spikes as capacity baseline.

Case Study: Real "Wait Slow" Mini-Loop (MDL Incident)

10.1 Phenomenon

2 AM alert: API RT spike + connection pool exhaustion. Dozens of sessions in Waiting for table metadata lock.

10.2 Identify Blocker/Waiter

SELECT * FROM sys.schema_table_lock_waits\G

Output:

Waiting PID 311
Waiting User app_rw
Waiting Lock Type MDL_SHARED_READ
Blocking PID 220
Blocking User rpt_user
Blocking Lock Type MDL_SHARED_READ  -- long query holds shared lock
Blocking Query: SELECT user_id, SUM(amount) ...

Blocker PID 220 = the 1800s reporting query from earlier. Root cause: long query + DDL + queue propagation chain.

10.3 Root Cause

Reporting session 220 held MDL-S on orders for 1800s. Subsequent DDL ( ALTER TABLE orders ADD INDEX ...) needed MDL-X, queued behind it. Because lock_wait_timeout default is 1 year, DDL never timed out; every new connection (read) queued behind DDL → connection pool exhaustion.

10.4 Fix & Verify

KILL 220; -- high risk: verify ownership (rpt_user@reporting-host-4) first
SHOW FULL PROCESSLIST; -- ALTER starts; minutes later (INPLACE build index)
SELECT COUNT(*) FROM information_schema.processlist WHERE State LIKE 'Waiting for table metadata lock'; -- expect zero

10.5 Rollback & Retrospective

Rollback plan: KILL the ALTER connection during INPLACE phase; MySQL auto-cleans temporary index, no data loss (cleanup takes time on huge tables, assess first).

Retrospective: global lock_wait_timeout → 600s (fail DDL fast rather than queue business); nightly long-running reporting queries added to whitelist and communicated to DBA before changes.

10.6 Another Mini-Loop: Row Lock Wait (Write Contention)

Symptom: "single commit slow", sessions in updating, no MDL queue.

Check: SELECT * FROM sys.innodb_lock_waits\G Output showed blocking PID 98 running large UPDATE orders SET status=... WHERE id IN (...) (hundreds of IDs) for 108s; waiters queued behind it.

Fix: KILL 98 (verify first); dev splits large UPDATE into batches of 100-500 rows with commits; keep innodb_lock_wait_timeout at default 50s with alerting.

Verify: sys.innodb_lock_waits empty, Innodb_row_lock_waits growth back to baseline.

Retrospective: alert on transaction runtime >60s; add batch-split rule to code review checklist.

Monitoring & Alerting: Turn Optimization into Maintainable System

11.1 Key Metrics (mysqld_exporter / PromQL)

# Slow query rate: should trend to 0 after remediation
rate(mysql_global_status_slow_queries[5m]) > 0.1

# Burst concurrency
mysql_global_status_threads_running{instance="db-primary"} > 64

# Connection saturation
mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.85

# Row lock waits
rate(mysql_global_status_innodb_row_lock_waits[5m]) > 0.5

# Buffer pool miss ratio rising
(1 - (mysql_global_status_innodb_buffer_pool_reads / mysql_global_status_innodb_buffer_pool_read_requests)) > 0.001

Thresholds must be calibrated to your own baseline : observe 1-2 weeks, define what's abnormal for your workload. Metric names vary by exporter version.

11.2 Slow Log & Digest Alerts

1. Slow log keyword alert: tail slow.log, grep for "Rows_examined: [0-9]{7,}" (≥1M rows scanned) → alert.
2. Digest alert: performance_schema shows new template with avg_ms > 500 → push template text to IM owner.
3. Monthly digest: Top 20 slow SQLs (by total time) to dev team; track "rows_examined / rows_sent" ratio long-term.

11.3 Daily Four-Check Script (cron, first thing morning)

-- 1) Replication health: lag & channel errors
SHOW REPLICA STATUS\G  -- Seconds_Behind_Source, Last_IO_Errno, Last_SQL_Errno

-- 2) Slow query velocity (counter diff, not log parsing)
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 3) Long transactions: >60s active (early signal for blocking faults)
SELECT trx_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec, trx_rows_locked
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

-- 4) Connection watermark
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

Any metric crossing your own baseline (not generic docs) gets handled same day. Script output to on-call channel — turns "remember to check" into "someone nudges if you don't".

Risk & Rollback Handbook

New index :

ALTER TABLE orders DROP INDEX idx_user_status_created, ALGORITHM=INPLACE, LOCK=NONE;

— online, immediate; optimizer reverts to old plan, behavior reverts.

Parameters (buffer pool etc.) : SET GLOBAL innodb_buffer_pool_size = <original>; — dynamic, gradual; restore from 4.2 snapshot.

SET PERSIST params : RESET PERSIST tmp_table_size; (8.0+) — removes persisted entry; only affects restart value; current runtime needs SET GLOBAL too.

Slow log :

SET GLOBAL slow_query_log = OFF; SET GLOBAL long_query_time = <original>;

— dynamic.

KILL big query : No rollback action; business logs ticket; check business retry logic.

App SQL rewrite : Deploy rollback / config center toggle — immediate; SQL fixes need independent feature flag, not tied to DDL rollback.

Production Considerations

Change window & canary : DDL always in window; verify plan on replica/test first; pre-check long transactions (Barrier 1) and set short lock_wait_timeout (Barrier 2).

Two-person review : DDL statement character-by-character (one typo turns ADD INDEX into DROP INDEX); record result in shift log.

Backup first :

mysqldump --single-transaction --set-gtid-purged=OFF bizdb orders > /backup/orders_$(date +%F).sql

(huge tables: XtraBackup physical backup, faster restore).

Huge tables beyond INPLACE : Use gh-ost (binlog-based async cutover) or pt-online-schema-change (trigger-based dual-write); both add replica lag and 1x extra disk — read docs, rehearse in test.

Least privilege : Query account has PROCESS but no DDL; read-only for daily; DDL by dedicated change account in window only.

SQL pre-flight : New SQL on core tables must attach EXPLAIN screenshot + estimated scanned rows; templates scanning >100K rows get dedicated review — choke "getting slower" at the gate.

Version Difference Recap (8.0 → 8.4 Upgrade Notes)

innodb_log_file_size

/ innodb_log_files_in_group removed in 8.4; only innodb_redo_log_capacity. mysql_native_password disabled by default; default_authentication_plugin removed (replaced by authentication_policy). Upgrade drivers to support caching_sha2_password; enable native plugin only if absolutely necessary (not recommended). SHOW SLAVE STATUS and other SLAVE syntax removed; use SHOW REPLICA STATUS. SET PERSIST available since 8.0; often disabled on managed RDS — use vendor console parameter groups.

Summary

The essence of "getting slower" is the slope of slowness . Finding the driver of that slope turns a past incident into a repeatable engineering action:

Separate the three slow types : execution slow via EXPLAIN evidence, wait slow via lock views, resource slow via hit rates & temp-table spill counts. Don't tune parameters until all three evidence chains are clear.

Minimal fix, testable verification : smallest change (one DDL or one parameter), before/after sampling with same SQL (this case: 62.8M rows scanned → 42 rows), rehearse on staging first.

Online DDL three barriers : check long transactions → limit lock_wait_timeout → canary on replica.

Rollback drawn out : DROP INDEX also online; parameter rollback via config snapshot; SET PERSIST gives "change with safety belt".

Institutionalize monitoring : slow-log rate, digest alerts, Rows_examined keyword — catch the next slope in the "slowing" phase, not after business feels it.

Completing this loop means one optimization doesn't just fix a query — it moves a whole class of problems from "discovered after launch" to "blocked before launch".