Re: Uneven rolling partition
Yes, partitioning supports row migration (updating a row's partition key will move it to the correct partition)
I think you might have a little problem with this from a maint perspective, however.
The date function for the partitioning key needs to be deterministic I believe, so, assuming you have a datetime column that you are planning to use for your partitioning key, you would not be able to do this:
alter table x partition by range (datediff (now(), mydatetime_column))
( partition p_hot values less than (1),
partition p_warm values less than (6),
partition p_cold values less than MAXVALUE
)
Because range partitioning only works with the values less than operator, it would be difficult to have the data automatically roll out into the p_warm partition. If you could range partition with a values greater than operator, it might be possible.
If you are willing to manage your own additional partitions (daily in this example), You could do something like the following (there may be other ways using date functions):
Note: for performance / maint reasons - it might make more sense to pre-allocate daily 'p_hot' partitions (say 30 or so), so that reorganizing partitions would not affect performance of a partition being heavily written to, or read from). You'll also want to do some 'explain partitions' on your most used queries to verify the optimizer is doing what you expect.
Assuming you have a table dtest:
create table dtest
(
id int auto_increment,
mydate datetime,
primary key (id,mydate)
);
use to_days for a partitioning function:
alter table dtest partition by range (to_days(mydate))
(
PARTITION p_cold VALUES LESS THAN (to_days('2008-04-08')),
PARTITION p_warm VALUES LESS THAN (to_days('2008-04-12')),
PARTITION p_hot VALUES LESS THAN MAXVALUE
);
given a bunch of data:
mysql> select * from dtest;
+----+---------------------+
| id | mydate |
+----+---------------------+
| 1 | 2008-04-12 22:44:50 |
| 2 | 2008-04-11 22:45:11 |
| 3 | 2008-04-10 22:45:16 |
| 4 | 2008-04-09 22:45:19 |
| 5 | 2008-04-08 22:45:23 |
| 6 | 2008-04-07 22:45:26 |
| 7 | 2008-04-06 22:45:31 |
| 8 | 2008-04-05 22:45:34 |
| 9 | 2008-04-04 22:45:38 |
| 10 | 2008-04-03 22:45:42 |
+----+---------------------+
this would give you 3 partitions, similar to what you want, but you'd have to reorganize your p_warm and p_cold partitions each day to 'roll out' the aged data:
mysql> select partition_name,table_rows from information_schema.partitions
where table_name = 'dtest';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p_cold | 5 |
| p_warm | 4 |
| p_hot | 1 |
+----------------+------------+
insert into dtest ( mydate) values ('2008-04-13'); -- new day
mysql> select partition_name,table_rows from information_schema.partitions
where table_name = 'dtest';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p_cold | 5 |
| p_warm | 4 |
| p_hot | 2 |
+----------------+------------+
Now you have to reorganize your partitions:
alter table dtest reorganize partition p_hot into
(
partition p_temp values less than (to_days('2008-04-13')),
partition p_hot values less than maxvalue
);
alter table dtest reorganize partition p_cold,p_warm,p_temp into
(
partition p_cold values less than (to_days('2008-04-09')),
partition p_warm values less than (to_days('2008-04-13'))
);
and now you only have 1 day in your 'hot' table.
mysql> select partition_name,table_rows from information_schema.partitions where table_name = 'dtest';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p_cold | 6 |
| p_warm | 4 |
| p_hot | 1 |
+----------------+------------+
To pre-allocate hot partitions, do something like:
alter table dtest partition by range (to_days(mydate))
(
PARTITION p_cold VALUES LESS THAN (to_days('2008-04-08')),
PARTITION p_warm VALUES LESS THAN (to_days('2008-04-12')),
PARTITION p_hot_1 VALUES LESS THAN (to_days('2007-04-13')),
PARTITION p_hot_1 VALUES LESS THAN (to_days('2008-04-14')),
...
PARTITION p_hot_max VALUES LESS THAN MAXVALUE
);