MySQL Partitions timestamp - datetime

So I recently realized that I have not yet talked much about MySQL partitions.
Many good blog posts on MySQL partitions already exists and I have listed a few below.

• http://glynndba.blogspot.com/2009/10/rolling-time-based-partitions.html
• http://www.mysqlperformanceblog.com/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/
• http://datacharmer.blogspot.com/2010/05/two-quick-performance-tips-with-mysql.html
• http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

I happen to come across the following situation and I hope it proves helpful to others.

While the timestamp data type is often used datetime works better with partitions.
( timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

So the following it an example how how to fix this.

To start with we have this simple table.


CREATE TABLE `t1` (
  `t1_id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(25) DEFAULT NULL,
  `field2` int(10) DEFAULT '0',
  `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`t1_id`),
  KEY `tr` (`time_recorded`)
) ENGINE=InnoDB AUTO_INCREMENT=856964


We want to partition on the time_recorded field. To make it direct we will break it out per month.


ALTER TABLE t1
 PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
     PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
     PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
     PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
     PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
     PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
     PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
     PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
     PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
     PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
     PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
     PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
     PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
     PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
 );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed


So now what....

Well I know we need the Primary key updated if we want the partition.


 ALTER TABLE t1  DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE;
Query OK, 0 rows affected (38.96 sec)
Records: 0  Duplicates: 0  Warnings: 0


Notice that I have LOCK=NONE this is because I am using MySQL 5.6

• http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html 
• http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-examples.html 
• https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

 The primary key change will not allow me to add the partitions though, I still need to adjust the datatype.  I am using the LOCKED=SHARED this time. Please review the hyperlinks above for more information. If you happen to pick a type that does not work, it usually suggests a workable solution.


 ALTER TABLE t1 CHANGE  time_recorded  time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED;
Query OK, 854312 rows affected (41.89 sec)
Records: 854312  Duplicates: 0  Warnings: 0


So now we can add our partition.


 ALTER TABLE t1
    ->  PARTITION BY RANGE ( TO_DAYS(time_recorded) ) (
    ->      PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
    ->      PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
    ->      PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    ->      PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
    ->      PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
    ->      PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    ->      PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
    ->      PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
    ->      PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    ->      PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
    ->      PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
    ->      PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    ->      PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
    ->  );
Query OK, 854312 rows affected (50.74 sec)
Records: 854312  Duplicates: 0  Warnings: 0


We can SELECT,DELETE,UPDATE,INSERT and etc  per partition. More on that here: https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html.


SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|       661752 |
+--------------+
1 row in set (0.55 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|        64952 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|        71336 |
+--------------+
1 row in set (0.04 sec)
SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014);
+--------------+
| COUNT(t1_id) |
+--------------+
|        56272 |
+--------------+
1 row in set (0.05 sec)


While this works and we do now have a partition. We also now have to consider the partition maintenance.  Glynn's blog gives a very nice example of an automated Dropping and adding of partitions. Granted if you do not want to drop the partition you will need to adjust the example.
I have adjusted it for my table example.  Please review Glynn's blog for more details.


DROP PROCEDURE IF EXISTS Rotate_t1_Partition;
DELIMITER ;;
CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME)
BEGIN
  -- Setup
  DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt;
  DECLARE partitionToDrop VARCHAR(64);

  -- Find and drop the first partition in the table.
  SELECT partition_name
    INTO partitionToDrop
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE table_schema='forums_mysql'
    AND table_name='t1'
    AND partition_ordinal_position=1;
  SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ',  partitionToDrop);
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;

  -- Add a new partition using the input date for a value limit.
  SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN (TO_DAYS(\'',  DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))');
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;

  -- Cleanup
  SET @stmt = keepStmt;
END;;
DELIMITER ;


So I can update this table now with the following easily. 


CALL Rotate_t1_Partition('2015-03-01');
Query OK, 0 rows affected (1.11 sec)


You can also, as Glynn's blog points out, you can use this with a NOW() +interval 1 MONTH or which ever time frame you decide for your partitions. Grant you have to keep in mind that it will fail if you pass a date that is already a partition.

To continue with the automation you can add this to an EVENT.


 CREATE EVENT Rotate_t1_Partition
    -> ON SCHEDULE EVERY 1 MONTH
    -> DISABLE ON SLAVE
    -> COMMENT 'Remove oldest partition and add a new one '
    -> DO
    -> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH);
Query OK, 0 rows affected (0.04 sec)


Keep in mind a valid time for the above example. I just used this as an example.