TRUNCATE TABLE statement sometimes hangs

Question

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.

Answer 1

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;

Answer 2

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 !!!

Answer 3

There is a long standing bug in InnoDB that affects both MySQL (#98869) and MariaDB (MDEV-9459) which results in long stalls, proportional to the size of your buffer pool (typically around 1 second per 32GB of buffer pool, depending on the performance of your CPU and memory bandwidth of your server).

This was fixed in MySQL 8.0.23 and MariaDB 10.2.19. If upgrading is not an option (and I understand that for many it may not be a palatable option for various reasons), there are mitigations and workarounds that can be applied for these DROP / TRUNCATE stalls with MySQL and MariaDB.

For example, the bug doesn't manifest of you DELETE all the rows from the table before you do a DROP. If this is affecting you in the context of use of temporary tables, you can switch your default temporary table engine to MyISAM (or explicitly specify MyISAM when creating your temporary tables).

Disclosure: I wrote the referenced article.

Answer 4

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).

Answer 5

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.

Answer 6

The TRUNCATE may hang, because other processes could use this table, so to check run in shell:

mysqladmin proc

Then kill the queries which are using it (change 123 to Id of the process):

mysqladmin kill 123

Also running:

mysql -e "SHOW ENGINE INNODB STATUS\G"

may also help to investigate any hangs.

Related: How to debug Lock wait timeout exceeded?

Answer 7

I faced same issue, and its resolved by restarting mysql server.

systemctl restart mysqld

I know this is not an expected solution, but if you are stuck then you can restart mysql server.