MySQL Online DDL (2) (R11 note 88)
For Online DDL, I briefly analyzed the Online DDL in some scenarios MySQL (first article) (R11 note day 3). In fact, there is a very key point not mentioned, that is, the algorithm of online DDL. At present, there are three operation options, default,inplace,copy is optional.
For more information, please refer to https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
> select count (*) from newtest
+-+
| | count (*) |
+-+
| | 22681426 |
+-+
1 row in set (45.76 sec) table structure information is as follows:
> show create table newtest\ G
* * 1. Row *
Table: newtest
Create Table: CREATE TABLE `newtest` (
`id` bigint (20) NOT NULL AUTO_INCREMENT
`game_ type`int (11) NOT NULL DEFAULT'- 1'
`Time`datetime NOT NULL DEFAULT '1970-01-01 login_ 0000'
`login_ roomt` varchar (100) DEFAULT NULL
`cn_ master`varchar (100) NOT NULL DEFAULT''
`client_ ip`varchar (100) DEFAULT''
PRIMARY KEY (`id`)
KEY `ind_tmp_ roomt1` (`login_ roomt`)
KEY `ind_login_time_ newtest` (`login_ time`)
) ENGINE=InnoDB AUTO_INCREMENT=22681850 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Default copy option
For example, we run the following SQL to add a field, which by default uses the copy algorithm, that is, a parallel copy of the data.
Alter table newtest add column newcol varchar (10) default''; this change process generates two temporary files. Frm,. Ibd
-rw-r- 1 mysql mysql 8840 Dec 5 18:13 newtest.frm
-rw-r- 1 mysql mysql 4353687552 Dec 5 18:45 newtest.ibd
...
-rw-r- 1 mysql mysql 8874 Feb 27 22:25 # sql-6273_2980ab.frm
-rw-r- 1 mysql mysql 41943040 Feb 27 22:25 # sql-ib280-3638407428.ibd
... During this change, the DML operation is run without any blocking.
> insert into newtest (game_type,login_time,login_account,cn_master,client_ip) values (1pm) 2017-02-27 1614 22 purl 10 hundred and fifty-eight 1500032 miners 572031626 minicles 183.128.143.113')
Query OK, 1 row affected (0.05sec)
Because the primary key is self-increasing, I can insert another record with the same statement without blocking at all.
> insert into newtest (game_type,login_time,login_account,cn_master,client_ip) values (1pm) 2017-02-27 1614 22 purl 10 hundred and fifty-eight 1500032 miners 572031626 minicles 183.128.143.113')
Query OK, 1 row affected (0. 00 sec) it's a bit easier to see the results of show processlist at this time. Unlike the previous version, there will be the word table metadata lock.
+
| | Id | User | Host | db | Command | Time | State |
+
| | 2719915 | root | localhost | test | Query | 75 | altering table |
Compare temporary files with existing configuration files
Let's take a brief look at the configuration file listed above. Frm
You can see a basic structural information through strings.
# strings newtest.frm
PRIMARY
Ind_tmp_account1
Ind_login_time_newtest
InnoDB
)
Game_type
Login_time
Login_account
Cn_master
Client_ip
Game_type
Login_time
Login_account
Cn_master
Client_ip
Instead, view the temporarily created .frm file
# strings "# sql-6273_2980ab.frm"
PRIMARY
Ind_tmp_account1
Ind_login_time_newtest
InnoDB
)
Game_type
Login_time
Login_account
Cn_master
Client_ip
Newcol
Game_type
Login_time
Login_account
Cn_master
Client_ip
The duration of the entire newcol add field operation is about 10 minutes.
> alter table newtest add column newcol varchar (10) default''
Query OK, 0 rows affected (10 min 31.64 sec)
Records: 0 Duplicates: 0 Warnings: 0 you can see that the modified .ibd file is a little larger than the size.
-rw-r- 1 mysql mysql 8874 Feb 27 22:25 newtest.frm
-rw-r- 1 mysql mysql 4047503360 Feb 27 22:34 newtest.ibd and if we look at it another way, we delete a field.
-- alter table newtest drop column newcol, ALGORITHM=INPLACE;-- this approach is problematic. In the following way, we declare that we use the inplace algorithm, but what is the actual situation?
> alter table newtest drop column newcol, ALGORITHM=INPLACE
Query OK, 0 rows affected (9 min 54.18 sec)
Records: 0 Duplicates: 0 Warnings: 0 We can see that the DML operation is unimpeded.
> insert into newtest (game_type,login_time,login_account,cn_master,client_ip) values (1pm) 2017-02-27 1614 22 purl 10 hundred and fifty-eight 1500032 miners 572031626 minicles 183.128.143.113')
Query OK, 1 row affected (0.15 sec) this process can see that the effect is the same as enabling the copy algorithm, why. Because adding fields and deleting fields is a process of data reorganization, this operation is also expensive.
Add / remove index
Then we add the index to enable the inplace algorithm.
Alter table newtest add index (client_ip), algorithm=inplace; is a special process. Temporary files of .frm will still be created, but the data files will not be copied, but will be changed now.
-rw-r- 1 mysql mysql 8840 Feb 27 22:49 newtest.frm
-rw-r- 1 mysql mysql 4018143232 Feb 27 23:06 newtest.ibd
...
-rw-r- 1 mysql mysql 8840 Feb 27 23:06 # sql-6273_2980ab.frm during this process, DML is still open.
> insert into newtest (game_type,login_time,login_account,cn_master,client_ip) values (1pm) 2017-02-27 1614 22 purl 10 hundred and fifty-eight 1500032 miners 572031626 minicles 183.128.143.113')
Query OK, 1 row affected (0.04 sec), the duration of the whole process is much shorter, about 3 minutes.
> alter table newtest add index (client_ip), algorithm=inplace
Query OK, 0 rows affected (3 min 42.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
If the index is deleted at this time, the process is like an unusual feeling and can be completed in less than a second.
> alter table newtest drop index client_ip, algorithm=inplace
Query OK, 0 rows affected (0.13 sec)
There is no change in the size of the .frm and .ibd files throughout Records: 0 Duplicates: 0 Warnings: 0.
-rw-r- 1 mysql mysql 8840 Feb 27 23:13 newtest.frm
-rw-r- 1 mysql mysql 4785700864 Feb 27 23:13 newtest.ibd and if we want to compare the costs in the same inpalce and copy operation scenarios, we can create an index using the copy display to get a basic comparison.
Alter table newtest add index (client_ip), algorithm=copy; the whole process because the .ibd file is larger, the duration will be enlarged a lot, in this environment, the execution time is 29 minutes, the difference is already very obvious.
> alter table newtest add index (client_ip), algorithm=copy
Query OK, 22681430 rows affected (29 min 13.80 sec)
Records: 22681430 Duplicates: 0 Warnings: 0
Summary
There are still some limitations in Online DDL, and many scenarios have not been fully tested, which need to be considered in the light of specific scenarios and requirements.
