Thread: mytop - See What's Slowing Your MySQL Server

mytop is an interesting and useful little utility program for monitoring your MySQL server.

In concept it is very similar to Unix's top command, which shows you the top processes (in terms of CPU usage) on your Unix system.

Here's a sample of program output from just a moment ago:

Code:

MySQL on mercury (5.1.32-log)                                                                                                                      up 1+17:31:48 [20:29:26]
 Queries: 15.0   qps:    0 Slow:     0.0         Se/In/Up/De(%):    45761547/00/00/00 
             qps now:    1 Slow qps: 0.0  Threads:    7 (   4/   1) 2875/00/00/00 
 Key Efficiency: 100.0%  Bps in/out:   0.0/  0.2   Now in/out:  16.8/ 1.5k
 Master: mysqlLOGb.000007/158175944 do:  ign: 

      Id      User         Host/IP         DB      Time    Cmd Query or State                                                                                              
      --      ----         -------         --      ----    --- ----------                                                                                                  
  798824      root         jupiter netbuilder         0  Query show full processlist                                                                                       
  798893      root         jupiter netbuilder         0  Query DELETE FROM blab_online WHERE usr_id=1 OR (1240194566-rtime)>12                                             
  798894      root         jupiter tech-faq-p         0  Query SELECT proxy FROM pl_proxy WHERE id = 33976                                                                 
  798852      root         jupiter netbuilder         1  Query select cache from carpautocache where id="9f4ad1ed051de475f72e2aa7b64e1ec6"                                 
  798890      root         jupiter tech-faq-p         1  Sleep                                                                                                             
  798864      root         jupiter howtodothe         6  Sleep                                                                                                             
  798851      root         jupiter netbuilder         9  Sleep

As you may be able to tell, mytop is getting this data from the MySQL SHOW PROCESSLIST and SHOW STATUS commands. mytop refreshes this data automatically and presents it in a more usable format.

None of this is exactly easy to read, but if you're troubleshooting a nasty MySQL performance issue you'll take the time to work through it.

A more general command is 'top'. My host doesn't come with 'mytop'.

top is good for monitoring server processes, but has no visibility inside MySQL.

Won't your host allow you to install applications?