Mysql online ddl

As you know, Internet businesses are typical OLTP (online transaction process) applications. This type of application accesses the database and features a large number of short transactions and high concurrency. Therefore, any actions that limit high concurrency are unacceptable and may even cause disasters to the website. For databases, high concurrency is usually a conflict with ACID in transactions. To ensure the ACID properties of transactions, some measures must be used to control concurrency, such as lock-based concurrency control, or MVCC-based concurrency control. The MVCC-based concurrency control only solves the read-not-blocking problem to a certain extent. However, for DML or DDL, the locking mechanism is still used to ensure transaction isolation.

Among all database operations, DDL locks have the largest granularity by including metadata locks and table Object locks. Common DDL operations include alter, create, drop, etc. For create and drop operations, the execution process is usually fast, so the impact is relatively small. For alter operations, especially for large tables, this process may take a long time. Because DML operations on table objects are blocked during the change process, an alter operation may cause a large number of database access timeouts on the front-end website applications. How can this problem be solved? The first is that the alter operation is not locked, so it does not affect the write operation. If not, the alter operation time can be shortened to reduce the time of the table being inaccessible.

For mysql databases, there is also a process to solve the alter problem. The online ddl function was not launched until mysql 5.6. Version 5.5 uses FIC (fast index creation) to increase the speed of adding and deleting indexes in the alter operation. version 5.6 optimizes more online ddl statements, added more "online" operations. Before introducing the principles of FIC and online ddl, let's take a look at some common alter operations. See table 1.

Alter action	Description
Add index, drop index	Add, delete, and modify secondary Indexes
Add column, drop column	Add, delete, and modify Columns
Add primary key, drop primary key	Add, delete, and modify primary key indexes
Set character set utf8/gbk	Modify Character Set and storage engine
Optimize table	Reorganizing a table

Table 1

For the above common scenarios, we can see what FIC and online ddl are doing and how they are implemented. The following analysis is based on the innodb table.

For A general alter operation, the principle is basically like this. Assume that you need to change the table structure of Table A, first create A temporary table B of the target table structure, and then lock the table, copy data from Table A to table B. rename table B to Table A to release the lock.

FIC is optimized for adding and deleting indexes. In this scenario, the storage structure of innodb tables is not changed, but there are more or less indexes. Therefore, there is no need to copy the entire table, you can directly add or delete indexes, which reduces the copy time and lock time. For alter operations that require the table Storage Structure, FIC is powerless. Because mysql does not show Online ddl versions, FIC scenarios are not common and will still block write operations, which is unacceptable to businesses. There is no way. In many cases, table structure changes need to be performed during off-peak hours (early hours) through Master/Slave database switchover. This is really a pain for DBAs.

Fortunately, before mysql5.6 appeared, percona provided the "online" table structure change tool pt-online-schema-change, which benefits DBAs. The core principle of the tool is to use insert... Select... Statement to perform a full copy. The trigger records the incremental data generated during the table structure change process to achieve the Table Structure Change goal. Assume that table A is changed. The main steps are as follows:

After using this method, when performing the alter operation, the read and write operations are no longer blocked, and the supported alter statements are more extensive. For example, these statements can be supported in several situations listed in Table 1, in addition to Optimize table.

The principle of Mysql online ddl is essentially the same as that of pt-online-schema-change, but this process is encapsulated in mysql. However, this method also has some drawbacks and restrictions. For example, if a primary key is required, the copy speed of the table is not as fast as that of the source lock table.

Finally, an example is provided to illustrate the impact of alter operations on DML in 5.5 and 5.6. From table 2, we can see that in Tables 5.5 and 5.6, the query and update will block the alter operation. In versions 5.5, the alter operation will not block reading, but will block writing; in version 5.6, alter does not block reading and writing.

Time Point	Session A (5.6)	Session A (5.5)	Session B	Session C
1	Set autocommit = 0; Update t set c2 = '000000' where c1 = 4;	Set autocommit = 0; Update t set c2 = '000000' where c1 = 4;
2			Alter table t drop column c3;
3				Show processlist; B: Waiting for table metadata lock
4		A: Submit the transaction. Commit
5				Show processlist; B: copy to tmp table
6			B: continue execution
7	Select count (*) from t; Normal execution	Select count (*) from t; Normal execution
8	Update t set c2 = '000000' where c1 = 4; Normal execution	Update t set c2 = '000000' where c1 = 4; blocking
8				Show processlist; A (5.5): Waiting for table metadata lock B: copy to tmp table
9			B execution completed
10		Executed by

Table 2