MySQL Partitions are a great way to rotate data in a table without incurring the overhead of running multiple delete statements. The most common use of this is to partition data by date. You must use MySQL 5.1 (or newer, I presume), and it is recommended to use MySQL 5.1.16 or later in order to take advantage of the ALTER TABLE features that allow adding partitions on existing tables.
First, make sure that the field you are partitioning on is either part of the PRIMARY KEY, or that the table does not have a PRIMARY KEY. In our example, we will be using the `date` field of the `example`.`log` table. This table looks like the following, and is just a simple example.
CREATE TABLE `log` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `date` int(11) NOT NULL DEFAULT '0', `value` varchar(255) NOT NULL, KEY `log_1` (`id`, `date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Now, let's add a few partitions to this table, based on the `date` field.
ALTER TABLE `log` PARTITION BY RANGE( date ) (
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00"))
);What these partitions do is forces any rows where the `date` field is less than "2011-07-01 00:00:00" to be stored in p201106, data where the `date` field is less than "2011-08-01 00:00:00" in p201107, and so on.
Now, let's say that 2011-09-01 is coming up, and we need another partition for the next month's data. This can be done with an ALTER TABLE command.
ALTER TABLE `log` ADD PARTITION (PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")));This is all well and good, but we need to either remember to add partitions as they're needed, or use some automated task to make sure they are present in time. Alternatively, a "catch-all" partition can be created so we have a place to store data that does not match any of the partition rules. Regardless of the method used for creating new partitions, it is a good idea to create one of these, just in case.
ALTER TABLE `log` ADD PARTITION (PARTITION pDefault VALUES LESS THAN MAXVALUE);
The catch is that if there is a partition like this present, new partitions cannot just be tacked on to the end, this catch-all partition needs to be split using REORGANIZE PARTITION.
ALTER TABLE `log` REORGANIZE PARTITION pDefault INTO (
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION pDefault VALUES LESS THAN MAXVALUE);And, finally for the major benefit of using partitions: deleting old data, and reclaiming space. The nature of InnoDB tables dictates that when a row is deleted, the space that was occupied by that row is not released back to the operating system. Instead, it remains allocated as part of the table file on the filesystem. With these partitions in place, we can easily delete old data, and reclaim the space that it was occupying. In addition, DELETE operations on InnoDB are somewhat intensive. Deleting a partition, however, is not.
So, let's look at our table first by issuing a SHOW CREATE TABLE log
| log | CREATE TABLE `log` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `date` int(11) NOT NULL DEFAULT '0', `value` varchar(255) NOT NULL, KEY `log_1` (`id`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE ( date) (PARTITION p201106 VALUES LESS THAN (1309496400) ENGINE = InnoDB, PARTITION p201107 VALUES LESS THAN (1312174800) ENGINE = InnoDB, PARTITION p201108 VALUES LESS THAN (1314853200) ENGINE = InnoDB, PARTITION p201109 VALUES LESS THAN (1317445200) ENGINE = InnoDB, PARTITION p201110 VALUES LESS THAN (1320123600) ENGINE = InnoDB, PARTITION pDefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
Now, let's say that we no longer need the log data from June, 2011, which is stored in the p201106 partition. To delete this, we issue another ALTER TABLE
ALTER TABLE `log` DROP PARTITION p201106;
This operation is relatively quick, it does not incur the same overhead as the DELETE statements to perform the equivalent operation on a standard table, and we have the benefit of reclaiming the space that was used by this data.
There are plenty of other uses for this technique, many of them are outlined in the MySQL documentation at http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
Attached is a script that will automatically rotate the partitions on a table that already has date-based partitions. The same can be, and often is, achieved using MySQL stored procedures, but I chose to use bash because I am more comfortable with it.
