A slow MySQL request is often waiting for another operation rather than consuming CPU. InnoDB exposes lock, I/O and concurrency behaviour through status, performance schema and engine-specific counters, but the useful question is always the same: what was waiting, what held the resource, and what changed at that time?
Begin with the affected workload
Take the user-visible symptom and set a narrow incident window. Compare active sessions, query duration, commits, reads, writes and CPU with the period before the slowdown. A rise in elapsed time with modest CPU can indicate lock waits, storage latency or a saturated connection path. Do not infer a wait category from one counter alone.
Separate lock waits from I/O waits
Lock contention normally has a blocking relationship: one transaction holds rows or metadata while another needs them. I/O pressure often appears with rising reads or writes, queueing, buffer-pool misses and longer physical access. Both can happen together when a long transaction holds locks while it performs expensive work, so trace the blocking statement and its transaction age before changing a timeout.
Useful questions during an incident
- Which statement or transaction owns the contested rows or metadata?
- How old is the transaction, and is it waiting on the application?
- Did DDL, a bulk change or a deployment introduce metadata locking?
- Are physical reads, write latency or buffer-pool misses changing at the same time?
Avoid the timeout-only fix
Increasing a timeout can hide a transient problem and prolong a bad one. Prefer reducing transaction scope, committing in sensible batches, ordering updates consistently and moving long reads away from critical write paths. If storage is the cause, validate read and write latency, workload shape and buffer-pool behaviour before changing InnoDB settings.
Collect evidence while the delay exists
Start with SHOW FULL PROCESSLIST to identify long-running operations and their states. On MySQL 8.0 and 8.4, Performance Schema data_lock_waits exposes relationships between requested and blocking data locks. INFORMATION_SCHEMA.INNODB_TRX adds transaction age and ownership. Metadata locks are a separate investigation; a migration waiting for table metadata may not appear in the row-lock relationships you expected.
SELECT trx_id, trx_started, trx_state,
trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
This is a current-state query, not an incident archive. A NULL statement does not establish that an old transaction is harmless: its client may have stopped issuing SQL while retaining locks. Use an account permitted to inspect the relevant sessions, and correlate the thread identifier with the application connection before taking action.
Example: a queue that looks like insufficient capacity
Imagine an order service with low CPU and a rapidly growing thread count. Twenty requests are waiting behind one transaction that updated an order, then called an external payment service before committing. Adding database cores will not release that row. Increasing the pool can create a larger queue, and retrying every timeout immediately can lengthen the incident.
The immediate decision belongs to the transaction owner: can the original operation complete, or must it be rolled back with an appropriate business recovery? The durable change is to shorten the database transaction and design the external interaction so it can recover safely. Recheck correctness as well as latency when changing these boundaries.
Distinguish a missing signal from zero waiting
Instrumentation and consumers determine which Performance Schema evidence exists. Empty tables may mean collection is disabled, a session has already finished, or the monitoring account lacks visibility. Record these limitations before concluding that the problem must be outside MySQL. Enable additional collection selectively and evaluate overhead under representative load.
For suspected I/O delays, ask whether many unrelated statements slowed together or whether one query suddenly reads far more pages. The former suggests shared resource pressure; the latter suggests changed work. Neither is conclusive on its own, but the distinction makes the next check specific enough to test.
Keep the evidence for the next incident
Mini DBA MySQL monitoring brings query and metric history together with InnoDB and connection diagnostics for investigating recurring incidents. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading