Hi,

Number of the fields indeed would solve the ADD COLUMN default case but has some limitations

ADD COLUMN m default xxx; would be still slow, xxx = NULL maybe possible, in other cases could we store default on SYS_COLUMNS?

DROP COLUMN would be still slow

Problem naturally is that we do not have suitable metadata on every page where to identify what columns are stored. I do not see way out of this problem without introducing yet a another file forma (we have too many of them already). 

R: Jan

On Tue, Jan 3, 2017 at 8:13 PM, Marko Mäkelä <marko.makela@mariadb.com> wrote:
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:

https://jira.mariadb.org/browse/MDEV-11424
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:

https://jira.mariadb.org/browse/MDEV-11369
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,

 Marko
--
DON’T MISS
M|17
April 11 - 12, 2017
The Conrad Hotel
New York City
https://m17.mariadb.com/

Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp