← Back to team overview

maria-developers team mailing list archive

InnoDB: Instant ADD COLUMN that works on old data files


I would like to seek technical review and feedback for an idea.

In the long term, I would like InnoDB to support instant ALTER TABLE
for a larger set of operations:

Instant ALTER TABLE of failure-free record format changes

Probably one of the most common operations is ADD COLUMN. For that, a
ticket had already been filed and some patches exist:

Instant add column for InnoDB

Tencent and Alibaba have ADD COLUMN patches that basically introduce
new InnoDB record formats so that each record will carry the number of
columns, similar to how ROW_FORMAT=REDUNDANT (the original InnoDB row
format) works. A problem with that approach is that we would only be
able to support instant ADD COLUMN on data files that already are in a
suitable format.

The whole purpose of instant ALTER TABLE is to avoid conversions of
huge data files. Requiring a conversion before the feature can be used
would in my opinion defeat the purpose.

I came up with a solution that only requires some changes to the
InnoDB page format, not to the record formats: Require each record in
a clustered index leaf page to contain the same number of fields (on
write, convert the whole page). We can repurpose the previously unused
(always 0) field PAGE_MAX_TRX_ID on clustered index leaf pages so that
the number of fields in each leaf page can be determined. This is the
idea that I posted in MDEV-11369 last week and edited today. As far as
I can tell, it should work on any existing data file, and it would be
a step towards the full-blown MDEV-11424 in a distant future version
of MariaDB.

One challenge with the page-at-a-time conversion is that inserting or
updating records into a leaf page may require the page to be split
into several pages, especially in the case where multiple CHAR(xxx)
NOT NULL columns would need to be materialized as a result of the
insert or update.

Does this seem to make sense to you? I am looking forward to your feedback.

Best regards,

April 11 - 12, 2017
The Conrad Hotel
New York City

Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

Follow ups