Using MySQL Built‑in SHOW Commands for Comprehensive Database Monitoring

There are many ways to monitor a database, divided into three categories: built‑in, commercial, and open‑source solutions. For MySQL, which has a very active community, the most important part of any monitoring method is the data itself; once comprehensive data is gathered, it can be displayed flexibly.

1. Connections (Connects)

show status like 'Max_used_connections' – maximum number of connections used

show status like 'Threads_connected' – current open connections

2. Buffer Cache

show status like 'Innodb_buffer_pool_reads' – reads not satisfied from the buffer pool

show status like 'Innodb_buffer_pool_read_requests' – total read requests from the buffer pool

show status like 'Innodb_buffer_pool_pages_total' – total pages in the buffer pool

show status like 'Innodb_buffer_pool_pages_free' – free pages in the buffer pool

Cache hit rate: (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

Cache usage: ((Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total) * 100%

3. Locks

show status like 'Innodb_row_lock_waits' – number of lock waits

show status like 'Innodb_row_lock_time_avg' – average lock wait time

show open tables where in_use>0 – shows if any table locks exist (non‑empty result means locked tables)

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

4. SQL

show variables like 'slow_query_log' – whether the slow‑query log is enabled (ON/OFF). Enable with set global slow_query_log=1 .

show variables like 'long_query_time' – current slow‑query threshold; adjust with set global long_query_time=0.1 .

show variables like 'slow_query_log_file' – path of the slow‑query log file.

Format slow‑query logs: mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log (shows top 10 longest‑running statements). This command must be run from the shell, not via JDBC.

5. Statement Counts

show status like 'Com_insert' – number of INSERT statements

show status like 'Com_delete' – number of DELETE statements

show status like 'Com_update' – number of UPDATE statements

show status like 'Com_select' – number of SELECT statements

6. Throughput (Database Throughputs)

show status like 'Bytes_sent' – bytes sent by the server

show status like 'Bytes_received' – bytes received by the server

Total throughput: Bytes_sent + Bytes_received

7. Server Configuration (serverconfig)

show variables

8. Slow SQL

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

MySQL provides several logs (binary log, relay log, redo log, undo log) and a dedicated slow‑query log ( slowlog ) that records statements taking longer than the threshold.

To monitor slow queries during performance testing, enable the slow‑query log, set an appropriate threshold, locate the log file, and use mysqldumpslow to analyze it.

Enable slow‑query log: set global slow_query_log=1

Set threshold: set global long_query_time=1 (1 second in this example)

Find log path with show variables like 'slow_query_log_file'

Analyze with mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log (top 10 most frequent queries)

Analyze with mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log (top 3 slowest queries)

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

Example output:

Count: 2 Time=1.5s (3s) Lock=0.00s (0s) Rows=1000.0 (2000), vgos_dba[vgos_dba]@[10.130.229.196]SELECT FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N

Count – number of executions of this pattern

Time – average execution time

Lock – lock time (usually 0)

Rows – rows returned per execution

Using this tool helps identify expensive SQL statements for further optimization (e.g., adding indexes).

9. Summary

Focus on the substance of monitoring rather than the variety of tools.

MySQL monitoring can be performed via SQL queries against the performance_schema database, provided it is initialized and data collection is enabled.

Select monitoring items that match the characteristics of your project.

When choosing a monitoring tool, consider its overhead on the database server to avoid impacting normal operations.

For further learning, the author promotes a big‑data and data‑analysis course (advertisement).

Original source: https://my.oschina.net/u/4090830/blog/5564849