TRUNCATE TABLE statement sometimes hangs

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb stalls. There are other bug numbers opened for prior versions as well.

You can use:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

It gets tricky for tables with AUTO_INCREMENT values: new table is created with an AUTO_INCREMENT value which is immediately taken in the working table. If you do no want to use same values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;

You must remember that TRUNCATE TABLE is DDL not DML.

Rather than figuring out where in the plumbing of TRUNCATE TABLE it is getting stuck, you may just have to take matters into your own hands by replacing this

TRUNCATE TABLE sampledb.datatable;

with this

CREATE TABLE sampledb.datatablenew LIKE sampledb.datatable;
ALTER TABLE sampledb.datatable RENAME sampledb.datatablezap;
ALTER TABLE sampledb.datatablenew RENAME sampledb.datatable;
DROP TABLE sampledb.datatablezap;

This may just offline the problem (perhaps hanging on the DROP TABLE), but the table becomes available quickly. DROP TABLE has been improved in MySQL 5.5.23.

I have discussed TRUNCATE TABLE in my past posts

• Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?
• Jan 17, 2012 ; Problem with InnoDB "per table" file sizes
• Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around

Give it a Try !!!