Innodb/XtraDB tables do benefit from being reorganized often. You can get data physically laid out in primary key order as well as get a better feel for the primary key and index pages, and so use less space, it’s just that MySQL OPTIMIZE TABLE might not be the best way to do it.
If you’re running Innodb Plugin on Percona Server with XtraDB you get the benefit of a great new feature – ability to build indexes by sort instead of via insertion. This process can be a lot faster, especially for large indexes which would get inserts in very random order, such as indexes on UUID column or something similar. It also produces a lot better fill factor. The problem is, OPTIMIZE TABLE for Innodb tables does not get the advantage of it for whatever reason.
Let’s take a look at little benchmark I did by running OPTIMIZE for a second time on a table which is some 10 times larger than the amount of memory I allocated for buffer pool:
|
1 |
CREATE TABLE `a` (<br> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,<br> `c` char(64) DEFAULT NULL,<br> PRIMARY KEY (`id`),<br> KEY `c` (`c`)<br>) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1<br><br>mysql> select * from a order by id limit 10;<br>+----+------------------------------------------+<br>| id | c |<br>+----+------------------------------------------+<br>| 1 | 813cf02d7d65de2639014dd1fb574d4c481ecac7 |<br>| 2 | 62960f5d5d50651e5a5983dacaedfa9a73a9ee87 |<br>| 3 | cea33998792ffe28b16b9272b950102a9633439f |<br>| 4 | 8346a7afa0a0791693338d96a07a944874340a1c |<br>| 5 | b00faaa432f507a0d16d2940ca8ec36699f141c8 |<br>| 6 | 8e00926cf6c9b13dc8e0664a744b7116c5c61036 |<br>| 7 | f151fe34b66fd4d28521d5e7ccb68b0d5d81f21b |<br>| 8 | 7fceb5afa200a27b81cab45f94903ce04d6f24db |<br>| 9 | 0397562dc35b5242842d68de424aa9f0b409d60f |<br>| 10 | af8efbaef7010a1a3bfdff6609e5c233c897e1d5 |<br>+----+------------------------------------------+<br>10 rows in set (0.04 sec)<br><br># This is just random SHA(1) hashes<br><br>mysql> optimize table a;<br>+--------+----------+----------+-------------------------------------------------------------------+<br>| Table | Op | Msg_type | Msg_text |<br>+--------+----------+----------+-------------------------------------------------------------------+<br>| test.a | optimize | note | Table does not support optimize, doing recreate + analyze instead |<br>| test.a | optimize | status | OK |<br>+--------+----------+----------+-------------------------------------------------------------------+<br>2 rows in set (3 hours 3 min 35.15 sec)<br><br>mysql> alter table a drop key c;<br>Query OK, 0 rows affected (0.46 sec)<br>Records: 0 Duplicates: 0 Warnings: 0<br><br>mysql> optimize table a;<br>+--------+----------+----------+-------------------------------------------------------------------+<br>| Table | Op | Msg_type | Msg_text |<br>+--------+----------+----------+-------------------------------------------------------------------+<br>| test.a | optimize | note | Table does not support optimize, doing recreate + analyze instead |<br>| test.a | optimize | status | OK |<br>+--------+----------+----------+-------------------------------------------------------------------+<br>2 rows in set (4 min 5.52 sec)<br><br>mysql> alter table a add key(c);<br>Query OK, 0 rows affected (5 min 51.83 sec)<br>Records: 0 Duplicates: 0 Warnings: 0<br> |
That’s right! Optimizing table straight away takes over 3 hours, while dropping indexes besides primary key, optimizing table and adding them back takes about 10 minutes, which is close than 20x speed difference and more compact index in the end.
So if you’re considering running OPTIMIZE on your tables consider using this trick, it is especially handy when you’re running it on the Slave where it is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure there is not a ton of queries starting reading table with no indexes and bringing the box down.
You can also use this trick for ALTER TABLE which requires a table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE.
P.S I do not know why this was not done when support for creating an index by sorting was implemented. It looks very strange to me to have this feature implemented but a majority of high-level commands or tools (like mysqldump) do not get the advantage of it and will use the old slow method of building indexes by insertion.
Note: In MySQL 5.5, OPTIMIZE TABLE does not take advantage of “InnoDB Fast Index Creation” feature. This limitation is documented in the MySQL 5.5 official documentation.
From: https://dev.mysql.com/
OPTIMIZE TABLEfor anInnoDBtable is mapped to anALTER TABLEoperation to rebuild the table and update index statistics and free unused space in the clustered index. This operation does not use fast index creation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key.
Percona Server 5.5.11 and higher allows utilizing fast index creation feature for “ALTER TABLE” and “OPTIMIZE TABLE” operations, which can potentially speed them up greatly. This feature is controlled by the expand_fast_index_creation system variable which is OFF by default. This variable was implemented in Percona Server – 5.5.16-22.0.
More Information:
https://www.percona.com/blog/
https://www.percona.com/doc/
MySQL 5.6 introduced the online DDL feature which provides support for in-place table alterations. As of MySQL 5.6.17, OPTIMIZE TABLE can be performed in-place for rebuilding regular and partitioned InnoDB tables which makes “OPTIMIZE TABLE” operation much faster.
Table 14.13 Online DDL Support for Table Operations
| Operation | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
|---|---|---|---|---|
| Optimizing a table | Yes* | Yes | Yes | No |
Optimizing a table
OPTIMIZE TABLE tbl_name;
Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables withFULLTEXTindexes. The operation uses theINPLACEalgorithm, butALGORITHMandLOCKsyntax is not permitted.
Resources
RELATED POSTS
