Saturday, May 16, 2020

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
  1. 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.
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
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
                       Please Try this new exciting features and any feedback is always welcome & highly appreciated
                                                                                     Thank You                 


No comments:

Post a Comment