Master MySQL Monitoring with Built‑in SHOW Commands: A Complete Guide

There are many ways to monitor databases, divided into built‑in, commercial, and open‑source categories; MySQL, with its active community, offers numerous monitoring methods. This guide shows how to obtain monitoring data solely with MySQL's own SHOW commands, achieving fast, convenient, and low‑overhead collection in a single‑node setup.

1 Connects

Maximum used connections: show status like 'Max_used_connections' Current open connections:

show status like 'Threads_connected'

2 Buffer Cache

Reads not from buffer pool: show status like 'Innodb_buffer_pool_reads' Reads from buffer pool: show status like 'Innodb_buffer_pool_read_requests' Total pages in buffer pool: show status like 'Innodb_buffer_pool_pages_total' Free pages in buffer pool: show status like 'Innodb_buffer_pool_pages_free' Cache hit rate:

(1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

Buffer pool usage:

((Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total) * 100%

3 Locks

Lock wait count: show status like 'Innodb_row_lock_waits' Average lock wait time: show status like 'Innodb_row_lock_time_avg' Check for table locks: show open tables where in_use>0 (non‑empty result indicates table locks)

Note: Lock‑wait numbers are cumulative; subtract previous values to obtain the current interval.

4 SQL

Check if slow‑query log is enabled: show variables like 'slow_query_log' (ON means enabled)

Enable slow‑query log: set global slow_query_log=1 Set slow‑query threshold: set global long_query_time=0.1 Locate slow‑query log file: show variables like 'slow_query_log_file' Format slow‑query log:

mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log

5 Statements

Insert count: show status like 'Com_insert' Delete count: show status like 'Com_delete' Update count: show status like 'Com_update' Select count:

show status like 'Com_select'

6 Throughput

Bytes sent: show status like 'Bytes_sent' Bytes received: show status like 'Bytes_received' Total throughput: Bytes_sent + Bytes_received

7 Server Configuration

List all variables:

show variables

8 Slow SQL

Slow SQL refers to statements whose execution time exceeds the long_query_time threshold. To monitor them, enable the slow‑query log, set an appropriate threshold, locate the log file, and use mysqldumpslow for analysis.

Typical mysqldumpslow usage:

Top 10 most frequent slow queries: mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log Top 3 longest‑time slow queries: mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log The output includes Count, Time, Lock, and Rows, which are explained as follows:

Count – number of executions of the statement type.

Time – average execution time (total time).

Lock – lock time.

Rows – number of rows returned per execution.

9 Summary

Focus on intrinsic metrics rather than superficial tools; flashy interfaces often hide overhead.

MySQL monitoring can be performed via SQL queries against the performance_schema tables after initializing and enabling data collection.

Select monitoring items that match your project's characteristics.

When choosing external monitoring tools, ensure they impose minimal load on the database server.