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

1 Connects (Connects)

Maximum used connections: show status like 'Max_used_connections'

Current open connections: show status like 'Threads_connected'

2 Buffer Cache (bufferCache)

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 buffer pool pages: show status like 'Innodb_buffer_pool_pages_total'

Free buffer pool pages: 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)

Lock wait count: show status like 'Innodb_row_lock_waits'

Average lock wait time: show status like 'Innodb_row_lock_time_avg'

Table lock existence: show open TABLES where in_use>0 (non‑empty result indicates a table lock)

Note: Lock wait numbers are cumulative; subtract previous values to obtain current statistics.

4 SQL

Check if slow‑query log is enabled: show variables like 'slow_query_log' (ON means enabled; set with set global slow_query_log=1 )

View slow‑query threshold: show variables like 'long_query_time' (adjust with 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 (shows top 10 longest queries; can change the number)

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

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 (Database throughputs)

Bytes sent: show status like 'Bytes_sent'

Bytes received: show status like 'Bytes_received'

Total throughput: Bytes_sent + Bytes_received

7 Server Configuration (serverconfig)

Retrieve all variables:

show variables

8 Slow SQL

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

MySQL provides a dedicated slow‑query log (slowlog) to record such statements.

Enable slow‑query log:

set global slow_query_log=1

Set threshold: set global long_query_time=1 (queries >1 s are considered slow)

Find slow‑query log path:

show variables like 'slow_query_log_file'

Analyze with mysqldumpslow (built‑in tool). Example commands:

Top 10 most frequent slow queries: ./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log Top 3 longest‑running slow queries: ./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log Note: mysqldumpslow shows query patterns, not full SQL text.

9 Common Slow‑SQL Troubleshooting Tips

Avoid subqueries; rewrite as joins when possible.

Do not use functions on indexed columns (e.g., WHERE YEAR(date) >= 2016 ); compare raw values instead.

Replace multiple OR conditions with IN lists.

Use left‑anchored LIKE patterns (e.g., name LIKE 'de%' ) to enable index usage.

Disable sorting for GROUP BY when not needed: add ORDER BY NULL .

Remove unnecessary ORDER BY clauses that force extra sorting.

10 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 after proper initialization.

Effective monitoring requires understanding core metrics and selecting methods that suit the specific project.

Choose monitoring tools that impose minimal overhead on the database server.