Using MySQL Built‑in Commands for Comprehensive Database Monitoring

MySQL offers several monitoring approaches, but the most fundamental is to obtain comprehensive monitoring data directly from the database itself.

In this article we demonstrate how to use MySQL's built‑in SHOW commands to gather monitoring data quickly, conveniently, and with minimal overhead in a monolithic deployment.

Connects

Maximum used connections:

show status like 'Max_used_connections'

Current open connections:

show status like 'Threads_connected'

Buffer Cache

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:

show status like 'Innodb_row_lock_time_avg'

Check for table locks:

show open TABLES where in_use>0;

Note: lock wait counts are cumulative; subtract previous values to obtain the current interval.

SQL

Check if the slow‑query log is enabled:

show variables like 'slow_query_log';

If OFF, enable it:

set global slow_query_log=1;

View the slow‑query 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 (command‑line only):

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

This command displays the top 10 longest‑running SQL statements, showing execution count, average time, and the statement itself.

Statement

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';

Database Throughputs

Bytes sent:

show status like 'Bytes_sent';

Bytes received:

show status like 'Bytes_received';

Total throughput:

Bytes_sent + Bytes_received

Server Config

Show all server variables:

show variables;

Slow SQL

Slow SQL refers to queries whose execution time exceeds long_query_time . Enabling the slow‑query log and setting an appropriate threshold allows developers to monitor and optimize costly statements.

Typical steps:

Ensure the slow‑query log is enabled ( slow_query_log ).

Set the threshold ( long_query_time ).

Locate the log file.

Use mysqldumpslow to summarize the log.

Common Slow‑SQL Troubleshooting

Avoid Subqueries

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

In MySQL 5.5 subqueries are executed by scanning the outer table first, which can be very slow; newer versions rewrite them to joins.

Avoid Function Indexes

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

MySQL cannot use indexes on functions; rewrite as a range condition:

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%';

Cannot use index; use a leading‑wildcard‑free pattern:

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

Disable Unnecessary ORDER BY 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;

Summary

Effective monitoring focuses on the essential metrics rather than the sheer number of tools; MySQL’s performance_schema provides the necessary data when enabled.

Choose monitoring methods that suit your project’s characteristics and avoid tools that impose excessive overhead on the database server.

Author: 安甲舒 Link: my.oschina.net/u/4090830/blog/5564849