binlog_commit_wait_count和binlog_commit_wait_usec两个参数是MariaDB 10.0开始引入的新参数,根据官方文档介绍:
binlog_commit_wait_count
- Description: For use in parallel replication. The binary log can delay the commit step of a transaction until the given number of transactions are all ready to commit together (group commit). The delay will however not be longer than the value set by binlog_commit_wait_usec. The default value of 0 means that no delay is introduced. Setting this value can reduce I/O on the binary log and give an increased opportunity for parallel apply on the slave, but too high a value will decrease the commit throughput. By monitoring the status variable binlog_group_commit_trigger_count (>=MariaDB 10.1.5) it is possible to see how often this is occurring.
- Starting with MariaDB 10.0.18 and MariaDB 10.1.4: If the server detects that one of the committing transactions T1 holds an InnoDB/XtraDB row lock that another transaction T2 is waiting for, then the commit will complete immediately without further delay. This helps avoid losing throughput when many transactions need conflicting locks. This often makes it safe to use this option without losing throughput on a slave with parallel replication, provided the value of slave_parallel_threads is sufficiently high.
- Commandline: --binlog-commit-wait-count=#]
- Scope: Global
- Dynamic: Yes
- Data Type: numeric
- Default Value: 0
- Range: 0 - 18446744073709551615
- Introduced: MariaDB 10.0.5
binlog_commit_wait_usec
- Description: For use in parallel replication. The binary log can delay the commit step of a transaction by this many microseconds. By monitoring the status variable binlog_group_commit_trigger_timeout (>=MariaDB 10.1.5) it is possible to see how often group commits are made due to binlog_commit_wait_usec. As soon as the number of pending commits reaches binlog_commit_wait_count, the wait will be terminated, though. Thus, this setting only takes effect if binlog_commit_wait_count is non-zero.
- Commandline: --binlog-commit-wait-usec#
- Scope: Global
- Dynamic: Yes
- Data Type: numeric
- Default Value: 100000
- Range: 0 - 18446744073709551615
- Introduced: MariaDB 10.0.5
这两个参数用于并行复制,在并行复制中做一定的延迟,进而使用到组提交这一特性,我的理解是为了提升主从复制的性能,而不影响独立运行的服务器,而实际情况是什么,我们进行如下测试:
测试场景一(binlog_commit_wait_count = 10,binlog_commit_wait_usec系统默认):
binlog_commit_wait_count = 10
binlog_commit_wait_usec = 6000
10000条数据插入:
# time mysql sbtest < sbtest.sql
real 1m6.715s
user 0m0.280s
sys 0m0.694s
# time mysql sbtest < sbtest.sql
real 1m6.700s
user 0m0.290s
sys 0m0.656s
测试场景二(binlog_commit_wait_usec设置小于系统默认值):
binlog_commit_wait_count = 0
binlog_commit_wait_usec = 6000
10000条数据插入:
# time mysql sbtest < sbtest.sql
real 0m2.211s
user 0m0.178s
sys 0m0.143s
# time mysql sbtest < sbtest.sql
real 0m2.280s
user 0m0.236s
sys 0m0.188s
测试场景三(binlog_commit_wait_usec = 0):
binlog_commit_wait_count = 0
binlog_commit_wait_usec = 0
10000条数据插入:
# time mysql sbtest < sbtest.sql
real 0m5.266s
user 0m0.277s
sys 0m0.261s
# time mysql sbtest < sbtest.sql
real 0m5.852s
user 0m0.354s
sys 0m0.312s
测试场景四(系统默认):
binlog_commit_wait_count = 0
binlog_commit_wait_usec = 100000
10000条数据插入:
# time mysql sbtest < sbtest.sql
real 0m2.598s
user 0m0.373s
sys 0m0.238s
# time mysql sbtest < sbtest.sql
real 0m2.542s
user 0m0.297s
sys 0m0.231s
测试场景五(binlog_commit_wait_usec设置小于系统默认值):
binlog_commit_wait_count = 0
binlog_commit_wait_usec = 200000
10000条数据插入:
# time mysql sbtest < sbtest.sql
real 0m2.398s
user 0m0.195s
sys 0m0.194s
# time mysql sbtest < sbtest.sql
real 0m2.235s
user 0m0.178s
sys 0m0.163s
结论:
根据官方文档介绍,这两个参数应该只影响主从复制的服务器架构,但实际使用对独立运行的服务器也有很大的影响,使用binlog_commit_wait_count时单表单线程10000条数据插入耗时1分6秒,不是用该参数时单表单线程10000条数据插入耗时不足3秒。同时关闭binlog_commit_wait_usec时单表单线程10000条数据插入耗时5秒多。binlog_commit_wait_usec取6000/默认值/2倍默认值差别不大,所以建议使用默认参数即可。