Brussels / 3 & 4 February 2018

schedule

Instant ADD COLUMN for InnoDB in MariaDB 10.3+

On the way to instant ALTER TABLE of failure-free record format changes


Normally, adding a column to an InnoDB table requires the full table to be rebuilt. The complexity of the operation is proportional to the size of the table. Online implementation of ALTER in MySQL 5.6+ or 3rd party tools like gh-ost or pt-online-schema-change may help to continue working while this change happens, but do not reduce the amount of work to do.

With InnoDB in MariaDB 10.3.2+ instant ADD COLUMN was implemented as backward-incompatible change. During this talk I am going to present some details about it and share simple benhcmarks results demonstrating the impact of this new feature. Further possible improvements to ALTER along the lines of https://jira.mariadb.org/browse/MDEV-11424 are also discussed.

Starting from MariaDB 10.1+, details of InnoDB implementation in MariaDB started to change notably comparing to upstream XtraDB from Percona and Oracle's InnoDB. Perhaps one of the most important InnoDB changes coming in MariaDB Server 10.3 is Instant ADD COLUMN for InnoDB tables. The design was brainstormed in April 2017 by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was developed by Vin Chen (陈福荣) from the Tencent Game DBA Team.

Normally, adding a column to a table requires the full table to be rebuilt. The complexity of the operation is proportional to the size of the table, or O(n*m) where n is the number of rows in the table and m is the number of indexes. Surely, with online ALTER in MySQL 5.6+ you would be able to modify the table while it is being rebuilt, but it would significantly increase the I/O and memory consumption and cause a replication lag.

With instant ADD COLUMN that is possible for a wide subset of use cases, all that is needed is an O(log n) operation to insert a special hidden record into the table, and an update of the data dictionary. For a large table, instead of taking several hours, the operation would be completed in the blink of an eye. The ADD COLUMN operation is only slightly more expensive than a regular INSERT, due to locking constraints.

In this talk I am going to share some of the details on the way this new feature is implemented and present benchmarks comparing speed of the same ALTER TABLE ... ADD COLUMN ... for simple and partitioned InnoDB tables in MySQL 5.6, 5.7, 8.0, MariaDB 10.2, 10.3 and maybe for MyRocks table as well. Possible further improvements for other kinds of ALTER (as presented in https://jira.mariadb.org/browse/MDEV-11424) are also discussed.

Speakers

Photo of Valerii Kravchuk Valerii Kravchuk

Links