Getting MySQL Status Values With mysqlreport

Version 1.0
Author: Falko Timme

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.

 

Falko Timme

About Falko Timme

Falko Timme is an experienced Linux administrator and founder of Timme Hosting, a leading nginx business hosting company in Germany. He is one of the most active authors on HowtoForge since 2005 and one of the core developers of ISPConfig since 2000. He has also contributed to the O'Reilly book "Linux System Administration".

Share this page:

Suggested articles

0 Comment(s)

Add comment