Test the Online DDL function of MySQL 5.6.

Test the Online DDL function of MySQL 5.6.

The predecessor of online DDL is innodb fast index creation (5.1 and 5.5). This function is extended in 5.6: Many alter table operations bypass table copying and support concurrent DML operations.

1. Support Test of online ddl:
1. add or delete a primary key
Add a primary key:
Supports online ddl and concurrent DML during primary key addition.
Primary Key deletion:
Online ddl is not supported. copy table is required to delete the primary key, leading to DML blocking.

2. add and delete Indexes
1) Common Index
5.5 use the new logic (fast index creation) and no longer copy the table (parameter old_alter_table) to accelerate index creation and deletion,
But it will block DML. You can select
5.6 creating and deleting indexes does not block dml
2) unique index
Same as above

3. add, delete, and modify Fields
New:
(Alter table test add hehe2 int default 100)
5.5 DML blocking, supports select
5.6 concurrent DML supported
Delete:
(Alter table test drop hehe2)
5.5 DML blocking, supports select
5.6 concurrent DML supported
Modify:
Only change the field name:
(Alter table test change hehe2 hehe20 int default '20140901 ')
5.5 DML blocking, supports select
5.6 support online ddl and concurrent DML
Modify Field Type:
(Alter table test change hehe20 hehe2 varchar (100) default '20140901 ')
5.5 DML blocking, supports select
5.6 online ddl is no longer supported

Http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
Table 14.5 in lists the detailed operation types supported by online ddl, but it does not need to be remembered so much:

Ii. New Syntax:
In 5.6, alter table adds a new syntax:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
ALGORITHM:
INPLACE: do not copy table
COPY: copy table
DEFAULT:

LOCK:
DEFAULT: mysql selects the least resource locking method.
NONE: select and DML are supported.
SHARED: select is supported. DML is not supported.
EXCLUSIVE: select is not supported and DML is not supported.

You can use this new syntax to test whether the alter table statement supports online DDL:
Create a table with the same structure and store a small amount of data:
Root: 3306: popo> alter table test change hehe2 hehe20 int default '20140901', LOCK = NONE;
ERROR 1846 (0A000): LOCK = NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK = SHARED.
As prompted, the alter table modified for this field type does not support concurrent DML operations.

Root: 3306: popo> alter table test change hehe2 hehe20 int default 100, ALGORITHM = inplace;
ERROR 1846 (0A000): ALGORITHM = INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM = COPY.
Similarly, the alter table operation also requires the copy table operation.

Iii. online ddl parameters and principles:
Innodb_online_alter_log_max_size
The principle of online ddl is that mysql records all insert, update, and delete operations within the ddl time to a log file, then, apply the incremental data to the corresponding table (after the transaction on the table is completely released). The upper limit of this temporary log file is specified by innodb_online_alter_log_max_size, each time innodb_sort_buffer_size is extended, if this parameter is too small, it may cause DDL failure. During this period, all uncommitted concurrent DML operations will be rolled back; however, if it is too large, the DDL operation may last lock the table for a longer time (lock the table and apply logs to the table ). Each changed index or table is assigned one.

This article permanently updates the link address: