Master MySQL Monitoring: Built‑in Commands for Fast, Low‑Impact Insights

There are many ways to monitor databases, divided into native, commercial, and open‑source solutions; for MySQL, the abundant community support offers numerous options, and the core of any method is collecting comprehensive monitoring data and presenting it flexibly. This article shows how to use MySQL’s built‑in commands to gather monitoring metrics with minimal overhead in a single‑node setup.

1 Connects

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

show status like 'Threads_connected'

2 Buffer Cache

Number of reads not from buffer pool: show status like 'Innodb_buffer_pool_reads' Number of reads from buffer pool: show status like 'Innodb_buffer_pool_read_requests' Total pages in buffer pool: show status like 'Innodb_buffer_pool_pages_total' Free pages in 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 Locks

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 (non‑empty result indicates a lock)

Note: Lock‑wait counts 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: If mysqldumpslow fails, copy the log locally before formatting.

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 Throughputs

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

Bytes_sent + Bytes_received

7 Server Configuration

Retrieve all variables:

show variables

8 Slow SQL

Slow SQL refers to queries whose execution time exceeds long_query_time. MySQL provides a slow‑query log (slowlog) to record such statements. To monitor slow SQL:

Enable the slow‑query log.

Set long_query_time to the desired threshold (seconds).

Locate the slow‑query log file.

Use mysqldumpslow to format and analyze the log.

Common mysqldumpslow commands:

Top 10 most‑frequent slow queries: ./mysqldumpsslow -s c -t 10 /export/data/mysql/log/slow.log Top 3 slowest queries by execution time: ./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log The tool shows aggregated information such as count, average time, lock time, and rows examined, but not the full original SQL text.

Common Slow‑SQL Pitfalls

Avoid subqueries; they may cause full table scans in older MySQL versions.

Avoid functions on indexed columns (e.g., YEAR(date)) which prevent index usage.

Replace multiple OR conditions with IN.

Use leading‑wildcard‑free LIKE patterns to enable index use.

Disable unnecessary sorting in GROUP BY with ORDER BY NULL.

Remove superfluous ORDER BY clauses when not needed.

Conclusion

Focus on the substance of monitoring rather than flashy tools.

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

Understanding the essence of monitoring and selecting metrics that match your project’s characteristics is more important than the variety of tools.

When choosing a monitoring solution, consider its own overhead on the database server.