Mysql Question posted in
The official MySQL documentation can be found here.

How to delete most data from a 2,000GB Mysql InnoDB table

I have a Mysql InnoDB table with storage size(+index) over 2,000GB, There are 14 billion rows in that table..

I need to drop data added over 1 month ago, and figure out a way to always keep recent 1 month data in that table.

For data deletion, what I know is I cannot just do below as it generates huge amount of undo logs:

delete * from where eventtime < CURRENT_DATE() - INTERVAL 30 DAY

What I figured is to create a new table with same structure to the original table, and then create INSERT, UPDATE, DELETE triggers on the original table to sync all the writes to the new table, let it be there for 1 month, and then:

RENAME TABLE myOriginalTableName TO Old, New TO myOriginalTableName;

Then

DROP TABLE Old;

As for always keeping recent 1 month data in the table, I’m planning to have a daily cronjob to delete data older than 1 month, and have a weekly cronjob to OPTIMIZE TABLE to reclaim the freed space after deletes.

Any issues with this plan? Any suggestions if it would not work?

Tags:

2

Answers


  1. 0

    you can use mysql event scheduler to trigger this process ( delete and optimize ) every day

    Create event delete_optimize
on schedule every 1 day 
do 
begin
  delete from myOriginalTableName where eventtime < current_date() - INTERVAL 30 day;
  Optimize table myOriginalTableName;
end
    Login or Signup to reply.
  2. 0

    You can limit the numbers of your delete, so you have smaller transactions, which execute very fast.

    DELTE * FROM tablename WHERE eventtime < CURRENT_DATE() - INTERVAL 30 DAY LIMIT 10000;

    Just schedule this from your application e.g. by a cron-job to run infinite or until no more rows are deleted.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search