adg类似于mysql半同步机制_MySQL基准测试异步复制和半同步复制延迟对比
測試環(huán)境
:MySQL 5.7.26 主從異步復制(GTID)
測試數(shù)據(jù)
:測試數(shù)據(jù):10張表,每張表500萬行數(shù)據(jù),共12G的數(shù)據(jù)。
每次測試,主實例服務器測試腳本不變,線程為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監(jiān)控主從延遲。
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,延遲對比如下圖:
可以看到,在其他條件不變的情況下,半同步復制的延遲比異步復制高,異步復制的效率要優(yōu)于半同步復制。
本文由 @狗老大[Vip] 發(fā)布于 職涯寶 ,未經(jīng)作者許可,禁止轉(zhuǎn)載,歡迎您分享文章
總結(jié)
以上是生活随笔為你收集整理的adg类似于mysql半同步机制_MySQL基准测试异步复制和半同步复制延迟对比的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: mysql复制文件迁移后看不到表_mys
- 下一篇: winform利用委托传值到datagr