Investigation of Slow MySQL 5.7 Binlog Replay and Performance Improvements in MySQL 8.0

Background – A customer reported that point‑in‑time recovery using xtrabackup and binlog on MySQL 5.7 got stuck for an excessively long time during the binlog replay phase, raising doubts about the correctness of the behavior.

Reproduction Preconditions – MySQL version 5.7.22, binlog format ROW, and a prepared binlog that contains deletions of more than 8 million rows.

Preparation

3.1 Create table and generate 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 the binlog file that records the deletion of over 8 million rows (shown in the attached screenshots).

3.3 Because the binlog replay and data generation are performed on the same instance, the GTID of the delete operation is smaller than that of the regenerated data; therefore a reset master is executed before replay.

3.4 After resetting, the instance contains both the delete and the newly generated data, allowing a proper replay.

Reproduction Test

4.1 Parse the MySQL binlog mysql-bin.000003 (see screenshot).

4.2 Import the parsed file into the database.

4.3 Observe the processlist; the import thread stays in Sleep state, matching the customer's symptom.

4.4 Interrupt the import and restart it while recording system calls with strace.

4.5 The strace log shows two read calls with irregular intervals, the shortest being about 140 ms, each reading only 4 KB, indicating very low I/O efficiency.

Analysis

Searching for “MySQL Mem Load Slow” reveals a bug: MySQL 5.7 client allocates memory inefficiently when reading large transactions (many rows), causing excessive time consumption. The bug was fixed in MySQL 8.0.13.

Retest

6.1 Using MySQL 8.0.18 client to replay the binlog results in “MySQL server has gone away”.

6.2 The error log shows the corresponding error message.

6.3 After increasing max_allowed_packet, the test is rerun.

6.4 The new strace shows each read operation now reads 16 MB, far larger than the previous 4 KB.

6.5 Thread state observation confirms the client is no longer stuck in Sleep.

6.6 Execution time is dramatically reduced; the MySQL 8.0.18 client processes the binlog much faster than the 5.7.22 client.

Conclusion

The issue was resolved in MySQL 8.0.13, where the client’s memory‑allocation inefficiency for bulk data loads was fixed. Consequently, using MySQL 8.0.18 for binlog replay yields a noticeable performance boost over MySQL 5.7.22.

References

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