Mysql的高可用/容災(zāi)架構(gòu)的性能測(cè)試討論

本文主要給大家介紹Mysql的高可用/容災(zāi)架構(gòu)的性能測(cè)試討論，希望可以給大家補(bǔ)充和更新些知識(shí)，如有其它問題需要了解的可以持續(xù)在億速云行業(yè)資訊里面關(guān)注我的更新文章的。

系統(tǒng)高可用/容災(zāi)，是系統(tǒng)運(yùn)維人員非常關(guān)心的一個(gè)話題。

基于AWS云平臺(tái)的數(shù)據(jù)庫(kù)，我們能做哪些高可用的架構(gòu)設(shè)計(jì)呢？

目前中國(guó)區(qū)，有北京和寧夏兩個(gè)獨(dú)立的Region。關(guān)于RDS，不僅能做到Multi-AZ的設(shè)置，同時(shí)AWS還支持RDS的跨Region主從同步。

今天，我們就討論基于Mysql的高可用/容災(zāi)架構(gòu)的性能測(cè)試。

1. 創(chuàng)建Replica Slave

2. 這里我們可以選擇將Replica slave創(chuàng)建到“北京region”或者“寧夏region”。

3. 由于北京和寧夏的距離因素，大家都會(huì)關(guān)心北京Master和寧夏Replica的同步，會(huì)不會(huì)因?yàn)榫W(wǎng)絡(luò)延時(shí)，而導(dǎo)致lag較大？

今天我就進(jìn)行測(cè)試，看看網(wǎng)絡(luò)延遲，會(huì)對(duì)主從同步有多大影響。

（注： 北京region和寧夏region是沒有直接連通的，也就是說，客戶的EC2或者其他服務(wù)的跨Region數(shù)據(jù)同步，都只能通過公網(wǎng)，或者自己準(zhǔn)備的專線方式傳輸數(shù)據(jù)。但是AWS內(nèi)部，會(huì)為RDS的主從同步，以及S3 的 Cross Region Replication功能提供專用的線路，并且針對(duì)每個(gè)account提供一定的帶寬）

測(cè)試環(huán)境準(zhǔn)備，

<1. EC2   m4.xlarge   （4CPU 16G）
<2. RDS  db.r4.xlarge    （4CPU 16G ） Mysql版本5.7.12
<3. 創(chuàng)建兩個(gè)replica，一個(gè)在寧夏，一個(gè)在北京，主要是為了比較同步情況，確認(rèn)和區(qū)分網(wǎng)絡(luò)因素的差異
<4. Mysql創(chuàng)建20個(gè)表，每個(gè)表1000W行數(shù)據(jù)，數(shù)據(jù)量在40G左右
<5. 主庫(kù)使用sysbench進(jìn)行壓力測(cè)試 ,sysbench使用方法參考：
      https://blog.51cto.com/hsbxxl/2068181
<6. 主庫(kù)的參數(shù)修改
      binlog_format=row
<7. 從庫(kù)的下面參數(shù)設(shè)置，利用5.7的relay work的并發(fā)性能
      slave_parallel_workers=50

4.  sysbench腳本

通過定時(shí)任務(wù)，執(zhí)行sysbench腳本，每5分鐘執(zhí)行一次
# crontab -l
*/5 * * * * /root/test.sh
每次執(zhí)行150s，50個(gè)session并發(fā)讀寫20個(gè)表，每個(gè)表1000W行數(shù)據(jù)
# cat test.sh
date >> sync.log
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=bjs.cbchwkqr6lfg.rds.cn-north-1.amazonaws.com.cn --mysql-port=3306 \
--mysql-user=admin --mysql-password=admin123 --mysql-db=testdb2 --oltp-tables-count=20 \
--oltp-table-size=10000000 --report-interval=10 --rand-init=on --max-requests=0 \
--oltp-read-only=off --time=150 --threads=50 \
run >> sync.log

5. 在主庫(kù)上，sysbench測(cè)試數(shù)據(jù)輸出如下，可以根據(jù)輸出，確定主庫(kù)的讀寫數(shù)據(jù)，進(jìn)行參考：

[ 10s ] thds: 50 tps: 1072.24 qps: 21505.53 (r/w/o: 15060.08/4296.37/2149.08) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 759.83 qps: 15195.04 (r/w/o: 10640.78/3034.21/1520.05) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 631.10 qps: 12645.47 (r/w/o: 8853.55/2529.71/1262.21) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
......
[ 130s ] thds: 50 tps: 765.94 qps: 15295.77 (r/w/o: 10701.91/3062.27/1531.59) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 690.08 qps: 13776.96 (r/w/o: 9636.39/2761.41/1379.16) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 749.51 qps: 15033.64 (r/w/o: 10532.97/3000.45/1500.22) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read:                            1458030
write:                           416580
other:                           208290
total:                           2082900
transactions:                        104145 (689.50 per sec.)
queries:                             2082900 (13790.04 per sec.)
ignored errors:                      0      (0.00 per sec.)
reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          151.0422s
total number of events:              104145
Latency (ms):
min:                                    8.92
avg:                                   72.39
max:                                 7270.55
95th percentile:                      167.44
sum:                              7538949.20
Threads fairness:
events (avg/stddev):           2082.9000/18.44
execution time (avg/stddev):   150.7790/0.44

現(xiàn)在我們一起看一下，在北京和寧夏的兩個(gè)replica的同步情況

6. 指標(biāo)Replica Lag (Milliseconds)

可以看到，北京和寧夏的replica的同步性能基本是一致的，在每個(gè)150s的寫周期，都會(huì)有l(wèi)ag的上升，基本延遲在40～50s左右。這說明，網(wǎng)絡(luò)不是瓶頸，因?yàn)楸本┑膔eplica和Master在同一個(gè)AZ的同一個(gè)subnet。

北京

寧夏

7. 根據(jù)上圖結(jié)果可以分析，主從的延遲，在Mysql自身replay的過程沒有跟上導(dǎo)致的延遲。

那么，我將兩個(gè)replica的RDS機(jī)型升級(jí)到 8CPU 32G，進(jìn)行測(cè)試，并比較結(jié)果。

可以看到，升級(jí)機(jī)型，還是有幫助的，lag明顯下降不少，將延遲維持在20～30S

北京

寧夏

也保持和北京相同的lag，更說明了，網(wǎng)絡(luò)不是同步的瓶頸。Mysql自身replay log，是一個(gè)性能瓶頸。

8. 再觀察CPU的使用率

由于兩個(gè)replica只有同步數(shù)據(jù)的負(fù)載，所以可以看到，同步數(shù)據(jù)的性能消耗，只有15%

后面的曲線是在我升級(jí)到8CPU 32G之后，只有7.5%的負(fù)載。說明硬件資源是足夠的，但是Mysql自身的原因?qū)е峦絣ag。

北京

寧夏

9. Write IOPS

穩(wěn)定在4000左右，我實(shí)際分配的IOPS是6000，所以磁盤也不是瓶頸。另外，主庫(kù)能完成讀寫操作，理論上從庫(kù)只有寫操作，負(fù)載更低。物理硬件資源不會(huì)是限制。

北京

寧夏

10. 主庫(kù)的性能指標(biāo),可以看到，同樣的配置，主庫(kù)的資源還是夠用的。能滿足讀和寫的需求。

CPU性能指標(biāo)

11. Write IOPS性能指標(biāo)

12. Read IOPS性能指標(biāo)

13. Write throughput性能指標(biāo)

14. 既然數(shù)據(jù)庫(kù)主機(jī)資源，和網(wǎng)絡(luò)不是瓶頸。那么Mysql自身的問題，就需要我們?nèi)ブ鸩秸{(diào)整了。

接下來，針對(duì)mysql的參數(shù)進(jìn)行調(diào)整優(yōu)化。

說是優(yōu)化，是盡量降低備庫(kù)相關(guān)參數(shù)的影響，將IO相關(guān)的參數(shù)基本全部關(guān)閉，去跟隨OS層面的機(jī)制進(jìn)行落盤。

實(shí)際上，replica的數(shù)據(jù)安全性要求相對(duì)主庫(kù)要小很多，所以，在從庫(kù)同步速度較慢的時(shí)候，結(jié)合實(shí)際情況，調(diào)整下面參數(shù)，會(huì)有顯著的效果。

innodb_flush_log_at_trx_commit=0
innodb_flush_neighbors=0
innodb_flush_sync=0
sync_binlog=0
sync_relay_log=0
slave_parallel_workers=50
master-info-repository  = TABLE
relay-log-info-repository = TABLE

15. 調(diào)優(yōu)之后的結(jié)果

寧夏

在調(diào)整完參數(shù)之后，效果立竿見影，從40s的延遲，降低到 5s左右

16. 再觀察一下北京區(qū)的replica（未調(diào)整Mysql參數(shù)）

依然還是維持之前的lag，保持在50s的延遲情況。

北京

17. 最后再調(diào)整一個(gè)參數(shù)，讓worker并行.默認(rèn)情況下，這個(gè)參數(shù)是database，也就是只有在多個(gè)database被修改的情況下，worker才會(huì)并行工作。而實(shí)際情況下，大部分客戶都是一個(gè)database（schema）多個(gè)tables的情況。所以，將參數(shù)修改成LOGICAL_CLOCK，才能起到并行的作用。

slave_parallel_type＝LOGICAL_CLOCK

當(dāng)worker并行之后，已經(jīng)能夠?qū)⒅鲝难舆t降低到2S以內(nèi)了，基本能滿足絕大部分業(yè)務(wù)場(chǎng)景了。

18. 測(cè)試總結(jié)

基于以下條件

硬件：4CPU 16G內(nèi)存  6000IOPS磁盤

50個(gè)并發(fā)，每秒700個(gè)TPS，1.5W個(gè)QPS，1W個(gè)寫，的情況下。

北京到寧夏的Mysql主從，能控制在10S以內(nèi)的延遲。

如果對(duì)于replica的數(shù)據(jù)安全性要求比較高，即不調(diào)整Mysql落盤的參數(shù)情況下，延遲在40～50s范圍。

根據(jù)實(shí)際情況，大家可以通過調(diào)整mysql參數(shù)，加大replica機(jī)型的方式，來獲取更好的同步性能。

Mysql作為一個(gè)開源RDBMS，雖然在Oracle的技術(shù)支持下，有了很大的進(jìn)步。但是replica的種種性能，和Oracle的Dataguard的同步相比，還是有很大的差距。

后續(xù)我會(huì)繼續(xù)研究，如何能讓Mysql同步的更快。

19. 關(guān)于Mysql主從同步的調(diào)優(yōu)：

<1. MySQL數(shù)據(jù)庫(kù)主從同步延遲原理。

答：談到MySQL數(shù)據(jù)庫(kù)主從同步延遲原理，得從mysql的數(shù)據(jù)庫(kù)主從復(fù)制原理說起，mysql的主從復(fù)制都是單線程的操作，主庫(kù)對(duì)所有DDL和 DML產(chǎn)生binlog，binlog是順序?qū)?，所以效率很高，slave的Slave_IO_Running線程到主庫(kù)取日志，效率很比較高，下一步， 問題來了，slave的Slave_SQL_Running線程將主庫(kù)的DDL和DML操作在slave實(shí)施。DML和DDL的IO操作是隨即的，不是順 序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭(zhēng)用，由于Slave_SQL_Running也是單線程的，所以一個(gè)DDL卡主了，需要 執(zhí)行10分鐘，那么所有之后的DDL會(huì)等待這個(gè)DDL執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。有朋友會(huì)問：“主庫(kù)上那個(gè)相同的DDL也需要執(zhí)行10分，為什 么slave會(huì)延時(shí)？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。

<2. MySQL數(shù)據(jù)庫(kù)主從同步延遲是怎么產(chǎn)生的。

答：當(dāng)主庫(kù)的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過slave一個(gè)sql線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待。

<3. MySQL數(shù)據(jù)庫(kù)主從同步延遲解決方案

答：最簡(jiǎn)單的減少slave同步延時(shí)的方案就是在架構(gòu)上做優(yōu)化，盡量讓主庫(kù)的DDL快速執(zhí)行。還有就是主庫(kù)是寫，對(duì)數(shù)據(jù)安全性較高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設(shè)置，而slave則不需要這么高的數(shù)據(jù)安全，完全可以講sync_binlog設(shè)置為0或者關(guān)閉binlog，innodb_flushlog也 可以設(shè)置為0來提高sql的執(zhí)行效率。另外就是使用比主庫(kù)更好的硬件設(shè)備作為slave。

看了以上關(guān)于Mysql的高可用/容災(zāi)架構(gòu)的性能測(cè)試討論，希望能給大家在實(shí)際運(yùn)用中帶來一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問題的。