If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the “next” partition? How and when do you drop your older partitions?
Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):
mysql> SELECT * FROM sql_range_partitions \G *************************** 1. row *************************** table_schema: test table_name: city sql_drop_first_partition: alter table `test`.`city` drop partition `p3` sql_add_next_partition: alter table `test`.`city` add partition (partition `p_20160101000000` values less than (736329) /* 2016-01-01 00:00:00 */ ) *************************** 2. row *************************** table_schema: test table_name: quarterly_report_status sql_drop_first_partition: alter table `test`.`quarterly_report_status` drop partition `p3` sql_add_next_partition: alter table `test`.`quarterly_report_status` reorganize partition `p_maxvalue` into (partition `p_20110401000000` values less than (1301608800) /* 2011-04-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE)
A closer look at why this is magic
This query just gave you the DROP PARTITION and ADD PARTITION for all tables in your databases that use a RANGE partitioning scheme. But, consider:
- The query automatically deduces the LESS THAN value of the new partition. It looks for a constant interval, time-based or integer-based, and keeps this interval onward.
- It understands that 5.1 does not allow you to partition by DATETIME, only via integers. It understands your integer may sometimes stand for TO_DAYS(), and sometimes for UNIX_TIMESTAMP() of your datetime. It auto-detects that.
- The query recognizes a MAXVALUE partition, and if such partition exists, it provides with a REORGANIZE PARTITION statement rather than ADD PARTITION statement.
- It suggests names for your partitions which give you a clue on what the partition contains. p_20160101000000 (can you splot the date/time?) tells me a lot more than some arbitrary p17.
- It recognizes the common case of using a LESS THAN (0) as first partition, to take care of NULLs. It skips this partition: the query does not offer to drop it, not does it consider it while examining the interval.
So I never have to tell the query “I want a 3 month interval between partitions, and these are implemented using TO_DAYS()”. I let it understand it on its own.
It is just a view
Which means you can SELECT sql_drop_first_partition, or you can SELECT sql_add_next_partition, or you can only SELECT … WHERE table_schema=’your_schema’. Or you can select them all.
You can eval() it
This view will be released with common_schema‘s next version. common_schema has a lot of these views which generate SQL statements. And it provides with the means to evaluate them: the eval() routine. So you don’t need to export the text INTO OUTFILE and execute it via SOURCE. You can simply:
call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
And it is done.
Get it
The sql_range_partitions view will be included in common_schema 1.2, schedules to be released soon. Meanwhile, you can import this file: [download id=”1″ format=”1″] onto your existing common_schema 1.1 install (what? You don’t already have common_schema installed? You should know it’s packed with lots of stuff like this one!)
If, by the time you read this, common_schema 1.2 is already out, you don’t need to add anything.
In action
Consider the following table on a MySQL 5.1 server:
CREATE TABLE test.quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL
)
PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2008-01-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2008-04-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2008-07-01 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2008-10-01 00:00:00')),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2009-01-01 00:00:00')),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2009-04-01 00:00:00')),
PARTITION p6 VALUES LESS THAN (MAXVALUE)
);
Unfortunately MySQL does not remember the definition expressions, so:
mysql> SHOW CREATE TABLE test.quarterly_report_status \G Create Table: CREATE TABLE `quarterly_report_status` ( `report_id` int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
Yikes! What does 1238533200 stand for?
No worries, let’s eval():
mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
mysql> SHOW CREATE TABLE test.quarterly_report_status \G
CREATE TABLE `quarterly_report_status` (
`report_id` int(11) NOT NULL,
`report_status` varchar(20) NOT NULL,
`report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated))
(PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM,
PARTITION p_20090701000000 VALUES LESS THAN (1246395600) ENGINE = MyISAM,
PARTITION p_maxvalue VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
Our query auto-detected the meaning of those numbers like 1238533200, and has found the next partition to be created: p_20090701000000. That’s ‘2009-07-01 00:00:00’, and now we know what the partition stands for. A new MAXVALUE partition called p_maxvalue is created.
Just for the fun of it, let’s issue the same a few more times and see what comes out:
mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
mysql> SHOW CREATE TABLE test.quarterly_report_status \G
Create Table: CREATE TABLE `quarterly_report_status` (
`report_id` int(11) NOT NULL,
`report_status` varchar(20) NOT NULL,
`report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated))
(PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM,
PARTITION p_20090701000000 VALUES LESS THAN (1246395600) ENGINE = MyISAM,
PARTITION p_20091001000000 VALUES LESS THAN (1254348000) ENGINE = MyISAM,
PARTITION p_20100101000000 VALUES LESS THAN (1262296800) ENGINE = MyISAM,
PARTITION p_20100401000000 VALUES LESS THAN (1270069200) ENGINE = MyISAM,
PARTITION p_20100701000000 VALUES LESS THAN (1277931600) ENGINE = MyISAM,
PARTITION p_maxvalue VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
Notice the number interval is not constant: we have different number of days in different quarters. We have Feb 29th every 4 years. Yet we get the right LESS THAN value.
Now isn’t this cool?
bakwas code!!!!!!!!!!!!