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:

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

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

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

http://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;
END
$

DELIMITER ;

DELIMITER $

CREATE EVENT log_purge_partition
ON SCHEDULE
EVERY 1 DAY STARTS ‘2008-02-20 00:00:01′
DO
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE part_name VARCHAR(25);
DECLARE cur1 CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_name = ‘log’
AND str_to_date(substr(partition_name from 2), ‘%y%m%d’) < date_sub(now(), INTERVAL 31 day);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
FETCH cur1 INTO part_name;
IF NOT done THEN
SET @stmt := CONCAT(
‘ALTER TABLE log DROP PARTITION ‘
, part_name
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;

CLOSE cur1;
END
$

DELIMITER ;
DELIMITER $

CREATE EVENT log_check_partition
ON SCHEDULE
EVERY 15 MINUTE STARTS ‘2008-02-20 00:00:01′
DO
BEGIN
DECLARE no_rows INT DEFAULT 0;
DECLARE part_name VARCHAR(25);
DECLARE cur1 CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_name = ‘log’
AND str_to_date(substr(partition_name from 2), ‘%y%m%d’) = date_format(now(), ‘%y%m%d’) ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_rows = 1;

OPEN cur1;

FETCH cur1 INTO part_name;

IF no_rows=1 THEN

SET @stmt := CONCAT(
‘ALTER TABLE log ADD PARTITION (‘
, ‘PARTITION p’
, DATE_FORMAT ( CURDATE(), ‘%y%m%d’ )
, ‘ VALUES LESS THAN (‘
, TO_DAYS( CURDATE() ) + 1
, ‘))’
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;

CLOSE cur1;
END
$

DELIMITER ;

Comments

Let me share with you this great post about time/date based partitioning and subpartitioning widh huge data flows:http://www.tritux.com/blog/2010/11/19/partitioning-mysql-database-with-h...It demonstrate how partitioning can be a good solution for scalable architectures.

Greg
Mon, 11/22/2010 - 13:28

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom