mysqlguy.net

I want to create a log table in 5.1 that is partitioned by day.  I want to roll-off old data and create new partitions for each day automatically.  Without writing a script and a cronjob, it seems like this should be possible with Events.  Let's start with the table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
)
PARTITION BY RANGE( TO_DAYS( logged ) ) (
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)
);

This seems pretty straight-forward:  I take my log entry time and convert it TO_DAYS and partition on that.  I have tomorrow's partition all ready to go, just in case I don't get around to adding it today.  Let's create an Event to do add tomorrow's partition for us automatically each day:

I've completed a beta implementation of my take on the replication pre-cache tool... Sorry nothing to download yet, I have to get it through an internal committee at Yahoo before I can release it (and you can imagine things are kind of crazy here).  I wrote it myself because:

1. I had it mostly done before I found out there were other versions out there
2. I have to maintain it inside of Yahoo anyway
3. I wanted to learn Ruby :)
   

It's just over 250 lines of Ruby, my new favorite language and fairly compact.  It doesn't use the Ruby Mysql library, rather just IO.popen calls to the mysql command line client.  I did this for two reasons: 

1. I haven't figured out the "right" way to deploy ruby gems at Yahoo yet (it's complicated).

Thanks to those who tried it out and left feedback, much appreciated.

Thanks to the few people who pointed out a copyright infringement with the name 'MySQL Gadgets' for my tool. It has now been renamed 'MyQ Gadgets'. This is actually more appropriate (but hopefully not a violation of some other copyright), since I use the 'myq' prefix on my MySQL scripts as an easy, unique prefix for command line tab completion in bash.

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