MySQL Partitioning

I’ve been setting up partitioning for various customers lately. The goals primarily have been easy purging of large growth tables and keeping indexes small enough to stay in memory and manageable. These have all been range partitions on dates, which is a rather common requirement. As you’ve probably noticed in previous posts, I absolutely hate environments where people let their tables grow like blackberry bushes. While doing research, I found the following links to be very helpful:

https://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

https://datacharmer.blogspot.com/2006/03/mysql-51-improving-archive-performance.html

https://blog.plasticfish.info/categories/tech/mysql/mysql-partitioning/

https://mysqlguy.net/2008/02/20/using-events-manage-table-partitioning-date-wrap

As far as partition management goes, I like the idea of DB events, primarily because you don’t have to worry about crontabs continually slamming a database that’s down, and the events are portable with the database making failovers, migrations and copies that much easier. So, what events do we need to manage partitions? There are three cases that I have been using. Remember, these are for date based partitions (logs, events etc…)

1. Create the next day (or week or month etc…) ’s partition.
2. Purge any partions older than n days.
3. Check that the necessary partition for the current time period exists, and if not, create it on the fly. After all, what happens if the DB is down when the event is supposed to fire? We need to remember the edge cases.

So, in the interest of sharing, here are some events I created. I consider these rudimentary at best. Remember that it isn’t just about functionality. These need to be robust. I’m still planning on adding more error handling and email notifications on failure but I wanted to share. (I’m a giver)

DELIMITER $

CREATE EVENT log_add_partition
ON SCHEDULE
EVERY 1 DAY STARTS ‘2008-02-19 23:59:00?
DO
BEGIN
SET @stmt := CONCAT(
‘ALTER TABLE log ADD PARTITION (‘
, ‘PARTITION p’
, DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 1 DAY ), ‘%y%m%d’ )
, ‘ VALUES LESS THAN (‘
, TO_DAYS( CURDATE() ) + 1
, ‘))’
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;