測試環(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)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。