As a follow up to the series of posts I've been making, I wanted to post what I ended up with. Â Thanks to everyone who posted comments, your help was extremely useful. To recap, I have a working pair of events to add and remove partitions to this 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)
);
The partitions are keyed off of the TO_DAYS function on the date/time of the log entries. Every day I want to create a new partition for tomorrow. It's important that we create this a day in advance so new entries at 12:00AM that day do not get a "partition not found" error. Our event to do so looks like this:
DELIMITER $ CREATE EVENT log_addpartition ON SCHEDULE EVERY 1 DAY DO BEGIN DECLARE new_partition CHAR(32) DEFAULT CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d')); DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +2; SET @s = CONCAT('ALTER TABLE log ADD PARTITION (PARTITION ', new_partition, ' VALUES LESS THAN (', max_day, '))'); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; END $ DELIMITER ;
DELIMITER $ CREATE EVENT log_removepartition ON SCHEDULE EVERY 1 DAY DO BEGIN DECLARE old_partitions CHAR(64) DEFAULT ''; SELECT CONCAT( 'ALTER TABLE log DROP PARTITION ', GROUP_CONCAT( PARTITION_NAME )) INTO @s FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=schema() AND TABLE_NAME='log' AND CREATE_TIME < DATE_SUB( CURDATE(), INTERVAL 1 WEEK ) GROUP BY TABLE_NAME; PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; END $ DELIMITER ;
I can check the status of the events in the Information Schema:
mysql> select * from INFORMATION_SCHEMA.EVENTS\G *************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: test EVENT_NAME: log_addpartition DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: BEGIN DECLARE new_partition CHAR(32) DEFAULT CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d')); DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +1; SET @s = CONCAT('ALTER TABLE log ADD PARTITION (PARTITION ', new_partition, ' VALUES LESS THAN (', max_day, '))'); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; END EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: DAY SQL_MODE: STARTS: 2008-02-20 07:05:05 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2008-02-20 07:05:05 LAST_ALTERED: 2008-02-20 07:05:05 LAST_EXECUTED: 2008-02-20 07:05:05 EVENT_COMMENT: ORIGINATOR:CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci
*************************** 2. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: test EVENT_NAME: log_removepartition DEFINER: root@localhost TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: BEGIN DECLARE old_partitions CHAR(64) DEFAULT ''; SELECT CONCAT( 'ALTER TABLE log DROP PARTITION ', GROUP_CONCAT( PARTITION_NAME )) INTO @s FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=schema() AND TABLE_NAME='log' AND CREATE_TIME < DATE_SUB( CURDATE(), INTERVAL 1 WEEK ) GROUP BY TABLE_NAME; PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; END EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: DAY SQL_MODE: STARTS: 2008-02-20 07:06:48 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2008-02-20 07:06:48 LAST_ALTERED: 2008-02-20 07:06:48 LAST_EXECUTED: 2008-02-20 07:06:48 EVENT_COMMENT: ORIGINATOR:CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci 2 rows in set (0.01 sec)
And finally, what does our table actually look like?
select DATE_FORMAT(logged, '%m/%d/%Y'), count(*) from log group by TO_DAYS(logged);
+---------------------------------+----------+
| DATE_FORMAT(logged, '%m/%d/%Y') | count(*) |
+---------------------------------+----------+
| 02/13/2008 | 1103 |
| 02/14/2008 | 2158 |
| 02/15/2008 | 2878 |
| 02/16/2008 | 2880 |
| 02/17/2008 | 2880 |
| 02/18/2008 | 2878 |
| 02/19/2008 | 2880 |
| 02/20/2008 | 892 |
+---------------------------------+----------+
8 rows in set (0.02 sec)
Note we have 8 days in this table because in our 'log_removepartition' event because we search for all CREATE_TIMEs that are greater than 1 week ago (not greater than and equal to).
Hi, thanks for this! I'm
Hi, thanks for this!
I'm trying to use it and noticed a couple of things:
1. There's no max value set, so I got an error the next day.
To remedy that, I did:
# 'lo' is a datetime field
alter table logs
PARTITION BY RANGE( TO_DAYS( lo ) ) (
PARTITION p20091107 VALUES LESS THAN (to_days('2009-11-07')),
PARTITION p20091108 VALUES LESS THAN (to_days('2009-11-08')),
PARTITION p20091109 VALUES LESS THAN (to_days('2009-11-09')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
Then I did:
DELIMITER $
CREATE EVENT logs_add_partition ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE+1,'00:00:01')
DO
BEGIN
DECLARE new_partition CHAR(32) DEFAULT
CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'));
DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +2;
SET @s =
CONCAT('ALTER TABLE logs ADD PARTITION (PARTITION ', new_partition,
' VALUES LESS THAN (', max_day, '))');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $
DELIMITER ;
DELIMITER $
CREATE EVENT logs_del_partition ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE+1,'00:00:02')
DO
BEGIN
DECLARE old_partitions CHAR(64) DEFAULT '';
SELECT CONCAT( 'ALTER TABLE logs DROP PARTITION ',
GROUP_CONCAT( PARTITION_NAME ))
INTO @s
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=schema() AND
TABLE_NAME='logs' AND
CREATE_TIME < DATE_SUB( CURDATE(), INTERVAL 1 MONTH )
GROUP BY TABLE_NAME;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $
# The only thing I added here was the STARTS TIMESTAMP(CURRENT_DATE+1,'00:00:01')
# to tell it to run ever day after midnight.
I'm not sure this will work...it hasn't run yet.
Do you foresee an issue with having to set the MAX?
The remove partition event
The remove partition event doesn't work if you're having partitions with hashes.. For example, you partition by date to be able to remove old data, but the partitions itselves are partitions with hashes to speed up even more. What happens is that the remove partition events tries to execute:
ALTER TABLE mobiledata DROP PARTITION p20100318,p20100318,p20100318,p20100318,p20100318,p20100318,p20100318,p20100318,p20100318,p20100318
That's illegal, and I assume because we're having the same partition name several times.
So I added a simple "DISTINCT" to:
GROUP_CONCAT( DISTINCT PARTITION_NAME ))
and it works.