AWS RDS Post Optimisation after a huge delete 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?
You may try below options.


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


Create 3 triggers on table_a after insert/update/delete. Have it apply the same changes to table_b.
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 &gt; date_time(LASTWEEK'S DAY) 
and date &lt; 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.
rename table_a to table_old, and table_b to table_a.
Once operations are going fine after rename, you may go ahead and drop table table_b.
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.