MySQL 8.0 replication Performance Improvement (translation), mysql8.0

MySQL 8.0 replication Performance Improvement (translation), mysql8.0
What's New With MySQL Replication in MySQL 8.0?

MySQL replication has been around for many years since its launch, and its stability and reliability are also steadily improved. This is a process of continuous evolution. Because many important functions of MySQL depend on replication, the rapid development of replication is easy to understand.

In the previous version of MySQL, MySQL improves the replication performance to a new level by implementing true parallel replication, because in MySQL 5.6, although it is said that parallel replication is implemented, parallel replication is schema-level, that is, if the binlog row event operates on different schema objects, parallel replication can be implemented without the DDL and foreign key dependencies. It only separates the objects in different schemas for execution, and cannot implement parallel replication in the true sense (for example, the master-slave architecture has only one schema, which is often seen in many cases ). Of course, if your master-slave architecture has multiple schemas, 5.6 of parallel replication will greatly improve the performance.

MySQL 5.7 adopts the group commit-based parallel replication method. Of the parallel replication methods, even if multiple of your tasks operate on the same schema, it can also be played back in parallel on the slave database. In short, if multiple items do not affect each other, they can be submitted as a group for disk flushing, we can even manually set the binlog_group_commit_sync_delay parameter in the slave database to control the waiting time for logs to be submitted before flushing the disk, thus improving the efficiency of slave database replication.

This solution seems perfect, but it does not have any disadvantages. The latency of transaction submission will eventually affect the user experience of the application. Of course, you can set the latency within several milliseconds, but even this will still affect the timeliness of the client.

Improved replication performance of MySQL8.0

So far (August 2017), MySQL 8.0 has released the latest beta version. It was initially developed for group replication (GR), but since GR is also used for common replication at the underlying layer, common replication also benefits a lot. The improvement we mentioned is that 8.0 adds some dependent tracing information to binary log. In MySQL8.0, MySQL can store the affected information (called writeset) of those rows that have been affected by those transactions in one way, and MySQL can compare the writeset information of different things, in this way, we can determine whether two objects operate on the same row of the same object. If not, they can be executed in parallel. Compared with MySQL5.7, this improves the replication level. What you need to keep in mind: In the end, the data you see from the database may be different from the data in the master database, which will never happen in the master database. The reason for this is that the sequence of executed transactions in the slave database may be inconsistent with that in the master database. Although this is not a problem, the parallel replication mechanism of MySQL5.7 will also cause this problem, unless you specify to enable the slave-preserve-commit-order parameter.

To avoid this problem, the binlog_transaction_dependency_tracking parameter is added to MySQL8.0. It can take the following three values:

• COMMIT_ORDER: default setting. The default setting is MySQL5.7.

• WRITESET: it can achieve better parallelization, and the master database starts to store and write writeset information in binary logs.

• WRITESET_SESSION: sets the sequence of the transaction execution in the slave database, which eliminates the possibility of different viewing data from the slave database from the master database. Setting this value will reduce the performance of parallel replication, but the performance is greatly improved compared with the default setting.

Benchmark Test

In February July, Vitor Oliveira wrote an article on mysqlhighavailability.com, trying to test the performance of the new replication mode. In his test, he used the ideal scenario-not doing data persistence, to compare the performance of the New and Old replication modes. We decided to use the same method, this time in a more close-to-production setting: Use log_slave_updates to enable binary logs. The persistence parameter is default in MySQL8.0 (sync_binlog = 1-this is the default value in MySQL8.0, enabling dual-write cache and innodb verification), and innodb_flush_log_at_trx_commit is set to 2.

Let's take a look at the machine configuration, 32G, 8-core (slave_parallel_workers is set to 8), and the test tool is the oltp_read_write.lua script of sysbench, the 16 million rows in 32 tables are stored on the 1000 GB gp2 volume (3000 IOPS). In all replication modes, we test and compare the performance of 1, 2, 4, 8, 16, and 32 concurrency. The test process is as follows: Stop the slave, execute 100000 things, open the slave, and calculate the time when the slave database catches up with the master database.

First, we do not know what happens when one thread is used to execute sysbench. Each test is performed five times after the warm machine runs. This special configuration was tested twice-the result is stable: the single-thread workload is the fastest. We will further study to find out why.

Everything is in line with our expectations. COMMIT_ORDER is the slowest, especially for low-traffic, 2-8 threads. WRITESET_SESSION is generally more efficient than COMMIT_ORDER, but it is slower than WRITESET for low-concurrency traffic.

What are the advantages of this for me?

The first benefit is obvious. If your database load is high and there is latency in the slave database, you can upgrade the master database to MySQL 8.0 to improve the replication performance. Pay attention to two aspects: first, this function is backward compatible, even if the slave database is MySQL 5.7, the performance can be improved very well; second, MySQL8.0 does not currently have GA, therefore, we do not recommend using the Beat version in the production database. The improvement of MySQL8.0 on replication not only solves the problem of slave database latency, but also has almost no Master/Slave latency in this case, latency may occur unless you add a new slave or reconfigure the slave. If the "WRITESET" mode is used, the process of configuring the new host is faster.

All in all, this feature may have a greater impact than you expected. In view of all the benchmark tests, it shows the performance regression when MySQL processes low-concurrency traffic, any operation that helps improve the replication efficiency in such an environment will be a huge improvement.

If you use cascade replication, this is also a feature you need to know. Any intermediate master node will add serialized information to the transaction processing and execution method-but the load on the intermediate node is smaller than that of the primary database. It uses some write components to achieve better parallelization, thus improving the parallel efficiency of itself and its slave database. You can even upgrade the master database of the intermediate node to MySQL8.0 to improve the node of the lower-level slave database (remember that MySQL 5.7 slave station can identify riteet data and use it, even if it cannot generate it by itself ). Of course, Master-slave replication from MySQL 8.0 to 5.7 Sounds tricky, not because MySQL does not yet have GA. Of course, in some cases, this can improve the CPU usage of the slave database.

Other changes in MySQL Replication

MySQL8.0 introduces writesets to improve replication, but this is not the only change. Let's take a look at some other important improvements. If your master database is MySQL 8.0 or earlier, MySQL will no longer support reading its binary logs, so if you are still using the old version of MySQL for traditional replication, it is time to upgrade your database version. To ensure the security and stability of replication, the default parameters master_info_repository and relay_log_info_repository are modified to table by default, and Expire_log_days is set to 30 by default. In addition to Expire_log_days, an additional parameter is added, this will implement more fine-grained control over binlog polling policies. Some additional timestamp information will be added to the binlog to better observe the replication latency and achieve a subtle level.

All in all, this is not a complete list of changes and functions related to MySQL replication. You can access the complete list to learn about all the improvements and improvements to the replication function.

As we know, MySQL replication is changing and getting better and better. As we said at the beginning, although this is a slow process of progress, we can see its great prospects. We are also glad to see that the underlying Replication of Group Replication also uses the regular Replication function.