Using Events to manage a Table Partitioned by Date

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 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:

Implementing a Replication precacher

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).

MyQ Gadgets 0.0.6 released

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

  • Changed /usr/local/bin/perl to /usr/bin/env perl to make the open source crowd happy.  Make sure the proper perl directory is first in your $PATH    
  • Made the usage string contain the valid modes in myq_status
  • Added check for mysql binary in
  • Quoted the --password option in to handle passwords with strange characters in them.
  • Setup default options string to be clearer which options are available to the scripts (like -? and -d)
  • Bumped the minimum repeat time for myq_status down to 1 second.  Note that it still might take more than 1 second for the check to run.

Whoops! First faux pas in releasing software

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.

Introducing 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 -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 

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
High Availability
Global Load Balancing
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile