O'Reilly logo

Linux Server Hacks by Rob Flickenger

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #81. Monitoring MySQL Health with mtop

Display MySQL threads in real time in a format similar to top

Much like its top counterpart, the mtop utility gives real time, running statistics of your mysql server all in a terminal window. On a busy database server, this can give you very precise details about what queries are running (and taking up all of your resources).

When running mtop, you'll need to pass at least the following two switches on the command line:

mysql --dbuser=monitor --password=n0telling

Naturally, substituting your own database username and password. If you're running mtop from some host other than your database server, also specify the --host={mysql_host} switch. Once it's running, you'll be presented with a top-like screen that refreshes every few seconds:

load average: 0.72, 0.47, 0.26 mysqld 3.23.51 up 33 day(s), 4:48 hrs
2 threads: 2 running, 0 cached. Queries/slow: 71.5K/0 Cache Hit: 99.99%
Opened tables: 42 RRN: 4.0M TLW: 0 SFJ: 0 SMP: 0

ID USER HOST DB TIME COMMAND STATE INFO
26049 root localhost test Query show full processlist
26412 root localhost nocat 1 Query Writing to n select * from Member where 
User like '%rob%'
---

Here we see the attached users, the hosts they are connecting from, the queries they're running (and on which databases), as well as how long each thread has been executing (in seconds). The numbers on the left are the thread ID of each running query, not the mysql PID. If a particular query is in danger of becoming a slow query, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required