Bulk Delete for Large Table in MySQL

Question

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?

Answer 1

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data 
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

1. NOTIFICATION is emptied fast by means switching in an empty table.
2. NOTIFICATION is immediately available for new INSERTs
3. The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
4. Dropping the old version of NOTIFICATION does not interfere with new INSERTs
5. NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

Answer 2

My favorite is pt-archiver from Percona Toolkit. It takes care of MySQL load, replication lag.

Answer 3

I agree with the above answer to use pt-archiver.

But configuring options can be confusing! I played with a bunch of settings and found the following to work pretty well. You can make this into a bash script and run it, ideally in a screen session as it'll take a while to work.

#!/bin/bash

# Deletes data in the given table.
# This takes the strategy of deleting based on
# some indexed key, possibly the primary key.
# It will repeatedly delete the first 1000
# records found up to the maximim $MAXVAL.
# This seems to have OK performance when working
# against a small Aurora instance.
# It deletes around 100,000 records every minute.

# You'll need to have Percona tools installed.
# This was tested against percona-toolkit-3.3.1
# Note the version of percona installed by apt
# is old as of writing (11/22/22). I had to
# download from Percona's website. 

export HOST=
export USER=
export DB=
export TABLE=
export FIELD=
export MAXVAL=

pt-archiver \
    --user $USER \
    --source h=$HOST,D=$DB,t=$TABLE \
    --purge \
    --where '$FIELD < $MAXVAL' \
    --bulk-delete \
    --progress 10000 \
    --statistics \
    --commit-each \
    --limit 1000 \
    --skip-foreign-key-checks \
    --no-check-charset \
    --ask-pass \
    --optimize $TABLE

https://gist.github.com/eastside/26eb4a093b0bb507115d63376e3a792f

Answer 4

create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ;

drop table notification;

RENAME notification_temp TO NOTIFICATION;