About MySQL Online DDL

1. Online DDL

In MySQL 5.1 (with InnoDB Plugin) and 5.5, a new feature called Fast Index Creation (FIC), is to add or remove the two-level index , you can not copy the original table. For the previous version of the addition of the index to remove such DDL operations, the MySQL database operation process is as follows:

1. First, create a new temp table, which is the newly defined structure of the altar table.
2. Then import the data from the original table into this temp table
3. Delete the original table
4. Finally, rename the temporary table to the original table name

In order to maintain the consistency of data, the intermediate copy data (copy table) lock table is read-only, if there are write requests come in will not be able to provide services, the number of connections exploded.

After the introduction of FIC, the creation of a two-level index will add an S lock to the original table, the creation process does not need to rebuild the table (no-rebuild); Deleting the InnoDB two level index only requires updating the internal view and marking the space available for the index, removing the definition of the index on the database metadata. This process also allows only read operations, cannot be written, but greatly accelerates the speed at which indexes are modified (without the primary key index, the InnoDB IoT feature determines that modifying the primary key still requires a Copy Table).

FIC is only valid for the creation of indexes, and the MySQL 5.6 Online DDL extends this feature to add columns, delete columns, modify column types, rename names, set defaults, and so on, and the actual effect depends on the options and action categories used.

1.1 Online DDL Options

The MySQL online DDL is divided into INPLACE COPY two ways, specified by the algorithm parameter in the ALTER statement.

• ALGORITHM=INPLACE, you can avoid the IO and CPU consumption of rebuilding tables, and ensure good performance and concurrency during DDL.
• ALGORITHM=COPY, the original table needs to be copied, so concurrent DML write operations are not allowed, readable. This copy method is not as efficient as inplace, because the former needs to record undo and redo log, and because the temporary use of buffer pool causes a short period of performance to be affected.

The above is only the implementation of the Online DDL, in addition to the lock option to control whether to lock the table, depending on the DDL operation type has different performance: default MySQL as far as possible to lock the table, but expensive operations such as modifying the primary key have to choose the lock table.

• LOCK=NONE, which allows concurrent read and write tables in the DDL period, such as to ensure that ALTER TABLE does not affect user registration or payment, can be explicitly specified, the advantage is that if the ALTER statement does not support continuing writes to the table, it will prompt for failure rather than being sent directly to the library for execution. ALGORITHM=COPYdefault Lock Level
• LOCK=SHARED, that is, the write operation on the table during the DDL is blocked, but does not affect the read.
• LOCK=DEFAULT, let MySQL himself to judge the mode of lock, the principle is MySQL as far as possible not to lock the table
• LOCK=EXCLUSIVE, that is, the table is unavailable during the DDL, blocking any read-write requests. If you want the alter operation to be completed in the shortest possible time, or if the table is not available for a short period of time, you can specify it manually.

However, it is important to note that, regardless of the mode, the online DDL requires a short time lock (exclusive) to prepare the environment, so after the alter command is issued, it waits for other operations on the table to complete, and the request after the ALTER command will appear waiting waiting meta data lock. Also before the end of the DDL, wait for all transactions in the alter period to complete and block for a short period of time. So try to make sure that there are no large transactions executing before ALTER TABLE, otherwise there will be a chain lock table.

1.2 Consider different types of DDL operations

As can be seen from the above introduction, not 5.6 Support online DDL can be arbitrary alter TABLE, lock the table to see the situation:

Tip: The following table lists the common actions picked according to the official Summary of Online Status for DDL Operations.

• In-place is a preference for Yes, indicating that the operation supports InPlace
• Copies table for No is a preference, because it requires rebuilding tables for Yes. Most of the situation is the opposite of In-place.
• allows Concurrent DML? Yes is a preference, indicating that the DDL period table is still readable and writable, you can specify Lock=none (MySQL automatically is NONE if the operation allows)
• allows Concurrent Query? The default is to allow query requests during all DDL operations, where it is only easy to reference
• notes will yes/no the previous columns with the * number

Operation	in-place?	Copies Table?	allows Concurrent DML?	allows Concurrent Query?	Notes
Add index	Yes*	no*	Yes	Yes	Some limitations on full-text indexing
Delete Index	Yes	No	Yes	Yes	To modify only the metadata for a table
OPTIMIZE TABLE	Yes	Yes	Yes	Yes	Start using algorithm=inplace from 5.6.17, of course, if you specify old_alter_table=1 or mysqld the boot band --skip-new will still be copy mode. If a full-text index on the table supports only copy
Set default values for a column	Yes	No	Yes	Yes	To modify only the metadata for a table
Modifying the value of a auto-increment to a column	Yes	No	Yes	Yes	To modify only the metadata for a table
Add FOREIGN KEY constraint	Yes*	no*	Yes	Yes	To avoid copying tables, the foreign_key_checks is disabled when the constraint is created
Delete FOREIGN KEY constraint	Yes	No	Yes	Yes	Foreign_key_checks does not affect
Change Column Name	Yes*	no*	Yes*	Yes	To allow DML concurrency, change the column name only if the same data type is maintained
Adding columns	Yes*	Yes*	Yes*	Yes	Although Algorithm=inplace is allowed, the data is heavily restructured, so it is still an expensive operation. DML concurrency is not allowed when the Add column is Auto-increment
Delete Column	Yes	Yes*	Yes	Yes	Although Algorithm=inplace is allowed, the data is heavily restructured, so it is still an expensive operation
modifying column data types	No	Yes*	No	Yes	Modifying a type or adding a length will copy the table and do not allow the update operation
Change column order	Yes	Yes	Yes	Yes	Although Algorithm=inplace is allowed, the data is heavily restructured, so it is still an expensive operation
Modify Row_format and Key_block_size	Yes	Yes	Yes	Yes	Although Algorithm=inplace is allowed, the data is heavily restructured, so it is still an expensive operation
Set column Properties null Or not NULL	Yes	Yes	Yes	Yes	Although Algorithm=inplace is allowed, the data is heavily restructured, so it is still an expensive operation
Add primary Key	Yes*	Yes	Yes	Yes	Although Algorithm=inplace is allowed, the data is heavily restructured, so it is still an expensive operation. InPlace is not allowed if the column definition must convert not NULL
Delete and add a primary key	Yes	Yes	Yes	Yes	InPlace is allowed only when the same ALTER TABLE statement is removed from the primary key, adding a new primary key, and the data is heavily reorganized, so it is still an expensive operation.
Delete primary key	No	Yes	No	Yes	Concurrent DML is not allowed, the table is copied, and the limit is received if the primary key is not added in the same Atler table statement
Change Table Character Set	No	Yes	No	Yes	If the new character set encoding is different, rebuild the table

As seen from the table, In-place for NO,DML must be no, indicating that a ALGORITHM=COPY copy table must occur, read-only. But it ALGORITHM=INPLACEE is also possible to copy tables, but you can concurrently DML:

• Add, remove columns, change column order
• Add or remove a primary key
• Change the row format Row_format and compression block size key_block_size
• Change column null or NOT NULL
• Optimizing Tables Optimize table
• Force rebuild the table

Cases where concurrent DML is not allowed are: Modify the column data type, delete the primary key, change the table character set, that is, the DDL for these types of operations cannot be online.

In addition, changing the primary key index is not the same as normal index processing, the primary key is the clustered index, which embodies the arrangement of the table data on the physical disk, contains the data row itself, need to copy the table, and the ordinary index by including the primary key column to locate the data, so the creation of ordinary index only need one scan primary key, It is also possible to build a two-level index on existing tables, which is more compact and more efficient to query in the future.

Modifying the primary key also means rebuilding all normal indexes. Deleting a Level Two index is simpler, modifying the INNODB system table information and data dictionary, marking it so that it does not exist, and the table space occupied by the tag can be re-exploited by the new index or data row.

1.3 Limitations of online DDL

• At ALTER TABLE, if a table copy operation is involved, make sure that the datadir directory has enough disk space to place the entire table, because copying the table is done directly in the data directory.
• Adding an index requires no table copy, but make sure that the tmpdir directory is sufficient to store the data in the index column (if it is a composite index, the current temporary sort file is deleted when it is merged into the original table)
• In the master-slave environment, the main library execution alter command does not enter the Binlog log event until it is completed, and if the allow DML operation does not affect the recording time, the period does not cause a delay. However, since the library is a single SQL thread that applies relay log sequentially, the ALTER statement is executed until the next one is completed, so the delay begins when the master DDL completes. (The Pt-osc can control the delay time, so it's more appropriate in this scenario)
• During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there is brief periods at the beginning and End requiring an exclusive lock on the table (the same kind of lock specified by the lock=exclusive clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, update s, deletes, or SELECT ... For UPDATE on the that table; and an online DDL operation might wait before finishing if a similar long-running transaction is started while the ALTER TABLE is in progress.
• When executing a allow concurrent DML online ALTER table, this thread will apply the incremental modification of the online log record before the end, and these modifications are generated in the other thread, so you may encounter duplicate key-value errors (Error 1062 (23000): Duplicate entry).
• When it comes to table copy, there is currently no mechanism to limit the pause DDL or limit IO thresholds
  At MySQL 5.7.6, the progress of ALTER TABLE can be observed through Performance_schema.
• In general, it is recommended to combine multiple alter statements together to avoid the consumption of multiple table rebuild. However, you should also pay attention to grouping, such as the need to copy table and just inplace can be done, should be divided into two alter statements.
• If the DDL executes for a long time, it generates a large number of DML operations that exceed the innodb_online_alter_log_max_size size specified by the variable, causing a db_online_log_too_big error. The default is 128M, especially for alter operations that require copying large tables, and consider temporarily increasing the value for greater log cache space
• When you're done ALTER TABLE , it's best ANALYZE TABLE tb1 to update the index statistics

2. Implementation process

Online DDL consists of 3 stages, prepare phase, DDL execution phase, commit phase, rebuild mode is more than No-rebuild mode in a DDL execution phase, prepare phase and commit phase are similar. The following is a main introduction to the three phases of the DDL execution process.

• prepare stage   :

  1. Create a new temporary frm file (not related to InnoDB)
  2. hold EXCLUSIVE-MDL lock, prohibit read and write /li>
  3. Depending on the alter type, determine how to execute (copy,online-rebuild,online-norebuild)
     If it is add Index, then Online-norebuild is the InPlace method
  4. Update the memory object of the data dictionary
  5. assign Row_log object record increment (only rebuild type required)
  6. generate new temporary IBD file (only rebuild type required)

• DDL execution phase   :

  1. downgrade EXCLUSIVE-MDL lock, allow read/write
  2. Scan old_table clustered index each record rec
  3. iterate through the clustered index and level Two index of the new table, processing it individually
  4. index entries corresponding to the REC construct
  5. Insert construct index entry into Sort_buffer block sort
  6. Update the Sort_buffer block to the new index
  7. record the increment generated during DDL execution (only rebuild types are required)
  8. replay the actions in Row_log to the new index (No-rebuild data is updated on the original table)
  9. generate DML operations between replay Row_log append to Row_log last block

• Commit phase :

1. The current block is row_log last, disable read/write, upgrade to EXCLUSIVE-MDL lock
2. Redo Row_log The last part of the increment
3. Update the data dictionary table for InnoDB
4. Commit TRANSACTION (Brush transaction redo log)
5. To modify statistical information
6. Rename temporary IDB file, frm file
7. Change complete

3. Online DDL Thinking Guide diagram

4. Online DDL Considerations:

• Disk space
  • Rebuild, DataDir space is enough
    • Because you copy IBD files, make sure you have enough space
  • Rebuild, Innodb_online_alter_log_max_size is enough.
    • In the rebuild process, the resulting DML involves the row record change log, whether it is sufficient to store
  • When InPlace, consider whether the tmpdir space is sufficient
• Whether DDL is acceptable for the effect of latency from the library
  • In the process of the main library online DDL, because there is no commit, other concurrency operations can be synchronized normally to the slave library
  • After the main library commit, the DDL is synchronized to the slave library
  • Since the library is a single-threaded execution of sql_thread, assuming that the DDL execution process takes 1 hours, the library will lag 1 hours +
  • Whether to allow lag from the library, if not allowed, can be optimized for processing by parallel replication
• Does row-log check for duplicate values or modify conflicts?
  • is checked against the primary key and the unique constraint.
• Copy table, how to pause DDL operations under InPlace
  • Show full processlist;
  • Kill ID; # (DDL SQL ID number)
  • Once this is done, the DDL can still be executed again, there will be no conflicts, and the temporary IDB and frm files created will be automatically deleted.
• Copy table, inplace down
  • In both cases, a DDL statement that is not completed will not continue after the outage
  • However, its generated FRM and IDB temporary files will not be deleted, can be manually deleted, or not manually deleted, even if not deleted, and will not affect the execution of the DDL again
  • But after the MySQL service is recommended, delete the unused temporary files
• Multiple DDL statements in the same table, do not execute one at a
• Please follow the support inplace and whether you need rebuild classification merge execution

5.5.7 Version of the online DDL can view progress through p_s

First, open function

Mysql>UPDATESetup_instrumentsSETENABLED=' YES 'WHERENAMELike' stage/innodb/alter% ';QueryOk,7RowsAffected(0.00Sec)RowsMatched:7Changed:7Warnings:0mysql> update setup_ Consumers set enabled =  ' YES '  WHERE name like  '%stages% ' ; Query ok3 rows affected  (0. XX sec) rows matched: 3 changed: 3 warnings : 0              

Second, the implementation of the change table operation

Mysql>alter table employees. Employees add column middle_name varchar  (14) after first_name query ok0 rows  Affected  (9. sec) records: 0 duplicates: 0 warnings: 0                

Third, view the progress of the change table

Mysql>SELECTEvent_Name,work_completed,work_estimatedFromEvents_stages_current;+------------------------------------------------------+----------------+----------------+|Event_Name|work_completed|work_estimated|+------------------------------------------------------+----------------+----------------+| stage/InnoDB/alter table (read PK and internal sort) | 280 | 1245 |  +------------------------------------------------------+----------------+----------------+1 row in set (0.  sec)               

About MySQL Online DDL