6

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

6

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.
  • Thanks for the insights. I followed your steps until the drop. I used pt-archiver which did a good job. – zvikico Oct 24 '14 at 16:42
  • I'd suggest to do a multi rename table taking the advantage that it's atomic: RENAME TABLE mytable TO mytable_old, mytable_new TO mytable – pconcepcion May 3 '17 at 10:37
5

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.

  • That would be my second option, but as no MySQL version is given, I will be optimistic and assume that the latest MySQL version is used with buffer pool partitioning. Call me a romantic if you want :-) – jynus Oct 21 '14 at 19:48
  • We use MySQL 5.5, so no... Thanks for referring me to the pt-archiver, interesting tool. I can do the deletion myself, I'm just looking for a faster way. – zvikico Oct 21 '14 at 22:01
4

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/.

  • I cannot disable the bin-log as this happens while serving production traffic. +1 for pointing out the bug, though, good find. – zvikico Oct 21 '14 at 22:02
  • 2
    @zvikico This does not disable the binlog, only does it for the current session (where you will execute the maintenance). It will not affect other session's binlogs. – jynus Oct 21 '14 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.