Comments on: Tip: faster than TRUNCATE https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate Blog by Shlomi Noach Fri, 22 Feb 2013 20:21:36 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Log Buffer #182, A Carnival of the Vanities for DBAs https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-183988 Fri, 22 Feb 2013 20:21:36 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-183988 […] 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. […]

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-59885 Tue, 22 Nov 2011 07:38:47 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-59885 @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.

]]> By: grace https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-59883 Tue, 22 Nov 2011 07:22:06 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-59883 hey shlomi,

just want to ask if number of records in the table affects renaming table performance?

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11334 Wed, 10 Mar 2010 16:09:17 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11334 @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).

]]> By: Hans-Henrik https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11332 Wed, 10 Mar 2010 13:46:38 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11332 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.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11328 Wed, 10 Mar 2010 11:34:05 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11328 @Vojtech,
Thank you!

]]> By: Vojtech Kurka https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11327 Wed, 10 Mar 2010 11:30:18 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11327 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

]]> By: Morgan Tocker https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11315 Tue, 09 Mar 2010 23:23:15 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11315 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.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11312 Tue, 09 Mar 2010 14:44:01 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11312 @Darius,

Not a problem, since the dropped table has no contention: no one is writing to it.

]]> By: Darius Jahandarie https://shlomi-noach.github.io/blog/mysql/tip-faster-than-truncate/comment-page-1#comment-11311 Tue, 09 Mar 2010 14:02:39 +0000 https://shlomi-noach.github.io/blog/?p=1896#comment-11311 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.

]]>