TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass.
TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the table and creating a new one (and it all depends on the MySQL version, see the manual).
What’s faster than TRUNCATE, then? If you don’t have triggers nor FOREIGN KEYs, a RENAME TABLE can come to the rescue. Instead of:
TRUNCATE log_table
Do:
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;
I found this to work well for me. Do note that AUTO_INCREMENT values can be tricky here: the “new” table is created with an AUTO_INCREMENT value which is immediately taken in the “working” table. If you care about not using same AUTO_INCREMENT values, you can:
ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;
Just before renaming.
I do not have a good explanation as for why the RENAME TABLE succeeds to respond faster than TRUNCATE.
“I do not have a good explanation as for why the RENAME TABLE succeeds to respond faster than TRUNCATE.”
Try deleting a large file with ext3 versus renaming it. This will explain a lot of the problem 😉
Hi Morgan,
So simple… :D, thanks!
At least on two machines I have xfs for sure, though… So there’s still a small mystery…
Isn’t the table being dropped after renamed though? It should still have the same contention on the disk (if that was the original problem), as long as you are deleting the data.
@Darius,
Not a problem, since the dropped table has no contention: no one is writing to it.
shlomi: The table renamed & dropped still has some contention issues (lock_open mutex will be held), but your trick is still useful for emptying a table and being able to use it again.
Morgan, large ibd files are not the only cause. Shlomi, It’s a bug, just look at: http://bugs.mysql.com/bug.php?id=35077 http://bugs.mysql.com/bug.php?id=51325 http://bugs.mysql.com/bug.php?id=51557
@Vojtech,
Thank you!
The big difference, i think is the time the table ‘log_table’ is unavailable.
If you truncate, the table will be locked until the space has been freed (8-10 seconds for _very_ large tables, if you have IO at the same time). If you do the renaming first, the empty table will appear _very_ fast, while the dropping of the old table does not involve locking of any table that is in use, and so – probably doesn’t matter much if it takes a few seconds.
@Hans,
Well, I would do well with 8-10 seconds. However, as I described, I waited for long minutes. Connections got into Locked state. Nothing would happen (on one occasion I waited for 15 minutes before calling it off).
hey shlomi,
just want to ask if number of records in the table affects renaming table performance?
@grace,
The RENAME itself should not be affected by number of rows, since it merely maps to a file rename. I say “should” though I’ve renamed very large tables with no overhead.
[…] Jamie Thomson asks why do you abbreviate schema names?. Shlomi Noach tries to solve the issue that “there is no consistent convention as for how to write [about table aliases in] an SQL query” in proper sql table alias use conventions. Noach also gives us a tip: faster than truncate. […]