MySQL Forums
Forum List  »  Partitioning

partitions to quickly drop lots of data each month
Posted by: Darren White
Date: March 22, 2012 02:20AM

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$$

Options: ReplyQuote


Subject
Views
Written By
Posted
partitions to quickly drop lots of data each month
2564
March 22, 2012 02:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.