Technology Sharing | MySQL master-slave replication binlog transmission compression

Author: Wang Xiang

A member of the Aikesheng DBA team, responsible for the operation and maintenance of the company's DMP products and the handling of customer MySQL issues. Good at database fault handling. Have a strong interest in database technology and python.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

Contents of this article:

Preface

How to enable binlog compression

The compression effect of binlog compression

• Create a compressed table to fill in data

• Disabled

  • Main library monitoring
  • Monitoring from library

• Open state

  • Main library monitoring
  • Monitoring from library

in conclusion

Precautions

Preface

Now when using MySQL, the binlog format is based on ROW, and the size of the binlog file has always been a relatively large issue. It will consume a certain amount of network resources when transmitting to the slave library. If there is a big transaction to copy 10 G from a binlog to the slave library, it will almost make the main library network tired. Below we introduce a parameter to enable binlog compression to alleviate this problem!

How to enable binlog compression

The slave_compressed_protocol parameter is used to control whether MySQL master-slave replication uses a compression protocol. Enabling binlog replication compression is helpful to alleviate the network bandwidth problem caused by the large amount of binlog data.

# 主库 & 从库
set global slave_compressed_protocol = ON ;

stop slave io_thread;start slave io_thread;
# 开启or关闭 slave_compressed_protocol参数，需重启复制，让该参数生效。

The compression effect of binlog compression

Does compression work? Don't talk nonsense directly to see the results.

Create a compressed table to fill in data

socket='/data/mysql/data/7777/mysqld.sock'
user='root'
password='123'
db='lubao7hao'
table_num='10'
table_size='500000'
thread_num='128'

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-
socket=${socket} --mysql-user=${user} --mysql-password=${password} --mysql-
db=${db} --oltp-tables-count=${table_num} --oltp-table-size=${table_size} --
report-interval=10 --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --
oltp-nontrx-mode=select --oltp-read-only=off --max-time=360000 --num-
threads=${thread_num} prepare

Disabled

Main library monitoring

Monitoring from library

Open state

Main library monitoring

Monitoring from library

in conclusion

	Main library cpu	From library cpu	Main library send	Main library recv	Send from library	From library recv
Uncompressed	4.24%	2.7%	14.50MB/s	7.31MB	35KB/s	14.60MB/s
compression	8.50%	3.18%	6.68MB/s	7.27MB/s	35KB/s	6.610MB/s

1. Turn on the slave_compressed_protocol parameter, the compression effect is obvious, the network data transmission volume is about half of the unopened, and the compression rate is 50%.

2. Turning on the slave_compressed_protocol parameter will consume CPU resources to a certain extent; if the CPU load is already high, it is not recommended to turn on compression.

Precautions

When MySQL is below 5.7.21, or below 8.0.4, when slave_compressed_protocol=ON is used with semi-synchronous replication, a bug will be triggered and the master database will be stuck in writing. I won’t say much here, so as not to deviate too much from the subject, see the address: https://bugs.mysql.com/bug.php?id=86230