One of the serious limitations in the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter has already blogged about it before, here I’ll just briefly reiterate other cases that might benefit from that feature:
- when
ALTER TABLEdoes require copying the data into a temporary table, secondary indexes are updated by inserting individual rows rather than sorting; OPTIMIZE TABLEcould be faster if secondary indexes were temporarily dropped and then recreated using fast index creation;- dumps produced by mysqldump first create tables with all secondary indexes and then load the data, which is also inefficient.
Percona Server as of versions 5.1.56 and 5.5.11 allows utilizing fast index creation for all of the above cases, which can potentially speed them up greatly. This feature is controlled by the expand_fast_index_creation system variable which is OFF by default.
Let’s look at each of the above cases in more detail.
ALTER TABLE
By temporarily dropping secondary indexes from the new table before copying the data, and then recreating them later, ALTER TABLE can take advantage of the fast index creation feature even when it has to copy the entire table.
To illustrate this, I have performed a number of simple benchmarks. Let’s start with a table containing 4 million rows and one secondary key:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT RAND() FROM t; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 . . . mysql> INSERT INTO t(c) SELECT RAND() FROM t; Query OK, 2097152 rows affected (10.11 sec) Records: 2097152 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t ADD KEY (c); Query OK, 0 rows affected (18.56 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Let’s trigger a table rebuild by adding a new column and see what execution time is like when the default method is used:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> SET profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD COLUMN v VARCHAR(1); Query OK, 4194304 rows affected (1 min 1.97 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SHOW PROFILE; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.000054 | | checking permissions | 0.000004 | | checking permissions | 0.000004 | | init | 0.000008 | | Opening tables | 0.000118 | | System lock | 0.000007 | | setup | 0.000027 | | creating table | 0.002255 | | After create | 0.000050 | | copy to tmp table | 61.816063 | | rename result table | 0.161528 | | end | 0.000007 | | Waiting for query cache lock | 0.000002 | | end | 0.000007 | | query end | 0.000003 | | closing tables | 0.000008 | | freeing items | 0.000021 | | cleaning up | 0.000003 | +------------------------------+-----------+ 18 rows in set (0.00 sec) |
Now let’s see how performance is affected when turning expand_fast_index_creation on. Here and in later examples I’m extending the VARCHAR column to trigger table rebuilds without affecting the table size.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t MODIFY v VARCHAR(2); Query OK, 4194304 rows affected (36.07 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SHOW PROFILE; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.000054 | | checking permissions | 0.000004 | | checking permissions | 0.000005 | | init | 0.000010 | | Opening tables | 0.000027 | | System lock | 0.000008 | | setup | 0.000040 | | creating table | 0.002176 | | After create | 0.000058 | | copy to tmp table | 18.083490 | | restoring secondary keys | 17.824109 | | rename result table | 0.162041 | | end | 0.000008 | | Waiting for query cache lock | 0.000002 | | end | 0.000007 | | query end | 0.000003 | | closing tables | 0.000008 | | freeing items | 0.000019 | | cleaning up | 0.000003 | +------------------------------+-----------+ 19 rows in set (0.00 sec) |
As seen from the SHOW PROFILE output, copying the data to a temporary table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast index creation. So we have 36 seconds in total which is about 1.7 times faster than updating indexes by insertion.
Let’s see if having more secondary indexes in the table makes any difference:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD KEY (c), ADD KEY(c); Query OK, 0 rows affected (36.42 sec) Records: 0 |