Quick Reference: Simple MySQL Performance Monitoring

Apr 7, 2009 mysql
This post is more than 18 months old. Since technology changes too rapidly, this content may be out of date (but that's not always the case). Please remember to verify any technical or programming information with 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.000

mysqladmin 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 -r

Other Resources

MySQL.com’s explanation of some performance monitoring options

MyTop - top clone for mysql

Go to All Posts