未加星标

A Metric for Tuning Parallel Replication in MySQL 5.7

字体大小 | |
[系统(linux) 所属分类 系统(linux) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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 .


A Metric for Tuning Parallel Replication in MySQL 5.7

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操作系统

主题: SQLMySQL
分页:12
转载请注明
本文标题:A Metric for Tuning Parallel Replication in MySQL 5.7
本站链接:http://www.codesec.net/view/531382.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(linux) | 评论(0) | 阅读(87)