![]() ![]() The extra information is kept in the physical record along with some meta-data in the data dictionary. This additional meta-data not required for the REDUNDANT row format because the number of columns is already stored in the physical record. 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. 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. The compact and its derived row formats removed some of the meta data from the redundant row format, to save space. The row format dynamic is a minor variant of compact. InnoDB has two main row formats, the redundant and compact row formats. Existing secondary indexes and even the non-leaf pages (internal nodes of the B-Tree) of the clustered index are not impacted. 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. Mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT Mysql> # Do two operations instantly in the same statement ![]() Mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT Mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT Mysql> # ADD/DROP virtual column can be instant Mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT Mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT Mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT Mysql> # DROP DEFAULT to a column can be instant Mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT Mysql> # SET DEFAULT to a column can be instant Mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT Mysql> # Rename the table through ALTER TABLE can be instant Mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT Mysql> # Modify the index can be instant if it's a trivial change Mysql> CREATE TABLE t1 (a INT, b INT, KEY(b)) 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. Furthermore, if ALGORITHM is not specified at all, server will first try the DEFAULT=INSTANT algorithm. This can now be achieved by (always) specifying ALGORITHM=INSTANT, this will guarantee that either the operation is done instantly or not at all. Many users have asked us about a way to avoid time consuming schema changes. The DDL is externalized after it completes. If replication was involved, user may have to wait a even longer time for slave to be ready.This steals resources from user transactions. The DDL operation is resource hungry and puts a high demand on CPU, Memory and IO.Disk space requirements will be more than double, roughly the same size as the existing table.For large tables it can take a long time especially in a Replication setup.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. INPLACE DDL is mostly handled by InnoDB while COPY row by row is handled in the server layer. Prior to MySQL 5.6 the only way to do DDL was to COPY the rows one by one. MySQL 5.6 was the first release to support INPLACE DDL. ![]() We would like to thank and acknowledge this important and timely contribution by Tencent Games. This INSTANT ADD COLUMN patch was contributed by the Tencent Games DBA Team. frm files are in an arcane format that is long past its use by date. Prior to MySQL 8.0 the meta-data (data dictionary) was stored in flat files called. The move to a new transactional data dictionary in MySQL 8.0 has made this task a lot easier for us. The ability to add ADD COLUMNs instantly is the first in a series of DDL statements that we plan to do instantly. Developers constantly need to add new columns to meet the constantly changing business requirements. With ever larger and rapidly growing datasets the ability to do DDL instantly is a must have feature in any web scale database. P.S.Instant DDL has been one of the most requested InnoDB features for a very long time. The first is recommended from that book I mentioned. INSERT INTO zzz (col1,lN) SELECT col1,lN FROM xxx ALTER TABLE zzz add yyy smallint default 0 ALTER TABLE zzz add yyy smallint default NULL - Not using 0 for now It goes something like this:Īssuming db is the database where xxx resides In the book "High Performance MySQL" Second Edition by Jeremy Zawodny & Peter Ziestev there is a high-speed, high-risk method of adding a column to table, regardless of table size. ![]()
0 Comments
Leave a Reply. |