Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11424

Instant ALTER TABLE of failure-free record format changes

    Details

      Description

      Implement instant ALTER TABLE operations where the specification of non-indexed columns is changed, for the cases that cannot be handled more efficiently. The special cases with efficient handling are:

      • MDEV-11369 Instant ADD COLUMN…LAST
      • MDEV-15562 Instant DROP COLUMN, ADD COLUMN anywhere, changing the order of columns
      • MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes

      This work extends MDEV-15563 (Instant NOT NULL removal and conversion to a wider type for ROW_FORMAT=REDUNDANT) to ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT tables. (As always, instant ALTER TABLE is not supported for ROW_FORMAT=COMPRESSED.)

      Some operations could avoid data changes, but would not be instantaneous due to the need to validate the data. Later, MDEV-16356 and MDEV-16291 can allow them with ALGORITHM=NOCOPY.

      Operations that involve adding or dropping indexes (also DROP COLUMN can imply this) are outside the scope of this work; they are supported with ALGORITHM=NOCOPY. ALTER TABLE…ADD [UNIQUE] INDEX supports concurrent modifications to the table since MariaDB 10.0. MDEV-16223 would defer ADD INDEX to a background operation.

      Operations that will continue to be refused by ALGORITHM=INSTANT (and ALGORITHM=NOCOPY even after MDEV-16291) include:

      • Changing ROW_FORMAT or ENGINE
      • Altering a table that is in ROW_FORMAT=COMPRESSED
      • Dropping, adding or changing PRIMARY KEY columns, or ADD/DROP PRIMARY KEY

      Any ALTER TABLE that would be refused with ALGORITHM=NOCOPY (anything that rebuilds the clustered index) will drop any ‘instant ALTER TABLE’ history. The history would also be deleted if a rebuild is explicitly requested by the use of the FORCE keyword.

      Metadata format changes

      In InnoDB, instant ALTER TABLE affects clustered index page leaf records only. The data dictionary will reflect the most recent table definition. Additional metadata for interpreting records that correspond to an earlier version of the table definition will be stored in the clustered index tree as follows.

      • MDEV-11369 in MariaDB 10.3.4 changed the root page type code to FIL_PAGE_TYPE_INSTANT to indicate that instant ALTER TABLE has been used. It also introduced a hidden 'default row' record at the start of the clustered index, on the leftmost leaf page. The new page type code prevents older MariaDB versions from opening the table.
      • MDEV-15562 will slightly modify the format of the 'default row' record to represent dropped and reordered index fields. (All columns will internally be added last in the user records in the clustered index leaf pages.) MariaDB 10.3 will refuse to open such tables, because new info_bits will be set in the 'default row' record.

      Data format changes

      User records in the clustered index leaf pages will have to indicate which format they correspond to.

      • For other than ROW_FORMAT=REDUNDANT, MDEV-11369 introduced REC_STATUS_COLUMNS_ADDED that indicates the presence of an optional record header that encodes the number of 'instantly added' columns that are present in the record.
      • For ROW_FORMAT=REDUNDANT, MDEV-11369 simply stores the number of fields in the record header.
      • In MDEV-11369 and MDEV-15562, any 'instantly added' columns whose values are missing from the end of the clustered index record will be substituted with the values stored in the 'default row' record.
      • MDEV-15562 will not change the user record format in any way. Instantly added columns are always added as last fields in the clustered index leaf page records.
      • MDEV-15563 changes the interpretation of clustered index leaf page records in ROW_FORMAT=REDUNDANT.
      • This work extends MDEV-15563 to ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT tables.

      Any INSERT or UPDATE after an instant ALTER that removes a NOT NULL constraint or changes a column to a wider type will cause all records in the affected clustered index leaf page to be rewritten in a format that resembles ROW_FORMAT=REDUNDANT, with the following differences:

      • NULL columns will occupy 0 bytes of storage, also when fixed-length.
      • CHAR(n) (unless NULL) will occupy n*mbminlenn*mbmaxlen bytes, instead of n*mbmaxlen.
      • For ROW_FORMAT=DYNAMIC, no local prefix of off-page columns will be stored. For ROW_FORMAT=COMPACT, we will continue to write the 768-byte prefixes.

      In ROW_FORMAT=REDUNDANT, the record header will store the length of each column (including fixed-length and NULL columns), using n_fields or 2·n_fields bytes. The fixed-length record header is 6 instead of 5 bytes. This will increase the size of each record by at least 1 byte, up to 2·n_fields+1 bytes.

      A note on MVCC

      Because ha_innobase::commit_inplace_alter_table() will be invoked while holding MDL_EXCLUSIVE, any transactions that read or modified the table must finish before the ALTER TABLE can commit. But it is possible that some old transaction tries to do its first access to the table after the ALTER TABLE committed. Such transactions may receive an error message 'table definition changed', as noted in MySQL Bug#28432. It would be too much effort to support MVCC if a transaction after ALTER modified a record (converting it to newer dictionary version) that would otherwise be visible to the old transaction.
      Here is the scenario in SQL:

      connection con1;
      START TRANSACTION WITH CONSISTENT SNAPSHOT; -- creates read view
      connection con2;
      ALTER TABLE t CHANGE COLUMN b b INT NULL;
      UPDATE t SET b=1 WHERE a=100;
      connection con1;
      SELECT * FROM t WHERE a=1; -- might be OK, if the record is still in old format
      SELECT * FROM t WHERE a=100; -- error: record is in to new format
      

      For simplicity and consistency, we could always return an error to the SELECT statements (after any ALTER TABLE).

      ALTER TABLE operations that potentially affect the format of a row

      In MariaDB Server 10.2, the following alter_table_operations might require a table to be rebuilt:

      alter_table_operations SQL Remarks
      ALTER_ADD_STORED_BASE_COLUMN ADD COLUMN MDEV-11369 (10.3)
      ALTER_ADD_VIRTUAL_COLUMN ADD COLUMN…AS Virtual columns are always added instantly
      ALTER_ADD_STORED_GENERATED_COLUMN ADD COLUMN…PERSISTENT AS cannot be instant; until MDEV-16354 requires rebuild with ALGORITHM=COPY
      ALTER_ADD_PK_INDEX ADD PRIMARY KEY Requires all indexes to be rebuilt.
      ALTER_DROP_PK_INDEX DROP PRIMARY KEY Requires all indexes to be rebuilt. Without ADD PRIMARY KEY, cannot even support online rebuilding (LOCK=NONE).
      ALTER_CHANGE_CREATE_OPTION ROW_FORMAT, KEY_BLOCK_SIZE, encryption Requires rebuild; see MDEV-16291.
      ALTER_CHANGE_CREATE_OPTION page_compressed, page_compression_level MDEV-16328
      ALTER_COLUMN_NULLABLE NULL MDEV-15563 for ROW_FORMAT=REDUNDANT
      ALTER_COLUMN_NULLABLE NULL COMPACT and DYNAMIC
      ALTER_COLUMN_NOT_NULLABLE NOT NULL needs validation; MDEV-16291 will allow NOCOPY
      ALTER_STORED_COLUMN_ORDER FIRST, LAST, AFTER MDEV-15562
      ALTER_DROP_STORED_COLUMN DROP COLUMN MDEV-15562
      ALTER_RECREATE_TABLE FORCE the sole purpose of this keyword is to explicitly request rebuild
      ALTER_STORED_COLUMN_TYPE CHANGE to wider type MDEV-15563 for ROW_FORMAT=REDUNDANT
      ALTER_STORED_COLUMN_TYPE CHANGE to wider type COMPACT and DYNAMIC
      ALTER_STORED_COLUMN_TYPE CHANGE type MDEV-16291
      ALTER_VIRTUAL_COLUMN_TYPE CHANGE type MDEV-16332
      ALTER_STORED_GCOL_EXPR CHANGE expr will require validation and possible updates of all data; depends on MDEV-16291
      ALTER_COLUMN_UNVERSIONED CHANGE…WITH[OUT] SYSTEM VERSIONING MDEV-16330
      ALTER_ADD_SYSTEM_VERSIONING ADD SYSTEM VERSIONING Must rebuild the PRIMARY KEY and thus the full table
      ALTER_DROP_SYSTEM_VERSIONING DROP SYSTEM VERSIONING Must rebuild the PRIMARY KEY and thus the full table
      ALTER_ADD_PERIOD ADD PERIOD FOR SYSTEM TIME must be combined with ADD SYSTEM VERSIONING
      ALTER_DROP_PERIOD DROP PERIOD FOR SYSTEM TIME must be combined with DROP SYSTEM VERSIONING
      ALTER_ADD_CHECK_CONSTRAINT ADD [CONSTRAINT] CHECK MDEV-16356
      ALTER_DROP_CHECK_CONSTRAINT DROP CONSTRAINT MDEV-16331

      Legend:

      • can be performed instantly without adding conversion recipes
      • can be performed without adding conversion recipes; not instant if any secondary indexes need to be rebuilt
      • can be performed instantly by adding a conversion recipe (as part of this work)
      • not instant; could later be performed with validation
      • will continue to require full table rebuild

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                marko Marko Mäkelä
              • Votes:
                5 Vote for this issue
                Watchers:
                13 Start watching this issue

                Dates

                • Created:
                  Updated: