陳俊超-騰訊-微信后臺(tái)開發(fā)高級(jí)工程師-PhxSQL設(shè)計(jì)與實(shí)現(xiàn)

超?個(gè)人介紹?背景?思路?個(gè)人信息?個(gè)人經(jīng)歷計(jì)和開發(fā)?查看附近的人，搖一搖，朋友圈?微信群聊，關(guān)系鏈hxSQLeee?異步復(fù)制無法保證主備數(shù)據(jù)一致SemisyncSemisyncccommit?/bug.php?id=80395?hIps:///browse/MDEV-162r–Master重啟導(dǎo)致主備數(shù)據(jù)不一致–Master重啟導(dǎo)致幻讀–Master切換導(dǎo)致調(diào)用端分裂–缺乏自動(dòng)選主機(jī)制–要強(qiáng)一致，不做主備切換–借助MHA實(shí)現(xiàn)高可用，容忍主備數(shù)據(jù)不一致BinlogSvrBinlogSvrcommitcommitrrSlaveSlaveMySQMySQLPhxsyncDataRead/WriteDataReplica_onEleconQuery?請(qǐng)求透?jìng)?非Master節(jié)點(diǎn)收到的請(qǐng)求透?jìng)鹘oMaster節(jié)點(diǎn)?Master節(jié)點(diǎn)收到的請(qǐng)求直接透?jìng)鹘oMySQL?Master節(jié)點(diǎn)收到的請(qǐng)求透?jìng)鹘o非Master節(jié)點(diǎn)?非Master節(jié)點(diǎn)收到的請(qǐng)求直接透?jìng)鹘oMySQL?請(qǐng)求透?jìng)鱵SQL?完全兼容MySQLSQL–透?jìng)髟碔P兼容IP權(quán)限設(shè)置異步代碼的性能?項(xiàng)目地址–hIps:///tencent-wechat/libco過同一個(gè)連接通信被rollback?hIp:///doc/refman/5.6/en/innodb-lbackhtml?Proxy使用1:1連接模型能實(shí)現(xiàn)完全兼容–權(quán)限管理基于(用戶，源IP)對(duì)ketIPADHeadUsernane:xxxPasswordHeadUsernane:xxxPassword:xxxSrcIP:?重啟時(shí)校準(zhǔn)本地binlog?正常運(yùn)行時(shí)提交binlog到BinlogSvrrebinloginitbinlog–使用原有的acer_?ush接口提交binlog?hIp:///bug.php?id=83158?可靠日志存儲(chǔ)SQLMasterbinlog點(diǎn)LSlavebinlog?維護(hù)Master信息?Paxos協(xié)議–保證各節(jié)點(diǎn)的數(shù)據(jù)一致–保證集群機(jī)器超過一半存活還能服務(wù)?Binlog存儲(chǔ)–通過校驗(yàn)IP防止非Master提交–通過樂觀鎖防止拜占庭錯(cuò)誤?兼容MySQLSlave復(fù)制協(xié)議–BinlogSvr支持MySQL的原生復(fù)制協(xié)議?Master管理–選舉和續(xù)期s?項(xiàng)目地址–hIps:///tencent-wechat/phxpaxos，新之后jectLast=100,now=10AcceptLast=100,now=10AcceptId=1001Last=99,now=100Reject!binlogPhxBinlogSvrPhxBinlogSvrPhxBinlogSvrogterastermasterCheckMasterstatus:?IsMaster_meout??HasMySQLexecutedallbinlogs??IsProxyCheckMasterstatus:?IsMaster_meout??HasMySQLexecutedallbinlogs??IsProxyStartvo_ngCCheckMasterstatusYYCheckMasterstatusCheckMasterstatusCheckMasterstatus:?IsMySQLhealthy??IsProxyrunning?點(diǎn)的數(shù)據(jù)(MySQLbinlog,inlogsvrMySQLbinlog據(jù)的md5sumaxoschecksuminlogsvrchecksum數(shù)據(jù)來源：hIp:///2016/10/11/PhxSQL-FAQ?MySQL版本?Percona5.6.31-77.0?機(jī)器信息?CPU:Intel(R)Xeon(R)CPUE5-24200@1.90GHz*24?Memory:32G?Disk:SSDRaid10?PingCosts?Master->Slave:3~4ms?Client->Master:4ms?工具和參數(shù)?sysbench?--oltp-tables-count=10--oltp-table-size=1000000--num-maxrequests000--report-interval=1--max-time=200200線程并發(fā)調(diào)用-QPS對(duì)比圖PhxSQLMySQL4633447528507640555076405500線程并發(fā)調(diào)用-QPS對(duì)比圖PhxSQLMySQL10592812153582607072MySQLPhxSQLMySQL500線程并發(fā)調(diào)用-平均耗時(shí)對(duì)比圖PhxSQLMySQL192.93270.384.584.1760.4170.64.584.17?QQ郵箱(域名郵箱)域名記錄服務(wù)器–讀寫比為20：1?機(jī)器配置–intel(r)xeon(r)cpux3440@2.53ghz8core，8gram?性能優(yōu)于半同步?完全兼容MySQL?適配MySQL5.7人phxteam@Github地址：hIps:///tencent-wechat/phxsql?重啟時(shí)執(zhí)行半同步?重啟時(shí)直接刪除PendingBinlog?重啟時(shí)直接提交PendingBinlog?重啟時(shí)校準(zhǔn)binlog數(shù)M刪aster重啟，除Pending