Limitations of Online DDL for MySQL

Take the following limitations into account when running online DDL operations:

During an online DDL operation that copies the table, files are written to the temporary directory ($TMPDIRon Unix,%TEMP%on Windows, or the directory specified by the–tmpdirconfiguration variable). Each temporary file is large enough to hold one column in the new table or index, and each one is removed as soon as it is merged into the final table or index.

The table is copied, rather than using Fast Index Creation when you create an index on aTEMPORARY TABLE. This has been reported as MySQL Bug #39833.

InnoDB handles error cases when users attempt to drop indexes needed for foreign keys. SeeSection 14.18.5, “InnoDB Error Codes”for information related to error1553.

TheALTER TABLEclauseLOCK=NONEis not allowed if there areON…CASCADEorON…SET NULLconstraints on the table.

During each online DDLALTER TABLEstatement, regardless of theLOCKclause, there are brief periods at the beginning and end requiring anexclusive lockon the table (the same kind of lock specified by theLOCK=EXCLUSIVEclause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, orSELECT … FOR UPDATEon that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while theALTER TABLEwas in progress.

When running an onlineALTER TABLEoperation, the thread that runs theALTER TABLEoperation will apply an“online log”of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the“online log”. This is similar to the idea of a foreign key constraint check inInnoDBin which constraints must hold during a transaction.

OPTIMIZE TABLEfor anInnoDBtable is mapped to anALTER TABLEoperation to rebuild the table and update index statistics and free unused space in the clustered index. Prior to 5.6.17, there is noonline DDLsupport for this operation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. As of 5.6.17,OPTIMIZE TABLEis supported with the addition ofonline DDLsupport for rebuilding regular and partitionedInnoDBtables. For additional information, seeSection 14.10.1, “Overview of Online DDL”.

InnoDBtables created before MySQL 5.6 do not supportALTER TABLE … ALGORITHM=INPLACEfor tables that include temporal columns (DATE,DATETIMEorTIMESTAMP) and have not been rebuilt usingALTER TABLE … ALGORITHM=COPY. In this case, anALTER TABLE … ALGORITHM=INPLACEoperation returns the following error:

These limitations are generally applicable to online DDL operations on large tables where table copying is involved:

There is no mechanism to pause an online DDL operation or to throttle I/O or CPU usage for an online DDL operation.

Progress monitoring capability for online DDL operations is limited until MySQL 5.7.6, which introduces Performance Schema stage events for monitoringALTER TABLEprogress. SeeMonitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema.

Rollback of an online DDL operation can be expensive should the operation fail.

Long running online DDL operations can cause replication lag. An online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed (Bug #7319开发云主机域名6).

An onlineALTER TABLEoperation can cause a server exit if the operation uses all of the available disk space on the file system where the data directory (datadir) resides (Bug #77497). To avoid this problem, ensure that there is enough disk space to accommodate operations that copy the table. During these operations, MySQL writes temporary sort files to the temporary directory (–tmpdir).

For additional information related to running DDL operations on large tables, seeSection 14.10.2, “Performance and Concurrency Considerations for Online DDL”.

相关推荐: 一键yum安装mysql

#!/bin/bashyum -y install wgetwget https://开发云主机域名dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpmyum -y install mysq…