Monitoring the Counters That Matter

A graph of Threads_running spikes and somebody gets paged. The database is using 14% of one core and nothing is waiting on a row lock: forty analytics sessions opened a connection each, ran one heavy query, and sat in Sleep for the rest of the hour.

MySQL exports several hundred status variables, and most are counts you cannot act on. Every counter worth keeping answers two questions: what value makes me look, and what do I do after I look? Everything below ran on MySQL 9.6.0 with InnoDB.

Seven counters carry the whole story

Signal	Look when	What you do
Threads_connected / max_connections	above about 0.7 and rising across a deploy	Check Max_used_connections_time against the release log, and count pools rather than processes.
Innodb_row_lock_current_waits	nonzero for more than a minute or two	Use performance_schema.data_lock_waits to name the blocking transaction, then its query.
Innodb_row_lock_time / Innodb_row_lock_waits	the average climbs above the latency you promise	Find the transaction that holds locks while it does something slow.
error 1213, ER_LOCK_DEADLOCK	a rise in rate, not any single event	Read the latest deadlock, fix the access order, and retry in the client.
Innodb_buffer_pool_wait_free	any sustained increase	Reads are queuing behind dirty-page flushes. Resize the pool online.
binlog bytes on disk	the total exceeds your retention claim	Compare binlog_expire_logs_seconds and max_binlog_size, then check what else shares the disk.
Seconds_Behind_Source	above the freshness your readers need	Decide whether the replica is slow or the primary is.

Most are counters rather than gauges, so what matters is the change over an interval, and they reset on restart.

An average over six weeks hides the incident you are looking for

The buffer pool formulas are reads / read_requests for the miss rate and its complement for the hit ratio. Over a long-lived server they average away everything: this instance had been up a day with a 128 MiB pool and a cumulative hit ratio of 99.96%.

What moves is the delta over an interval:

200 keyed lookups on orders:  read_requests +2905   reads +28   hit ratio 99.036%
2000 scattered lookups, 156 MiB table:
                             read_requests +15924  reads +732  hit ratio 95.400%
one full scan, 156 MiB table:
                             read_requests +15056  reads +447  hit ratio 97.031%

The small table fits in the pool and stays at 99%. The 156 MiB table does not — 9,984 pages against a pool of 8,192 — so it reads lower and drives other pages out. That is a working set that does not fit, which is what a low ratio means when it means something.

The case that makes people replace a healthy server is the cold one. After a restart the pool is empty and the first interval reads low because every page is a genuine miss; a cache that climbs back to the warm level is working as designed. A warm cache with Innodb_buffer_pool_pages_free near zero that keeps reading low is too small, and the second number to watch is Innodb_buffer_pool_wait_free.

Size the pool from the working set, not the database. A cheap proxy is the sum of data_length + index_length for the tables you serve, from information_schema.tables; here that is a few hundred megabytes against 128 MiB. Resize in innodb_buffer_pool_chunk_size chunks without a restart.

Row lock waits are an instantaneous fact and a cumulative one

Innodb_row_lock_current_waits        1
Innodb_row_lock_time              88330
Innodb_row_lock_time_avg           4907
Innodb_row_lock_time_max          50488
Innodb_row_lock_waits                22

That reading was taken while one session held a row lock inside an open transaction and a second tried to update the same row. current_waits is the live fact; the rest are cumulative. When the blocked session gave up, the gauge returned to 0 and the totals kept the 3,215 ms.

Naming the holder is the payoff. performance_schema.data_lock_waits has one row per waiting lock, naming the requesting and blocking ENGINE_TRANSACTION_ID values — InnoDB's identifiers, so join performance_schema.threads for a PROCESSLIST_ID and data_locks for the row:

waiting_pid  waiting_age_s  blocking_pid  blocking_idle_s  waiting_mode   blocking_mode  locked_row
      10878              2         10877                3  X,REC_NOT_GAP  X,REC_NOT_GAP           1

The blocking session is idle: three seconds old, two of them holding a row the other session needs. That is an application holding a transaction across a network round trip, fixed in code rather than by tuning innodb_lock_wait_timeout. sys.innodb_lock_waits packages the same join.

Deadlocks are not a wait. Two sessions took the same two rows in opposite order and InnoDB chose a victim: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. There is no deadlock status counter here; events_errors_summary_global_by_error records them with a count and a LAST_SEEN if that instrument is on. Otherwise the engine keeps the latest with both statements:

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 309820, ACTIVE 2 sec starting index read
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10882, query id 447871 localhost root updating
UPDATE customers SET country='B2' WHERE id=1
*** (1) HOLDS THE LOCK(S):
... index PRIMARY of table `m6b`.`customers` trx id 309820 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
... index PRIMARY of table `m6b`.`customers` trx id 309820 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
... UPDATE customers SET country='A2' WHERE id=2

The fix is usually visible in the two queries: they touched the same rows in a different order, so order writes consistently or shorten the transactions. Alert on the rate and on ER_LOCK_WAIT_TIMEOUT rising with it, and remember the client has to retry.

Read the digest table first, the slow log second

The slow query log answers "how slow was this one run" when the question is "which shape of query costs me most". Its threshold here was long_query_time = 10. At 0.5, one statement produced a real entry:

# Time: 2026-10-16T19:48:07.965347Z
# User@Host: root[root] @ localhost []  Id: 10922
# Query_time: 0.843603  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1  Errno: 0
  Created_tmp_disk_tables: 0  Start: 2026-10-16T19:48:07.121744Z End: 2026-10-16T19:48:07.965347Z
use m6b;
SET timestamp=1792180087;
SELECT SLEEP(0.8);

Query_time against Rows_examined and Rows_sent separates "did a lot of work" from "returned a lot": four thousand rows examined to send one is a missing index, and one row examined over two seconds is waiting on something else. Created_tmp_disk_tables, there because log_slow_extra is on, says a sort spilled to disk. A 14 ms join never appeared, being under the threshold: the log samples the slow tail, not the whole census.

The census is performance_schema.events_statements_summary_by_digest: one normalised statement shape per row with a count and accumulated timings, literals replaced by ?. Insensitive to whitespace, case and literals but not to structure, which lets it aggregate queries that look different but behave the same:

execs  DIGEST_TEXT
    4  SELECT COUNT ( * ) FROM `orders` WHERE `customer_id` = ?
    1  SELECT COUNT ( * ) FROM `orders` WHERE `customer_id` = ? AND STATUS = ?

Those four were WHERE customer_id = 3, WHERE customer_id = 77, the same query in lower case with no spaces, and the same query over three lines; adding a condition produced a separate row. That consolidation is the point: a query running ten thousand times a minute at 2 ms never reaches a slow log and shows up here as the largest total. It also makes DIGEST_TEXT runnable as the shape to explain:

SELECT SCHEMA_NAME, LEFT(DIGEST_TEXT, 55) AS digest_text, COUNT_STAR AS execs,
       ROUND(AVG_TIMER_WAIT/1000000000, 3) AS avg_ms,
       SUM_ROWS_EXAMINED AS rows_examined, SUM_NO_INDEX_USED AS no_index
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

m6b       SELECT COUNT ( * ) FROM `orders` `o` JOIN `customers` ...   20 execs  14.079 avg_ms  106660 examined  20 no_index
woo_m4_lab SELECT `option_value` FROM `wp_options` WHERE `option_n... 839 execs   0.253 avg_ms     164 examined   0 no_index
woo_m4_lab UPDATE `wp_options` SET `option_value` = ? WHERE `optio... 165 execs   0.918 avg_ms     165 examined   0 no_index
wpperf_m5  SELECT `option_name` , `option_value` FROM `wp_options`... 42 execs   3.224 avg_ms    5334 examined  42 no_index
sfct_m3_lab SELECT `umeta_id` FROM `wp_usermeta` WHERE `meta_key` ...  1 execs  90.647 avg_ms       0 examined   0 no_index

The first row is twenty executions of a join I wrote, examining 106,660 rows, every one flagged as not using an index. It never entered the slow log because each run was 14 ms, so an alert on the log misses it and an alert on SUM_NO_INDEX_USED catches it. The last row is not my schema: the digest table is server-wide. From there to the plan is a step:

-> Aggregate: count(0)  (cost=495 rows=1)
    -> Filter: (orders.`status` = 'pending')  (cost=403 rows=400)
        -> Table scan on orders  (cost=403 rows=4000)

The plan says table scan and the digest says no_index for every execution. That agreement is what you want before spending an index on it. When they disagree, the digest describes the history and the plan the schema as it is now.

Connection counts are a capacity gauge, and Threads_running is not

Threads_connected against max_connections is the one ratio worth alerting on by itself, because its failure mode is total. With twenty sessions open, each of them sleeping, this server reported:

Threads_connected    21
Threads_running      22
Threads_cached        3
Max_used_connections 20
Aborted_connects     23
Connection_errors_max_connections 0

Read the second line. Threads_running counted 22 while Threads_connected counted 21, and nineteen of those sessions were doing nothing but waiting out a SLEEP. A controlled pair of readings makes the counter's behaviour plain. With only the client connection open, Threads_connected read 1 and Threads_running read 2; with one session running SELECT SLEEP(4), both rose by exactly one, to 2 and 3. The extra thread is the event scheduler, and the sleeping session counted as running.

So Threads_running matters more as a symptom than as an alert. Connection slots run out at a fixed number and error the moment they do, so alerting at 70–80% of the limit gives you time to act, and Max_used_connections_time says whether the peak came with a deploy or a batch job. Threads_running has no fixed ceiling — the useful limit is your core count — and it moves for reasons that are not load. High with CPU saturated is load; high with CPU idle is threads waiting. The manual describes the counter as the number of threads that are not sleeping, which is the reading it invites and not the one it gave.

Three things grow while nobody is looking

The binary log fills disks. This server had 23 files totalling 817 MiB, with max_binlog_size at 1 GiB and binlog_expire_logs_seconds at 2,592,000, or thirty days. Write volume, retention and file size multiply into the requirement, and a binlog directory sharing a volume with the data and backups turns one full disk into every failure at once.

Undo tablespaces hold the previous versions of rows that open transactions and consistent reads need. Here the two were 32 MiB and 16 MiB, with innodb_undo_log_truncate on and innodb_max_undo_log_size at 1 GiB. A long transaction, or a session idle inside one, stops purge from advancing and they grow toward that ceiling.

Temporary tables are the third. Sorts and joins that do not fit in memory spill into the temporary tablespace, and the InnoDB files persist between statements. Created_tmp_disk_tables stood at 39 against Created_tmp_tables at 23,126, and Count_hit_tmp_table_size was 31 — the temporary tables that crossed tmp_table_size, which is how a query gets slower for no visible reason.

Replication lag is not the same thing as a slow primary

I started a second MySQL 9.6.0 instance as a replica to read this metric rather than describe it. With the applier stopped and writes continuing on the primary, the byte gap is visible and Seconds_Behind_Source is not:

Source_Log_File        binlog.000134
Read_Source_Log_Pos    857139746   -- what the IO thread has fetched
Exec_Source_Log_Pos    857138474   -- what the applier has executed
Relay_Log_Space        98274       -- bytes sitting in the relay log
Seconds_Behind_Source  NULL        -- NULL while the applier is not running
Replica_SQL_Running    No

Restart the applier and the metric appears, then collapses as the replica catches up:

sample 1: Exec_Source_Log_Pos 857098350  Seconds_Behind_Source 3
sample 2: Exec_Source_Log_Pos 857098350  Seconds_Behind_Source 4
sample 3: Exec_Source_Log_Pos 857102166  Seconds_Behind_Source 0
sample 4: Exec_Source_Log_Pos 857102166  Seconds_Behind_Source 0

Three things follow. The value is null when the applier is stopped, so a monitor reading only Seconds_Behind_Source reports no lag for a replica applying nothing; read Replica_SQL_Running and Last_SQL_Error with it. It comes from the timestamp of the transaction being executed, so it measures replication delay and not primary latency. And a byte gap of 1.3 KiB produced a four-second reading, which is the argument for time over bytes.

To tell a lagging replica from a slow primary, ask the primary. If its tail is slow — Slow_queries climbing, Innodb_row_lock_time_avg rising, Threads_connected near the limit — lag is a symptom of a problem to fix at the source. If the primary is healthy, the problem is on the replica: one applier thread behind a parallel workload, a different index set, or a replica doing double duty as a reporting server. And do not read four seconds as four seconds of backup, because a replica back from a day of downtime reports the same four seconds while it works through a day of changes.

Show the solution and the reasoning

The table separates two kinds of number. Threads_connected, Innodb_row_lock_current_waits and Relay_Log_Space are gauges, where the current value is the message. Innodb_row_lock_time, Slow_queries, Created_tmp_disk_tables and Innodb_buffer_pool_reads are cumulative and meaningful only as a rate, so a zero delta is a quiet minute.

On the digest exercise, similar queries should be one row; three rows means they differ in more than literals. The scanning query is the interesting half: under long_query_time it will not be in the slow log however many rows it examines, which is why Rows_examined in the digest table is where a full scan shows up. On the locks, SHOW GLOBAL STATUS gives counts but no identities, and data_lock_waits gives engine transaction ids and lock modes; only the join through performance_schema.threads, or sys.innodb_lock_waits, gives the blocking connection's PROCESSLIST_ID and its query.

On sizing, if your hot tables exceed the pool then raise the pool first, because it converts disk reads into memory reads for every query at once. If the pool is larger than the hot set and Innodb_buffer_pool_wait_free is still climbing, the problem is write pressure rather than capacity.

Six modules have been one argument. A schema is a contract the server enforces: constraints make bad states unrepresentable, transactions and isolation describe what a reader may see, indexes make the truth cheap to ask for, migrations change the contract without breaking it, and this module keeps the contract honest once it is live. A backup is a claim about the past until a restore proves it, a counter a claim about the present until the interval proves it, and a pool a claim about the future until the ceiling arrives. The database is the only component in the stack that can tell you the truth about itself, and nearly all the work is asking it the right question.