Deleting data from a large table with replication

Question

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

Answer 1

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.

Answer 2

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. With pt-archiver it's super easy. It won't let slaves lag, there will be no sudden flushing spikes etc, no inflating undo segment.

Answer 3

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way.

If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with

SET sql_log_bin = 0;

The underlying bug is probably this one: http://www.percona.com/blog/2011/04/20/drop-table-performance/.