I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month.
If I do DELETE FROM NOTIFICATION WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);
, all the IOPS will be taken, the process will take hours, and a lot of new entries cannot be inserted due to "Lock wait timeout exceeded; try restarting transaction".
I was trying to do the way describe in here:http://mysql.rjweb.org/doc.php/deletebig
However, I am using UUID instead of increment ID.
What is the correct and efficient way to delete those rows while not affect new data being insert/update?
Best Answer
Make a temp table, switch it in and out, and copy the last 30 days data into it.
In your off hours, drop the old table
Here are the Advantages to doing DELETEs like this
NOTIFICATION
is emptied fast by means switching in an empty table.NOTIFICATION
is immediately available for new INSERTsNOTIFICATION
while new INSERTs can take place.NOTIFICATION
does not interfere with new INSERTsGive it a Try !!!