Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking into partitioning a table in my InnoDB database. I have a column corresponding to a UTC timestamp, and I want to partition around that.

The basic idea that I want to capture is a partitioning scheme where, with a fixed number of partitions, we cycle through them as follows:

Given 3 partitions (3 for simplicity)

  • Data with timestamp column from day 1 goes into partition 1
  • Data with timestamp column from day 2 goes into partition 2
  • Data with timestamp column from day 3 goes into partition 3
  • Data with timestamp column from day 4 goes into partition 1
  • ..... day 5 .... partition 2
  • ... rinse and repeat

Basically, extract the day out of the timestamp and put the row into partition DAY MOD N where DAY is the day that the timestamp corresponds to (filtering out hours/minutes/seconds) and N is the number of partitions.

This is probably easily done, but I can't find a similar example to emulate this with. What would the ALTER TABLE query be to partition in this fashion?

Update

By atxdba's suggestion, I tried partitioning by hash. I tried the following statement:

ALTER TABLE table_to_partition PARTITION BY HASH(DAYOFMONTH(FROM_UNIXTIME(my_timestamp))) partitions 8;

This results in error code 1564: This partition function is not allowed. Looking at this list of limitations for partitioning, it doesn't appear that FROM_UNIXTIMESTAMP is supported for partioning a table, so a different transformation from timestamp to date is required.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression. But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows 
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+
share|improve this answer
    
That's a good workaround. I was trying something like that out using "/" instead of the DIV keyword and having no luck. Thanks! –  Chris Sep 30 '13 at 20:54
    
Right, because the "/" division operator returns a float, not an integer. –  Bill Karwin Sep 30 '13 at 21:07
add comment

You might want to look at hash partitioning. You just define how many "buckets" you want and a column to hash by, mysql takes care of distributing the rows for you.

share|improve this answer
    
I looked into that, but that looks like it will just take the mod of the value (which as a unix timestamp, isn't super useful since data will not be contiguous by day). Is there something smarter that MySQL does to handle that scenario? –  Chris Sep 30 '13 at 17:38
    
I thought about your suggestion for hash partitioning, and tried the following: ALTER TABLE table_to_partition PARTITION BY HASH(DAYOFMONTH(FROM_UNIXTIME(my_timestamp))) partitions 8;. This didn't work though, with the error message: Error Code 1564: This partition function is not allowed –  Chris Sep 30 '13 at 18:03
    
An alternative to ensure date distribution is to use Range partitioning - dev.mysql.com/doc/refman/5.5/en/partitioning-range.html. The caveat with this approach is mysql will not auto extend for you so you need a maintenance script to maintain adding new partitions as time goes on. –  atxdba Sep 30 '13 at 18:18
    
There are two problems... FROM_UNIXTIME() takes an integer as an argument, and returns a DATETIME value... so a TIMESTAMP column wouldn't be valid input for that function... PARTITION BY HASH(DAYOFMONTH(my_timestamp)) ... would have been the more correct expression, but that's not valid for a different reason: ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed. –  Michael - sqlbot Sep 30 '13 at 20:25
add comment

quasi-related: remember if you have an auto incrementing column in a partitioned table, it will act different depending on the table type:

MyISAM: the auto inc. column will increment for each partition

InnoDB: the auto inc. column will be unique in the table.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.