Re: RollOver one table - having 10millin records - Still 100,000 insertion/sec at bottom - Need to delete 30,00,000 records in 1second after every 30 min

[Mikael Ronström]

Hi,
Sounds like the perfect application for a partitioned table.

CREATE TABLE XYZ (....., datetime time_field)
PARTITION BY RANGE (to_days(time_field))
(PARTITION p0 VALUES LESS THAN (to_days("2001-01-12")),
PARTITION p1 VALUES LESS THAN (to_days("2001-01-19")),
PARTITION p2 VALUES LESS THAN (to_days("2001-01-25")),
PARTITION p3 VALUES LESS THAN (to_days("2001-02-02")),
PARTITION p4 VALUES LESS THAN (to_days("2001-02-08")),
PARTITION p5 VALUES LESS THAN (to_days("2001-02-11")),
PARTITION p6 VALUES LESS THAN (to_days("2001-02-15")),
PARTITION p7 VALUES LESS THAN (to_days("2001-02-19")),
......
PARTITION p32 VALUES LESS THAN (to_days("2001-05-03")));

Then whenever you want to increase the time range you add a
partition to the table by:
ALTER TABLE XYZ ADD PARTITION
(PARTITION p33 VALUES LESS THAN (to_days("2001-05-10"));

And when you need to truncate your table you drop partitions
by the command:
ALTER TABLE XYZ DROP PARTITION p0;

Both ADD and DROP partition should be very quick operations
hopefully within your requirements.

Above table is just an example, to_days is a function which the
optimiser recognizes and is thus efficient in handling range
optimisations.

Rgrds Mikael


Ijaz Rashid wrote:
> Need little SQL help please!
>
> How to implement Roll-over in one table of
> database.
> I'm having a table, XYZ containing 10million
> records. I want if record count exceed 10millions,
> then 30 lack
>
> records from top of table should be deleted very
> efficiently. XYZ contains 10milloin records and
> still 1lack
>
> records are inserted in it every second. If i use
> delete statment like
>
> delete from group_1sec_1 ORDER BY time limit
> 3000000;
>
> it takes many 10+ minutes to delete records
> If i use multiple tables, then TRUNCATE table
> works.
> But i want to avoid multiple tables
>
>
> Is there any way to physically / logically divide
> one table into two tables/clusters/diskSpaces...
>
> In this way we can truncate one
> tables/clusters/diskSpaces... which takes 0.5 sec
> for 10million records.
>
> Please send me some reference article as well.
> Thank you for your help.
>
> Ijaz R

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com