SQL
MySQL diagnostics, autonomous.
dbexpertAI diagnoses MySQL autonomously: detection paths for InnoDB lock waits, replication lag, buffer-pool pressure, redo-log churn, and optimizer misbehavior run every 90 seconds and return root cause, evidence, and resolution steps.
MySQL's failure modes concentrate in InnoDB internals and replication — places where the evidence exists (performance_schema, information_schema.innodb_*, SHOW ENGINE INNODB STATUS) but is famously unpleasant to read under pressure. Detection paths read it every 90 seconds so nobody has to parse a lock-wait graph at 3am.
Failure modes we detect
How MySQL actually fails in production
InnoDB lock waits and history-list growth
Long-running transactions block purge, the history list grows, and lock waits cascade behind row locks the application never meant to hold. The signature — trx age, history list length, lock-wait chains — is all in the InnoDB status output, if something is actually reading it.
Replication lag with silent drift
Single-threaded (or badly parallelized) replica apply falls behind bursty writes; worse, row-based replication can silently drift after mixed-format mistakes. Seconds_Behind_Master is a famously misleading metric — the real diagnosis needs relay-log position math and apply-thread state.
Buffer-pool thrash after working-set growth
The working set quietly outgrows innodb_buffer_pool_size; hit rate collapses, disk reads spike, and every query gets uniformly slower. Because degradation is smooth rather than sudden, teams often chase phantom "slow query" causes for weeks.
Redo log pressure and checkpoint stalls
Undersized redo logs under write bursts force aggressive flushing and periodic stalls — the notorious sawtooth latency pattern. The evidence lives in checkpoint-age tracking, not in any default dashboard.
Optimizer misestimates on skewed data
MySQL's optimizer has fewer statistics to work with than Postgres's; skewed distributions and stale index stats produce wrong join orders and index choices that ANALYZE TABLE fixes in seconds — once someone identifies that as the cause.
A real diagnosis, walked through
What a MySQL detection path delivers
// symptom
Order-processing workers time out in waves every few minutes; between waves, everything is fast.
// diagnostic evidence
- information_schema.innodb_trx: one transaction open 41 minutes, holding row locks on inventory
- lock-wait chain: 63 sessions queued directly or transitively behind that transaction
- the blocking session is idle — last statement completed 40 minutes ago (connection-pool leak)
- history list length climbing continuously: purge is blocked by the same transaction
// root cause
An application worker opened a transaction, updated inventory rows, then hung on an external call without committing — a classic zombie transaction. Every wave of timeouts is the lock-wait timeout expiring for the queue behind it.
// resolution steps
- Immediate: KILL the blocking session (id and originating host provided in the evidence)
- Set innodb_kill_idle_transaction / application-side transaction timeouts so zombies die automatically
- Fix the worker: move the external call outside the transaction boundary
Composite walkthrough drawn from the MySQL detection-path library — the shape of every diagnosis: symptom, evidence, root cause, resolution. Yours will name your tables.
Path library coverage
What the MySQL detection paths watch
- InnoDB: lock waits, deadlocks, history-list growth, buffer-pool hit rate and sizing
- Replication: apply lag, thread state, drift signatures, GTID gaps
- Redo/undo: checkpoint age, log-file sizing, purge health
- Query layer: slow-query outliers, optimizer misestimates, missing indexes, stale statistics
- Capacity: connection limits, thread cache, temp-table spill to disk
- Configuration: settings contradicting workload shape (flush behavior, log sizing, pool partitioning)
Connect your MySQL — free.
Read-only agent, on-prem, 90 seconds to your first diagnosis. Your first database is free for life — make it the MySQL that hurts.
First database free. For life.
Runs on Windows, macOS and Ubuntu — inside your network, read-only. Enterprise fleet or want humans in the loop? Talk to us.