MySQL Monitor: MySQL Monitoring Script

As a sysadmin or DBA, having a quick way to monitor MySQL’s performance metrics can save a lot of time. Over the years, I’ve used many tools, but sometimes you just want something simple and lightweight but at the same time provides a ton of actionable insight. That’s where this script comes in.

I designed this script to provide a quick overview of the most important MySQL metrics and their purpose. It’s not meant to replace deeper tools like these. It’s mainly for those moments when you need to jump into the terminal and get the most important stats and move on.

mysql-monitor-script: for sysadmins and DBAs. (screenshot)

What MySQL Monitor Does

The script uses mysqladmin extended-status to pull a selection of MySQL metrics and combines them with system memory stats for an overall view. The output is clean and formatted for readability, with explanations to help you understand the values. Here’s what it does:

• InnoDB Metrics: Tracks key buffer pool stats like free pages and write requests.
• Temporary Tables: Shows the number of temporary tables created in memory and on disk.
• Query Performance: Slow queries, table scans and unoptimized joins.
• Connection Stats: Monitors connected threads, cached threads and aborted connections.
• Memory Usage: Shows system memory, free and available memory with warnings when resources are critically low.

It’s minimalist by design, only showing the metrics database administrators care about during routine checks.

Get MySQL Monitor

So I’ve been using this script for a long while now, and I figured it could be useful for others too.

You can download “MySQL Monitor” via GitHub.

Understanding the Output

The MySQL Monitor script is for sysadmins and DBAs who need a quick and dirty view of their MySQL database performance. While the script itself gives you the metrics at a glance, understanding what each variable means will make the output even more useful. Below you’ll find a brief explanation of each variable, what it’s for and how to read it.

Innodb_buffer_pool_size

This is the total size of the InnoDB buffer pool, used for caching data and indexes. A properly sized buffer pool minimizes disk I/O. If your workload is write-heavy and this is too low, you’ll see a lot of disk reads and writes which is bad for performance.

Innodb_buffer_pool_pages_free

The number of free pages in the buffer pool. It should never be zero, or too low, so that the buffer has space to cache. If this is consistently low, you might need to increase innodb_buffer_pool_size.

Innodb_buffer_pool_wait_free

How many times MySQL had to wait for a free buffer page. A non-zero value here is a red flag, means writes are being delayed because of insufficient buffer pool space.

Innodb_buffer_pool_write_requests

Number of write requests to the buffer pool. High is normal in write heavy environments. But if combined with high disk I/O you should look into optimizing your queries or increasing buffer size.

Innodb_os_log_fsyncs

Number of fsync() to the redo log file. This is critical for transaction durability. High value is expected in write heavy workloads. If this number goes up unexpectedly, check disk performance.

Innodb_data_fsyncs

Number of fsync() to flush data files to disk. This often spikes during checkpoints. High values mean a lot of disk writes, which can be optimized by increasing buffer pool size or transaction commit settings.

Innodb_log_waits

This is the number of waits for log buffers to flush. Even a small non-zero value means your innodb_log_buffer_size is too small for your workload.

Threads_connected

Number of active connections to the server. A high value compared to max_connections means you need connection pooling or query optimization to handle the load.

Threads_running

Number of threads running queries. A high value compared to total connections means long-running queries or contention issues that need to be looked into.

Slow_queries

Number of queries that took longer than long_query_time. This should be monitored closely, a high-value could mean poorly optimized queries or slow hardware. Use the slow query log to fix them.

Key_reads

Number of index blocks read directly from disk. A low value is better because frequent disk reads indicate that the key buffer is too small to cache your indexes effectively.

Key_writes

Number of index blocks written to disk. Zero or a low/slow incrementing value in comparison to QPS (queries per second) is always good, means no unnecessary writes to disk. Use the script’s 10 second auto-refresh to gauge the frequency/count of disk writes.

Open_tables

Tables open by the server. If this approaches table_open_cache then queries will start to slow down. Increase table_open_cache if needed.

Opened_tables

Total number of tables opened since the server started. A high-value can indicate frequent table cache misses, which can often be addressed by tuning table_open_cache.

Questions

All statements sent to the server, including queries, commands, and other client activity. Not just queries.

Select_scan

Number of full table scans. If this is high, means missing indexes. Optimize queries with proper indexing will reduce this value.

Table_locks_waited

Number of table lock waits. Non-zero value means contention. Switch to InnoDB or optimize locking in queries.

Sort_merge_passes

Number of merge passes during a sort operation. High-value means sort_buffer_size is too small and forcing disk use. Increase this buffer size with caution!!

Innodb_data_reads

Number of reads directly from disk. High-value can indicate the buffer pool is too small, or your workload is read heavy.

Innodb_data_writes

Number of writes to InnoDB data files. High value is normal for write heavy workloads, but unexpected spikes need to be looked into.

Uptime

Shows how long the server has been running. Longer is better but also a reminder to monitor memory and cache usage as they can degrade over time. For accurate stats, you should have at least 24 hours of uptime before relying heavily on MySQL’s runtime stats.

InnoDB Buffer Pool Free

Amount of free memory in the InnoDB buffer pool. A high value means there is enough memory for caching MySQL data and indexes, reducing the need for disk I/O. If this is low, consider increasing innodb_buffer_pool_size to avoid performance problems due to disk I/O.

Queries per Second (QPS)

Number of queries per second. This is the load on the database. High QPS is normal for busy servers, but sudden spikes may be due to application inefficiencies or unoptimized queries hitting the server.

Temp Tables on Disk

Percentage of temp tables created on disk instead of in memory. Disk-based temp tables are much slower than in-memory ones, so this should be as close to 0% as possible. High values mean you should increase tmp_table_size or max_heap_table_size to reduce disk usage for temp tables.

Thread Cache Hit Ratio

Percentage of MySQL threads reused from the thread cache instead of creating new threads. High ratio (close to 100%) means efficient thread handling which is critical for fast connection responses. Low values mean you should increase thread_cache_size to improve connection handling under load.

Table Cache Hit Ratio

Percentage of table accesses served from the table cache instead of opening the table. High ratio reduces table opening overhead and query response times. If this is low, increasing table_open_cache can help with workloads that access multiple tables frequently.

InnoDB Buffer Pool Hit Ratio

Percentage of read requests served directly from the InnoDB buffer pool instead of disk reads. A high ratio (close to 100%) means most reads are cached in memory. If this goes below 90% increase innodb_buffer_pool_size to reduce disk I/O and improve read performance—especially when there is high QPS!

Total Memory

Total system memory on the server. This gives context to how well MySQL can use the available resources, especially for memory-hungry settings like InnoDB buffer pool and query cache.

Used Memory

Amount of system memory in use. High memory usage means MySQL processes are consuming a lot of resources. If combined with low available memory, check memory allocation parameters like innodb_buffer_pool_size  and all the *_buffer_size settings Also read: MySQL Database Performance: Avoid this common mistake.

Free Memory

Amount of system memory not in use. While free memory alone doesn’t tell the whole story, low free AND low available memory consistently means MySQL is running close to the server’s limits.

Available Memory

Memory available for MySQL to allocate for new processes or caches. This is more accurate than free memory, as it accounts for buffers and cache that can be reused. If this goes below 10% MySQL performance can degrade and queries take longer due to memory shortage. The script has a built-in warning for this.

These variables and additional metrics give you a good view of your MySQL database health. By knowing what they are and how to interpret them, you can troubleshoot performance issues and optimize your server with confidence. Haven’t tried the script yet? Download from GitHub and let me know how it goes!

Also, read addition articles tagged “MySQL”…

Get in Touch

If you have suggestions, improvements, or even better ways to write this script, reply in the comments section below or on GitHub. I’m sure, I’m missing that one thing, or more, ha! Or, didn’t approach something the best way. I guess that’s also why I’m sharing. Keep in mind that it’s just a basic script I used to save time. It’s not anything groundbreaking. But hopefully, you find it as useful as I do.

Happy monitoring!