Monitoring: What to Watch in MySQL

A database that nobody watches fails on a Saturday night. Monitoring does not need to be complicated. A short list of signals, checked automatically with alerts, catches most problems early. Tools such as Prometheus with mysqld_exporter, Percona Monitoring and Management (PMM), Zabbix, Datadog, or your cloud provider’s console collect these for you. This lesson shows what to watch and the queries behind it.

1. Is it up, and how many connections?

1
2

SELECT @@max_connections AS max_connections;
SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected', 'Threads_running', 'Max_used_connections', 'Aborted_connects', 'Connection_errors_max_connections');

1
2
3
4
5
6
7
8
9

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| Aborted_connects                     | 2     |
| Connection_errors_max_connections    | 0     |
| Max_used_connections                 | 9     |
| Threads_connected                    | 3     |
| Threads_running                      | 2     |
+--------------------------------------+-------+

(Values from the test server.) What to look at:

Error 1040 (Too many connections) usually has one of two causes: connections that are not closed or pooled, or a slow query pile-up, where every request waits and new ones keep arriving.

2. What is running now

1
2
3
4

SELECT id, user, host, db, command, time AS seconds, state, LEFT(info, 60) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

1
2
3
4
5

+----+------+-----------------+------+---------+---------+-----------+---------------------------------------------------------+
| id | user | host            | db   | command | seconds | state     | query                                                   |
+----+------+-----------------+------+---------+---------+-----------+---------------------------------------------------------+
| 12 | app  | 10.0.0.12:50432 | shop | Query   |      41 | executing | SELECT c.name, SUM(o.total) FROM orders o JOIN custome |
+----+------+-----------------+------+---------+---------+-----------+---------------------------------------------------------+

A statement that has run for 41 seconds is a candidate for EXPLAIN, a missing index, or KILL QUERY 12; if it is hurting the server. In MySQL 8 the sys schema has friendlier views, for example sys.processlist and sys.session (Performance Schema and sys). SHOW FULL PROCESSLIST shows the same list.

3. Long transactions and lock waits

An open transaction that does nothing holds locks, and keeps old row versions alive (the undo log grows). Look for old transactions:

1
2
3
4

SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds, trx_rows_locked, trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

1

(no rows on an idle server)

A transaction that is minutes old is a bug or a forgotten START TRANSACTION in a session. Who is blocked, and by whom:

1

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age_secs FROM sys.innodb_lock_waits;

1

(an empty set when nothing is waiting)

Alert on lock waits older than a few seconds. The patterns behind them are in Locking and Deadlocks.

4. Slow queries

Turn on the slow query log, and look at the digest of the worst statements regularly (Slow Query Log):

1

SELECT @@slow_query_log AS enabled, @@long_query_time AS threshold_seconds, @@log_queries_not_using_indexes AS log_no_index;

1
2
3
4
5
6

+---------+-------------------+--------------+
| enabled | threshold_seconds | log_no_index |
+---------+-------------------+--------------+
|       0 |         10.000000 |            0 |
+---------+-------------------+--------------+
1 row in set

Also watch the counters Slow_queries and Select_full_join, and compare per hour, not as one total.

5. InnoDB and memory

The buffer pool caches data and indexes. If it is too small, queries read from disk:

1

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_free');

1
2
3
4
5
6
7
8

+---------------------------------+---------+
| Variable_name                   | Value   |
+---------------------------------+---------+
| Innodb_buffer_pool_pages_free   | 1840    |
| Innodb_buffer_pool_pages_total  | 4096    |
| Innodb_buffer_pool_read_requests| 5819244 |
| Innodb_buffer_pool_reads        | 20418   |
+---------------------------------+---------+

Innodb_buffer_pool_reads are reads that missed the cache and went to disk, and read_requests are all logical reads. The hit ratio is 1 - reads / read_requests. Here it is about 99.6%. A ratio that falls (say, below 99%) while the database is busy suggests that the buffer pool is too small for the working set: look at innodb_buffer_pool_size (Server Settings). Also watch the machine’s own memory: if the operating system starts to swap, MySQL becomes very slow.

6. Disk space

A full disk stops MySQL (and corrupts nothing, but it makes writes fail). Track:

• Free space on the data volume and the binary log location (alert at 80%, then 90%).
• The growth of the largest tables:

1
2
3
4
5

SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
GROUP BY table_schema
ORDER BY size_mb DESC;

1

(your databases, largest first)

The usual causes of a sudden jump: runaway binary logs (check binlog_expire_logs_seconds and SHOW BINARY LOGS), a huge temporary table, a table that grows without cleanup, and the undo log of a forgotten long transaction.

7. Replication (if you have replicas)

1

SHOW REPLICA STATUS;

1

Empty set

On a server that is not a replica, the result is empty, as above. On a replica, check Replica_IO_Running and Replica_SQL_Running (both Yes) and Seconds_Behind_Source (alert if it is high or NULL) (Replication Basics).

8. The error log

Many problems appear in the error log first: crashes, corrupt pages, failed startup, “Aborted connection” patterns, deadlock reports (with innodb_print_all_deadlocks). Read where it is:

1

SELECT @@log_error AS error_log_file;

1

(the path of the error log on your server, or stderr)

Ship it to a central log system, and alert on lines with [ERROR] and [Warning] patterns you care about.

9. Checks from outside

The best test is the one that behaves like a user:

• A synthetic check: every minute, connect with a monitoring account and run SELECT 1 and one real read query. Alert on failure or slowness.
• Backup success and backup age.
• Certificate expiry for TLS connections (Encrypted Connections).

A starter set of alerts

Start with a few alerts that mean something, and tune them. A system that sends hundreds of alerts a day teaches people to ignore them.