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 ?
Best Answer
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 ontouser_engagements
, that could potentially hold up aTRUNCATE TABLE
as well.You could rename the table so that it is immediately available
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 whatTRUNCATE TABLE
now does.If you have MySQL 5.1.15 and back, you need DELETE privilege, which my answer covers.