Introducing MyQ Gadgets

Submitted by jay on January 14, 2008 - 7:25pm

myq gadgets

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!

Trackback URL for this post:

http://mysqlguy.net/trackback/4

Technically you should change

Submitted by Anonymous (not verified) on January 15, 2008 - 1:06am.

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

Submitted by Nils (not verified) on January 15, 2008 - 1:34am.

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

Submitted by Markus (not verified) on January 15, 2008 - 12:35pm.

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

Submitted by jay on January 16, 2008 - 12:10pm.

Noted on most things and will be fixed in the next release. A few things I can't or won't fix:

1) The password option is a limitation of Getopt::Long in perl. If you can figure out how to make that work correctly for Getopt, I'd be happy to make the change. (I did fight with it when I first wrote it to make it work like the mysql client, but no go).

2) The 15 second time limit: It turns out this is an artificial limit I imposed on myq_status because of some code I stole from another script. I simplified that mechanism now and you can go down to 1 second sleep time. However, because the script does take some time to run each cycle, you'll still end up with 2-3 seconds between runs.

However, on the myq_innodb_status script, the limit (which I believe is 10) is necessary because SHOW INNODB STATUS outputs new data since the last run, so if you run it too quickly, the data isn't a good sample.