Friday, 24 April 2009

Getting MySQL Status Values With mysqlreport

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 2009

__ 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:/#

No comments: