[MDEV-13134] Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT Created: 2017-06-20  Updated: 2022-02-24  Resolved: 2018-05-07

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Fix Version/s: 10.3.7

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: ddl, online-ddl

Issue Links:
Blocks
blocks MDEV-16099 Use alter algorithm=nocopy or algorit... Open
Duplicate
is duplicated by MDEV-14246 Skip the ha_innobase::prepare_inplace... Closed
Problem/Incident
causes MDEV-16288 ALTER TABLE…ALGORITHM=DEFAULT does no... Closed
Relates
relates to MDEV-13301 Optimize DROP INDEX, ADD INDEX into R... Closed
relates to MDEV-14246 Skip the ha_innobase::prepare_inplace... Closed
relates to MDEV-15641 InnoDB crash while committing table-r... Closed
relates to MDEV-15719 ALTER TABLE…ALGORITHM=INPLACE is unne... Closed
relates to MDEV-16282 ALTER TABLE t ADD COLUMN c INT, ADD I... Stalled
relates to MDEV-18570 ALGORITHM=INSTANT or NOCOPY fails to ... Confirmed
relates to MDEV-18845 Introduce alter_lock to allow refusin... Open
relates to MDEV-20419 ADD COLUMN with DROP COLUMN wrongly i... Confirmed
relates to MDEV-21693 ALGORITHM=INSTANT does not work for p... Closed
relates to MDEV-11369 Instant add column for InnoDB Closed
relates to MDEV-16830 ALTER TABLE DROP FOREIGN KEY - unexpe... Closed
relates to MDEV-20590 Introduce a file format constraint to... Closed
relates to MDEV-21855 Document difference between DEFAULT a... Open
relates to MDEV-27940 Instant alter drop index not supporte... Closed

 Description   

When MySQL 5.6 introduced the ALGORITHM attribute to ALTER TABLE, only two possible values were introduced in addition to the implied value ALGORITHM=DEFAULT: ALGORITHM=COPY to refer to the old-style ALTER TABLE that basically consists of CREATE TABLE, INSERT…SELECT, RENAME TABLE and DROP TABLE, and ALGORITHM=INPLACE to refer to the "fast" ALTER TABLE whose first incarnation was implemented in the InnoDB Plugin for MySQL 5.1.

This choice of keywords is unfortunate, because the supposedly "fast" algorithm would sometimes copy the table. Already the InnoDB Plugin would do it when executing ADD PRIMARY KEY. In MySQL 5.6, several ALGORITHM=INPLACE operations would actually copy the table. Most notably, these would be ADD COLUMN, DROP COLUMN, reordering columns, and changing ROW_FORMAT, and naturally the FORCE attribute that forces a rebuild.

The following was originally written in MDEV-11369, which would make certain cases of ADD COLUMN an instantaneous operation:

It would be nice to introduce new syntax to prevent nasty surprises. When an operation is expected to be quick, it could be better to return an error than to perform a disruptive (resource-intensive and time-consuming) operation.

ALGORITHM=INSTANT will refuse any operation that must modify any data in files. (Updates to metadata are possible.)

ALGORITHM=NOCOPY will refuse any operation that would rebuild the clustered index (and the whole table).

With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY.

Example:

# No data file change (MDEV-11369 instant ADD COLUMN)
 
ALTER TABLE t ADD COLUMN b INT, ALGORITHM=INSTANT;
 
# The following will change data files (ADD INDEX), but not rebuild the table:
 
ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY;
 
# The following are changing data files (not instant operation)
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 
ALTER TABLE t DROP INDEX c, ALGORITHM=INSTANT;
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 
ALTER TABLE t ADD COLUMN d INT, ADD INDEX(d), ALGORITHM=INSTANT;
 
# All of the above should be allowed with any other ALGORITHM.
 
# With ALGORITHM=DEFAULT or with no ALGORITHM, the most efficient
 
# available algorithm will be used.



 Comments   
Comment by Elena Stepanova [ 2017-06-20 ]

As discussed with marko, it might be good in the scope of this task to get rid of this rather pointless warning:

ALTER TABLE t1 ADD FULLTEXT KEY ftidx (c);
 
Warnings:
 
Warning	124	InnoDB rebuilding table to add column FTS_DOC_ID

Comment by Marko Mäkelä [ 2017-09-25 ]

As part of this change, I think that we should strongly consider optimizing away non-changes to ENGINE or ROW_FORMAT or possibly other attributes.
Currently, the following two ALTER TABLE will cause a full table rebuild:

CREATE TABLE t(a INT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
 
ALTER TABLE t ENGINE=InnoDB;
 
ALTER TABLE t ROW_FORMAT=COMPACT;

If a rebuild is desired, the FORCE keyword can be specified:

ALTER TABLE t FORCE;

Comment by Marko Mäkelä [ 2018-03-26 ]

This looks OK from the InnoDB point of view, after addressing my review comments.

Comment by Marko Mäkelä [ 2018-03-29 ]

I posted some more review comments. The only thing that is missing from the InnoDB side is the removal of the warning InnoDB rebuilding table to add column FTS_DOC_ID and the testing of ADD FULLTEXT INDEX…ALGORITHM=NOCOPY.

The non-change of ENGINE or ROW_FORMAT will continue to rebuild the table, for now. And those operations will be refused by ALGORITHM=NOCOPY or ALGORITHM=INSTANT.

Comment by Thirunarayanan Balathandayuthapani [ 2018-03-30 ]

https://github.com/MariaDB/server/commit/45be7b5ff7abc61c48e7b79877b8ed0bb430e5f3

This is the latest commit.

Comment by Thirunarayanan Balathandayuthapani [ 2018-04-02 ]

bb-10.3-MDEV-13134

Comment by Marko Mäkelä [ 2018-04-04 ]

The latest looks OK from the InnoDB perspective; the only problem is a misleading error message for refusing ADD FULLTEXT INDEX…ALGORITHM=NOCOPY when a hidden FTS_DOC_ID column would be created.

I think that we should consider extending the existing Boolean parameter old_alter_table to integer as follows:

  • old_alter_table=0 maps to ALGORITHM=DEFAULT (choose the best available algorithm)
  • old_alter_table=1 maps to ALGORITHM=COPY
  • old_alter_table=2 maps to ALGORITHM=INPLACE
  • old_alter_table=3 maps to ALGORITHM=NOCOPY
  • old_alter_table=4 maps to ALGORITHM=INSTANT

The values 0 and 1 would correspond to the current behaviour. Values 1 to 4 would limit the choice of ALTER TABLE operations. If one wants to avoid performance surprises on the server, one could configure old_alter_table=4 or old_alter_table=3. Then, any ALTER TABLE would be refused, unless it is instantaneous or avoids a table rebuild. Users could explicitly specify ALGORITHM if they want a more expensive operation.

Comment by Marko Mäkelä [ 2018-04-05 ]

The name old_alter_table is not very intuitive. Perhaps an alias alter_algorithm should be defined, with the default value alter_algorithm=default (0).

Comment by Thirunarayanan Balathandayuthapani [ 2018-05-07 ]

Introduced new alter algorithm type called NOCOPY & INSTANT for inplace alter operation.

NOCOPY - Algorithm refuses any alter operation that would rebuild the clustered index.
It is a subset of INPLACE algorithm.

INSTANT - Algorithm allow any alter operation that would modify only meta data. It is a
subset of NOCOPY algorithm.

Introduce new variable called alter_algorithm. The values are DEFAULT(0), COPY(1), INPLACE(2), NOCOPY(3), INSTANT(4)

Message to deprecate old_alter_table variable and make it alias for alter_algorithm variable.

alter_algorithm variable for slave is always set to default value.

Comment by Thirunarayanan Balathandayuthapani [ 2018-05-07 ]

Removed the warning for InnoDB table building to add FTS_DOC_ID as well.

Comment by Matthias Leich [ 2018-05-07 ]

10.3 commit c5b28e55f6ff2a77bf67a2052cc4f4ddd73bc151 compiled with debug
Asserts observed up till now
egrep -i 'Assert.* failed' last_rmr_workdir/*.log | sed -e 's/^.*10\.3\.A//g' | sort -u
/storage/innobase/handler/handler0alter.cc:9238: virtual bool ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool): Assertion `ctx->need_rebuild()' failed.
/storage/innobase/row/row0ins.cc:3115: dberr_t row_ins_sec_index_entry_low(ulint, ulint, dict_index_t*, mem_heap_t*, mem_heap_t*, dtuple_t*, trx_id_t, que_thr_t*, bool): Assertion `thr_get_trx(thr)->error_state == DB_SUCCESS' failed.
/storage/innobase/trx/trx0rec.cc:1915: dberr_t trx_undo_report_rename(trx_t*, const dict_table_t*): Assertion `++loop_count < 2' failed.
/storage/innobase/row/row0trunc.cc:1894: dberr_t row_truncate_table_for_mysql(dict_table_t*, trx_t*): Assertion `logger->debug()' failed.

Generated at Thu Feb 08 08:03:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.