Adding new partitions beyond MAXVALUE
I have found that MySQL RANGE partitions on the primary key are a great way to achieve scale for insert-heavy InnoDB tables. I have used this to maintain an excellent and predictable insert rate, to avoid some of the well documented problems with insert performance as table sizes grow (especially with large/many secondary indexes). In addition purging old data is fast and non-blocking because you can just DROP PARTITION as a single very fast operation.
However, MySQL partitioning brings up an interesting little issue when you exceed the space you initially allocate for your partitions. Here in our sample table we define 4 partitions to handle 100M records, and everything after that will fall into the “pmx” bucket.
CREATE TABLE partition_test (
`id` int unsigned primary key auto_increment,
`payload` varchar(35) not null default '',
`stamp` timestamp default current_timestamp on update current_timestamp
) ENGINE=InnoDB
PARTITION BY RANGE (`id`) (
PARTITION p00 VALUES LESS THAN (025000000),
PARTITION p01 VALUES LESS THAN (050000000),
PARTITION p02 VALUES LESS THAN (075000000),
PARTITION p03 VALUES LESS THAN (100000000),
PARTITION pmx VALUES LESS THAN MAXVALUE
);
Let’s say that you get to 75M records, and now you want to extend your partition set beyond your current allocation. The simple way is to just ADD PARTITION, like so:
ALTER TABLE partition_test ADD PARTITION (
PARTITION p04 VALUES LESS THAN (125000000)
);
# yields:
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
This is uncool. One option is to DROP the MAXVALUE partition, and then add it… but I find that a bit scary. Thankfully there is a better way to add the new partition, by using REORGANIZE:
ALTER TABLE partition_test REORGANIZE PARTITION pmx INTO (
PARTITION p04 VALUES LESS THAN (125000000),
PARTITION pmx VALUES LESS THAN MAXVALUE
);
By using REORGANIZE, you can rebuild pmx and recreate it in the same step. Any values that are currently in pmx will have to be rebuilt with the REORGANIZE statement, but if you are thinking ahead and have no records in that partition, REORGANIZE is a very fast and safe operation.
April 2nd, 2009 at 11:49 am
I actually have some code up on the forge that illustrates this: http://forge.mysql.com/tools/tool.php?id=162
It’s a complete stored procedure that anyone can adapt to their own needs.
This procedure is based on date partitioning, but again, it can be adapted. This one is designed to run daily – checks to the next 4 future dates, and creates new paritions if required. I look at four dates out so that if the event doesn’t trigger for one reason or another, you have a few days to fix it
But beware – this script also drops old partitions – so you want to remove that portion if you don’t want to delete your old partitions.
This snippet was part of my Use Case entry.
April 2nd, 2009 at 11:50 am
Good tip.
Just to be clear, though, you are not inserting a partition “beyond” MAXVALUE, but below it.
At the end of the operation, MAXVALUE will remain at the top (or at the bottom, if you prefer) of the definition.
Giuseppe
April 2nd, 2009 at 11:57 am
Gregory, that is an interesting solution to date-based partition schemas. With the particular access pattern I use however, there is significant advantage to having the primary (clustered) index on the sequence column.
April 2nd, 2009 at 12:07 pm
Giuseppe, good point. When using MAXVALUE I suppose you already allocating partitions for all possible records.
I was referring of course to rows which exceed the parameters of the the explicitly defined partitions.
August 31st, 2009 at 9:07 pm
Thank you.