测试环境 :MySQL 5.7.26 主从异步复制(GTID)
测试数据:测试数据:10张表,每张表500万行数据,共12G的数据。
每次测试,主实例服务器测试脚本不变,线程为30。分别将从库复制线程设置为1,6,12,18,24,30,36。
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=30 --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: 30
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 30 tps: 844.34 qps: 16977.36 (r/w/o: 11892.53/3390.15/1694.68) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 30 tps: 612.09 qps: 12214.15 (r/w/o: 8552.03/2437.95/1224.17) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 30 tps: 601.93 qps: 12067.79 (r/w/o: 8448.21/2415.72/1203.86) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 30 tps: 609.61 qps: 12177.33 (r/w/o: 8524.49/2433.63/1219.21) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 30 tps: 536.99 qps: 10732.24 (r/w/o: 7508.69/2149.57/1073.98) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 30 tps: 536.20 qps: 10714.24 (r/w/o: 7499.83/2142.01/1072.40) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 30 tps: 551.20 qps: 11032.47 (r/w/o: 7727.45/2202.61/1102.41) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 30 tps: 495.75 qps: 9939.28 (r/w/o: 6956.16/1991.62/991.51) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 30 tps: 505.15 qps: 10079.89 (r/w/o: 7054.76/2014.82/1010.31) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 30 tps: 455.59 qps: 9098.04 (r/w/o: 6368.29/1818.57/911.18) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 30 tps: 498.80 qps: 9991.43 (r/w/o: 6994.62/1999.21/997.60) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 30 tps: 475.94 qps: 9523.93 (r/w/o: 6668.51/1903.55/951.87) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 30 tps: 472.86 qps: 9444.96 (r/w/o: 6611.61/1887.63/945.72) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 30 tps: 475.30 qps: 9496.58 (r/w/o: 6645.58/1900.40/950.60) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 30 tps: 418.00 qps: 8393.36 (r/w/o: 5876.97/1680.39/836.00) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 30 tps: 459.69 qps: 9172.21 (r/w/o: 6421.67/1831.16/919.38) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 30 tps: 441.30 qps: 8825.76 (r/w/o: 6177.17/1765.99/882.60) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 30 tps: 418.10 qps: 8390.66 (r/w/o: 5869.84/1684.61/836.21) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 30 tps: 431.89 qps: 8615.47 (r/w/o: 6033.91/1717.77/863.79) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 30 tps: 402.90 qps: 8054.69 (r/w/o: 5638.26/1610.62/805.81) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 30 tps: 429.89 qps: 8608.32 (r/w/o: 6025.61/1722.94/859.77) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 30 tps: 393.78 qps: 7878.96 (r/w/o: 5511.89/1579.51/787.56) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 30 tps: 403.78 qps: 8087.73 (r/w/o: 5665.07/1615.11/807.55) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 30 tps: 452.25 qps: 9025.71 (r/w/o: 6317.44/1803.78/904.49) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 125s ] thds: 30 tps: 418.32 qps: 8359.89 (r/w/o: 5852.54/1670.70/836.65) lat (ms,95%): 186.54 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.
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 ]
0.99s [ 0.05s, 0.01s, 0.00s ]
1.99s [ 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 ]
2.99s [ 0.28s, 0.06s, 0.02s ]
3.00s [ 0.33s, 0.07s, 0.02s ]
4.00s [ 0.40s, 0.08s, 0.03s ]
4.00s [ 0.47s, 0.09s, 0.03s ]
4.00s [ 0.53s, 0.11s, 0.04s ]
5.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 ]
6.99s [ 1.30s, 0.26s, 0.09s ]
7.00s [ 1.42s, 0.28s, 0.09s ]
6.98s [ 1.53s, 0.31s, 0.10s ]
7.98s [ 1.67s, 0.33s, 0.11s ]
8.00s [ 1.80s, 0.36s, 0.12s ]
9.00s [ 1.95s, 0.39s, 0.13s ]
9.00s [ 2.10s, 0.42s, 0.14s ]
10.00s [ 2.27s, 0.45s, 0.15s ]
10.00s [ 2.43s, 0.49s, 0.16s ]
10.00s [ 2.60s, 0.52s, 0.17s ]
11.00s [ 2.78s, 0.56s, 0.19s ]
11.00s [ 2.96s, 0.59s, 0.20s ]
12.00s [ 3.16s, 0.63s, 0.21s ]
12.00s [ 3.36s, 0.67s, 0.22s ]
多线程复制与延迟的图像如下:
![](https://i-blog.csdnimg.cn/blog_migrate/973a383c94e1658eb3ca2601d39843bc.png)
可以看到,此次测试中,当从库复制线程为12,18,24,30,36时候,延迟差异不大,说明复制效率提升不高。而延迟越来越高,说明当前环境,从库消费不掉主库30个线程同步来的binlog。
此次测试也提醒我们,对于应用而言,并不是线程数越多越好。