Technology Sharing | Reasons for the low efficiency of MySQL Binlog importing the database through the MySQL client

Author: Guo Binbin

A member of the Aikesheng DBA team, responsible for the daily problem handling of the project and the troubleshooting of the company's platform.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

1. Background

Customers reported that in the production environment, when MySQL 5.7 used xtrabackup + Binlog to perform point-in-time recovery operations, it continued to be stuck in the Binlog playback stage, which was prolonged and outrageous for a long time. He has doubts about this protracted operation, and wants to confirm whether this behavior of the database is reasonable, so he has the Binlog recharge verification operation in this article.

Second, the premise of recurrence

MySQL Version：5.7.22

Binlog format： Row

Prepare to delete the Binlog of more than 8 million records

Three, recurrence preparation

3.1 Create a table, structure data

mysql> create table t1(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.02 sec)
    
mysql> insert into t1 values(1,repeat('a',10));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 select (select count(1) from t1)+id,name from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
    ………………
mysql> insert into t1 select (select count(1) from t1)+id,name from t1;
Query OK, 4194304 rows affected (57.75 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

3.2 Prepare a Binlog file that deletes more than 8 million records

MySQL Binlog mysql-bin.000003 is used for recharge test

3.3 Since Binlog's recharge and count are on the same instance, in order to build the Binlog of Delete 8 million records, the data has been deleted, so before binlog recharge, you need to use the previous count method to rebuild number

3.4 Delete first on the same instance, and then rebuild new data. The GTID that causes the Delete operation is smaller than the GTID of the reengineering operation. To ensure normal recharge, you can execute reset master

Four, recurrence test

4.1 Parsing MySQL Binlog mysql-bin.000003

4.2 Import analysis file

A Few Moment Later

4.3 Checking the processlist, it is found that the import thread has been in the Sleep state, and the phenomenon is consistent with the customer's description.

4.4 Immediately interrupt the import operation, re-initiate the import and use strace to record the operation.

4.5 By observing the generated strace.log, it is found that the time interval between two reads is not fixed, and it takes about 140ms for a few reads, but the read size is only 4k (4096), which is low in reading efficiency.

Five, analysis

A Google search for "MySQL Mem Load Slow" found that this is a BUG. When MySQL 5.7 Client reads large transactions (involving multi-row operations), it consumes a lot of time due to the low memory allocation efficiency. It has been in MySQL 8.0. Fix in 13.

Six, retest

6.1 The Mysql 8.0.18 client recharges the Binlog parsing file, prompting that MySQL Server has gone away

6.2 The database did not trigger a restart when the derivative reported an error, check the error log, the following error is reported:

6.3 Retest after increasing the max_allowed_packet configuration

6.4 Observe strace log, read Binlog size 16M each time, much higher than the original 4k

6.5 Observe thread status

6.6 Observe the execution time. The derivative time of MySQL 8.0.18 client becomes shorter, and the efficiency is improved significantly.

7. Conclusion

At present, the official version of MySQL 8.0.13 has solved the problem of "low memory allocation efficiency when using MySQL Client to perform batch derivatives". Therefore, MySQL 8.0.18 client reads files after refilling Binlog analysis. The file efficiency is significantly higher than that of the 5.7.22 client, which improves the efficiency of Binlog playback.

Reference link

https://bugs.mysql.com/bug.php?id=85155

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-bug