Posted in MySQL, work on January 14th, 2010 by feef
MySQL introduced partitioning with version 5.1, with alpha releases of 5.1.3 offering it available from December 2005. Since then, it’s been developed further and with the relatively recent 5.2.41 release, many of the bugs and issues surrounding partitioning have been ironed out.
One issue I’ve had some users come across relates to the partition management on a live database. How big do partitions need to be? how often do we need to repartition? What do we do with the “old” data? How can we make the management of partitions easier in general?
I’ve addressed these questions with a stored procedure and by taking advantage of MySQL’s recent Event Scheduler.
My example uses a MyISAM table, partitioned on a bigint column which contains a unix timestamp and an ‘archive’ table that contains the same structure but using a different storage engine, for long-term storage and archiving. My Sample also automates the creation of the log table, and can be run as a “deployment script” to set up a logging database ready for use.
First: create the log table:
DROP TABLE IF EXISTS table_log;set @qry := concat(‘CREATE TABLE table_log (id int(10) unsigned NOT NULL AUTO_INCREMENT,unix_time bigint(20) unsigned NOT NULL,logging_stuff varchar(512) DEFAULT NULL,PRIMARY KEY (id,unix_time)) ENGINE=MyISAMPARTITION BY RANGE ( unix_time) (PARTITION p0 VALUES LESS THAN (‘,unix_timestamp(now())*1000,’) ENGINE = MyISAM)’);prepare stmnt from @qry;execute stmnt;
Then, create the archive table
DROP TABLE IF EXISTS table_log_archive;CREATE TABLE table_log_archive (id int(10) unsigned NOT NULL AUTO_INCREMENT,unix_time bigint(20) unsigned NOT NULL,logging_stuff varchar(512) DEFAULT NULL,PRIMARY KEY (id,unix_time)) ENGINE=MyISAM
And now the stored Proc:
DROP PROCEDURE IF EXISTS partition_logs;DELIMITER $$CREATE PROCEDURE partition_logs(in tblname varchar(100),in partsize int,in lwater int,in hwater int)BEGINDECLARE p_start_time bigint;DECLARE p_end_time bigint;DECLARE p_start_name bigint;DECLARE p_end_name bigint;DECLARE p_low_water bigint;DECLARE p_high_water bigint;DECLARE p_new_name bigint;DECLARE p_new_time bigint;DECLARE cnt int;DECLARE deld int;DECLARE partition_column varchar(100);set partsize = partsize * 3600; — turn the passed partition size in hours, into the unixtimestamp.SELECT cast((substr(partition_name from 2)) as SIGNED ) into p_start_name FROM information_schema.PARTITIONS where table_name = tblname order by partition_ordinal_position limit 1;SELECT cast((substr(partition_name from 2)) as SIGNED ) into p_end_name FROM information_schema.PARTITIONS where table_name = tblname order by partition_ordinal_position desc limit 1;SELECT partition_description into p_start_time FROM information_schema.PARTITIONS where table_name = tblname order by partition_ordinal_position limit 1;SELECT partition_description into p_end_time FROM information_schema.PARTITIONS where table_name = tblname order by partition_ordinal_position desc limit 1;SELECT partition_expression into partition_column from information_schema.PARTITIONS where table_name = tblname limit 1;select unix_timestamp(date_sub(date(now()), interval lwater day)) into p_low_water;select unix_timestamp(date_add(date(now()), interval hwater day)) into p_high_water;set @plw := p_low_water;set @tbl := tblname;set @col := partition_column;set @ins := concat(‘replace into ‘,@tbl,’_archive select * from ‘,@tbl,’ where ‘,@col,’ < ‘,@plw);prepare instmnt from @ins;execute instmnt;select p_start_time,p_low_water;set deld = 0;while (p_start_time < p_low_water)doselect p_start_time,p_low_water;set @pstart := p_start_name;set @droppart := concat(‘ALTER TABLE ‘,@tbl,’ drop partition p’,@pstart);prepare dropstate from @droppart;execute dropstate;SELECT partition_description into p_start_time FROM information_schema.PARTITIONS where table_name = tblname order by partition_ordinal_position limit 1;SELECT cast((substr(partition_name from 2)) as SIGNED ) into p_start_name FROM information_schema.PARTITIONS where table_name = tblname order by partition_ordinal_position limit 1;set deld = deld+1;END WHILE;set cnt=0;while (p_end_time < p_high_water)doset p_end_name = p_end_name + 1;IF (p_end_time < p_low_water)THEN set p_end_time = p_low_water;ELSE set p_end_time = p_end_time + partsize;END IF;set @pendname := p_end_name;set @phighwater := p_end_time;set @alter_log := concat(‘ALTER TABLE ‘,@tbl,’ ADD PARTITION (PARTITION p’,@pendname,’ VALUES LESS THAN(‘,@phighwater,’))’);prepare stmnt from @alter_log;execute stmnt;set cnt = cnt+1;END WHILE;select partsize as ’seconds’,p_low_water as ’start at’,p_high_water as ’stop at’,cnt as ‘partitions created’,deld as ‘partitions dropped’;END$$
This procedure takes advantage of the partition information being held in the information_schema, and uses it to calculate the current, obsolete and new partitions. it takes any “obsolete” data dumps it into the _archive table before dropping the partitions. In addition, it’s flexible enough that it can be called against any table that’s partitioned in the same way thus:
call partition_logs(‘table_name’,[hours per partition],[number of days to retain],[number of days in advance])
e.g. call partition_logs(‘table_log’,2,7,7);
…will repartition the table_log table, dumping any data older than 7 days old into the archive table and removing those partitions, and will create enough partitions for the next 7 days where each partition is 2hrs in size.
When called, the proc will also output some basic information on the specified partition size as well as how many partitions were dropped and created. The only caveat with this procedure as it is, is that the “top” partition doesn’t use “less than max value” and has a hard limit. This means that, if the procedure isn’t called when it should be, you may hit a ‘hard’ limit on the partitioned table where it won’t allow inserts due to the time integer being higher than the maximum partition value.
With a scheduled event calling this procedure on a regular basis, you have a completely automatic partition management system.
