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

Connects

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

show status like 'Threads_connected'

BufferCache

Number of reads not from the buffer pool: show status like 'Innodb_buffer_pool_reads' Number of reads from the buffer pool:

show status like 'Innodb_buffer_pool_read_requests'

Total pages in the buffer pool:

show status like 'Innodb_buffer_pool_pages_total'

Free pages in the buffer pool:

show status like 'Innodb_buffer_pool_pages_free'

Cache hit rate calculation:

(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

Buffer pool usage rate:

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

Lock

Number of lock waits: show status like 'Innodb_row_lock_waits' Average lock wait time per occurrence: show status like 'Innodb_row_lock_time_avg' Check for table locks: show open TABLES where in_use>0; Note: lock‑wait statistics are cumulative; subtract previous values to obtain the current interval.

SQL

Check whether the slow‑query log is enabled: show variables like 'slow_query_log'; If OFF, enable it: set global slow_query_log=1; View the long‑query time threshold: show variables like 'long_query_time'; Adjust the threshold: set global long_query_time=0.1; Locate the slow‑query log file: show variables like 'slow_query_log_file'; Format the slow‑query log using the built‑in tool:

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

Note: this command must be run from the command line, not via JDBC.

Statement

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

show status like 'Com_select';

Database Throughputs

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

Bytes_sent + Bytes_received

Server Configuration

Show all server variables:

show variables;

Slow SQL

Slow SQL refers to queries whose execution time exceeds the long_query_time setting.

Enable the slow‑query log and set an appropriate threshold, then locate the log file and use mysqldumpslow to summarize the most expensive statements.

Common mysqldumpslow usages:

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

./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log

Note: the tool shows query patterns, not full SQL text.

Common Slow‑SQL Optimizations

Avoid Subqueries

SELECT ... FROM t1 WHERE id (SELECT id FROM t2 WHERE name='...');

In MySQL 5.5 subqueries are executed as nested loops; newer versions rewrite them to joins.

Avoid Functions on Indexed Columns

SELECT ... FROM t WHERE YEAR(d) >= 2016;

Replace with a range condition that can use the index:

SELECT ... FROM t WHERE d >= '2016-01-01';

Replace OR with IN

SELECT ... FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

Better:

SELECT ... FROM t WHERE LOC_ID IN (10,20,30);

Use Prefix LIKE

SELECT ... FROM t WHERE name LIKE '%de%';

Prefer:

SELECT ... FROM t WHERE name LIKE 'de%';

Disable Unnecessary Sorting in GROUP BY

SELECT goods_id, COUNT(*) FROM t GROUP BY goods_id ORDER BY NULL;

Avoid Unneeded ORDER BY

SELECT COUNT(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

Conclusion

Effective MySQL monitoring relies on understanding the essential metrics rather than the sheer number of tools; using native SHOW commands minimizes overhead while providing the data needed for performance tuning.

When selecting a monitoring solution, ensure it does not impose significant load on the database server.