$ guides / mysql ▌

MYSQL · OPERATIONS PLAYBOOK

Running MySQL in production, without the 3 a.m. surprises

A connection-per-thread server, an InnoDB buffer pool, a circular redo log, undo-based MVCC, and a binlog replication contract. The mental model of how MySQL actually behaves under load, where it tends to break, what to monitor as your operation matures, and the runbooks for the incidents you'll see.

> Start with the monitoring checklist → # Jump to the full guide list

MySQL is famously easy to start and famously unforgiving once the write rate, the connection count, or the dataset grows past what the defaults assumed.

The defaults work. Until the InnoDB redo log fills, a sharp checkpoint forces a synchronous flush, and every write on the server freezes at once. Until an idle BEGIN nobody committed pins an MVCC read view, the history list climbs past a million, and every read across every table slows down. Until an ALTER TABLE queues behind that same transaction and the whole table goes dark behind Waiting for table metadata lock. Until the working set outgrows the buffer pool and a system that was fine at 98% falls off a cliff at 101%. Until max_connections is reached and the server answers every client with ERROR 1040 (HY000): Too many connections while looking, to the database itself, perfectly healthy.

These guides are written for engineers who already run MySQL, not for people learning what a table is. The goal is to give you the mental model of how the server actually behaves under load, the failure patterns that keep recurring, the monitoring story that catches problems before they page anyone, and the runbooks you wish someone had handed you before your last incident.

How MySQL actually runs in production

MySQL is not just a place to put rows. It is a connection-threaded SQL processor sitting on top of InnoDB — a buffer pool, a write-ahead redo log, undo-based MVCC, and a streaming binary-log replication contract. Most production failures live between these layers, not inside any one of them.

clients / connection threads

Every client connection gets a dedicated thread holding session memory — sort, join, and net buffers, plus temp tables. The count is bounded by <code>max_connections</code>; exceed it and new connections are refused with <code>Too many connections</code>. Idle <code>Sleep</code> connections still hold a slot and memory.

CLIENT

parser + optimizer

Each statement is parsed, then the optimizer picks a plan. A plan that loses its index turns a point lookup into a full scan — <code>Handler_read_rnd_next</code> and <code>Select_full_join</code> climb, and the same query that was fast yesterday is slow after a deploy or an <code>ANALYZE TABLE</code>.

OPTIMIZER

InnoDB buffer pool

A fixed-size LRU cache of 16 KB pages split into young (hot) and old (cold) sublists. The single strongest predictor of read latency. When the working set outgrows it, the hit ratio collapses non-linearly and every read goes to disk.

BUFFERPOOL

row locks + MVCC (undo)

InnoDB takes row and next-key locks held until commit, and keeps old row versions in undo for consistent reads. Lock waits time out at <code>innodb_lock_wait_timeout</code>; a held read view blocks the purge thread and the history list grows.

MVCC

metadata locks (DDL)

A separate locking layer from InnoDB row locks. DDL needs an exclusive metadata lock; any open transaction holding a shared one makes it wait — and every <code>DML</code> on that table queues behind the exclusive request. Invisible in <code>SHOW ENGINE INNODB STATUS</code>.

MDL

redo log + checkpointing

All modifications hit the circular redo log before the data files. As checkpoint age approaches <code>innodb_redo_log_capacity</code>, InnoDB forces a sharp checkpoint that stalls every write. Page cleaners flush dirty pages in the background to keep that age down.

REDO

binlog + replication

Commits also write the binary log, which replicas fetch (I/O thread) and apply (SQL thread). The apply side can fall behind the primary's write rate; <code>Seconds_Behind_Source</code> grows, relay logs accumulate, and a lagging replica can block binlog purge on the primary.

REPLICA

OS, memory + storage

Total memory is the buffer pool plus per-connection buffers times <code>max_connections</code> — overshoot it and the kernel OOM killer fires. Redo and binlog <code>fsync</code> latency on the data volume sets the floor on commit throughput, and a full disk stops writes hard.

Why this matters: MySQL can be slow because of a missing index, a buffer-pool miss, a row-lock wait, a metadata-lock stall, a checkpoint freeze, purge lag, an fsync stall, or replication apply falling behind. The symptom is often the same — MySQL is slow — but each layer has a different signal and a different fix.

The failures you'll actually see

Most MySQL incidents fall into a small set of recurring patterns. Recognise the shape, and triage gets dramatically faster.

CRITICAL

The redo log checkpoint stall

The write rate outruns the page cleaners. Dirty pages accumulate and checkpoint age climbs toward innodb_redo_log_capacity. Past roughly 85% InnoDB forces a sharp checkpoint — a synchronous flush that stalls every write on the server at once. This is MySQL's most dangerous cliff edge, and the most common cause of a sudden, unexplained write freeze.

checkpoint age above 85% of redo log capacity
Questions or commit rate collapsing while clients stay connected
Innodb_buffer_pool_pages_dirty ratio flat or climbing
disk write latency spiking with normal queue depth

Investigate →

IMMINENT

The metadata lock cascade

A DDL statement needs an exclusive metadata lock. A long-running (often idle) transaction holds a shared lock on the same table, so the ALTER TABLE waits — and every new query on that table queues behind the exclusive request. Within minutes the connection pool fills with sessions stuck in Waiting for table metadata lock. One table goes dark while the rest of the server looks fine.

many threads in Waiting for table metadata lock on one table
Threads_running climbing while Questions drops
Threads_connected approaching max_connections
no rise in Innodb_row_lock_waits — this is not row locking

Investigate →

ACTIVE

The purge lag history-list explosion

A single transaction left open — a forgotten BEGIN, an ORM that never committed, a pool returning a connection without rollback — pins an MVCC read view. The purge thread cannot reclaim old row versions, the history list grows without bound, and every consistent read has to traverse longer version chains. The whole server gets slower by the hour, with no single slow query to blame.

trx_rseg_history_len above 1,000,000 and climbing
one very old transaction in INNODB_TRX with trx_query NULL
read latency rising across all tables at a steady read rate
undo tablespace growing on disk

Investigate →

CRITICAL

The connection exhaustion cliff

Connections reach max_connections and MySQL answers new ones with ERROR 1040 (HY000): Too many connections. To clients it looks like MySQL is down; to the server it is simply full. Low Threads_running underneath means a connection leak or idle accumulation; high Threads_running means genuine overload backing up. A reserved admin slot is your only way in.

Connection_errors_max_connections incrementing
Threads_connected / max_connections above 0.95
low Threads_running (leak) or high Threads_running (overload)
SHOW PROCESSLIST full of long Sleep connections

Investigate →

IMMINENT

The buffer pool cliff edge

The working set outgrows the buffer pool. Every query contends for a shrinking pool of in-memory pages, evictions force disk reads, the disk saturates, and all queries slow at once. The hit ratio drops non-linearly — gentle from 99.9% to 99%, fast from 99% to 95%, then catastrophic. A system that ran fine at 98% utilization falls off the edge at 101%.

buffer pool hit ratio dropping below 99%, then 95%
Innodb_buffer_pool_reads rate rising sharply
Innodb_buffer_pool_wait_free becoming nonzero
disk read I/O near 100% with latency up across all queries

Investigate →

WATCHFUL

The replication lag spiral

The replica's apply threads cannot keep up with the primary's write rate. Seconds_Behind_Source grows monotonically, relay logs pile up on the replica's disk, and reads return progressively staler data. If the primary's binlog expiry fires before the replica catches up, replication breaks and the replica needs a rebuild. Single-threaded apply and large transactions are the usual culprits.

Seconds_Behind_Source increasing without a plateau
Relay_Log_Space growing faster than the apply rate
replica Threads_running elevated, primary healthy
replica_parallel_workers at 0 or 1 on a multi-core replica

Investigate →

MySQL monitoring maturity levels

MySQL observability works in four practical levels. Each is a complete operation, not a stepping stone. Pick the level that matches how much your database matters. Most production instances should land at the second level.

Level 1: Survival

Know that something is wrong

Survival monitoring is the floor. With these signals you can answer one question: is MySQL still functioning? You will not learn what broke, but you will learn that something broke before users do. Survival is enough for dev environments and side-project databases.

SELECT 1 liveness Does the server answer a real query path within a couple of seconds?
Uptime / unexpected restarts Did Uptime reset without your permission (crash or OOM kill)?
Threads_connected vs max_connections Are you within the hard connection ceiling?
Replica_IO_Running / Replica_SQL_Running Are both replication threads still Yes?
Seconds_Behind_Source Is the replica applying, even if the number is rough?
Disk free on the data volume Is the volume hosting data, redo, and binlogs near full?
Error log for [ERROR] lines Is MySQL writing crash, corruption, or replication errors?

Level 2: Operational

Diagnose most incidents on your own

Operational monitoring is what most production databases should target. Survival tells you something is wrong; operational tells you what. With this coverage your team can usually diagnose an incident on its own: lock contention, buffer-pool pressure, slow queries, connection pile-up, replication thread state.

Questions rate Is throughput where it should be — or have queries stalled?
Threads_running vs CPU cores Is active load oversubscribing the cores?
Slow_queries rate (long_query_time ≤ 1s) Is query latency regressing? The 10s default hides everything.
Buffer pool hit ratio Is the working set still served from RAM?
Innodb_row_lock_waits and lock_deadlocks Is row-lock contention throttling the workload?
Connection_errors_max_connections Are clients being refused at the ceiling?
Aborted_connects / Aborted_clients Are auth or network failures climbing?
Created_tmp_disk_tables ratio Are temp tables spilling to disk?
Seconds_Behind_Source trend Is replication lag growing, stable, or recovering?

Level 3: Mature

Catch problems before they become incidents

Mature monitoring catches problems before they wake anyone up. Checkpoint age creeping toward redo capacity, the history list drifting up, a metadata-lock wait forming, an old transaction sitting open, binlogs outgrowing their expiry. None of these will page you on day one. They become page-out incidents on day thirty.

Checkpoint age / redo log capacity How close is the next sharp-checkpoint write stall?
trx_rseg_history_len (history list) Is purge keeping up, or is a read view blocking it?
Metadata lock waits (performance_schema.metadata_locks) Is a DDL cascade forming on one table?
Oldest open transaction age (INNODB_TRX) Is anything holding locks or a read view for minutes?
Innodb_os_log_pending_fsyncs Is the storage layer keeping up with durable commits?
Binary log growth vs expiry Will binlogs fill the disk before they purge?
Opened_tables rate / table_open_cache Is the table cache churning file descriptors?
Threads_created rate Is connection pooling actually being used?
GTID set comparison across topology Are replicas truly caught up, or just reporting zero lag?

Level 4: Expert

Reactive instrumentation after real incidents

Expert signals enter your stack the day after a specific incident proved you needed them. Lock-wait chain reconstruction, per-index access stats, adaptive-hash-index latch contention, page-cleaner efficiency, GTID errant transactions, sort-merge pass rates. Most teams never need every signal here. Add the ones your incident history says you do.

data_lock_waits + data_locks chains (8.0) Who is blocking whom, in real time?
Per-index access from table_io_waits_summary Which indexes are unused or hot?
btr_search latch waits (SEMAPHORES) Is the adaptive hash index spinning CPU without progress?
Page cleaner flush rate vs dirty ratio Are checkpoints flushing what the cleaners should?
GTID errant transactions Does a replica hold transactions the source never had?
Sort_merge_passes / sort scans Is sort_buffer_size too small for real queries?
Handler_read_rnd_next step-changes Did a deploy introduce a new full-table scan?
performance_schema digest top-N Which query shapes actually cost the most time?

Operating mistakes worth avoiding

The traps MySQL teams keep falling into. Each has a clear, well-known fix. Most teams only learn it after an incident.

Alerting on Threads_connected instead of Threads_running

Teams page on "too many connections" while the server is idle, and miss the real killer: too many <em>active</em> connections. High <code>Threads_connected</code> with low <code>Threads_running</code> is a pool leak; low connected with high running is a crisis. Alert on <code>Threads_running</code> relative to CPU cores, not on raw connection count.

Leaving long_query_time at the 10-second default

The default misses every operationally relevant slowdown, so incidents show "no slow queries" while users wait seconds. Set <code>long_query_time ≤ 1</code> for OLTP and track the <code>Slow_queries</code> / <code>Questions</code> ratio, not the absolute count.

Not monitoring checkpoint age

Checkpoint stall is the most common mysterious write freeze in production MySQL, yet it is rarely watched until the first incident. There is no <code>Innodb_checkpoint_age</code> in <code>SHOW STATUS</code> — derive it from the <code>Innodb_redo_log_*</code> status variables (8.0.30+) or <code>INNODB_METRICS</code>, and alert at 75% of capacity.

Not monitoring the history list length

<code>trx_rseg_history_len</code> was removed from <code>SHOW STATUS</code> in 8.0 and lives in <code>INNODB_METRICS</code>. Teams learn about purge lag during their first incident with a forgotten transaction. A history list above a million degrades every read on the server — alert on it.

Running ALTER TABLE without checking for long transactions

An online schema change still takes a brief exclusive metadata lock, and if any open transaction holds a shared lock it cascades into a full-table stall. Check <code>INNODB_TRX</code> for old transactions before any DDL, and never assume "online" means "lock-free".

Trusting Seconds_Behind_Source for failover decisions

It reports 0 when the I/O thread is behind, NULL (not infinity) when broken, and jumps to thousands after an idle source bursts — and it is inaccurate with parallel replication. Use <code>pt-heartbeat</code> or GTID set comparison for any decision that matters.

No disk-space monitoring on the binlog and tmpdir volumes

Binlogs fill the disk when a replica lags and blocks purge; <code>tmpdir</code> fills when a query spills a huge temp table. Both stop the server with <code>No space left on device</code>, and neither is on the data volume teams usually watch. Monitor every filesystem MySQL writes to.

Treating the error log as optional

InnoDB crash recovery details, page corruption, assertion failures, and full replication errors are written only to the error log — not to any status variable. Many teams never collect it, so the most valuable forensic data during an incident sits in a file nobody was watching.

MySQL runbooks in this section

Each guide is a focused runbook for one symptom or topic. Pick one when you have an incident, or use the categories to learn the area.

▸

Start here

▸

Connections, threads, and pooling

▸

Row locks, deadlocks, and lock waits

▸

Metadata locks and DDL stalls

▸

InnoDB redo log and checkpointing

▸

InnoDB buffer pool and memory

▸

Purge lag, undo, and the history list

▸

Replication, lag, and GTID

▸

Slow queries, scans, and temp tables

▸

Disk, binlogs, and tablespaces

▸

Security, auth, and integrity

WHERE TO GO NEXT

Setting up MySQL monitoring, or putting out a fire?

If you're starting from scratch, the monitoring checklist is the path of least regret. If you're mid-incident, jump straight to the symptom that matches what you're seeing.

> Start with the checklist > Back to Operations Guides