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:
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
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.