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

Engine-independent online ALTER TABLE




      Implement online ALTER TABLE above the storage engine layer by mimicking what InnoDB does since MariaDB 10.0.


      ALTER TABLE can perform many various table metadata alterations, individually or batched (many alterations at once). It supports different algorithms for applying those alterations and different lock levels restricting access to the table while it's being altered. What algorithm and lock level to use depends on the storage engine, requested alterations and explicitly specified algorithm and lock, if any. If no algorithm or lock level is explicitly specified, the server is supposed to select the best algorithm/lock combination automatically.

      While certain alterations (like adding a column) can be done by certain storage engines (like InnoDB) internally (using InnoDB-specific ALGORITHM=INSTANT) and without locking the table (LOCK=NONE), the most universal ALTER TABLE algorithm that supports arbitrary alterations in arbitrary combinations is the COPY algorithm and it locks the table, allowing only read access during the whole ALTER TABLE duration. When the server has to resort to the COPY algorithm (because no other one can perform the requested set of alterations) it often means long periods of the application being essentially down, because the table cannot be written into.

      The goal of this task is to allow the COPY algorithm to work without read-locking the table. In other words, this should make the combination ALGORITHM=COPY, LOCK=NONE possible.


      The COPY algorithm for ALTER ONLINE TABLE is supposed to do the following:

      1. Exclusively acquire the table Metadata Lock (MDL).
      2. Acquire the table lock for read (TL_READ)
      3. Read the first record. In table is empty, online is skipped (goto 11).
      4. Set up (a separate, per-table one) row-based replication for tracking changes from concurrent DMLs ("online changes").
      5. Downgrade the MDL lock.
      6. Copy the table contents (using a non-locking read if supported by the storage engine).
      7. Apply the online changes from the replicated contents.
      8. Unlock the table lock
      9. Exclusively lock the table MDL (upgrade to MDL_SHARED_WRITE).
      10. Apply any remaining online changes.
      11. Swap the old and new table, unlock, drop the old table.

      This would remove some limitations that currently exist with the InnoDB-only online table rebuild. Basically, anything that is supported by ALGORITHM=COPY should 'just work' (however see the limitations section). The bulk copying could still happen in copy_data_between_tables(). A few examples:

      1. Arbitrary changes of column type will be possible, without duplicating any conversion logic.
      2. It will be possible to add virtual columns (materialized or not) together with adding indexes, while allowing concurrent writes (MDEV-13795, MDEV-14332).
      3. The ENGINE or the partitioning of a table can be changed, just like any other attribute.

      [Not implemented here] We should remove the online table rebuild code from InnoDB (row_log_table_apply() and friends), and just let InnoDB fall back to this. The only ALTER ONLINE TABLE that could better be implemented inside storage engines would be ADD INDEX. Then, ALGORITHM=INPLACE would no longer be misleading, because it would mean exactly the same as the ALGORITHM=NOCOPY that was introduced in MDEV-13134. Before this, we must implement MDEV-515 (bulk load into an empty InnoDB table) to avoid a performance regression.

      Behavior of different engines

      The per-engine behavior depends on what operations can happen concurrently while TL_READ is held.

      • Innodb can do any DML (except TRUNCATE i presume). It lazily opens the read view once the first record is read during the copy stage. This means that in theory some transaction can slip concurrently between TL_READ-locked table and first record is read. This is why we first read one record out, and then set up the online change buffer.
      • Myisam/Aria only allow inserts in parallel with reads: The last table's record offset is remembered for the table handle, so copy stage will read out only the changes, that are already there. Other DMLs will be blocked until table lock is released.
      • Online is disabled for temporary tables.
      • For other engines, it depends on whether is it possible to acquire a particular table lock in parallel with TL_READ.


      • Embedded server doesn't support LOCK=NONE, Until HAVE_REPLICATION is enabled there (or until some finer refactoring).
      • DROP SYSTEM VERSIONING is not currently supported, but the support can be added on demand
      • ALTER TABLE ... ORDER BY is not and cannot be supported
      • Tables which are referenced by FOREIGN KEYs with CASCADE operations, see MDEV-29068
      • Adding autoinc to the existing column, when NO_AUTO_VALUE_ON_ZERO is not present, and there were no unchanged UNIQUE NOT NULL keys. A NULL column is always impossible to update to AUTOINC with Online COPY.
      • Sequences are not supported
      • MODIFY ... NOT NULL DEFAULT(NEXTVAL(..)), if the column initially was NULLable
      • Sequences
      • Engines S3 and CONNECT

      [Old part] Challenges

      We should replicate the online rebuild on slaves in parallel, so that the master and slaves will be able to commit at roughly the same time. This would be something similar to MDEV-11675, which would still be needed for native online ADD INDEX, which would avoid copying the table.

      In InnoDB, there is some logic for logging the changes when the PRIMARY KEY columns are changed, or a PRIMARY KEY is being added. The 'row event log' online_log will additionally contain the PRIMARY KEY values in the new table, so that the records can easily be found. The online_log will contain INSERT, UPDATE, and DELETE events.

      We will need some interface from ROLLBACK inside the storage engine to the 'row event log', so that BEGIN; INSERT; ROLLBACK will also create a DELETE event. Similarly, we will need an interface that allows CASCADE or SET NULL operations from FOREIGN KEY constraints to be relayed to the 'row event log'.

      Starting with MariaDB 10.2, there is an optimization that avoids unnecessarily sorting the data by PRIMARY KEY when the sorting does not change. Search for skip_pk_sort. It would be nice if the future MDEV-515 code inside InnoDB could be informed of this, so that it can assume that the data is already sorted by PRIMARY KEY.

      If there exist FOREIGN KEY constraints on the being-rebuilt table, then this approach should work just as fine as the current online table rebuild in InnoDB: The constraints would be enforced on the old copy of the table until the very end where we switch the tables, and from that point on, on the new copy of the table.

      Initially, we could disable ONLINE...ADD FOREIGN KEY. That could be easier to implement after moving the FOREIGN KEY processing from InnoDB to the SQL layer.


        1. output.txt
          343 kB
          BJ Quinn
        2. Remove-InnoDB-online-table-rebuild.patch
          133 kB
          Marko Mäkelä

        Issue Links



              nikitamalyavin Nikita Malyavin
              marko Marko Mäkelä
              10 Vote for this issue
              28 Start watching this issue



                Git Integration

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