A useful MySQL troubleshooting query answers one incident question and preserves context: what changed, which work is waiting, who is blocking it, whether capacity is saturated, and whether replicas are healthy. Run the least invasive query first, capture its UTC timestamp and server identity, and compare rates with a known baseline. The examples below target MySQL 8.4 and use current source/replica terminology.
Start With Scope, Not a Tuning Change
Record the user-visible symptom, affected service and Region, release or schema-change timeline, and whether the problem is latency, errors, stale reads, unavailable connections, or data correctness. Confirm the server you reached before interpreting any result:
SELECT UTC_TIMESTAMP() AS observed_at,
VERSION() AS mysql_version,
@@hostname AS host_name,
@@server_uuid AS server_uuid,
@@read_only AS read_only,
@@super_read_only AS super_read_only;
SHOW GLOBAL STATUS WHERE Variable_name IN
('Uptime', 'Threads_connected', 'Threads_running',
'Connections', 'Aborted_connects', 'Aborted_clients');Connections, Aborted_connects, and Aborted_clients are cumulative counters, while Threads_connected and Threads_running are current levels. Calculate counter rates over a measured interval or with monitoring data; a large absolute value after months of uptime is not automatically an incident.
Find Active Work and Expensive Digests
Use Performance Schema rather than enabling persistent logging during an incident. The current-event query shows foreground sessions with executing SQL. Timers are reported in picoseconds, so the conversions below produce seconds.
SELECT t.PROCESSLIST_ID AS connection_id,
t.PROCESSLIST_USER AS user_name,
t.PROCESSLIST_HOST AS client,
t.PROCESSLIST_DB AS db_name,
ROUND(es.TIMER_WAIT / 1000000000000, 3) AS elapsed_s,
ROUND(es.LOCK_TIME / 1000000000000, 3) AS lock_s,
es.ROWS_EXAMINED,
es.ROWS_SENT,
es.SQL_TEXT
FROM performance_schema.events_statements_current AS es
JOIN performance_schema.threads AS t USING (THREAD_ID)
WHERE t.TYPE = 'FOREGROUND'
AND es.SQL_TEXT IS NOT NULL
ORDER BY es.TIMER_WAIT DESC;Current statements explain what is running now. Statement digests aggregate normalized statements since the relevant statistics reset and reveal where time accumulated:
SELECT SCHEMA_NAME,
DIGEST_TEXT,
COUNT_STAR,
ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_s,
ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT,
SUM_NO_INDEX_USED,
FIRST_SEEN,
LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;A digest with high total time may simply be frequent; a high average may be rare; SUM_NO_INDEX_USED is a clue, not proof that an index is correct. Capture the digest and a safe representative statement, then use EXPLAIN ANALYZE only on a read-only query in a controlled context. The MySQL performance-tuning guide covers remediation after triage.
Trace Lock Waits Before Killing Anything
The MySQL 8.4 sys.innodb_lock_waits view joins lock and transaction evidence into a practical wait chain:
SELECT wait_started,
wait_age_secs,
locked_table_schema,
locked_table_name,
locked_index,
waiting_pid,
waiting_query,
blocking_pid,
blocking_query,
blocking_trx_age,
blocking_trx_rows_modified
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;An idle blocker can have a NULL blocking query, so also inspect its transaction. A long transaction is risky even when it is not currently executing:
SELECT trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_s,
trx_mysql_thread_id,
trx_rows_locked,
trx_rows_modified,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;Do not execute the view's generated KILL text blindly. Confirm session ownership, business operation, rollback size, replica or failover impact, and application retry behavior. Prefer cancelling only the current statement when appropriate; killing a connection rolls back its open transaction, which can extend the incident. Preserve the wait chain first. The live InnoDB lock runbook provides the guarded intervention procedure.
Check Connections and Resource Symptoms
Break down foreground connections without relying on the deprecated Information Schema process list:
SELECT PROCESSLIST_USER AS user_name,
PROCESSLIST_HOST AS client,
PROCESSLIST_STATE AS state,
COUNT(*) AS sessions
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND'
GROUP BY PROCESSLIST_USER,
PROCESSLIST_HOST,
PROCESSLIST_STATE
ORDER BY sessions DESC;Compare connected and running threads with max_connections, application pool limits, CPU, and request latency. Raising max_connections during saturation can increase memory and scheduling pressure. Identify the pool or client creating sessions and restore backpressure first.
For InnoDB and temporary-work symptoms, collect the native counters below and compare their rates over the same interval:
SHOW GLOBAL STATUS WHERE Variable_name IN (
'Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_reads',
'Innodb_row_lock_waits',
'Innodb_row_lock_time',
'Created_tmp_tables',
'Created_tmp_disk_tables'
);The ratio of physical buffer-pool reads to logical read requests is a workload observation, not a universal pass mark. Temporary-disk-table growth can reflect query shape and object types as well as memory limits. Diagnose the digests creating the work before changing global memory settings.
Check Replication on MySQL 8.4
SHOW REPLICA STATUS is the supported MySQL 8.4 statement; SHOW SLAVE STATUS was removed. For every channel, inspect Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source, retrieved and executed GTID sets, relay-log space, and the last I/O and SQL errors:
SHOW REPLICA STATUS\G
SELECT CHANNEL_NAME,
WORKER_ID,
SERVICE_STATE,
LAST_ERROR_NUMBER,
LAST_ERROR_MESSAGE,
LAST_APPLIED_TRANSACTION,
APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
ORDER BY CHANNEL_NAME, WORKER_ID;Seconds_Behind_Source can be NULL and is not sufficient by itself. Distinguish a disconnected receiver, stopped applier, active backlog, deliberate delay, idle source, and a parallel-worker error. Correlate GTID or log-position progress with a business heartbeat. Do not skip transactions merely to turn the status green; reconcile the failed transaction and data consequences. See the MySQL replication-lag guide for a full workflow.
Measure Table Footprint and Error Evidence
SELECT TABLE_SCHEMA,
TABLE_NAME,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 1) AS allocated_mib,
TABLE_ROWS,
UPDATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA NOT IN
('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC
LIMIT 20;For InnoDB, table rows and sizes are estimates and UPDATE_TIME is not a universal change clock. Store periodic samples to measure growth; one snapshot cannot tell you what filled the disk. Include binary logs, relay logs, temporary files, undo, and filesystem or managed-service metrics in the capacity investigation.
If log_error_services includes the Performance Schema sink, query recent structured errors; otherwise use the configured MySQL error-log destination and provider logs:
SELECT LOGGED, PRIO, ERROR_CODE, SUBSYSTEM, DATA
FROM performance_schema.error_log
WHERE LOGGED > UTC_TIMESTAMP() - INTERVAL 1 HOUR
ORDER BY LOGGED DESC;Do not enable the general log or set a very low global slow-query threshold as an emergency reflex. Both can add overhead, volume, and sensitive SQL text. Any persistent logging change needs an owner, retention and access controls, an impact estimate, and a rollback time.
Daily and Incident Checklist
- Confirm server identity, role, uptime, and deployment timeline.
- Review service latency and errors before database internals.
- Compare connection, query, lock, temporary-table, and aborted-client rates with baseline.
- Inspect top statement digests and active work without resetting evidence.
- Check each replication channel and worker, not only one lag field.
- Review table and log growth from time-series samples.
- Record every intervention, expected effect, result, and reversal condition.
Turn Evidence Into One Reversible Experiment
A diagnostic snapshot is not a root cause by itself. Write a short hypothesis that connects the user symptom to the observed evidence: for example, a new statement digest increased rows examined, holds locks longer, and aligns with request latency. State what measurement would disprove that explanation. Compare the affected period with the same workload before the change, and account for traffic, cache warmth, failover, statistics resets, and maintenance.
Change one lever at a time through the normal change process. Prefer a bounded query cancellation, traffic reduction, feature flag, or tested index over an undocumented global variable change. Record the baseline, command, owner, expected effect, observation window, abort threshold, and reversal step. After the intervention, repeat the same queries and application checks. If the evidence does not move as expected, reverse the change and return to the decision tree instead of stacking additional guesses. Preserve the before-and-after capture for the incident review and for future baselines.