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.mak...@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 >
_______________________________________________ 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