MySQL 5.7

Background demand

Adding fields in production is a very common operation. However, the current scenario is that a single table has about hundreds of millions of rows of data, and the disk occupies hundreds of GIGABytes. There will be a large number of SELECT DELETE operations within 1 second, and the time is sensitive. In order to not affect the production environment, we should first investigate and carefully operate.

Data summary

MySQL OSC(Online Schema Change) MySQL OSC(Online Schema Change) 1.MySQL5.6 OnlineDDL (built-in function) 2.Percona pt-online-schema-change. 3.OAK Openark-kit. The implementation steps of the three ways and possible problems.

MySQL official document: English: dev.mysql.com/doc/refman/… English version: www.docs4dev.com/docs/zh/mys…

DDL allows concurrent DML, such as select,update,delete… Unless incremented auto-increase columns are not allowed.

Take a look at lecture 45 of MySQL Practice

Therefore, in MySQL 5.5, MDL was introduced to add, delete, alter, and query data to a table. Add an MDL write lock to a table when making structural changes to the table. Read locks are not mutually exclusive, so you can have multiple threads adding, deleting, or modifying a table at the same time. Read/write locks and write locks are mutually exclusive to ensure the security of changing the table structure. Therefore, if two threads are adding fields to a table at the same time, one of them will wait for the other to finish before starting to execute. Although MDL locks are added by default, they are a mechanism you can’t ignore. For example, I often see people fall into this trap: adding a field to a small table causes the entire library to hang.

We can see that session A starts first and an MDL read lock is placed on table T. Since session B also requires an MDL read lock, it works fine. Session C is then blocked because session A's MDL read lock has not been released, while session C needs the MDL write lock and can only be blocked. It doesn't matter if only Session C itself is blocked, but all subsequent requests for MDL read locks on table T will also be blocked by Session C. SQL > alter table read lock (MDL); SQL > alter table read lock If the queries on a table are frequent and the client has a retry mechanism, meaning that a new session will be requested after a timeout, the library threads will quickly fill up. As you should know by now, the MDL lock in a transaction is claimed at the beginning of the statement execution, but is not released immediately after the statement completes, but after the entire transaction commits. Based on the above analysis, let's discuss how to safely add fields to small tables. First we need to resolve long transactions, which will hold the MDL lock until they commit. In the Innodb_TRx table of MySQL's Information_SCHEMA library, you can look up the transaction that is currently executing. If the table to which you want to make DDL changes happens to have a long transaction in progress, consider suspending the DDL first or killing the long transaction. But consider this scenario. If the table you want to change is a hotspot table, the data volume is small, but the request is frequent, and you have to add a field, what do you do? At this point, kill may not work because new requests are coming in. An ideal mechanism would be to set the wait time in the ALTER TABLE statement. If you can get the MDL write lock within the specified wait time, it is better not to block subsequent business statements. The process is then repeated by the developer or DBA with a retry command. MariaDB has incorporated AliSQL's functionality, so both open source branches currently support DDL NOWAIT/WAIT N syntax. ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...Copy the code

As you can see, ALTER table will add MDL to block other DML, which seems to be different from MySQL’s official table expression. However, the way to avoid long transactions can be used for reference. There is no case of long transactions in the business, but the transaction is submitted after the execution of a single line, and the problem of long waiting will not occur.

Validation and testing

MySQL 5.7 master/slave configuration to simulate a production environment and insert 670W rows into a single table. Run the service logic to frequently read and write the table. 200 SELECT /s,200 DELETE /s, and 100 INSERT /s Execute the prepared DDL statement

ALTER TABLE table_name ADD app_name varchar(64) NOT NULL DEFAULT '' ,
ADD INDEX APP_NAME_IDX(app_name) USING BTREE ;
Copy the code

DML execution is not affected, and DDL statement execution takes 150s. Check the log can’t find MySQLTransactionRollbackException, everything was normal.

If you are executing DDL in code, you may encounter

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was{xxx} milliseconds ago. The last packet sent successfully to the server was {xxx} milliseconds ago.

The connection times out. Set the socketTimeout parameter to a larger value

conclusion

Adding fields and indexes to a large table in MySQL 5.7 does not block DML statements (or for very short periods of time). You can use MySQL’s built-in functionality instead of using other tools. Note, however, that multiple ALTER statements can be concatenated with commas to avoid long transactions or the wait keyword.

Other MySQL versions

  • MySQL 8.0 supports the second plus field by default

Jishuin.proginn.com/p/763bfbd2c…

  • AWS Amazon Aurora MySQL

Docs.aws.amazon.com/AmazonRDS/l…

  • PolarDB MySQL Ali Cloud native database

Help.aliyun.com/document_de…