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?