Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.