MySQL 8.0 Replication Performance Improvement (translation)

What's New with MySQL Replication in MySQL 8.0

MySQL replication has been around for many years now, and its stability and reliability are steadily improving. This is an evolutionary process, because many of MySQL's important functions are dependent on replication, so the rapid development of replication is also easy to understand.

In the last version of MySQL, MySQL has raised the performance of replication to a new level by enabling true parallel replication, because in MySQL version 5.6, although it is claimed to be a parallel replication, parallel replication is the schema level, that is, if the Binlog row The event operates on objects of different schemas, and in the case of determining that there is no DDL and foreign key dependencies, it is possible to implement parallel replication, which is not called true meaning replication. It just separates things from different schemas, and does not really realize parallel replication (such as the case of a master-slave architecture with only one schema or many). Of course, if your master-slave architecture has multiple schemas, 5.6 of concurrent replication is a great boost to performance.

MySQL5.7 parallel replication is a method of parallel replication based on group commit, 5.7 parallel replication, even if you have multiple things that operate on the same schema and can be played back in parallel from the library. In short, if there are multiple things that do not affect each other, the brush disk can be used as a group to submit the brush disk, we can even by manually set Binlog_group_commit_sync_delay from the library this parameter to control the log before the brush disk time to wait for the log submission, This improves the efficiency of replication from the library.

The plan seems perfect, but it's not without its drawbacks. The delay in the submission of a thing will ultimately affect the user experience on the application side. Of course, you can set the delay in a few milliseconds, but even that will affect the timeliness of the client.

MySQL8.0 Replication Performance Improvements

As of today (August 2017), MySQL 8.0 has recently released a beta version, originally developed for group replication (GR), but common replication also benefited from regular replication using GR at the bottom. The improvement we mentioned is that 8.0 has added some dependent trace information to binary log. In MySQL8.0, MySQL uses a method to store records of those rows that are affected by those things (called Writeset), and MySQL can compare the writeset information of different things, so that two things can be done on the same line of the same object, if not, you can and Line execution. This increases the level of replication compared to MySQL5.7. What you need to keep in mind is that, ultimately, the data you see from the library may appear in a different situation from the main library, which never happens in the main library. This happens because the order in which things are executed from the library may be inconsistent with the order in which the main library executes things. While this is not a problem, MySQL5.7 's parallel replication mechanism will also produce this problem unless you specify that the Slave-preserve-commit-order parameter is enabled.

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

• Commit_order: Default setting, default mechanism for MySQL5.7

• Writeset: It enables better parallelization, and the main library begins to store write Writeset information in the binary log

• Writeset_session: Setting things up from the library is done sequentially, which eliminates what we said from the library to see that the data might exist differently from the main library. Setting this value will reduce the performance of parallel replication, but there is a significant improvement in performance over the default setting.

Benchmark Test

In July, Vitor Oliveira wrote an article on mysqlhighavailability.com, trying to test performance in the new model of replication. In his tests, the best case was used-no data persistence to compare performance in the old and new replication modes. We decided to use the same method this time in a more near production setting: Use Log_slave_updates to enable binary logging. The persistence parameter is the default in MySQL8.0 (sync_binlog=1-this is the default in MySQL8.0, double write cache and InnoDB check are turned on), and Innodb_flush_log_at_trx_commit is set to 2.

We look at the configuration of the machine, the 32g,8 core (Slave_parallel_workers set to 8), the test tool is the Oltp_read_write.lua script for Sysbench, and 16 million rows in 32 tables are stored in 1000GB On GP2 volumes (that is, up to IOPS), we test and contrast performance in 1,2,4,8,16,32 concurrency, respectively, in all replication modes. The test process is as follows: Stop slave, execute 100,000 things, open slave and calculate the time to chase the main library from the library.  

First of all, we don't know what happens when we use 1 threads to execute a sysbench. Each test was performed five times after the heater was running. This particular configuration was tested two times-the result is stable: the single-threaded workload is the fastest. We will study further to understand exactly why.

In addition, 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 concurrent traffic.

What good is this for me?

The first benefit is obvious, and if you have a high database load and a delay from the library, you can boost replication performance by upgrading the main library to MySQL 8.0来. There are two aspects to note here: First, this feature is backwards compatible, even if the library is MySQL 5.7, performance can also be improved; second, MySQL8.0 does not have GA at the moment, so it is not recommended to use the beat version in the production library. MySQL8.0 the promotion of replication is not only a good solution to the problem from the library delay, in this case the master-slave delay is almost no, unless you re-add a new slave or reconfigure the slave may incur a delay. Using the "Writeset" mode makes it quicker to configure the new host.

All in all, this feature may have a greater impact than you expected, given that all benchmarks show the performance regression when MySQL handles low concurrency traffic, and any action that helps improve the efficiency of replication in this environment will be a huge step forward.

If you use cascading replication, this is also a feature you need to know. Any intermediate master node adds some serialized information to the way the transaction is processed and executed-but in the real case, the load on the intermediate node is smaller than the main library. Because he uses some write components to achieve better parallelization, which improves the parallel efficiency of itself and itself from the library. You can even increase the lower-level node from the library by upgrading the intermediate node's main library to MySQL8.0 (keep in mind that the MySQL 5.7 slave can recognize the Riteet data and use it, even if it cannot generate it itself). Of course, MySQL 8.0 to 5.7 master-slave replication sounds really tricky, not because MySQL8.0 has no GA. In some cases, of course, this can lead to a good increase in CPU usage from the library.

Other changes to MySQL replication

MySQL8.0 the main promotion for replication is the introduction of Writesets, but this is not the only change. Let's take a look at some other important improvements, if your main library is MySQL5.0 the following version, 8.0 will no longer support reading its binary log, so if you're using an older version of MySQL for traditional replication, it's time to upgrade your database version. To ensure the security and stability of replication, the default parameters have been modified: Master_info_repository and relay_log_info_repository default to Table,expire_log_days set to 30, In addition to Expire_log_days, a new parameter binlog_expire_log_seconds is added, which provides finer-grained control over Binlog's polling strategy. Additional timestamp information will be added to the Binlog information in order to better observe the latency of monitoring replication and reach a subtle level.

In summary, this is not a complete list of changes and features related to MySQL replication. You can get complete listing information here to learn all the improvements and enhancements to the replication feature.

As we know, MySQL replication is changing and getting better. As we begin to say, although this is a slow progress, we can already see the bright future of it. And we are happy to see that the underlying replication of group replication is also a function of conventional replication.

MySQL 8.0 Replication Performance Improvement (translation)