[HowTo] Mysql Reporter

[Hvu]

Yet Another MySQL performance reporter.


wget http://hackmysql.com/scripts/mysqlreport



chmod 755 mysqlreport



./mysqlreport -all -user YOUR_USERNAME -password


if you want to call from a PHP/perl/etc script use
.mysqlreport -all -user YOUR_USERNAME -password YOUR_PASSWORD

Give you reports like so


__ Key __________________________________________________ _______________
Buffer usage 6.22M of 7.00M %Used: 88.80
Write ratio 0.04
Read ratio 0.01

__ Questions __________________________________________________ _________
Total 3.17M 2.21/s
DMS 2.76M 1.92/s %Total: 86.98
Com_ 291.91k 0.20/s 9.21
COM_QUIT 122.69k 0.09/s 3.87
-Unknown 1.93k 0.00/s 0.06
Slow 1.07M 0.75/s 33.74 %DMS: 38.79
DMS 2.76M 1.92/s 86.98
SELECT 2.46M 1.72/s 77.75 89.39
UPDATE 205.46k 0.14/s 6.48 7.45
INSERT 49.37k 0.03/s 1.56 1.79
DELETE 37.21k 0.03/s 1.17 1.35
REPLACE 342 0.00/s 0.01 0.01
Com_ 291.91k 0.20/s 9.21
change_db 119.76k 0.08/s 3.78
set_option 40.42k 0.03/s 1.28
show_tables 39.91k 0.03/s 1.26

__ SELECT and Sort __________________________________________________ ___
Scan 950.74k 0.66/s %SELECT: 38.59
Range 95.65k 0.07/s 3.88
Full join 13.55k 0.01/s 0.55
Range check 0 0.00/s 0.00
Full rng join 6 0.00/s 0.00
Sort scan 347.95k 0.24/s
Sort range 219.08k 0.15/s
Sort mrg pass 0 0.00/s

__ Table Locks __________________________________________________ _______
Waited 156 0.00/s %Total: 0.00
Immediate 3.53M 2.46/s

__ Tables __________________________________________________ ____________
Open 234 of 350 %Cache: 66.86
Opened 60.91k 0.04/s

__ Connections __________________________________________________ _______
Max used 15 of 1200 %Max: 1.25
Total 122.69k 0.09/s

__ Created Temp __________________________________________________ ______
Disk table 33.85k 0.02/s
Table 128.38k 0.09/s
File 1.03k 0.00/s

[vps-vince]

Yes, that guy sure knows his MySQL
Here is a good post by him which helped me a lot:

http://www.webhostingtalk.com/showthread.php?t=402465

- Vince

[BornOnline]

pretty cool.. now I can see how bad I have tuned :D
Thanks Hvu

[StingRay]

Looks great Hvu thanks. But I never know where to "install" these kind of things. I always feel like I'll be cluttering up the file system.

So to help the ignorant masses like me, suggesting a few places to "cd" to before the "wget" would be helpful.

[Hvu]

I placed mine in /var/www/html/mysqlreport with a simple php script that exec it. and ofcourse it password protected :)

[elix]

just to add if you want this cron'd and to e-mail the results to you...

# mkdir /root/mysqlreport
# cd /root/mysqlreport
# wget http://hackmysql.com/scripts/mysqlreport -O mysqlreport.pl
# echo "perl /root/mysqlreport/mysqlreport.pl | mail -s 'Daily MySQL Report' root" > /etc/cron.daily/mysqlreport.sh
# chmod 0755 /etc/cron.daily/mysqlreport.sh

[Zaf]

hey elix...wow good job man.....cut paste artist transforming into a script kiddie ;)

[elix]

hey elix...wow good job man.....cut paste artist transforming into a script kiddie ;)
;) hey, long time no talk.