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

This article mainly introduces MySQL5.7 online DDL related knowledge, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the MySQL5.7 online DDL related knowledge.

1. Different versions of MySQL have different ways to deal with DDL. There are three main ways:

①: Copy Table method: this is the earliest method supported by InnoDB. As the name implies, it is realized by copying temporary tables. Create a new temporary table with a new structure, copy all the data of the original table to the temporary table, and then Rename to complete the creation operation. In this way, the original table is readable and not writable. But it consumes twice as much storage space.

②: Inplace mode: this is native MySQL 5.5, as well as the method provided in innodb_plugin. The so-called Inplace, that is, directly on the original table, will not copy the temporary table. This is more efficient than the Copy Table approach. The original table is also readable, but not writable.

③: Online method: this is the method provided in MySQL version 5.6 and above, and it is also the way we focus on today. No matter in Copy Table mode or Inplace mode, the original table can only be read, not written. There are great restrictions on applications, so in the latest version of MySQL, InnoDB supports the so-called Online mode DDL. Compared with the above two methods, online supports DDL not only to read, but also to write, which is a great improvement for dba.

2. Online ddl: (algorithm=inplace) in MySQL5.7

ALGORITHM=INPLACE, which can avoid the IO and CPU consumption caused by rebuilding the table, and ensure good performance and concurrency during ddl. -*

ALGORITHM=COPY, you need to copy the original table, so concurrent DML writes are not allowed and can be read. This copy approach is still not as efficient as inplace because the former needs to record undo and redo log, and performance is affected in a short period of time because of the temporary occupation of buffer pool.

①: In-Place is the preferred option for Yes, indicating that the operation supports INPLACE

②: Copies Table for No is preferred because the table needs to be rebuilt for Yes. In most cases, it is the opposite of In-Place.

③: Allows Concurrent DML? Yes is the preferred option, which means that the ddl interval table can still be read and written, and you can specify LOCK=NONE (mysql is automatically NONE if the operation allows)

④: Allows Concurrent Query? By default, query requests are allowed during all DDL operations. This is for easy reference only.

II. Restrictions on online DDL

1) in alter table, if table copy operation is involved, make sure that the datadir directory has enough disk space to hold the whole table, because the operation of copying the table is done directly under the data directory.

2) add an index without table copy, but make sure that the tmpdir directory is sufficient to hold a column of data in the index (if it is a combined index, the current temporary sort file will be deleted as soon as it is merged into the original table) 3) in the master-slave environment, the master library executes the alter command will not enter the binlog record event before it is completed, and if the dml operation is allowed, it will not affect the recording time, so the period will not cause delay. However, since the relay log is applied sequentially to a single SQL Thread from the library, the next entry is not available until the ALTER statement is executed, so there is a delay from the library after the master ddl is completed. (pt-osc can control the delay time, so it is more appropriate in this scenario.)

4) During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring an exclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT... FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.

5) when executing an online ALTER TABLE that allows concurrent DML, the thread applies incremental changes to the online log record before the end, and these changes are generated in other thread, so it is possible to encounter duplicate key value errors (ERROR 1062 (23000): Duplicate entry).

6) when it comes to table copy, there is no mechanism to limit the suspension of ddl or the threshold of IO. 7) starting from MySQL 5.7.6, you can observe the progress of alter table through performance_schema.

In general, it is recommended to merge multiple alter statements together to avoid the consumption caused by multiple table rebuild. But also pay attention to grouping, such as the need for copy table and only inplace can be done, should be divided into two alter statements.

8) if DDL takes a long time to execute, and a large number of dml operations occur during that time, so that it exceeds the size specified by the innodb_online_alter_log_max_size variable, it will cause a DB_ONLINE_LOG_TOO_BIG error. The default is 128m, especially for alter operations that need to copy large tables, consider temporarily increasing this value to get more log cache space.

9) after ALTER TABLE, it is best to update the index statistics by ANALYZE TABLE tb1.

Third, the realization process of Online DDL

Online ddl mainly includes three stages: prepare stage, ddl implementation stage and commit stage. Rebuild has essentially one more ddl implementation stage than no-rebuild, and the prepare stage is similar to the commit stage. The following will focus on the process of the three stages of ddl execution.

3.1Phase of Prepare:

①: create a new temporary frm file (independent of InnoDB) ②: hold an EXCLUSIVE-MDL lock, disable reading and writing ③: determine the execution mode (copy,online-rebuild,online-norebuild) according to the alter type

If it is Add Index, then choose online-norebuild or INPLACE mode.

④: memory object for updating data dictionaries

⑤: assign row_log object record increments (required only by rebuild type) ⑥: generate a new temporary ibd file (required only by rebuild type)

3.2.The execution phase of ddl:

①: degrades the EXCLUSIVE-MDL lock to allow reading and writing

②: scan every record of old_table 's clustered index rec ③: traverse the new table's clustered index and secondary index, processing them one by one

④: construct the corresponding index entry according to rec

⑤: insert the construction index entry into the sort_buffer block sort ⑥: update the sort_buffer block to the new index ⑦: record the increments generated during the execution of the ddl (required by the rebuild type only) ⑧: replay the operations in the row_log to the new index (the no-rebuild data is updated on the original table) ⑨: generate the dml operation append between the playback row_log to the last Block of row_log

3. Commit stage:

①: disable reading and writing when the current Block is the last one in row_log, upgrade to EXCLUSIVE-MDL lock ②: redo the last part of row_log incremental ③: update innodb's data dictionary table ④: commit transaction (brush transaction redo log) ⑤: modify statistics ⑥: rename temporary idb file, frm file ⑦: change completed

-mysql5.7 official document description:

15.13.1 Online DDL Overview

The online DDL feature enhances many DDL operations that formerly required a table copy or blocked

DML operations on the table, or both. Table 15.10, "Online Status for DDL Operations" shows how the

Online DDL feature applies to each DDL statement.

With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned

InnoDB tables follow the same rules that apply to regular InnoDB tables. For more information, see

Section 15.13.7, "Online DDL for Partitioned Tables".

Some factors affect the performance, space usage, and semantics of online DDL operations. For more

Information, see Section 15.13.8, "Online DDL Limitations".

The "In-Place?" Column shows which operations permit the ALGORITHM=INPLACE clause.

The "Rebuilds Table?" Column shows which operations rebuild the table. For operations that use

The INPLACE algorithm, the table is rebuilt in place. For operations that do not support the INPLACE

Algorithm, the table copy method is used to rebuild the table.

The "Permits Concurrent DML?" Column shows which operations are performed fully online. You can

Specify LOCK=NONE to assert that concurrent DML is permitted during the DDL operation. MySQL

Automatically permits concurrent DML when possible.

Concurrent queries are permitted during all online DDL operations. You can specify LOCK=SHARED

To assert that concurrent queries are permitted during a DDL operation. MySQL automatically permits

Concurrent queries when possible.

The "Notes" column provides additional information and explains exceptions and dependencies related

To the "Yes/No" values of other columns. An asterisk indicates an exception or dependency

The experiments are summarized as follows:

1. The experimental environment is MySQL5.7.18.

[mysql@localhost] $mysql-u root-pEnter password: Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 9Server version: 5.7.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

2. Create the test table test_emp and insert the data

MySQL [(none)] > create database test Query OK, 1 row affected (0.07 sec) MySQL [(none)] > use testDatabase changedMySQL [test] > create table test_emp (id int (10) unsigned NOT NULL AUTO_INCREMENT, C1 int (10) NOT NULL DEFAULT '0mm,-> c2 int (10) unsigned DEFAULT NULL, c5 int (10) unsigned NOT NULL DEFAULT' 0mm, c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-> c4 varchar (200) NOT NULL DEFAULT', PRIMARY KEY (id), KEY idx_c1 (C1) KEY idx_c2 (c2) ENGINE=InnoDB Query OK, 0 rows affected (0.11 sec)-create a test table: test_empMySQL [test] > delimiter / / MySQL [test] > create procedure insert_test_emp (in row_num int)-> begin-> declare i int default 0;-> while I

Insert into test_emp (c1, c2, c5Powerc3, c4) values (floor (rand () * row_num), now (), repeat ('su', floor (rand () * 20));-> set I = ionome1;-> END while;-> end-> / / Query OK, 0 rows affected (0.01 sec) MySQL [test] > MySQL [test] > call insert_test_emp (100000) -insert data Query OK into the test table test_emp, 1 row affected (8 min 24.34 sec) MySQL [test] > desc test_emp +-+-+ | Field | Type | Null | Key | Default | Extra | | +-+ | id | int (10) unsigned | NO | PRI | NULL | auto_increment | | | C1 | int (10) | NO | MUL | 0 | | c2 | int (10) unsigned | YES | MUL | NULL | | c5 | int (10) unsigned | NO | | 0 | | | c3 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | c4 | varchar (200) | NO | | +-| -+-- + 6 rows in set (0.00 sec) MySQL [test] >

3. Modify fields online:

MySQL [test] > alter table test_emp add c6 varchar (60) not null default'; Query OK, 0 rows affected (2.04 sec) Records: 0 Duplicates: 0 Warnings: 0MySQL [test] > select count (*) from test_emp +-+ | count (*) | +-+ | 100000 | +-+ 1 row in set (0.02 sec) MySQL [test] >-use the ALGORITHM=INPLACE option to modify MySQL [test] > alter table test_emp ALGORITHM=INPLACE,modify c6 varchar (80) not null default''online; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0MySQL [test] >

-you can see that the execution time is 0.09 seconds and the execution speed is very fast.

However, ALGORITHM usage is only valid for varcahr types, for example, we change the C1 column int type:

MySQL [test] > alter table test_emp ALGORITHM=INPLACE,modify C1 int (11) unsigned not null

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

MySQL [test] >

-Note:

①: it's OK to just change the number of int digits, but it doesn't make any sense, because no matter how much int you have, you can only save up to 10 digits, which is why our production library development specification defines that all int uses int (10).

②: if the field attribute is greater than or equal to varchar (256) (where 256means bytes (UTF8 takes up 3 bytes) or reduces varchar (80) to varchar (70) or less, you still need to copy the data and lock the entire table.

Mysql > alter table test_emp ALGORITHM=INPLACE,modify c6 varchar (84) not null default'; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > alter table test_emp ALGORITHM=INPLACE,modify c6 varchar (85) not null default''; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > alter table test_emp ALGORITHM=INPLACE,modify c6 varchar (86) not null default'; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. Mysql > alter table test_emp ALGORITHM=INPLACE,modify C6 varchar (40) not null default''; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. Mysql > alter table test_emp ALGORITHM=INPLACE,modify C6 varchar (70) not null default''; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Note: when you add the field alter table, the addition, deletion, modification and query of the table will not lock the table. Before that, when the table is accessed, you need to wait until its execution is complete before alter table can be executed.

Summary:

In terms of varchar changing the field length, the new feature ALGORITHM parameter of 5.7 allows you to quickly adjust the field length of the varchar type. 5.7 like 5.6, add, delete fields or indexes do not lock the whole table, delete the primary key to lock the whole table. Therefore, when you go online, be sure to execute the show processlist command and observe whether there is some slow SQL operating on the table at the moment, so as to avoid locking the table when you alter table the table.

*

1. Add indexes online:

Alter table test_emp add index idx_id (C1), ALGORITHM=INPLACE

2. Add fields online:

Alter table test_emp add name varchar not null default', ALGORITHM=INPLACE

3. Modify field properties online:

Alter table test_emp ALGORITHM=INPLACE,modify c6 varchar (85) not null default''

Syntax:

1.PRIMARY KEY (primary key index)

Mysql > ALTER TABLE `table_ name` ADD PRIMARY KEY (`column`), ALGORITHM=INPLACE

2.UNIQUE (unique index)

Mysql > ALTER TABLE `table_ name` ADD UNIQUE (`column`), ALGORITHM=INPLACE

3.INDEX (General Index)

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column`), ALGORITHM=INPLACE

4.FULLTEXT (full text Index)

Mysql > ALTER TABLE `table_ name` ADD FULLTEXT (`column`), ALGORITHM=INPLACE

5. Multi-column index

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column1`, `column2`, `column3`), ALGORITHM=INPLACE

Note:

In MySQL5.6, online DDL locks the whole table: adding or deleting fields or indexes does not lock the whole table, and deleting the primary key locks the whole table.

After reading the above knowledge about MySQL5.7 online DDL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.