Today I am introducing my first open source contribution to MySQL: MyQ Gadgets. These are a small collection of tools I wrote to make it easier to see what is going on inside of a running MySQL server. There's nothing magical about them, they simply collect values from SHOW STATUS, SHOW SLAVE STATUS, and SHOW INNODB STATUS and present them in an easier-to-digest format similar to that of iostat (for those who are familiar with it). Here's an example of some data from SHOW INNODB STATUS:
./myq_innodb_status -h mysqlhost.domain.com -u user -p password -t 20 row Inno Engine (/sec) Buffer (/sec) (%) Log OS (/sec) Semaphores time read ins upd del new read wrte full dirt hit io/s read wrte fsyc spw rnds osw 16:10:55 3.8k 9.8 9.7 0 0.3 0 13.7 93 13 100 14.1 0 19.4 14.7 30m 94m 485k 16:11:15 1.0k 10.0 4.8 0 0.3 0.1 17.6 93 13 100 11.5 0.1 20.0 12.4 3.0 21.0 1.0 16:11:35 0.1 5.8 0.1 0 0.2 0 13.8 93 13 100 4.0 0 11.2 4.6 0 0 0 16:11:56 0 5.2 0 0 0.2 0 12.2 93 13 100 3.2 0 10.2 3.7 0 0 0
Here's another example showing MyISAM and query stats:
$ ./myq_status -h host -u user -p pass -t 15 myisam row Queries (/sec) Table Key Buffer time sel ins upd del lock full used dirt rmiss wmiss 16:16:11 341m 256m 135m 5.4m 0.02% 57.8% 442.34M 0.00% 0.74% 34.2% 16:16:27 56.9 19.2 0 0 0.00% 57.6% 442.34M 0.00% 0.36% 21.1% 16:16:41 136 22.1 0 0 0.00% 57.4% 442.34M 0.00% 3.95% 20.7% 16:16:56 27.1 19.7 0 0 0.00% 57.3% 442.34M 0.00% 0.58% 35.8%
the query cache:
./myq_status -h host -u user -p pass -t 15 qcache row Query Cache Info time type sel hits %hits ins %ins notc %notc tot lowm full free 16:18:37 ON 867m 526m 60.7% 336m 38.8% 4.2m 0.49% 40k 100m 92.9% 150.11M 16:18:53 ON 121 64.2 53.0% 56.7 46.8% 0.2 0.15% 41k 0 93.9% 147.04M 16:19:09 ON 75.4 46.2 61.3% 28.6 37.8% 0.6 0.75% 42k 0 94.2% 145.71M 16:19:23 ON 111 50.0 45.1% 59.9 54.1% 0.8 0.71% 43k 0 94.7% 143.17M
There are a lot more reports to explore, take a look at the --help output for each script.
A caveat: I fully accept that some of these calculations may be incorrect. I've tried to arrange many of the variables in ways that seemed more useful than just printing simple rates. Feel free to contact me with any bugs. I've also only really tested this on MySQL 4.1 (sorry), but I suspect it will work just fine on newer versions.
Happy downloading!
Technically you should change
Technically you should change the name from MySQL Gadgets as it infringes on the MySQL trademark. Â The MySQL toolkit recently did this by renaming itself to MaaToolkit, as blogged about here http://www.xaprb.com/blog/2007/11/18/mysql-toolkit-is-now-maatkit/
Other than that keep up the good work :D
Hi,You might want to change
Hi,
You might want to change the name. Because of MySQL trademark policies, you shouldn't let your tool's names start with MySQL.
http://www.xaprb.com/blog/2007/11/06/mysql-toolkit-needs-a-new-name/
Hi,I don't know how many
Hi,
I don't know how many people have perl actually installed in /usr/local/bin/perl but it may be more convenient for the masses if this would point to /usr/bin/perl .
When I just run ./myq_status I got:
$ ./myq_status
Mode must be one of 'myisam', 'commands', 'qcache', 'cttf', 'throughput', 'query', 'temp', 'handler'
When I run e.g.
$ ./myq_status -unknown
Unknown option: unknown
myq_status [-t <secs>] [-u user [-p [pass]]] [-h host] [-P <port>] [myisam|commands|qcache|cttf|throughput|query|temp|handler]
I also get the help. I think would be nice if the help is also displayed in the first case.
What do you think about making -p accept the password like mysql, only when directly appended? Because if not it makes the following fail:
$ ./myq_status -u root -h db01 -p myisam
Mode must be one of 'myisam', 'commands', 'qcache', 'cttf', 'throughput', 'query', 'temp', 'handler'
It needs to be written like this
$ ./myq_status -u root -h db01 -p -- myisam
Of course I also didn't pay attention once I got past the arguments that I didn't had mysql client binaries installed on this vhost. I think it would be nice to give a nicer error message then this:
$ ./myq_status -u root -h db01 -p -- myisam
Password:
sh: mysql: command not found
Next bump was that my password contained a '$'. This confused the mysql_call which generates a shell command which doesn't protect against such characters. Modifying the source and quoting with '--password=icontaina$' worked.
I tried with five seconds interval and got the message:
$ ./myq_status -t 5 -u root -h db01 -p -- myisam
Repeat time must be at least than 15 seconds
English is not my native language but I think this should read something along "Repeat time must be at least 15 seconds" ?
Btw, I would really appreciate a smaller amount of seconds, on busy server 15 seconds may be too much already.
During my password problem I had to manually set $DEBUG in MySQL_Script_Utils.pm to 1. It would be nice if this could be turned on with kind of verbose switch, e.g. -v .
I really like such small tools, gives a nice comprehensive overview. Thanks!
Noted on most things and will
Noted on most things and will be fixed in the next release. Â A few things I can't or won't fix:
Post new comment