Instant DDL has been one of the most requested InnoDB features for a very long time. With ever larger and rapidly growing datasets the ability to do DDL instantly is a must have feature in any web scale database. Developers constantly need to add new columns to meet the constantly changing business requirements. The ability to add ADD COLUMNs instantly is the first in a series of DDL statements that we plan to do instantly. The move to a new transactional data dictionary in MySQL 8.0 has made this task a lot easier for us. Prior to MySQL 8.0 the meta-data (data dictionary) was stored in flat files called .frm files. The .frm files are in an arcane format that is long past its use by date.
This INSTANT ADD COLUMN patch was contributed by the Tencent Games DBA Team. We would like to thank and acknowledge this important and timely contribution by Tencent Games.
Background
MySQL 5.6 was the first release to support INPLACE DDL. Prior to MySQL 5.6 the only way to do DDL was to COPY the rows one by one. INPLACE DDL is mostly handled by InnoDB while COPY row by row is handled in the server layer. Up until 8.0 (see labs release), InnoDB used to add columns to a table by rebuilding the table even for the INPLACE DDL algorithm.
- For large tables it can take a long time especially in a Replication setup.
- Disk space requirements will be more than double, roughly the same size as the existing table.
- The DDL operation is resource hungry and puts a high demand on CPU, Memory and IO. This steals resources from user transactions.
- If replication was involved, user may have to wait a even longer time for slave to be ready. The DDL is externalized after it completes.
New INSTANT algorithm
Many users have asked us about a way to avoid time consuming schema changes. This can now be achieved by (always) specifying ALGORITHM=INSTANT , this will guarantee that either the operation is done instantly or not at all. Furthermore, if ALGORITHM is not specified at all, server will first try the DEFAULT=INSTANT algorithm. If it can not be done, then server will try INPLACE algorithm; and if that can not be supported by SE, server will finally try COPY algorithm. The new syntax looks like:
|
1 |
ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT; |
The benefit of the INSTANT algorithm is that only metadata changes are made in the data dictionary. There is no need to acquire metadata lock during SE changes and we don’t touch the data of the table. This change impacts the LOCK=… semantics too. There is no need to specify the LOCK for INSTANT algorihtm. With ALGORITHM=INSTANT, LOCK can not be set to any other value than DEFAULT, otherwise you will get an error:
|
1
2
3
4
|
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 11, ALGORITHM=INSTANT, LOCK=NONE; ERROR HY000: Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE # ALGORITHM=INSTANT and LOCK=DEFAULT are OK though. ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 13, ALGORITHM=INSTANT, LOCK=DEFAULT; |
If you set ALGORITHM=INSTANT for any operation that cannot be done instantly you will get an error, as below. The idea here is to fail upfront and fail quickly and not do a silent conversion and switch to another algorithm behind the scenes.
|
1
2
|
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 12, DROP COLUMN j, ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. |
Currently, InnoDB supports INSTANT algorithm for these operations:
- Change index option
- Rename table (in ALTER way)
- SET/DROP DEFAULT
- MODIFY COLUMN
- Add/drop virtual columns
- Add columns(non-generated) – We call this instant ADD COLUMN
You can specify more than one operation in a single statement with ALGORITHM=INSTANT.
Here are some simple examples for the operations which can be done instantly:
|
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
39
40
41
42
43
44
45
46
47
|
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b)); Query OK, 0 rows affected (0.70 sec) mysql> # Modify the index can be instant if it's a trivial change mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # Rename the table through ALTER TABLE can be instant mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.26 sec) mysql> # SET DEFAULT to a column can be instant mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # DROP DEFAULT to a column can be instant mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # MODIFY COLUMN can be instant mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # ADD/DROP virtual column can be instant mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.38 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> # Do two operations instantly in the same statement mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DROP TABLE t2; Query OK, 0 rows affected (0.36 sec) |
How does it work?
The problem we face is how to parse the physical record on a page once the metadata changes after an instant ADD COLUMN? Note that the physical record here means the record stored in the leaf pages of a clustered index. Existing secondary indexes and even the non-leaf pages (internal nodes of the B-Tree) of the clustered index are not impacted.
InnoDB has two main row formats, the redundant and compact row formats. The row format dynamic is a minor variant of compact. The compact and its derived row formats removed some of the meta data from the redundant row format, to save space. Due to this “space saving” change we always need to lookup the meta-data from the internal meta-data structures when we have to deserialise the data in the physical rows on a page. To make instant add column work we need to add some metadata to the physical record on a page for DYNAMIC and COMPACT row formats. This additional meta-data not required for the REDUNDANT row format because the number of columns is already stored in the physical record.
The extra information is kept in the physical record along with some meta-data in the data dictionary. This is very different to some of the downstream hacks based on the same Tencent patch that store similar meta-data in obscure and unused parts of the tablespace. We think that storing the meta-data in the appropriate data dictionary tables and making it transactionally consistent makes it more robust and is a more natural fit. This new metadata is stored in the physical record. This new meta-data includes a flag that is stored in info_bits. This new information in the info_bits is used to track if the record was created after first instant ADD COLUMN or not. We also use info_bits to track the number of fields/columns in the physical record. The number of columns when the table undergoes first instant ADD COLUMN and all default value of newly added columns are stored in the data dictionary. These two pieces of information are stored in se_private_data column of the data dictionary tables.
With this extra information, it’s now possible for the ADD COLUMN operation to be executed instantly, without modifying any of the rows in the table. If there is no instant ADD COLUMN then all rows in a table will be in the same format as before. After an instant ADD COLUMN is issued, any update to the table will write rows in the new format along. The default values if any are looked up from the data dictionary.
In every instant ADD COLUMN, the default value of the newly added columns is tracked separately. The default value of these columns can be changed at any time. Therefore both the number of instant columns and default values can be discarded after the table gets rebuilt or truncated, furthermore, the rows in the table can be changed into old format as before. If the table is a partitioned table, then it’s possible that different partitions have different number of instant columns, and require different number of default values. If some partitions get rebuilt, truncated or re-created, the rows in the partition can be changed into old format as before too.
How to observe
User can observe the result of instant ADD COLUMN via views from information_schema. More specifically, some new fields are added to I_S.innodb_tables and I_S.innodb_columns. Please note that for other operations which can be done instantly, there is no need to provide new status for observation. Please see example below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> CREATE TABLE t1 (a INT, b INT); Query OK, 0 rows affected (0.06 sec) mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%'; +----------+---------+--------------+ | table_id | name | instant_cols | +----------+---------+--------------+ | 1065 | test/t1 | 0 | +----------+---------+--------------+ 1 row in set (0.22 sec) mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065; +----------+------+-------------+---------------+ | table_id | name | has_default | default_value | +----------+------+-------------+---------------+ | 1065 | a | 0 | NULL | | 1065 | b | 0 | NULL | +----------+------+-------------+---------------+ 2 rows in set (0.38 sec) |
As we can see, a new column called ‘instant_cols’ which represents the number of instant columns is introduced in innodb_tables, while two new columns about the default value called ‘has_default’ and ‘default_value’ are introduced in innodb_columns.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%'; +----------+---------+--------------+ | table_id | name | instant_cols | +----------+---------+--------------+ | 1065 | test/t1 | 2 | +----------+---------+--------------+ 1 row in set (0.03 sec) mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065; +----------+------+-------------+---------------+ | table_id | name | has_default | default_value | +----------+------+-------------+---------------+ | 1065 | a | 0 | NULL | | 1065 | b | 0 | NULL | | 1065 | c | 1 | NULL | | 1065 | d | 1 | 800003e8 | +----------+------+-------------+---------------+ 4 rows in set (0.36 sec) |
Note that the table_id didn’t change. It’s not a rebuild of the table any more! And as we can see the ‘instant_cols’ is now set to 2, this means that there are column a and column b in the table when the first instant ADD COLUMN happens. The default values of column c and d are remembered in the innodb_columns. The User can now know if a column is added instantly if the has_default is 1. Also if ‘has_default’ is 1, the default value of this column is stored in ‘default_value’ field. The default_value of d is set to the internal binary format of the value 1000.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!'; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%'; +----------+---------+--------------+ | table_id | name | instant_cols | +----------+---------+--------------+ | 1065 | test/t1 | 2 | +----------+---------+--------------+ 1 row in set (0.03 sec) mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065; +----------+------+-------------+--------------------------+ | table_id | name | has_default | default_value | +----------+------+-------------+--------------------------+ | 1065 | a | 0 | NULL | | 1065 | b | 0 | NULL | | 1065 | c | 1 | NULL | | 1065 | d | 1 | 800003e8 | | 1065 | e | 1 | 48656c6c6f204d7953514c21 | +----------+------+-------------+--------------------------+ 5 rows in set (0.36 sec) |
After another instant ADD COLUMN, again the table_id didn’t change. The ‘instant_cols’ won’t change, and default values of column e is also remembered.
Side effects and trade offs
Since the instant ADD COLUMN won’t rebuild the table any more, so there are some side effects:
- In older versions the row size would have been be checked upfront, and so the ADD COLUMN will have failed at the start. However, with the new instant ADD COLUMN, row size will only be checked by future updates to the rows.
- In earlier versions, if the table or index was corrupted, it was possible to ‘fix’ things by rebuilding the table. With instant ADD COLUMN it is more challenging and we are looking at ways to mitigate this.
Limitations
Currently there are some limitations:
- Only support adding columns in one statement, that is if there are other non-INSTANT operations in the same statement, it can’t be done instantly
- Only support adding columns at last, not in the middle of existing columns
- Not support COMPRESSED row format, which is seldom used
- Not support a table which already has any fulltext index
- Not support any table residing in DD tablespace
- Not support temporary table(it goes with COPY)
How to add columns in old ways
Although the instant ADD COLUMN is supported, user can still add columns in the old way. That is if user intends to add column by rebuilding the table or copying rows, then they can still issue the ALTER TABLE with ALGORITHM=INPLACE/COPY, or even specify the FORCE keyword. In these ways, the ADD COLUMN would be done traditionally.
Conclusion
MySQL now supports a new algorithm called INSTANT for ALTER TABLE. Along with this, the most exciting part is that now ADD COLUMN (at last) can be done instantly in InnoDB, so it’s no longer a pain point for users. The new function can be observed easily. Please try with this exciting new feature and any feedback is welcome!
Thanks for using MySQL!