mysqlguy.net

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 

I'm working on an implementation of a mysql slave precache tool as described by Paul Tuckfield at last year's User conference.

I can easily tell where the SQL Thread is currently is reading from in the local relay log, but it's a harder problem to solve when I want to scan ahead in that log some number of binlog entries and find a valid position. I can simply add to the position some number of bytes, but then I have to stop and loop through calls to 'mysqlbinlog' until I get a position that doesn't throw an error.

This turns out to be a pretty expensive operation, and I'm having a problem where by the time I find a good position and can start my precacher, the slave is already past me!

Does anyone have any clever ways to find good binary log positions besides this (this is in ruby, in case you're wondering):

def mysqlbinlog_cmd( position, length )

It's not that I've really posted that much at http://mysqlguy.blogspot.com, but I wanted someplace that was a little more permanent going forward.  The reason should become clear hopefully later this month, but let's just say I've been working out some details to allow me to contribute directly to the MySQL community in some tangible ways.  In any case, stay tuned here.