Data Management

Monitoring MySQL with mytop

Vincent Danen tells you how to use mytop to monitor MySQL threads and database performance. Here are a few pointers about configuration and getting started.

Mytop is a tool written in Perl for monitoring MySQL databases. Similar to how top monitors system processes, mytop monitors MySQL threads and the database's overall performance, allowing system administrators or developers to get some insight on how applications are interacting with a database.

Mytop is included in the Fedora repositories, so it is just a yum install away. If you are using Red Hat Enterprise Linux or CentOS, mytop is available via the RPMForge third-party repositories. Other distributions may provide mytop as a package, or you can install it from source by downloading it from the Web site.

Mytop requires credentials to access the database, which can be provided via a prompt, on the command-line, or stored in the configuration file. In the interest of security, the best method is to use the —prompt option to mytop, which asks for the password each time. If you prefer, you can store the password in the configuration file. Avoid using the -p option that allows you to provide the password as one of the command-line arguments; that will display the password in the process list for any user with access to the ps command to view.

The configuration file mytop uses is ~/.mytop:

user=root
#pass=sekret
host=localhost
db=test
#port=3306
socket=/var/lib/mysql/mysql.sock
header=1
color=1

The above defines the defaults mytop will use. Here you can use the pass option to store the password in the file; if you do, make sure that ~/.mytop is mode 0600 so no one other than you can read it. Also, if connecting to a local MySQL server, using the MySQL socket is better; if that is not an option use the port option to connect to the localhost on that port (default MySQL port is 3306). Provide the socket option the full path to the mysql.sock socket file (typically this file is where the MySQL databases are stored, such as /var/lib/mysql/).

To start mytop, use:

$ mytop —prompt
Password:

Enter your credentials for the chosen user (the user option in ~/.mytop) and you will see a top-like output:

MySQL on localhost (5.1.44)                                  up 5+20:03:30 [11:56:50]
 Queries: 91.3k  qps:    0 Slow:     2.0         Se/In/Up/De(%):    01/98/00/00
             qps now:    2 Slow qps: 0.0  Threads:    3 (   2/   0) 22/00/00/00
 Key Efficiency: 93.3%  Bps in/out:  43.9/  9.0   Now in/out: 111.1/ 3.0k
      Id      User         Host/IP         DB      Time    Cmd Query or State
       —      ——         ———-         —      ——    —- —————
      193        rq       localhost        rqp         0  Query show full processlist
      195        rq       localhost        rqp         0  Query SELECT DISTINCT p_tag
      190        rq       localhost        rqp         4  Sleep

One downside to mytop is that it won't display information on multiple databases at one time, so if you have a server with multiple databases, you can only observe one at a time. The advantage to mytop, however, is that it can help diagnose problems with a slow database, or help you to fine tune your code by showing you slow queries, breaking down individual tasks, and so forth. While it may not be as useful as some other development tools, it is light-weight and easy to install and use on a remote server to keep an eye on key MySQL databases.

Get the PDF version of this tip here.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks

Free Newsletters, In your Inbox