Fastest Way to ADD Column with MySQL 8.0 ALTER INSTANT Features

MySQL8.0: Performance Improvement with new features INSTANT ADD Column

       

Abstract- Flexible and dynamic concepts can help to balance maintainability and complexity. With ever bigger and growing dataset, the ability to do DDL instantly will help developers to scale more complex RDBMS solutions.

In this paper, we outline performance measurements of various options of ALTER TABLE algorithm-COPY, INPLACE, INSTANT.

We describe the definition of concept, advantages, and limitations and how it is value add on current business cases based on existing performance test.

                                                                           I.  Introduction

ALTER TABLE changes the structure of table. ALTER operations that supports the INSTANT ADD COLUMN is introduced in MySQL 8.0.12.if algorithm is not specified, server will first try the DEFAULT=INSTANT algorithm. If it cannot be done, then server will try INPLACE algorithm; at last server will try COPY algorithm.

The new syntax looks like:

ALTER TABLE table_name [alter_specification] ,ALGORITHM=INSTANT;

If you set ALGORITHM=INSTANT for any operation that can’t be done instantly you will get an error, as below. The idea here is to fail upfront, fail quickly, not do a silent conversion, and switch to another algorithm behind the scenes.

                                              

INSTANT Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous.

INSTANT Algorithm include:

1)     Adding a column. This feature is referred to as “INSTANT ADD COLUMN”

2)     Adding or dropping a virtual column.

3)     Adding or dropping a column default value.

4)     Changing the index type.

5)     Renaming a table

Views in information_schema (I_S) Tables

Users can see INSTANT ADD COLUMN information from the below table

1. information_schema.innodb_tables :- Number of columns before first INSTANT ADD COLUMN would be displayed for every table for the schema.

I_S.innodb_tables has column called INSTANT_COLS, to remember the number of columns when first instant ADD column happened this table.For partition table, this number  may differ for each partition

2. Information_schema.innodb_columns:- Every default value of columns would be displayed for each table.and default value would only be displayed in a binary format which is used in InnoDB internally.

I_S.innodb_columns has two more columns called HAS_DEFAULT & DEFAULT_VALUE. If HAS_DEFAULT =1 means this is instant column with default value. DEFAULT_VALUE is only valid when HAS_DEFAULT=1. It shows the internal binary for the default value.

Limitations

Currently these 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 temporary table
• Not support a table which already has any fulltext index.
• Not support COMPRESSED row format, which is seldom used

                                                                                                     II. STUDIES AND FINDINGS

To test the various ALTER algorithm COPY, INSTANT, INPLACE. I am going to demonstrate table called tbl_ITRequesthistory ,which holds 15 millions of real time customer data.

MySQL 8.0.13 ALTER TABLE with COPY Algorithm.

Command to ADD column in table:

mysql> alter table tbl_ITRequestHistory add column col1 varchar(20) , algorithm=copy;

 

MySQL 8.0.13 ALTER TABLE with INPLACE Algorithm

Command to ADD column in table: 

mysql> alter table tbl_ITRequestHistory add column col10 varchar(20) , algorithm=inplace;

MySQL 8.0.13  ALTER TABLE with INSTANT Algorithm.

Command to ADD column in table : 

mysql> alter table tbl_ITRequestHistory add column col15 varchar(20) , algorithm=instant;

                                                                            III. RESULTS

MySQL 8.0.13 ALTER MODE	COPY Algorithm	INPLACE Algorithm	INSTANT Algorithm
Time	50.17 sec	20.59 sec	0.07 sec

                                                                             IV. CONCLUSION

•    Application can have more flexible schema design.
•  Shorten maintaince time dramatically
• This features can save lots of I/O & CPU usage
• These leads to cost of both dev and ops team.

                                                               V. References

[1]     https://dev.mysql.com/worklog/task/?id=11250

[2]     https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

[3]     https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

*************************************************************************************************

                       Please Try this new exciting features and any feedback is always welcome & highly appreciated

                                                                                     Thank You                 

*************************************************************************************************