Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?

I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.

The hanging statement is simply:

TRUNCATE TABLE sampledb.datatable;

What can cause this to happen, and how could I fix it?

Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.

share|improve this question
1  
Could you clarify what your migrating from/to? Is it MyISAM to InnoDB, and MySQL to MariaDB? –  Mat Nov 2 '12 at 6:54
    
Sorry, migrate from MySQL to MariaDB. Search engine used in both of is MyISAM –  Haseena Nov 2 '12 at 7:12
    
Are other processes accessing your target DB while migration is running? –  dezso Nov 2 '12 at 20:22
    
After truncate query, insert data into the table. In MySQL, there is no issue. –  Haseena Nov 5 '12 at 4:28
add comment

4 Answers

Without further information it is difficult to say, and I'm not expert specifically on MySQL/MariaDB, but the general reasons for a query unexpectedly taking longer than usual to run are:

  • Locks on that table: the query could be sat waiting for other transactions, that hold locks on that table, to complete. In this case the statement will pause, using no CPU or I/O resource, until the competing locks are released.
  • Locks on other tables: if you have foreign keys elsewhere referring to that table, the truncate operation will need to make sure it is not dropping rows that are still referred to. This means that locks on those other tables could cause it to pause too.
  • Differences in referential integrity checks: if your new DB has FKs defined referring to that table where the old one didn't, that could also explain a difference in truncate speed. If the table being truncated and/or the referring tables are very large this could be far from instant (if this is the reason for your different observed behaviours you'd expect to see the statement impose some IO and/or CPU load).
share|improve this answer
add comment

In mariadb truncate table acts as a drop table and implicit recreation of the empty table. However truncation operations cannot be performed if the session holds an active table lock.

Only with InnoDB table, InnoDB processes TRUNCATE TABLE by deleting rows one by one if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition. As you state that if a table has few records truncate is fast and if you have many records truncate is infinite I can suppose you have InnoDB and FK.

Also, before truncate your table try IS_FREE_LOCK(str) function to check if the table is locked. If so, there is the IS_USED_LOCK function to retrieve the thread is holding the lock.

share|improve this answer
add comment

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

Give it a Try !!!

share|improve this answer
add comment

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;
share|improve this answer
    
In my answer, I said it would be best not to go through the plumbing of TRUNCATE TABLE. It looks like you did, and found the clog. +1 !!! –  RolandoMySQLDBA May 27 '13 at 20:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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