partitions to quickly drop lots of data each month

[Darren White]

I have (log) tables growing at a fixed period of 1 row per second. So 2.7 million rows after one month. The customer wants these logs tables in a 30 day "rotating buffer".

So I thought a simple way to do this would be to let the tables grow to 2 months then each month delete the previous month. I need some help implementing this. I was given this advise in another forum area:

Question> I'll just let it grow to 2 months then every month just delete the previous month using an EVENT?
Answer> Or use PARTITIONs, and DROP PARTITION, plus REORGANIZE PARTITION to get a new one, each month.

Sounds great but I need perhaps more specific help in implementing..

Here is a typical log table:


delimiter $$
CREATE TABLE `log_01` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tension` float NOT NULL DEFAULT '0',
`cable_speed` float NOT NULL DEFAULT '0',
`reel_speed_rpm` float NOT NULL DEFAULT '0',
`meter_out` float NOT NULL DEFAULT '0',
`deviation` float NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54337 DEFAULT CHARSET=latin1$$