1

I'm using AWS RDS MySQL. All the tables are in InnoDB and enable innoDB_file_per_table.

One of my logging tables is growing too fast. I'd like to remove older than 1-week data. Even 1 week of data is 120GB. Every weekend this job will run and remove the old data.

I want to reclaim the disk space and update the statistics. OPTIMIZE table is really painful for us.

What are the optimizations I should perform after deleting a huge number of records?

2

PARTITION BY RANGE(TO_DAYS(date)) and have daily partitions. Every night DROP PARTITION for the week-old partition and REORGANIZE the normally empty "future" partition into tomorrow and a new "future".

That makes the delete essentially free and instantaneous. And keeps the disk space down to not much more than a week's worth of data.

More details .

(No Triggers are needed, no table rotation. Queries can work transparently on PARTITIONed tables. There may need to be some changes to the indexes.)

2

You may try below options.

  1. Using Partition with DateRange

    • Create table with date range each partition containing weekly data.

    • Things to consider are selects and updates which means you should use partition name to specify any where conditions other than PK. Which means that there must be a code change.

    • Have a job that does:

      alter table drop partition 1WEEKAGO_PARTITION_PARTITION-NAME
      

    This solves quick removal of older data.

  2. Rotate Table

    • Let's say your big table is table_a. Create an empty table, call it table_b, with a similar structure to table_a.

    • Once a week, follow below operations in non-peak or night hours for the database.

      1. Create 3 triggers on table_a after insert/update/delete. Have it apply the same changes to table_b.

      2. Now take the data from last week from table_a and apply it to table_b in 1000-record batches until today.

        insert into table_b 
        select * from table_a 
        where date > date_time(LASTWEEK'S DAY) 
        and date < date_time(TODAY_TIME_AFTER_ADDING_TRIGGERS); 
        

        You may break down the where condition in thousand chunk records do this for the above where clause.

        Ensure that the insert from table_a to table_b is started once if triggers have started to populate the data.

      3. rename table_a to table_old, and table_b to table_a.

      4. Once operations are going fine after rename, you may go ahead and drop table table_b.
      5. Don’t forget to drop the triggers.

This solves weekly defragmentation and space usage. But it is a lengthy process.

For option 2 (Rotate Table), I also had reservations when touching a table that had 200 DMLs per second, and per week data of more than 200 GB. It worked. But you may simulate this in test environment and see the throughput. You should have solid confidence in the response time to apps and commands to trigger. Anyway your call. It's just the direction I could suggest which I have crossed. All the best.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.