Dynamic MySQL partitioning based on UnixTime

Question

My DB design includes multiple MYISAM tables with measurements collected online,

Each row record contains auto-incremented id, some data and an integer representing unixtime.

I am designing an aging mechanism, and i am interested to use MySQL partitioning to partition each such table based on unixtime dynamically.

Say that i am interested that each partition will represent single month of data, last partition should represent 2 months, if records arrive for the next not represented month, the partition that represented 2 months should be reorganized to represent single month, and new partition should be created representing 2 month (1 taken from the last partition and 1 for future measurements),

Additionally, when a new partition is created i am interested that the oldest partition will be dropped.

1. What type of partitioning i should use (my unixtime is not a unique key, and how would i use unixtime for partitioning purposes)?
2. How would i design the partitioning to be fully dynamical based on new records added to the tables?

UPDATE 12.12.12

I have found and interesting link to similar approach to what i have described your-magical-range-partitioning-maintenance-query.

Dowable

Answer 1

1. Partitioning does not need to be based solely on a unique key. However if unique key is present, then it should be included in columns used to partition the table on. To partition table on UNIXTIME column do:

   ALTER TABLE MyTable
   PARTITION BY RANGE COLUMNS (UNIX_TIMESTAMP(datetime_column))
   (
     PARTITION p01 VALUES LESS THAN (2),
     PARTITION p02 VALUES LESS THAN (3),
     PARTITION p03 VALUES LESS THAN (4),
     PARTITION p04 VALUES LESS THAN (MAXVALUE));
   

   Or you can partition on datetime column stright away in MySQL 5.5+ :

   ALTER TABLE MyTable
   PARTITION BY RANGE COLUMNS (datetime_column)
   (
     PARTITION p01 VALUES LESS THAN ('2013-01-01'),
     PARTITION p02 VALUES LESS THAN ('2013-02-01'),
     PARTITION p03 VALUES LESS THAN ('2013-03-01'),
     PARTITION p04 VALUES LESS THAN (MAXVALUE));
   

2. Fully automated version (it would keep every month in its own partition, 5 months of data held):

   ALTER TABLE MyTable
   PARTITION BY RANGE COLUMNS (YEAR(datetime_column)*100 + MONTH(datetime_column))
   (
     PARTITION p201301 VALUES LESS THAN (201301),
     PARTITION p201302 VALUES LESS THAN (201302),
     PARTITION p201303 VALUES LESS THAN (201303),
     PARTITION p201304 VALUES LESS THAN (201304),
     PARTITION p201305 VALUES LESS THAN (201305),
     PARTITION p_MAXVALUE VALUES LESS THAN (MAXVALUE));
   
   
   
   DECLARE @Min_Part int
   DECLARE @Last_Part int
   DECLARE @SQL varchar (1000)
   
   If (select count (distinct MONTH(datetime_column)) from MyTable) > 5 THEN
       BEGIN
   
       select @Min_Part = (select min(year(datetime_column)*100 + month(datetime_column)) from MyTable),
       @Last_Part = (select max(year(datetime_column)*100 + month(datetime_column)) from MyTable)
   
       set @SQL = 'Alter table MyTable REORGANIZE PARTITION p_MAXVALUE (into partition p' +TO_CHAR (@Last_Part) + 'values less than (' + TO_CHAR (@Last_Part) + ')' 
   
       call common_schema.eval (@sql)
   
       set @SQL = 'Alter table MyTable DROP PARTITION p' + TO_CHAR (@Min_Part) 
   
       call common_schema.eval (@sql)
   
   
   END
   

P.S. Apologies if SQL is not exactly correct - cannot parse it right now.

Outspoken