adg类似于mysql半同步机制_MySQL基准测试异步复制和半同步复制延迟对比

测试环境

：MySQL 5.7.26 主从异步复制(GTID)

测试数据

：测试数据：10张表，每张表500万行数据，共12G的数据。

每次测试，主实例服务器测试脚本不变，线程为10，复制方式分别为异步复制和半同步复制。

sysbench测试脚本如下：

/usr/local/bin/sysbench /tmp/sysbench-master/src/lua/oltp_read_write.lua --mysql-user=sysbenchuser --mysql-password=sysbenchuser --mysql-port=3307 --mysql-host=localhost --mysql-socket=/mysql/mysql3307/mysqld.sock --mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=10 --report-interval=5 --time=600 run

sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:

Number of threads: 10

Report intermediate results every 5 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 5s ] thds: 10 tps: 793.99 qps: 15902.86 (r/w/o: 11136.10/3176.77/1589.99) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00

[ 10s ] thds: 10 tps: 893.86 qps: 17880.58 (r/w/o: 12518.03/3574.84/1787.72) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00

[ 15s ] thds: 10 tps: 735.60 qps: 14706.67 (r/w/o: 10292.25/2943.21/1471.21) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 10 tps: 921.55 qps: 18434.26 (r/w/o: 12904.94/3686.21/1843.11) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00

[ 25s ] thds: 10 tps: 651.78 qps: 13040.68 (r/w/o: 9126.18/2610.94/1303.57) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 10 tps: 589.21 qps: 11777.82 (r/w/o: 8246.96/2352.44/1178.42) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00

[ 35s ] thds: 10 tps: 588.18 qps: 11766.79 (r/w/o: 8236.31/2354.12/1176.36) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 10 tps: 530.26 qps: 10599.79 (r/w/o: 7419.84/2119.44/1060.52) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00

[ 45s ] thds: 10 tps: 435.76 qps: 8711.51 (r/w/o: 6096.97/1743.02/871.51) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 10 tps: 421.04 qps: 8437.49 (r/w/o: 5905.02/1690.38/842.09) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00

[ 55s ] thds: 10 tps: 422.33 qps: 8438.50 (r/w/o: 5906.89/1686.94/844.67) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 10 tps: 446.56 qps: 8933.20 (r/w/o: 6256.04/1784.04/893.12) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00

[ 65s ] thds: 10 tps: 462.46 qps: 9246.92 (r/w/o: 6469.78/1852.22/924.91) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 10 tps: 424.98 qps: 8501.88 (r/w/o: 5952.38/1699.54/849.97) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00

[ 75s ] thds: 10 tps: 433.95 qps: 8684.25 (r/w/o: 6079.53/1736.81/867.90) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 10 tps: 414.67 qps: 8284.98 (r/w/o: 5800.77/1654.88/829.34) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00

[ 85s ] thds: 10 tps: 492.01 qps: 9847.13 (r/w/o: 6892.49/1970.63/984.01) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 10 tps: 499.76 qps: 9984.33 (r/w/o: 6988.99/1995.83/999.51) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00

[ 95s ] thds: 10 tps: 470.19 qps: 9400.18 (r/w/o: 6578.65/1881.16/940.38) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 10 tps: 444.84 qps: 8917.39 (r/w/o: 6241.76/1785.96/889.68) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00

[ 105s ] thds: 10 tps: 438.43 qps: 8752.83 (r/w/o: 6127.84/1748.12/876.86) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 10 tps: 426.91 qps: 8533.64 (r/w/o: 5973.97/1705.85/853.82) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00

[ 115s ] thds: 10 tps: 437.33 qps: 8753.15 (r/w/o: 6129.19/1749.31/874.66) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 10 tps: 430.14 qps: 8594.11 (r/w/o: 6013.30/1720.54/860.27) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00

跑sysbench脚本的同时，在从库使用pt-heartbeat监控主从延迟。

pt-heartbeat h='172.16.254.163',u='pt_checksum',p='mysql',P=3308 -D test  --table=heartbeat --monitor --master-server-id=1633307

*******************************************************************

Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client

is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER

possibly with SSL_ca_file|SSL_ca_path for verification.

If you really don't want to verify the certificate and keep the

connection open to Man-In-The-Middle attacks please set

SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.

*******************************************************************

at /usr/local/bin/pt-heartbeat line 4556.

*******************************************************************

Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client

is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER

possibly with SSL_ca_file|SSL_ca_path for verification.

If you really don't want to verify the certificate and keep the

connection open to Man-In-The-Middle attacks please set

SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.

*******************************************************************

at /usr/local/bin/pt-heartbeat line 4556.

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

0.00s [  0.00s,  0.00s,  0.00s ]

1.00s [  0.02s,  0.00s,  0.00s ]

1.00s [  0.03s,  0.01s,  0.00s ]

1.00s [  0.05s,  0.01s,  0.00s ]

1.00s [  0.07s,  0.01s,  0.00s ]

1.00s [  0.08s,  0.02s,  0.01s ]

2.00s [  0.12s,  0.02s,  0.01s ]

2.00s [  0.15s,  0.03s,  0.01s ]

2.00s [  0.18s,  0.04s,  0.01s ]

3.00s [  0.23s,  0.05s,  0.02s ]

3.00s [  0.28s,  0.06s,  0.02s ]

4.00s [  0.35s,  0.07s,  0.02s ]

4.00s [  0.42s,  0.08s,  0.03s ]

4.00s [  0.48s,  0.10s,  0.03s ]

4.00s [  0.55s,  0.11s,  0.04s ]

4.00s [  0.62s,  0.12s,  0.04s ]

5.00s [  0.70s,  0.14s,  0.05s ]

5.00s [  0.78s,  0.16s,  0.05s ]

5.00s [  0.87s,  0.17s,  0.06s ]

6.00s [  0.97s,  0.19s,  0.06s ]

6.00s [  1.07s,  0.21s,  0.07s ]

7.00s [  1.18s,  0.24s,  0.08s ]

8.00s [  1.32s,  0.26s,  0.09s ]

8.00s [  1.45s,  0.29s,  0.10s ]

9.00s [  1.60s,  0.32s,  0.11s ]

10.00s [  1.77s,  0.35s,  0.12s ]

10.00s [  1.93s,  0.39s,  0.13s ]

11.00s [  2.12s,  0.42s,  0.14s ]

11.00s [  2.30s,  0.46s,  0.15s ]

12.00s [  2.50s,  0.50s,  0.17s ]

13.00s [  2.72s,  0.54s,  0.18s ]

13.00s [  2.93s,  0.59s,  0.20s ]

13.00s [  3.15s,  0.63s,  0.21s ]

14.00s [  3.38s,  0.68s,  0.23s ]

14.00s [  3.62s,  0.72s,  0.24s ]

15.00s [  3.87s,  0.77s,  0.26s ]

15.00s [  4.12s,  0.82s,  0.27s ]

16.00s [  4.38s,  0.88s,  0.29s ]

16.00s [  4.65s,  0.93s,  0.31s ]

17.00s [  4.93s,  0.99s,  0.33s ]

17.00s [  5.22s,  1.04s,  0.35s ]

18.00s [  5.52s,  1.10s,  0.37s ]

18.00s [  5.82s,  1.16s,  0.39s ]

19.00s [  6.13s,  1.23s,  0.41s ]

19.00s [  6.45s,  1.29s,  0.43s ]

20.00s [  6.78s,  1.36s,  0.45s ]

21.00s [  7.13s,  1.43s,  0.48s ]

21.00s [  7.48s,  1.50s,  0.50s ]

22.00s [  7.85s,  1.57s,  0.52s ]

22.00s [  8.22s,  1.64s,  0.55s ]

23.00s [  8.60s,  1.72s,  0.57s ]

24.00s [  9.00s,  1.80s,  0.60s ]

24.00s [  9.40s,  1.88s,  0.63s ]

25.00s [  9.82s,  1.96s,  0.65s ]

25.00s [ 10.23s,  2.05s,  0.68s ]

26.00s [ 10.67s,  2.13s,  0.71s ]

26.00s [ 11.10s,  2.22s,  0.74s ]

27.00s [ 11.55s,  2.31s,  0.77s ]

27.00s [ 12.00s,  2.40s,  0.80s ]

28.00s [ 12.47s,  2.49s,  0.83s ]

29.00s [ 12.95s,  2.59s,  0.86s ]

29.00s [ 13.42s,  2.69s,  0.90s ]

29.00s [ 13.88s,  2.78s,  0.93s ]

30.00s [ 14.37s,  2.88s,  0.96s ]

异步复制和半同步复制各测试120s，延迟对比如下图：

可以看到，在其他条件不变的情况下，半同步复制的延迟比异步复制高，异步复制的效率要优于半同步复制。

本文由 @狗老大[Vip] 发布于 职涯宝 ，未经作者许可，禁止转载，欢迎您分享文章