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

Instant ALTER TABLE for failure-free column type changes

    XMLWordPrintable

Details

    Description

      The original InnoDB ROW_FORMAT=REDUNDANT is essentially storing each field as variable-length and possibly NULL. For that format, we can trivially allow an instantaneous change of a column from NOT NULL to NULL.

      The space-optimized row formats COMPACT and DYNAMIC do not allow NULL values to be represented for columns that were originally declared as NOT NULL. They also do not store any length for fixed-length columns. Because of this, some failure-free conversions that are instantaneous for ROW_FORMAT=REDUNDANT in MDEV-15563 would require the table to be rebuilt when it is in the COMPACT or DYNAMIC format.

      Let us create a hybrid format that allows us to avoid rebuilding the table in the cases covered by MDEV-15563:

      1. To prevent downgrade or IMPORT TABLESPACE to older MariaDB releases, add a MDEV-15562 metadata record.
      2. For ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT, if the metadata record is present, allow clustered index leaf pages to be in ROW_FORMAT=REDUNDANT with the differences noted below.

      The clustered index leaf pages of the table would be gradually converted into something that resembles ROW_FORMAT=REDUNDANT as a result of modifications. This will increase the size usage a little. Also, ROW_FORMAT=REDUNDANT limits the maximum in-page record size to 16,383 bytes, which for innodb_page_size=64k is less than the limit for COMPACT or DYNAMIC.

      Because secondary index records would remain in the original ROW_FORMAT, secondary indexes may have to be rebuilt when an indexed column is changed. That is, changing an indexed column from NOT NULL to NULL will require the indexes to be rebuilt if ROW_FORMAT is not REDUNDANT.

      Any INSERT or UPDATE after an instant ALTER that removes a NOT NULL constraint (or changes a column to a wider type later in MDEV-15563) 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.

      The metadata BLOB that was introduced in MDEV-15562 will be augmented. The flag 1U << 14 will be set in dict_instant_t::non_pk_col_map[] for ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC columns that were originally created as NOT NULL but no longer carry this attribute. Based on this information, we will initialize n_nullable and n_core_null_bytes for the clustered index based on the original column definition, instead of the latest one. Secondary indexes and ROW_FORMAT=REDUNDANT will use the latest definition.

      Attachments

        1. MDEV-17520_sec_indexes.10.4.3.diff
          16 kB
          Aleksey Midenkov
        2. MDEV-17520.patch
          4 kB
          Marko Mäkelä

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.