How to add colum using INSTANT MySQL 8.0: InnoDB now supports Instant ADD COLUMN

MySQL 8.0: InnoDB now supports Instant ADD COLUMN 

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.

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:

ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;

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.

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)