Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to carry out MySQL parallel replication testing, many people may not know much about it. In order to make you understand better, the editor summarized the following content for you. I hope you can get something from this article.

In fact, master-slave delay has always been a controversial topic. In the MySQL camp, if a certain delay is tolerated, it is a very good plan to achieve read-write separation through master-slave, but how acceptable is the delay rate, what is the effect of parallel replication in the new version, and whether there are any changes in different versions? can we find some reference data to support it? We can illustrate this point through some small tests.

First of all, in order to basically follow the same reference standard, we installed the 5.6 MySQL service on the same server and built the slave library on the other server.

The database version is 5.6.23 Percona branch, 5.7.17 MySQL official version

Pt tool is installed on the server to detect master-slave delay, and a new version of sysbench is installed to do pressure testing.

Main library: 10.127.128.227 RHEL6U3 32G R710

Slave library: 10.127.128.78 RHEL6U3 32G R710 in order to basically reach the same benchmark for testing, I first start the 5.6database service, the test is complete, start the 5.7service. Avoid the parallel interference of multiple instances.

The initialization data is similar to the following script, which is similar in version 5.6 and 5.7.

Ten tables were created, and then 5 million data was inserted to test.

Sysbench/ home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua-mysql-user=root-mysql-port=3308-mysql-socket=/home/mysql_5.7.17/mysql.sock-mysql-host=localhost-mysql-db=sysbenchtest-tables=10-table-size=5000000-threads=50 prepare

The pressure test uses the following sysbench script with a duration of 300 seconds sysbench/ home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua-- mysql-user=root-- mysql-port=3308-- mysql-socket=/home/mysql_5.7.17/mysql.sock-- mysql-host=localhost-- mysql-db=sysbenchtest-- tables=10-- table-size=5000000-- threads=50-- report-interval=5-- time=300 run to check the master-slave delay and complete it using pt-heartbeat.

Start the background task:

Pt-heartbeat hobbies 10.127.128.78 min upright delay checksummons ptents checksummpons ptchecks checksummpons 3307-D sysbenchtest-- create-table-- interval=1-- update-- replace-- daemonize enable master-slave delay detection:

Pt-heartbeat hobbies 10.127.128.78, master-server-id=3308, frames=5s, checksummpons, packs, checksummons, packs, 3308-D sysbenchtest-- table=heartbeat-- monitor-- master-server-id=3308-- frames=5s-- interval=5

Because there are still some differences between master-slave replication in 5.6 and 5.7, we test the differences and improvements between single-threaded and multithreaded replication.

Basic configuration of parallel replication

5.6 enable parallel replication

Mysql > stop slave

Mysql > set global slave_parallel_workers=8

Mysql > start slave

5.7 enable parallel replication

It is worth mentioning that some improvements have been made in slave-parallel-type= DATABASE / LOGICAL_CLOCK.

-- DATABASE-- parallel replication based on the library level is the same as 5.6

-- LOGICAL_CLOCK-- logical clock, how the master executes in parallel, and how it is played back in parallel from the top. So we turned on logical_clock.

Mysql > stop slave

Mysql > set global slave_parallel_type='LOGICAL_CLOCK'

Mysql > set global slave_parallel_workers=8

Mysql > stop slave

Comparison of the effects of parallel replication

The following is an overview of the result. The horizontal axis is the test time and the vertical axis is the delay time.

On the whole, the improvement of parallel replication efficiency in MySQL 5.6is not obvious, but the improvement in 5.7is very significant.

After reading the above, do you have any further understanding of how to do MySQL parallel replication testing? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.