maria-developers team mailing list archive

[Jan Lindström <jan.lindstrom@xxxxxxxxxxx>]

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@xxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>