Mysql 5.6 online ddl Test

Mysql 5.6 online ddl testing in the production environment, because of business needs, changes the mysql table structure, especially when the data volume is particularly large, millions or even tens of millions of data, mysql online ddl operations are very frustrating for mysql DBAs. Analyticdb 5.6 supports online ddl. Why are mysql ddl operations worrying DBAs? Because the internal principle of mysql ddl operations is as follows: 1 create table tmp like table2 (create a temporary table, similar to the original table) 2 insert into tmp select * from table2 (the original data is copied to the temporary table, and the index and other information are the same as the original table) 3 drop table table1 delete original table 4 rename tmp to table1 temporary table name renamed original table during the whole process, original table table1 add share lock (s lock ), in this way, the business is greatly affected, and the job is congested (my colleague added a field at, which affects the order business). Next, let's test it. In mysql5.6, We optimized it. alter table table1 add .... During the operation, adding, deleting, modifying, and querying tables will not lock the table. first, prepare the test data, and try to make it more than 10 million, obvious test results: [root @ aeolus1 bin] # sysbench -- test = oltp -- oltp-table-size = 15000000 -- mysql-db = test -- mysql-user = root prepare sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Creating table 'sbtest '... creating 15000000 records in table 'sbtest '... one session executes mysql> alter table sbtest add testvarchar (10); the other session executes mysql> insert Sbtestvalues (0.44, 'abc', 'abc'); Query OK, 1 row affected (2000 sec) mysql> delete from sbtest where id =; Query OK, 1 row affected (1.21 sec) mysql> update sbtest set k = 3 whereid = 20000; Query OK, 1 row affected (0.59 sec) Rows matched: 1 Changed: 1 Warnings: 0 is successfully executed, and no lock table exists. When a session is accessed, it takes a long time to perform the alter table sbtest add operation. The alter table sbtest operation will wait until the session ends and then perform the ddl operation.