How to get a detailed MySQL status report

If you use or administer a MySQL server you probably know what a pain it can sometimes to get a proper status report that you can easily decipher and understand. mysqlreport is an script written in Perl that makes the job a lot easier for you. The script takes the output of the command SHOW STATUS’ in MySQL and displays it in a neatly formatted manner which makes it a lot easier on the eyes, and as a result easier to figure out what’s going on.

Let’s take a closer look at it by installing it. I’ll be using a Linux machine for this exercise. I’m assuming that you have MySQL up and running and that you have Perl installed. Get the latest release of mysqlreport from the Download section on the project’s website. Copy it to some place in your computer’s PATH, such as /usr/bin/. Make the script executable with the following command:

# chmod 755 /usr/bin/mysqlreport

Installation is now complete. Let’s proceed to usage. We’ll take a look at some basic usage here. For a detailed list of what you can do with mysqlreport run the following command:

# mysqlreport --help

To get a report of your MySQL server run the following command, replacing the hostname, username and password with the correction credentials for your server:

# mysqlreport --host dbserver --user dbuser --password
Password for database user dbuser:
MySQL 5.0.37-standard-l uptime 50 17:19:6 Thu Apr 23 12:45:58 2009

__ Key _________________________________________________________________
Buffer used 71.21M of 512.00M %Used: 13.91
Current 102.20M %Usage: 19.96
Write hit 99.45%
Read hit 99.78%

__ Questions ___________________________________________________________
Total 2.66G 607.8/s
Com_ 6.88G 1.6k/s %Total: 258.37
-Unknown 5.67G 1.3k/s 212.99
DMS 1.42G 323.4/s 53.20
QC Hits 28.37M 6.5/s 1.06
COM_QUIT 9.56M 2.2/s 0.36
Slow 4 s 12.43k 0.0/s 0.00 %DMS: 0.00 Log: ON
DMS 1.42G 323.4/s 53.20
SELECT 1.39G 317.9/s 52.30 98.31
UPDATE 14.81M 3.4/s 0.56 1.04
INSERT 7.65M 1.7/s 0.29 0.54
DELETE 1.41M 0.3/s 0.05 0.10
REPLACE 29.24k 0.0/s 0.00 0.00
Com_ 6.88G 1.6k/s 258.37
set_option 2.18G 497.6/s 81.87
stmt_execut 1.37G 313.6/s 51.60
stmt_prepar 1.09G 248.7/s 40.92

__ SELECT and Sort _____________________________________________________
Scan 29.92M 6.8/s %SELECT: 2.15
Range 3.34M 0.8/s 0.24
Full join 22.95k 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 105.53k 0.0/s 0.01
Sort scan 20.06M 4.6/s
Sort range 25.60M 5.8/s
Sort mrg pass 23 0.0/s

__ Query Cache _________________________________________________________
Memory usage 60.15M of 128.00M %Used: 46.99
Block Fragmnt 19.67%
Hits 28.37M 6.5/s
Inserts 14.41M 3.3/s
Insrt:Prune 20.34:1 3.1/s
Hit:Insert 1.97:1

__ Table Locks _________________________________________________________
Waited 38.89k 0.0/s %Total: 0.00
Immediate 2.82G 643.8/s

__ Tables ______________________________________________________________
Open 1317 of 1536 %Cache: 85.74
Opened 36.20k 0.0/s

__ Connections _________________________________________________________
Max used 235 of 250 %Max: 94.00
Total 9.60M 2.2/s

__ Created Temp ________________________________________________________
Disk table 1.19M 0.3/s
Table 41.34M 9.4/s Size: 32.0M
File 51 0.0/s

__ Threads _____________________________________________________________
Running 2 of 152
Cached 26 of 64 %Hit: 99.93
Created 6.52k 0.0/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 75.29k 0.0/s
Connects 475 0.0/s

__ Bytes _______________________________________________________________
Sent 3.67G 837.6/s
Received 1.13G 258.4/s

__ InnoDB Buffer Pool __________________________________________________
Usage 1.46G of 1.46G %Used: 100.00
Read hit 97.49%
Free 0 %Total: 0.00
Data 95.00k 98.96 %Drty: 0.07
Misc 999 1.04
Latched 1 0.00
Reads 2.02G 462.0/s
From file 50.74M 11.6/s 2.51
Ahead Rnd 2211210 0.5/s
Ahead Sql 1773580 0.4/s
Writes 130.18M 29.7/s
Flushes 13.17M 3.0/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 503 0.0/s
Current 0
Time acquiring
Total 845761 ms
Average 1681 ms
Max 5182 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Reads 76.58M 17.5/s
Writes 16.05M 3.7/s
fsync 5.67M 1.3/s
Reads 0
Writes 0
fsync 0

Created 302.89k 0.1/s
Read 189.02M 43.1/s
Written 13.17M 3.0/s

Deleted 861.14k 0.2/s
Inserted 6.58M 1.5/s
Read 2.20G 502.4/s
Updated 19.54M 4.5/s

In case you need help understanding how to read the report generated by mysqlreport the project has a fine guide to help you with that. Feel free to play around with some of the other options shown in the mysqlreport --help command output.

4 thoughts on “How to get a detailed MySQL status report”

  1. Pingback: How to setup slow query logging in MySQL | TuxWire : The Linux Blog

  2. hello adminstrator , i am iranian , i know low english, plesase help me for “to report of several table on the mysql or ( to report compos of mysql)”.

  3. Pingback: Daily Links | :: Professional Web Design, Development, Programming, Hacks, Downloads, Math and being a Web 2.0 Hipster?

Leave a Comment

Your email address will not be published. Required fields are marked *