How to Collect Comprehensive MySQL Metrics Using Only Built‑In SHOW Commands

MySQL offers three major monitoring approaches—native, commercial, and open‑source—but the most lightweight method is to use MySQL’s own SHOW commands. This article demonstrates how to extract a full set of performance metrics from a single MySQL instance without external tools.

1. Connection Metrics (Connects)

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

show status like 'Threads_connected'

2. Buffer Cache Metrics

Reads not satisfied from the buffer pool: show status like 'Innodb_buffer_pool_reads' Requests served 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:

(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. Lock Metrics

Number of lock waits: show status like 'Innodb_row_lock_waits' Average lock wait time: show status like 'Innodb_row_lock_time_avg' Tables currently locked: show open tables where in_use>0 (non‑empty result indicates table locks)

Lock‑wait counts are cumulative; subtract previous snapshots to obtain interval values.

4. SQL‑Related Settings

Slow‑query log enabled: show variables like 'slow_query_log' (set with set global slow_query_log=1 if OFF)

Slow‑query threshold: show variables like 'long_query_time' (adjust with set global long_query_time=0.1)

Slow‑query log file path: show variables like 'slow_query_log_file' Format slow‑query log with mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log (shows top 10 longest queries with count, avg time, and query pattern)

5. Statement Execution Counts

INSERT: show status like 'Com_insert' DELETE: show status like 'Com_delete' UPDATE: show status like 'Com_update' SELECT:

show status like 'Com_select'

6. Throughput (Bytes Sent/Received)

Bytes sent: show status like 'Bytes_sent' Bytes received: show status like 'Bytes_received' Total throughput: sum of the two values

7. Server Configuration Variables

Retrieve all current system variables with show variables. The article includes a screenshot of the output.

8. Slow‑Query Analysis

Slow queries are those whose execution time exceeds long_query_time. The guide walks through enabling the slow‑query log, setting the threshold, locating the log file, and using mysqldumpslow to summarize the log. Example command:

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

Typical output fields:

Count – number of executions

Time – average execution time

Lock – lock time

Rows – rows examined per execution

The tool shows query patterns rather than full statements, which is sufficient for identifying problematic SQL.

9. Common Slow‑SQL Pitfalls and Optimizations

Avoid subqueries in MySQL 5.5 (they cause full‑table scans); rewrite as joins when possible.

MySQL lacks functional indexes; replace functions on indexed columns with range predicates (e.g., WHERE d >= '2016-01-01').

Replace multiple OR conditions with IN lists for better index usage.

Leading wildcard LIKE patterns ( %term%) cannot use indexes; use prefix patterns ( term%) instead.

Disable unnecessary sorting in GROUP BY by adding ORDER BY NULL.

Remove superfluous ORDER BY clauses when they are not needed for the result set.

10. Summary

The article emphasizes that effective MySQL monitoring relies on understanding the essential metrics rather than the sheer number of tools. Using native SHOW commands provides low‑overhead visibility into connections, buffer usage, locks, statement activity, throughput, configuration, and slow queries, enabling administrators to select monitoring strategies that suit their specific workloads without adding significant load to the database server.