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
Link | March 9th, 2010 at 3:00 pm
Hi Morgan,
So simple...
, thanks!
At least on two machines I have xfs for sure, though... So there's still a small mystery...
Link | March 9th, 2010 at 3:19 pm
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.
Link | March 9th, 2010 at 4:02 pm
@Darius,
Not a problem, since the dropped table has no contention: no one is writing to it.
Link | March 9th, 2010 at 4:44 pm
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.
Link | March 10th, 2010 at 1:23 am
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
Link | March 10th, 2010 at 1:30 pm
@Vojtech,
Thank you!
Link | March 10th, 2010 at 1:34 pm
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.
Link | March 10th, 2010 at 3:46 pm
@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).
Link | March 10th, 2010 at 6:09 pm
hey shlomi,
just want to ask if number of records in the table affects renaming table performance?
Link | November 22nd, 2011 at 9:22 am
@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.
Link | November 22nd, 2011 at 9:38 am
Log Buffer #182, A Carnival of the Vanities for DBAs wrote:
[...] 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. [...]
Link | February 22nd, 2013 at 10:21 pm