Mytop is a very useful program to see what all queries a server is currently processing as well as which user is executing them. Think of mytop as top for mysql. If you see a lot from one particular user, that means they are probably the hog. Mytop can also be useful for figuring out exactly which queries are causing the problem in case you have a self-designed website.
Mytop is the utility for monitoring the performance of mysql server. If you are a regular Linux user, you might already know about the Linux utility top which is used to monitor the running system. Mytop is a similar utility that monitors performance and lists threads of mysql server. Developed and maintained by Jeremy D. Zawodny, mytop is released under GNU GPL (version 2), and is available for windows as well. This tutorial provides an overview of mytop tool.
Mytop can be downloaded from Jeremy Website.
It needs some additional packages for its proper functioning. Ensure that the following packages are installed on your system:
Perl 5.005 or newer Config::IniFiles Getopt::Long DBI::mysql and DBD::mysql Term::ReadKey from CPAN
Term::ReadKey can be downloaded from CPAN
Others are likely to be installed on most of the systems. If you want color support in mytop, install Term::ANSIColor from CPAN.
With all of these installed, your system is now ready to run mytop.
Mytop syntax is simple as shown below:
By default mytop uses username as "root" and password none. You can specify user with "-u user" option and password with "-p password". Once you have provided required password, mytop will present you with an interactive screen that looks like this:
MySQL on localhost (5.1.63-0ubuntu0.11.04.1) up 0+01:45:40 [10:40:38] Queries: 1.1k qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00 qps now: 0 Slow qps: 0.0 Threads: 2 ( 1/ 0) 00/00/00/00 Key Efficiency: 100.0% Bps in/out: 4.0/649.7 Now in/out: 8.4/ 1.5k Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- -------------- 40 root localhost 0 Query show full processlist 35 raghu localhost 5024 Sleep
Mytop is an interactive command. It will wait for user input once you run it and your prompt is missing. To quit from mytop, just hit ‘q’.
Mytop output is divided into two parts. The first part is header, the top 4 lines are presented here:
MySQL on localhost (5.1.63-0ubuntu0.11.04.1) up 0+01:45:40 [10:40:38] Queries: 1.1k qps: 0 Slow: 0.0 Se/In/Up/De(%): 00/00/00/00 qps now: 0 Slow qps: 0.0 Threads: 2 ( 1/ 0) 00/00/00/00 Key Efficiency: 100.0% Bps in/out: 4.0/649.7 Now in/out: 8.4/ 1.5k
First line displays hostname of server ('localhost' here) and version of mysql (5.1.63-0ubuntu0.11.04.1) with platform. It also shows uptime of mysql server in ‘days+hours:minutes:seconds’ and current time.
Second line displays total number of queries that have been processed, average queries per second, average number of slow queries and percentage of SELECT, INSERT, UPDATE and DELETE queries. Third line is like 2nd line, but shows real-time values. Fourth line is for the server performance. It shows Key Efficiency, i.e. how many keys are read from the buffer, than from hard disk. It follows number of bytes sent and received so far, and the real time values (i.e. in the last cycle).
The second part shows threads being processed by the server.
Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- -------------- 40 root localhost 0 Query show full processlist 35 raghu localhost 5024 Sleep
The following information is displayed in the above output:
• Thread IDs • User connected with server • Hostname from which a user is connected • The database they are connected to • Idle time • Command executed by thread • Query info of the thread
These threads are sorted by their idle time by default.
Interacting with mytop
You can press ‘?’ to see what commands are available at any given time in mytop output. When you press ‘?’, the following is displayed:
Help for mytop version 1.6 by Jeremy D. Zawodny <Jeremy@Zawodny.com>
? - display this screen # - toggle short/long numbers (not yet implemented) c - command summary view (based on Com_* counters) d - show only a specific database e - explain the query that a thread is running f - show full query info for a given thread F - unFilter the dispaly h - show only a specifc host's connections H - toggle the mytop header i - toggle the display of idle (sleeping) threads I - show innodb status k - kill a thread p - pause the display m - switch [mode] to qps (queries/sec) scrolling view o - reverse the sort order (toggle) q - quit r - reset the status counters (via FLUSH STATUS on your server) s - change the delay between screen updates t - switch to thread view (default) u - show only a specific user : - enter a command (not yet implemented)
Visit Jeremy website for more help
Mytop Configuration file
Mytop provides you with a lot of command line options for specifying username, password, hostname, database to connect to, port number etc. But all of these parameters can be stored in a configuration file in your home directory, so that you don't have to provide them at command line every time you run mytop. A sample configuration file given in the manual page of mytop command is:
user=root pass= host=localhost db=test delay=5 port=3306 socket= batchmode=0 header=1 color=1 idle=1
In this file,
• user is the username with which command is run (default: root), • pass is the password for that user (default none), • host is hostname of mysql server (dafault: localhost), • db is the database to connect to (default: none), • delay is the delay (in seconds) between refreshing the myoutput screen (default: 5), • port specifies the port at which mysql is running (default: 3306), • socket is the location of socket file, if connection is to be made through socket instead of standard tcp/ip connection (default: none), • batchmode specifies if the command is to be run non interactively, just once and exit (default: unset (or 0)), • If header is 0 (or noheader), header part will not be displayed. Header can be toggled with h key later (default is header (1)), • color specifies if you want colors in the output or not (default is color, if you have color support), • idle species whether you want to display idle threads or not (default: 1).
This must be stored in a file named ".mytop" in the home directory of a user (hidden file).