adg类似于mysql半同步机制-ag凯发k8国际
测试环境
: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] 发布于 职涯宝 ,未经作者许可,禁止转载,欢迎您分享文章
总结
以上是ag凯发k8国际为你收集整理的adg类似于mysql半同步机制_mysql基准测试异步复制和半同步复制延迟对比的全部内容,希望文章能够帮你解决所遇到的问题。
- 上一篇:
- 下一篇: winform利用委托传值到datagr