Re: help making event for auto delete of partitions
> The customer has specified they do not require more than 1 month of data
Then, shouldn't you partition by day, not by month???
It seems like the discussion so far leads to 11-12 months of data being kept. If you need a full 12 months, there are issues.
I prefer to have a 'rolling' set of partitions (32 days / 13 months / whatever), and have the EVENT (or Perl script) check to see if it is time to do maintenance, then do the maintenance. That would be
* If the oldest partition is 'too old', then ALTER TABLE DROP PARTITION ...;
* If we are about to spill into a new day/month, add a new partition. This would be via
ALTER TABLE REORGANIZE PARTITION future
INTO
PARTITION from... VALUES LESS THAN (...)
PARTITION future VALUES LESS THAN MAXVALUE;
The information_schema provides the list of partitions, and I give the partitions recognizable names. For the current month, I would have:
PARTITION from201206 VALUES LESS THAN (TO_DAYS('2012-07-01'))
Note that June ends before Jul 01.
Further, I run the script more often than necessary (hourly for daily partitions; daily for monthly partitions), so it usually does nothing. Why run "too often"?
* If the script fails to run once, it will soon be run again.
* If I need to change the things (eg, drop from 60 days to 30 days), the extra runs will gradually do the adjustment.
There are situations (especially with DATETIME) where the 'first' partition is checked in all queries. (This is because of the possibility of NULL values.) Hence, it is slightly beneficial to have an empty 'start' or 'old' partition.
I like to have a 'future' partition that I never deliberately use (or briefly use). This is because the maintenance script might fail to run. If it does, then new data piles up in 'future'. Once I discover the problem (and get the script running again), then the REORGANIZE will automagically clean things up (after a few iterations).