Quick Reference: Simple MySQL Performance Monitoring
This post is more than 18 months old. Technology changes quickly, so some of this content may be out of date (but that's not always the case). Please verify any technical or programming information against the current release.
There are a few quick ways to monitor MySQL performance. This isn’t much of an in-depth reference, just a quick reminder. Let’s look:
mysqladmin status
Shows a quick status:
mysqladmin status
Uptime: 102594 Threads: 1 Questions: 39 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 0 Queries per second avg: 0.000mysqladmin processlist
Show the active processes and what they’re doing:
mysqladmin processlist
+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------+----+---------+------+-------+------------------+
| 25 | root | localhost:3185 | | Query | 0 | | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+mysqladmin extended
This is short for extended-status, which shows you pretty much everything you want to know about the current system.
mysqladmin extended
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
<snip>
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
| Uptime | 102658 |
+-----------------------------------+----------+Add iterations or relative comparisons with -i and -r. For example, the following updates the list every 10 seconds with relative numbers (shows change well).
mysqladmin extended -i10 -rOther Resources
MySQL.com’s explanation of some performance monitoring options