I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves).

I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands records.

For some reason, the TRUNCATE TABLE command takes really long time to execute (both on the master and on the slave). It takes about 400K ms to execute!! When it runs on the slave, it causes it to lag from the Master. After the TRUNCATE TABLE finishes, everything is back to normal.

I know that one of the slaves didn't receive any reads while performing the TRUNCATE TABLE since its a dedicated slave and the process that reads from that slave was down. Also, on this slave, it took the same amount of time to execute.

Here is the table structure: http://pastebin.com/qEQB4juR

Any thoughts on how I can speed up the TRUNCATE TABLE ?

  • Is there any partition on the table ? Aug 18, 2014 at 7:47
  • Consider FLUSH TABLES tbl_name; prior to TRUNCATE TABLE tbl_name; to ensure there are no 'dirty pages' for the table you are trying to truncate. Your FLUSH TABLES tbl_name; will take more time to flush 30 dirty pages than one dirty page. Using innodb_flush_neighbors=2 may assist with reducing time required and could always be ON to minimize delays. Jul 11, 2022 at 16:25

2 Answers 2


Using TRUNCATE TABLE on an InnoDB table requires a full table lock because TRUNCATE TABLE is DDL (Data Definition Language) not DML (Data Manipulation).

Doing DELETE FROM user_engagements; will not help because MVCC info is written to the undo logs in ibdata1, and that can hold up the table from being emptied. If any uncommitted transactions are holding onto user_engagements, that could potentially hold up a TRUNCATE TABLE as well.

You could rename the table so that it is immediately available

CREATE TABLE user_engagements_new LIKE user_engagements;
ALTER TABLE user_engagements RENAME user_engagements_zap;
ALTER TABLE user_engagements_new RENAME user_engagements;
DROP TABLE user_engagements_zap;

This should replicate quickly except for the last statement.

Give it a Try !!!

If you have MySQL 5.1.16+, TRUNCATE TABLE requires DROP privilege. My answer performs what TRUNCATE TABLE now does.

If you have MySQL 5.1.15 and back, you need DELETE privilege, which my answer covers.

  • 2
    I would use a multi-part RENAME TABLE statement instead of ALTER TABLE in order to make the rename atomic: RENAME TABLE user_engagements TO user_engagements_zap, user_engagements_new TO user_engagements; Another consideration is that DROP TABLE causes the LRU_mutex to be locked while the LRU list is scanned and each entry is removed - this will stall your server. Percona Server has innodb_lazy_drop_table to help with this, but DROP TABLE still can take a long time on ext filesystems. Jul 10, 2012 at 2:01
  • could it be that the truncate table is taking a lot of time because of the partitions on the table (1000), I can remove them if that will lead to the solution?
    – Ran
    Jul 10, 2012 at 6:56
  • What is result of SHOW GLOBAL STATUS LIKE '%dirty%'; ? A large number of dirty pages may be contributing to 'slow truncate' for your table. Jul 1, 2022 at 15:23
  • @WilsonHauck Yes, it can. Each dirty page that belongs to the table being truncated must be flushed first before it can be invalidated from the buffer pool. Jul 1, 2022 at 15:38

First of all you should use latest version of MySQL or MariaDB as in these version this bug has been fixed.

This bug can come up when you DROP or TRUNCATE a table and you have a large buffer pool, there can be server lock-up or stall of multiple seconds which makes your table unresponsive.

Depending on the server’s CPU and memory bandwidth, the stall can be as much as 1 second per 32GB of RAM. If your buffer pool is 1TB that can mean your entire database locks up for as long as 30 seconds. There is different workaround for this bug depending on your circumstances as you have not shared complete detail so, I am suggesting some. You can also find more details MySQL troubleshooting article.

One possible solution can be delete all rows before dropping a table, this will not cause a memory stall because there will be no data in memory.

Another solution can be use of non-InnoDB storage engine for temporary tables. You can specify ENGINE=MyISAM in your CREATE TABLE statement. If you have a large code base and don’t explicitly specify the storage engine, you can change the default storage engine to MyISAM by adjusting the following configuration option:

default_tmp_storage_engine = MyISAM

The good news is that the fix itself has been implemented MariaDB 10.2.19, MySQL 8.0.23 and later.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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