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. 

Planning MySQL downtime and other Status variables for review

We all know that while MySQL is stable and can run for a very long time without restarts. Every so often we need to restart to it adjust some variables or for upgrading though. Some might assume that overnight is the best time to do this because their users are asleep.  However, often the overnight also includes backups and other crontab related scripts that could be doing different data mining and etc.

So how can you get a guideline on when you have the least activity on the server?  Now each application and database are different, so you need to taken into account what related activity is running, during the different time frames.  If  you start to log  the "THREADS_CONNECTED" status variable every minute to 5 to 10 minutes, again this depends on how loaded your system is once an hour might be fine, you will soon gather the amount of different MySQL connections across your logged time-frame.

This can be done a numerous of ways. It is already provided in many enterprise level tools and you can write scripts and etc, but just to keep it in MySQL. I will use an event and routine for this simple example.

Create a simple table:

CREATE TABLE `threads_count` (
`threads_count_id` int(11) NOT NULL AUTO_INCREMENT,
`variable_value` varchar(255) DEFAULT NULL,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`threads_count_id`)
) ENGINE=InnoDB;


Create a simple routine:

delimiter //
CREATE PROCEDURE threads_tally()
BEGIN
INSERT INTO threads_count SELECT NULL , VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('Threads_connected');
END//
delimiter ;


Create a simple event:

SET GLOBAL event_scheduler = ON;
CREATE EVENT `threads_tally`
ON SCHEDULE EVERY 5 MINUTE
DISABLE ON SLAVE
COMMENT 'keeps a count of Threads_connected'
DO
CALL threads_tally();
DELIMITER ;


Now but implementing the above example, you will have a table that you can select the values from (likely put into a graph or just for review) so you can see when the least number of connections existed on your server. Depending on your application  and traffic you can keep this running long enough to gain a valid perspective and then disable the event.

select variable_value , time_recorded FROM threads_count;

Now you can course take this a step further, if you do not have enterprise tools, and keep a log of all of your status variables for review:

Table for values:

CREATE TABLE `status_review` (
`status_review_id` int(11) NOT NULL AUTO_INCREMENT,
`variable_name` varchar(255) DEFAULT NULL,
`variable_value` varchar(255) DEFAULT NULL,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`status_review_id`)
) ENGINE=InnoDB;


Routine to gather values:

delimiter //
CREATE PROCEDURE status_reviews()
BEGIN
INSERT INTO status_review SELECT NULL , VARIABLE_NAME, VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS;
END//
delimiter ;


Event to execute routine:

SET GLOBAL event_scheduler = ON;
CREATE EVENT `status_reviews`
ON SCHEDULE EVERY 1 HOUR
DISABLE ON SLAVE
COMMENT 'keeps a eye on status values'
DO
CALL status_reviews();
DELIMITER ;


You might only want certain variable names and values for review and you can adjust the query in the routine accordingly. The run time is dependent on your needs, I used an hour in this example.

This of course it going to generate a lot of information over time so if relevant some cleaning out of these tables over time might be needed. It all depends on how often you log and review the information provided.

While you will see a steady increase of many of the values because of your data growth and system use you will also be able to pinpoint data spikes in the data as and correlate that to your application and system use.

MySQL enterprise for example already will graph all of this type of information for you but this is a way you can also do it yourself with a little time and reviewing of the data.