Getting MySQL Status Values With mysqlreport

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 11/27/2007

mysqlreport is a Perl script that displays a well-formatted report of important MySQL status variables (taken from MySQL's SHOW STATUS; output) that can help you gain an understanding of what is happening under MySQL's hood. It can help diagnose problems.

I do not issue any guarantee that this will work for you!



1 Preliminary Note
mysqlreport works on any distribution. Of course, Perl and MySQL must already be installed and working.



2 Installing mysqlreport
The installation is very easy. Just run:

cd /usr/local/sbin
wget hackmysql.com/scripts/mysqlreport
chmod 755 mysqlreport
cd /

That's it!



3 Using mysqlreport
Run

mysqlreport --help

to get a list of available options:

server2:/# mysqlreport --help
mysqlreport v3.2 May 26 2007
mysqlreport makes an easy-to-read report of important MySQL status values.

Command line options (abbreviations work):
   --user USER       Connect to MySQL as USER
   --password PASS   Use PASS or prompt for MySQL user's password
   --host ADDRESS    Connect to MySQL at ADDRESS
   --port PORT       Connect to MySQL at PORT
   --socket SOCKET   Connect to MySQL at SOCKET
   --no-mycnf        Don't read ~/.my.cnf
   --infile FILE     Read status values from FILE instead of MySQL
   --outfile FILE    Write report to FILE
   --email ADDRESS   Email report to ADDRESS (doesn't work on Windows)
   --flush-status    Issue FLUSH STATUS; after getting current values
   --relative X      Generate relative reports. If X is an integer,
                     reports are live from the MySQL server X seconds apart.
                     If X is a list of infiles, reports are generated
                     from the infiles in the order that the infiles are given.
   --report-count N  Collect N number of live relative reports (default 1)
   --detach          Fork and detach from terminal (run in background)
   --help            Prints this
   --debug           Print debugging information

   Extra Reports:
   --dtq           Show Distribution of Total Questions
   --dms           Show DMS details
   --com N         Show top N number of non-DMS questions
   --sas           Show SELECT and Sort report
   --qcache        Show Query Cache report
   --tab           Show Thread, Aborts, and Bytes reports
   --innodb        Show InnoDB report
   --innodb-only   Show only InnoDB report (hide ALL other reports)
   --dpr           Show Data, Pages, Rows report in InnoDB report
   --all           Show ALL extra reports (if possible)

Visit http://hackmysql.com/mysqlreport for more information.
server2:/#

The standard usage of mysqlreport is as follows:

mysqlreport --user root --password

server2:/# mysqlreport --user root --password
Password for database user root: xxxxxxx
MySQL 4.0.21-log         uptime 533 16:36:2     Tue Nov 27 15:29:50 2007

__ Key _________________________________________________________________
Buffer used    15.22M of  16.00M  %Used:  95.13
Write hit      60.57%
Read hit       99.50%

__ Questions ___________________________________________________________
Total           1.88G    40.7/s
Slow              594     0.0/s  %Total:   0.00  %DMS:   0.00
DMS            57.33M     1.2/s            3.05

__ Table Locks _________________________________________________________
Waited          4.51k     0.0/s  %Total:   0.01
Immediate      72.89M     1.6/s

__ Tables ______________________________________________________________
Open               64 of   64    %Cache: 100.00
Opened          4.04M     0.1/s

__ Connections _________________________________________________________
Max used          354 of  500      %Max:  70.80
Total           5.48M     0.1/s

__ Created Temp ________________________________________________________
Disk table    166.53k     0.0/s
Table           1.23M     0.0/s
File               10     0.0/s
server2:/#

In order to learn what the values in the report mean, take a look at The Guide To Understanding mysqlreport.