MySQL maintains numerous operational metrics (e.g., connections, questions, etc), which can be accessed by running ’show status’ or one of it’s variants from the mysql client. The mysqlreport Perl script can be used to summarize this data into a nicely formatted report with several useful performance metrics:
$ mysqlreport –user privuser –password password -all
MySQL 5.0.24 uptime 0 10:20:0 Tue Aug 8 01:05:17 2006 __ Key _________________________________________________________________ Buffer usage 77.00k of 64.00M %Used: 0.12 Write ratio 0.04 Read ratio 0.02 __ Questions ___________________________________________________________ Total 3.55k 0.10/s QC Hits 2.11k 0.06/s %Total: 59.38 DMS 964 0.03/s 27.15 Com_ 240 0.01/s 6.76 COM_QUIT 238 0.01/s 6.70 Slow 0 0.00/s 0.00 %DMS: 0.00 DMS 964 0.03/s 27.15 SELECT 935 0.03/s 26.34 96.99 UPDATE 17 0.00/s 0.48 1.76 INSERT 12 0.00/s 0.34 1.24 REPLACE 0 0.00/s 0.00 0.00 DELETE 0 0.00/s 0.00 0.00 Com_ 240 0.01/s 6.76 change_db 238 0.01/s 6.70 show_variab 1 0.00/s 0.03 show_status 1 0.00/s 0.03 __ SELECT and Sort _____________________________________________________ Scan 566 0.02/s %SELECT: 60.53 Range 44 0.00/s 4.71 Full join 0 0.00/s 0.00 Range check 0 0.00/s 0.00 Full rng join 0 0.00/s 0.00 Sort scan 558 0.01/s Sort range 49 0.00/s Sort mrg pass 0 0.00/s __ Query Cache _________________________________________________________ Memory usage 3.06M of 16.00M %Used: 19.09 Block Fragmnt 0.06% Hits 2.11k 0.06/s Inserts 932 0.03/s Prunes 1 0.00/s Insrt:Prune 932:1 0.03/s Hit:Insert 2.26:1 __ Table Locks _________________________________________________________ Waited 0 0.00/s %Total: 0.00 Immediate 1.22k 0.03/s __ Tables ______________________________________________________________ Open 19 of 128 %Cache: 14.84 Opened 25 0.00/s __ Connections _________________________________________________________ Max used 2 of 128 %Max: 1.56 Total 240 0.01/s __ Created Temp ________________________________________________________ Disk table 159 0.00/s Table 399 0.01/s File 0 0.00/s
The output from mysqlreport includes metrics on key cache and query cache utilization, the number of operations (SELECT, UPDATE, etc.) performed, thread utilization, connection volumes, table locks, and the types of temporary tables used by sorts. The folks over at hackmysql provide a nice writeup on what each report means, and the typical value ranges you should see in each report. Running mysqlreport is a great way to get a high-level understanding of how a database is performing, and can greatly assist with identifying the areas that are worth reviewing in greater detail.