How to evaluate whether it's time to optimize a MySQL InnoDB table?

Question

I have a bunch of InnoDB tables in a MySQL server. I would like to see if I can improve the performance of these tables. I'm considering using the command OPTIMIZE TABLE to do the trick. However, before actually executing the OPTIMIZE TABLE command, I would like to know whether it's worthwhile to execute it.

Is there any command to evaluate if a table needs to be optimized? For example, given that the physical data structure of InnoDB tables are trees, is there a command to know whether the trees are balanced or not?

Thank you.

Answer 1

Short Answer: Not really. Don't bother with OPTIMIZE TABLE.

Long Answer:

• SHOW TABLE STATUS provides Data_free, but there are many flaws in that number, making it mostly useless.

• InnoDB keeps its B+Trees relatively well balanced. A DELETE will make some effort to combine two adjacent blocks with little in them.

• The disk allocation for a table only grows, never shrinks. Certain ALTERs, plus OPTIMIZE, will rebuild the table, thereby giving space back to the OS. But this is about disk space, not about 'performance'. And that won't even work if the table were built with innodb_file_per_table = OFF.

• Fragmentation in a B+Tree has very little impact on performance.

• Deleting lots of rows in a large table leads to fragmentation of the table and performance during the Delete. There are several ways to avoid such a Delete. But that is a different topic -- http://mysql.rjweb.org/doc.php/deletebig

• OPTIMIZE TABLE mostly blocks other operations on the table for a long time for a big table. This is another argument against ever doing it.