A Metric for Tuning Parallel Replication in MySQL 5.7
mysql 5.7 introduced the LOGICAL_CLOCK type of multi-threaded slave ( MTS ). When using this type of parallel replication (and when slave_parallel_workers is greater than zero), slaves use information from the binary logs (written by the master) to run transactions in parallel. However, enabling parallel replication on slaves might not be enough to get a higher replication throughput (VividCortex blogged about such a situation recently in Solving MySQL Replication Lag with LOGICAL_CLOCK and Calibrated Delay ). To get a faster slave with parallel replication, some tuning is needed on the master.
I already wrote/spoke many times about this tuning (when presenting slave group commit on the Booking.com blog , when speaking about MariaDB 10.0 parallel replication , and when speaking about MySQL 5.7 parallel replication ). I call this slowing down the master to speed-up the slave . This consists of delaying commit in the hope of identifying more parallelism on the master and in the hope that the slaves will replicate faster. This has visible side effects on clients by increasing commit latency, which could be a problem if a session is in a tight commit loop (the commit throughput will drop) or in other situations where a client relies on commit being quick. So this tuning should be done with caution and its impact should be monitored carefully.In MariaDB Server 10+
When doing the "slowing down the master to speed-up the slave" tuning in MariaDB Server10+ (10.0, 10.1 and 10.2), the binlog_commit_wait_count and binlog_commit_wait_usec global variables are used. By default, the delay is 100 milliseconds ( binlog_commit_wait_usec=100000 ), but delaying commit is disabled ( binlog_commit_wait_count=0 ). To enable delaying commit, binlog_commit_wait_count should be increased to a positive value.
When tuning those parameters, the binlog_commits and binlog_group_commits global statuses should be monitored and from those, an average group commit size can be computed. This average group commit size is useful to get a sense the tuning quality. While increasing binlog_commit_wait_usec :if the group commit size grows : the tuning has a good change of giving better parallel replication speed (I write "a good chance" here as some situation, like large transactions , could slowdown parallel replication and this is not shown by this metric). if the group commit size stays constant : the tuning should probably be reverted because the parallelism sent to slave does not increase and commit latency probably grows. if the commit rate drops noticeably : the tuning might have an undesirable effect on the application as throughput is reduced, and we should be extra-careful in moving forward.
Below is an example of this tuning where the commit rate clearly drops (slowing down the master) and the group commit size increases noticeably (speeding up the slave). The value of binlog_commit_wait_usec is the default (100000). In this particular case, after more investigations and because I needed a maximum slave throughput, I went even went further in to obtain a much higher replication throughput .
Also, not only the group commit size allows to measure the tuning quality and to quantify the amount of parallelism identified by the master and sent to slaves, it allows to size the value of slave_parallel_threads to use on the slave. In my opinion, the average group commit size is an invaluable metric to have to monitor and optimize performance.
So in MariaDB Server, we have all instrumentation to perform an efficient and safe tuning of parallel replication on the master and on the slaves. What about MySQL 5.7 ?In MySQL 5.7
When doing the "slowing down the master to speed-up the slave" tuning in MySQL 5.7, the binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count global variables are used. By default, both values are set to zero, which disable delaying commit. To enable this, binlog_group_commit_sync_delay should be set to a positive value. Note the difference between MySQL and MariaDB here: binlog_commit_wait_count=0 disable delay in MariaDB and binlog_group_commit_sync_no_delay_count=0 simply put no upper bound on group commit size in MySQL.
However, because MySQL 5.7 does not rely on group commit to identify parallelism, there is no corresponding global statuses allowing to measure the tuning quality. This increases the complexity of this already challenging tuning. At this point, the only way to measure the tuning efficiency is to look at the slave throughput. But if slaves are lagging when doing the tuning, we need to wait until the optimized transactions reach the slave and then measure if the slave is actually faster. This is not very practical.The Wish for a MySQL 5.7 MTS Tuning Metric I explain above how the average group commit size can be used as a metric to tune parallel replication in MariaDB Server. I wanted to find
本文系统（linux）相关术语:linux系统 鸟哥的linux私房菜 linux命令大全 linux操作系统
本文标题：A Metric for Tuning Parallel Replication in MySQL 5.7