MySQL Replication – Deleting Data from Large Tables

deleteMySQLreplication

I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication lag or impacting the performance.

After some research, I tried dropping the table. That took quite a few long seconds, during which time my master DB was locked out.

I know I can gradually delete in smaller batches, just wondering is there's a quicker way.

Thanks,
Z

Best Answer

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table.

EXAMPLE

Suppose the table is called mydb.mytable. Do it like this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Doing it this way let's mytable become empty immediately and does not get locked during the deletion of the data. Now, this should go quick on the Master and should replicate. The last line DROP TABLE mytable_old; might take the longest. If that concerns you, then run this on both Master and Slave (based on the answer from Jynus)

SET sql_log_bin = 0;
USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
SET sql_log_bin = 1;

GIVE IT A TRY !!!

CAVEATS

As for deleting data in small chunks, that would not be a good idea if you need to table immediately available after the table data is deleted. Why ?

Doing DELETE FROM mytable.mytable; is a single transaction. All the rows would be prepared for rollback in the InnoDB Architecture. After the DELETE is complete, all that MVCC info has to be discarded. That explains why it takes so long.
Deleting in small chunks would just create more transactions and smaller rollbacks. Notwithstanding, it still creates the same amount of rollback information and work. It may allow you to monitor how much data is left to delete.
Please don't run DELETE FROM mydb.mytable LIMIT 1000;. Using LIMIT on a DELETE without a WHERE clause is not replication safe.

CONSIDERATION #1

Using DELETE FROM tbl ... LIMIT 5000 is not replication-safe because the 5000 rows being deleted may not be the same order or the same set of 5000 rows on a slave. See MySQL Documentation (Replication and LIMIT). Should a DELETE on a Master get interrupted, it has to rollback. In that event, it could be remotely possible that the number of times the DELETE FROM tbl ... LIMIT 5000 is called may not be the same. Extra calls of the DELETE on a Slave is not critical. Too few calls could be bad. This should not happen.

SUGGESTION : Make sure the row count for RTesAll is identical on the Master and all Slaves.

CONSIDERATION #2

Since you are running a Stored Procedure on the Master, the DELETE command will not begin its execution on a Slave until the completion of the DELETE on the Master and the DELETE command is posted in the Master's Binary Logs. With one Master and 4 Slaves (5 DB Servers), there are a certain of calls to DELETE multiplied by 5.

SUGGESTION #1 : You should add set sql_log_bin = 0; to the Stored Procedure

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET sql_log_bin             = 0;
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;
    
    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;          
    SET sql_log_bin             = 1;
END $$
DELIMITER ;

to prevent all DELETEs from replicating. If you are deleting 611992998 rows, deleting every 5000 rows requires executing DELETE ... LIMIT 5000 122399 times (That's 611992998 / 5000 rounded up to next integer).

This will prevent having to write to the binlogs 122399 times.

SUGGESTION #2 : Run the Stored Procedure on the Master and Slaves at the same time.

This prevents replication lag
This gets rid of all the rows at the same time
This is best done during maintenance cycles or slow read times

CONSIDERATION #3

Make sure autocommit is enabled on Master and Slaves. Otherwise, all the deletes will pile up in the undo logs inside ibdata1 before either doing all the deletes at once as a single transaction or a huge rollback.

Give it a Try !!!

MySQL – Deleting Large Amounts of Cascading Records with Minimal Locking

You may do deletes in batches from some script:

1) mark account for deletion (seems you are doing it right now)

2) in script - select one user for some marked account and delete it - as you expect many users per account, this will delete only small portion of all messages and attachments so should be fast enough

3) repeat 2 with remaining users of a given account, one small batch at a time, other processes can get work done between the locks

4) delete the account - no users so fast

If deleting one user can still take too long, you can go one level down and delete batches of say 1000 messages and then delete the account with all users, now without messages so fast again.

The problem is that you lose ACID properties as it all runs in multiple transactions -> you cannot rollback after committing first delete and other queries running in between will see partial data of users (incomplete lists of messages/users depending on the granularity of your deletes). But only you can say if it is a real problem for you or if is_deleted=1 means that account is not used anywhere anymore so it is safe.