MySQL 5.6.17: Improved Online Optimize Table For INNODB and PARTITIONED INNODB Tables

OPTIMIZE TABLE is a maintenance operation used to recover the disk space and improve IO efficiency. It is recommended that the operation is carried out under the cases noted in the optimize table documentation.

MySQL versions prior to MySQL 5.6.17 does not allow concurrent changes (inserts, updates, deletes) to the table when the OPTIMIZE TABLE operation is being performed on that table. This causes downtime for user applications and is relatively high for large tables.

OPTIMIZE TABLE for INNODB or PARTITIONED INNODB tables used ALTER TABLE FORCE operation internally to address the fragmentation problem prior to MySQL 5.6.17. This involves doing a table copy and taking a strong lock in the SQL-layer which blocks all concurrent changes to the table, thus causing the downtime.

MySQL supports online rebuild of tables within the storage engine for INNODB starting from MySQL 5.6.4. This capability has been used utilized to improve the following operations for versions starting from MySQL 5.6.17:

a) OPTIMIZE TABLE.
b) ALTER TABLE FORCE and
c) ALTER TABLE ENGINE= INNODB(Same engine during creation)

The online rebuild involves taking strong lock only for brief interval of time thus reducing the downtime for the operations. Hence while performing the OPTIMIZE TABLE maintenance operation, concurrent changes(inserts, updates and deletes) to the table can be performed in parallel.

Also operations ‘b’ and ‘c’ involves rebuild of the table and the support of online rebuild is enabled for versions starting from MySQL 5.6.17.

Note that these operations will continue to use table copy under any of the below conditions:

a) ‘old_alter_table’ system variable is turned ON.

b) ‘skip-new’ mysqld option is enabled and OPTIMIZE TABLE operation is performed.

c) Algorithm ‘COPY’ is explicitly specified for the ALTER TABLE operations.

d) The table contains FULLTEXT indexes.

4 thoughts on “MySQL 5.6.17: Improved Online Optimize Table For INNODB and PARTITIONED INNODB Tables

  1. Great update in 5.6.17. Thanks! It seems to work as documented. Question: Can you tell me why it reports that “Table does not support optimize, doing recreate + analyze instead” when I do an optimize? Also, why do I observe a temp table being created? Is that the change capture? If not, where is change capture being done?

    Here’s my statement and the output. During this, I was able to update an existing row, so I believe it is not locking.

    mysql> optimize no_write_to_binlog table tablename;
    +————-+———-+———-+——————————————————————-+
    | Table | Op | Msg_type | Msg_text |
    +————-+———-+———-+——————————————————————-+
    | dbname.tablename | optimize | note | Table does not support optimize, doing recreate + analyze instead |
    | dbname.tablename | optimize | status | OK |
    +————-+———-+———-+——————————————————————-+

    1. Hello Valerie,

      Thanks for your comment. Since the ‘optimize table operation’ does involve re-creating the table while allowing concurrent DML operations, the ‘NOTE’
      to indicate the re-creation of the table is retained. Certain ONLINE operations does involve creating a temporary table and is noted in the documentation:
      http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html.

      INNODB uses temporary log files in order to capture the change log due to the concurrent DML operations.

      Regards,
      Nisha.

Leave a Reply

Your email address will not be published. Required fields are marked *


− 5 = zero

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">