Comprehensive MySQL Monitoring Using Built‑in SHOW Commands

1. Connections (Connects)

Maximum used connections: show status like 'Max_used_connections'

Current open connections: show status like 'Threads_connected'

2. Buffer Cache

Reads not from buffer pool: show status like 'Innodb_buffer_pool_reads'

Buffer pool read requests: 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 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 statistics are cumulative; subtract previous values to obtain current increments.

4. SQL

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

View slow‑query threshold: show variables like 'long_query_time' ; modify with set global long_query_time=0.1 .

Locate slow‑query log file: show variables like 'slow_query_log_file'

Format slow‑query log (command‑line only): mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log This displays the top 10 longest‑running queries with execution count, average time, and query pattern.

5. Statement Counts

Insert statements: show status like 'Com_insert'

Delete statements: show status like 'Com_delete'

Update statements: show status like 'Com_update'

Select statements: show status like 'Com_select'

6. Throughputs

Bytes sent: show status like 'Bytes_sent'

Bytes received: show status like 'Bytes_received'

Total throughput: sum of Bytes_sent and Bytes_received.

7. Server Configuration

All server variables: show variables

8. Slow SQL

Slow queries are those whose execution time exceeds long_query_time . Enable the slow‑query log and set an appropriate threshold.

Use mysqldumpslow to analyze the log, e.g.: ./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log (top 10 most frequent) ./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log (top 3 longest). The tool reports count, average time, lock time, rows, and a query pattern (not the full SQL).

9. Common Optimization Tips

Avoid subqueries in SELECT; use JOIN instead.

Do not rely on function‑based indexes; rewrite date filters to use raw column values.

Replace multiple OR conditions with IN.

Use a leading pattern in LIKE (e.g., name LIKE 'de%' ) to enable index usage.

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

Remove unnecessary ORDER BY clauses.

10. Conclusion

MySQL monitoring can be performed entirely via SQL queries against the performance_schema database, provided it is initialized and data collection is enabled. Effective monitoring focuses on understanding essential metrics rather than the sheer number of tools, and selecting lightweight methods that do not add significant overhead to the database server.