MySQL Health Checks

This page is original PmaControl documentation. It keeps a practical index structure for MySQL optimization work, without copying third-party content or depending on an external product.

Objective

A MySQL health check verifies whether the server is operationally healthy: availability, capacity, coherent limits, absence of critical signals, and minimum configuration quality.

Essential Checks

1. Uptime and recent restarts.
2. Connection saturation compared with max_connections.
3. Disk-read ratio from the buffer pool.
4. Temporary tables created on disk.
5. Connection errors and Aborted_clients.
6. Long locks, deadlocks, and waiting queries.
7. Free space for datadir, binary logs, and redo logs.

Basic SQL

SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW ENGINE INNODB STATUS\G

Interpretation

A health check should not only output a red/green list. It must explain impact, priority, and how to confirm the diagnosis.

Start with identity and a fresh snapshot

Record the hostname, server version, role, observation time and application impact. Check the whole path from application through proxy to database: a successful SQL connection does not prove that clients can reach the intended writer. Establish the last healthy period and correlate restarts with deployments, host events and the error log.

SELECT NOW() AS observed_at, @@hostname AS hostname,
       VERSION() AS server_version, @@global.read_only AS read_only;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'max_connections', 'datadir', 'tmpdir', 'log_error'
);
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Uptime', 'Threads_connected', 'Threads_running',
  'Max_used_connections', 'Connection_errors_max_connections',
  'Aborted_clients', 'Aborted_connects', 'Innodb_log_waits',
  'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests',
  'Created_tmp_tables', 'Created_tmp_disk_tables'
);

Read rates and pressure, not isolated totals

For increasing counters, use two samples from the same uninterrupted interval. Physical-read fraction = ΔInnodb_buffer_pool_reads / ΔInnodb_buffer_pool_read_requests when the denominator is positive. It describes reads missing the buffer pool, not storage health; compare physical reads per second and disk latency too. Max_used_connections is a high-water mark since reset, while Threads_connected is current. Sleeping connections and actively executing work have different capacity costs.

The disk temporary-table ratio is only a clue. MySQL TempTable memory-mapped overflow is not fully represented by Created_tmp_disk_tables; a low ratio does not prove that temporary work stays in RAM. Rising aborted connections require application, timeout and network evidence before attributing them to a database defect.

Inspect transactions, replicas and the host

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;
SHOW ENGINE INNODB STATUS\G

A long transaction can be idle while retaining locks or delaying purge. Identify its owner and business operation before stopping it; rollback itself can take time. On a MySQL 8.0.22+ replica, inspect SHOW REPLICA STATUS\G: receiver/applier state, last errors and replication delay. MariaDB and older MySQL use version-specific commands and fields. Zero reported delay is not a freshness guarantee; verify an application watermark and every configured channel.

df -h /var/lib/mysql
df -i /var/lib/mysql
vmstat 1 5

Replace the example path with the actual datadir and check separate mounts for temporary files and binary logs. Inspect free bytes, inodes, I/O latency, memory pressure and active swap I/O. Exclude the first vmstat report from interval comparisons. Confirm backup freshness with a successful restore test, measured recovery time and the required recovery point, not just a completed backup job.

Prioritize the findings

Continue the investigation

• Use the performance evaluation to record owner, evidence and next review date.

Official references

• MySQL replication status
• MySQL internal temporary tables