MySQL Online DDL principle

Background

One of the DBA's daily work must be a DDL change, a DDL change that locks the table, which can be said to be a perpetual pain in the DBA's mind, especially when a DDL change is performed, causing a large number of threads on the library to be in the "waiting for meta Data lock" state. So MySQL 5.6 's online DDL feature is the new feature that DBAs expect most, which solves the problem of executing DDL lock tables, ensuring that when table changes are made, there is no blocking of online business reading and writing, and that the library is still able to provide access to the database as it changes. Online DDL on the article a lot, but the principle of very few, are introduced grammar and the like, this article will detail the principles of online DDL, know it, but also know its why.

How DDL is implemented

Prior to the launch of the 5.6 online DDL, there were two main ways to execute the DDL in copy and InPlace mode, also known as the InPlace method (Fast index creation). The InPlace method does not copy data, so it is faster than copy mode. However, this method only supports the addition and deletion of indexes, and the same as the copy method requires a full lock table, the practicality is not very strong. The following Gazzo cited as an example, a brief introduction of the implementation of the two methods of the process.

Copy Method

(1). Create a new temporary table with indexes

(2). Lock the original table, prohibit DML, allow query

(3). Copy the original table data to a temporary table (no sorting, one line copy)

(4). Perform rename, upgrade dictionary lock, prohibit read and write

(5). Completing the CREATE INDEX operation

InPlace Way

(1). Data dictionary for new index

(2). Lock table, prohibit DML, allow query

(3). Read the clustered index, construct a new index entry, sort and insert a new index

(4). Wait for all read-only transaction commits that open the current table

(5). CREATE INDEX End

Online DDL implementation

The online method also contains the copy and InPlace mode, for DDL operations that do not support online, such as modifying the column type, deleting the primary key and so on, and for the inplace mode, the MySQL internal is also divided into two categories: "Whether to modify the record format". A class needs to rebuild the table (modify the record format), such as adding, deleting columns, modifying the default values of the column, and so on, the other is only need to modify the table metadata, such as adding, deleting indexes, modifying column names and so on. MySQL will refer to these two types of methods as rebuild mode and No-rebuild mode respectively. 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
2. Hold EXCLUSIVE-MDL lock, prohibit read and write
3. Determine the execution mode (copy,online-rebuild,online-norebuild) according to the alter type
4. Update the memory object of the data dictionary
5. Assigning Row_log object record increments
6. Generate a new temporary IBD file

DDL Execution Phase:

1. Downgrade EXCLUSIVE-MDL lock, allow read and write
2. Scan Old_table's 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. Inserting a constructed index entry into a sort_buffer block
6. Insert a sort_buffer block into a new index
7. Handles the increment generated during DDL execution (only rebuild types are required)

Commit phase

1. Upgrade to EXCLUSIVE-MDL Lock, disable read/write
2. Redo last part increment in last Row_log
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   

Key function Stacks

Copy data

Row_merge_build_indexes
row_merge_read_clustered_index //Copy full amount

{

traversing the clustered index of cousin

Row_build//Create a row

row_merge_buf_add//add row to Sort_buffer
Row_merge_insert_index_tuples//Insert to New table (clustered index + Level two index)
}
row_log_table_apply// for rebuild type, processing increment

{

Row_log_table_apply_insert//Take insert as an example

Row_log_table_apply_convert_mrec//Convert BUF item to tuple

{

Inserting a clustered index//row_ins_clust_index_entry_low

Insert Level Two index//Row_ins_sec_index_entry_low

}

}

Modify Table Data Dictionary

Commit_try_norebuild,commit_try_rebuild

Common DDL Operations

Type	Concurrent DML	Algorithm	Note
Add/Remove Indexes	Yes	Online (No-rebuild)	Full-text indexing does not support
modify default value Modify column name Modify the self-increment column value	yes	nothing	just modify metadata
add/Remove columns interchange column order modify Null/not NULL modify Row-format	yes	online (Rebuild)
modifying column types Optimize table Convert Character Set	No	Copy	Lock table is required and online is not supported

Several issues

1. How to achieve data integrity

After using the online DDL, there must be a doubt in the user's mind, while doing the DDL, while doing DML, the data in the table is not messy? The key component in this is Row_log. Row_log records the newly generated DML operations during DDL changes and applies them to the new table at the end of the DDL execution to ensure data integrity.

How 2.online and data consistency can be combined

In fact, the online DDL is not the entire process of online, in the prepare phase and commit phase will hold the mdl-exclusive lock, prohibit read and write; Since the prepare and commit phases are particularly short in relation to the DDL execution phase, they can generally be considered online. The prepare phase and commit phase are forbidden to read and write, mainly to ensure data consistency. Prepare stage needs to generate Row_log object and modify the memory dictionary; commit phase, prohibit read and write, redo the last part increment, then commit, ensure the data is consistent.

3. How to implement server layer and InnoDB layer consistency

During the prepare phase, the server layer generates a temporary FRM file containing the format of the new table, the INNODB layer generates a temporary IBD file (rebuild mode), copies the data from the original table to the temporary IBD file during the DDL execution phase, and Row_ The log increment is applied to the temporary IBD file, and in the commit phase, the INNODB layer modifies the table's data dictionary and submits it, and finally the FRM and IDB files are renamed at the InnoDB layer and the MySQL level respectively.

Reference documents

http://hedengcheng.com/?p=405

http://hedengcheng.com/?p=421

http://hedengcheng.com/?p=148

MySQL Online DDL principle