Tweeted twitter.com/StackDBAs/status/896929762261610496

occurred Aug 14, 2017 at 3:01

Added replication tag since TRUNCATE TABLE passes through Master/Slave setup and would affect each DB Server on execution

Link

edited Aug 13, 2017 at 23:31

RolandoMySQLDBA

184.3k
33
323
531

added 59 characters in body; edited title

Source Link

edited Jul 9, 2012 at 21:27

RolandoMySQLDBA

184.3k
33
323
531

What can cause truncate tableTRUNCATE TABLE to take a really long time?

I'm running MySql 5MySQL5.5 with MMaster/SSlave replication (1 master, 2 slaves).

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

fromFor some reason, the truncateTRUNCATE TABLE command takes really long time to execute (both on the master and on the slave) it. It takes about 400K ms to execute!! and whenWhen it runs on the slave, it casescauses it to lag from the masterMaster. afterAfter the truncate doTRUNCATE TABLE finishes, everything is back to normal.

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

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

anyAny thoughts on how I can speed up the truncateTRUNCATE TABLE ?

What can cause truncate table to take a really long time?

I'm running MySql 5.5 with M/S replication (1 master, 2 slaves).

I have a process that runs once a week and truncate a specific table, the table is not large, a few thousands records.

from some reason, the truncate command takes really long time to execute (both on the master and on the slave) it takes about 400K ms to execute!! and when it runs on the slave it cases it to lag from the master. after the truncate do finishes, everything is back to normal.

I know that one of the slaves didnt received any reads while performing the truncate since its a dedicated slave and the process that reads from that slave was down, and also on this slave it took the same time to execute.

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

any thoughts how I can speed the truncate?

What can cause TRUNCATE TABLE to take a really long time?

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 ?