Comments on: How often should you use OPTIMIZE TABLE? – followup https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup Blog by Shlomi Noach Tue, 04 Aug 2015 10:02:47 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: shlomi https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-322508 Tue, 04 Aug 2015 10:02:47 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-322508 Rick,
The data is real, the table is real. Unfortunately this is 5-year old data and I do not have access to it anymore.

]]> By: Rick James https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-322214 Wed, 29 Jul 2015 22:20:11 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-322214 Look at the index and leaf node page counts for `s` after OPTIMIZE. 705 non-leaf nodes. That’s a “fanout” of about 7:1. That’s terrible. I would expect something like 400:1 since the index is BINARY(16)+INT = 20 bytes plus overhead.

So, either the stats are bogus, or the optimize does a lousy job of packing, or there are a lot of preallocated (but not yet used) blocks. I wonder which it is?

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-22823 Fri, 10 Dec 2010 19:08:06 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-22823 @Jason,

It is safe, in that the table is completely locked down for the duration of the ALTER operation. You can’t read from it, you can’t even look at it.

If you can guarantee no one will access this table, it also makes for an online operation. Otherwise, it is often undesired to work out on a working server, as so many connections get locked down, as well as table cache.

]]> By: Jason https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-22810 Fri, 10 Dec 2010 17:12:46 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-22810 Is it safe to run OPTIMIZE TABLE while the database is in use by an application? Or does it need to be taken down and brought up with the “skip-networking” option so no can connect to it?

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-18493 Tue, 05 Oct 2010 09:38:53 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-18493 Daniel,
For InnoDB tables, OPTIMIZE maps to ALTER TABLE.
see manual

]]> By: Daniël van Eeden https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-18488 Tue, 05 Oct 2010 08:57:50 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-18488 What about a ‘ALTER TABLE…ENGINE=InnoDB’ instead of a OPTIMIZE TABLE to reclaim space?

]]> By: Morgan Tocker https://shlomi-noach.github.io/blog/mysql/how-often-should-you-use-optimize-table-followup/comment-page-1#comment-18443 Mon, 04 Oct 2010 14:03:37 +0000 https://shlomi-noach.github.io/blog/?p=2882#comment-18443 Hi Shlomi,

A couple of things to note:

– I would recommend using xtrabackup –stats to make an assessment on fragmentation. See: http://www.mysqlperformanceblog.com/2009/09/14/statistics-of-innodb-tables-and-indexes-available-in-xtrabackup/

– I don’t think it matters that you are truncating from the end of your primary key versus midway through a primary key.

(More related to the original blog post) What fragmentation OPTIMIZE will not help with, is if ID is monotonic, but (s), (name) and (origin) are not – or worst case are in complete opposite order. As optimize creates it’s temporary table, it will not pre-sort these indexes before creating them – so you have a trickle load of data that causes page splits straight away.

]]>