阿里云數(shù)據(jù)庫RDS for MySQL只讀實(shí)例同步延遲原因與處理
只讀實(shí)例延遲介紹
RDS for MySQL只讀實(shí)例通常用于分擔(dān)主實(shí)例的查詢壓力，或者用于運(yùn)行OLAP類型的分析應(yīng)用，避免復(fù)雜統(tǒng)計(jì)查詢對(duì)主實(shí)例的性能影響。
由于RDS只讀實(shí)例采用MySQL原生的基于Binlog的復(fù)制技術(shù)（異步復(fù)制或半異步復(fù)制），必然會(huì)有延遲。延遲會(huì)導(dǎo)致只讀實(shí)例與主實(shí)例的數(shù)據(jù)出現(xiàn)不一致，進(jìn)而導(dǎo)致業(yè)務(wù)出現(xiàn)問題。另外延遲也有可能引起Binlog堆積，導(dǎo)致只讀實(shí)例空間被迅速消耗（如果主實(shí)例當(dāng)前正產(chǎn)生大量的Binlog），這種情況下有可能會(huì)使只讀實(shí)例被鎖定。
只讀實(shí)例產(chǎn)生延遲的原因及解決方案
?只讀實(shí)例規(guī)格過小
分析
這類延遲場景經(jīng)常出現(xiàn)在只讀實(shí)例規(guī)格和主實(shí)例規(guī)格相差較大，而且只讀實(shí)例上負(fù)載較重，比如只讀實(shí)例上IOPS打滿。
只讀實(shí)例的數(shù)據(jù)為了和主實(shí)例保持同步，采用了MySQL原生的binlog復(fù)制技術(shù)，由一個(gè)IO線程和一個(gè)SQL線程來完成。IO線程負(fù)責(zé)將主實(shí)例的binlog拉取到只讀實(shí)例，SQL線程負(fù)責(zé)將這些binlog日志應(yīng)用到只讀實(shí)例。這兩個(gè)線程會(huì)消耗只讀實(shí)例的IO資源，所以當(dāng)只讀實(shí)例的IOPS配置不夠的時(shí)候，會(huì)導(dǎo)致只讀實(shí)例的數(shù)據(jù)出現(xiàn)延遲。
解決方案
建議您升級(jí)只讀實(shí)例規(guī)格，避免由于只讀實(shí)例規(guī)格較小導(dǎo)致延遲。RDS推薦只讀實(shí)例的配置大于或者等于主實(shí)例的配置。
?主實(shí)例的TPS（Transaction Per Second）過高
分析
由于只讀實(shí)例與主實(shí)例同步采用的是單線程同步，而主實(shí)例的壓力是并發(fā)多線程寫入，這樣在主實(shí)例TPS過高的情況下容易出現(xiàn)只讀實(shí)例的數(shù)據(jù)延遲，可以通過觀察只讀實(shí)例的TPS與主實(shí)例的TPS性能數(shù)據(jù)來判斷。
解決方案
排查主實(shí)例的TPS是否正常，如果正常則需要對(duì)業(yè)務(wù)進(jìn)行優(yōu)化或者拆分，保證主實(shí)例的TPS不會(huì)導(dǎo)致只讀實(shí)例出現(xiàn)延遲。
?主實(shí)例的大事務(wù)
分析
主實(shí)例執(zhí)行一個(gè)涉及數(shù)據(jù)量非常大的update、delete、insert…select、replace…select等事務(wù)操作，生成大量的binlog數(shù)據(jù)傳送到只讀實(shí)例。只讀實(shí)例需要花費(fèi)與主實(shí)例相同的時(shí)間來完成該事務(wù)，進(jìn)而導(dǎo)致了只讀實(shí)例的同步延遲。例如在主實(shí)例上執(zhí)行一個(gè)持續(xù)80秒的刪除操作，只讀實(shí)例進(jìn)行相同操作時(shí)也需要花費(fèi)很長時(shí)間，這時(shí)就出現(xiàn)了延遲。
在只讀實(shí)例出現(xiàn)大事務(wù)導(dǎo)致延遲時(shí)，通過 show slave status \G命令，可以看到 Seconds Behind_Master不斷變化，而 Exec_Master_Log_Pos卻保持不變，這樣可以判斷只讀實(shí)例的SQL線程在執(zhí)行一個(gè)大的事務(wù)或者DDL操作。
?主實(shí)例執(zhí)行大事務(wù)
解決方案
建議將大事務(wù)拆分為小事務(wù)（例如在delete語句中增加where條件子句，限制每次刪除的數(shù)據(jù)量，將一次刪除操作拆分為多次數(shù)據(jù)量較小的刪除操作進(jìn)行），這樣只讀實(shí)例可以迅速的完成事務(wù)的執(zhí)行，不會(huì)造成數(shù)據(jù)的延遲。
?主實(shí)例的DDL語句執(zhí)行時(shí)間長
分析
?只讀實(shí)例和主實(shí)例數(shù)據(jù)同步是串行進(jìn)行的，如果DDL操作在主實(shí)例執(zhí)行時(shí)間很長，那么同樣在只讀實(shí)例也會(huì)消耗同樣的時(shí)間導(dǎo)致延遲。常見操作例如create index、repair table、alter table add column等。
?只讀實(shí)例上執(zhí)行的查詢或未完成的事務(wù)阻塞了來自主實(shí)例的DDL執(zhí)行。在只讀實(shí)例上執(zhí)行show processlist命令查看SQL線程的狀態(tài)為waiting for table metadata lock。
解決方案
?對(duì)于DDL直接引起的只讀實(shí)例延遲，建議在業(yè)務(wù)低峰期執(zhí)行這些DDL。
?對(duì)于來自主實(shí)例的DDL在只讀實(shí)例上被阻塞的情況，需要kill掉只讀實(shí)例上引起阻塞的會(huì)話來恢復(fù)只讀實(shí)例和主實(shí)例的數(shù)據(jù)同步，詳情請(qǐng)參見解決MDL鎖導(dǎo)致無法操作數(shù)據(jù)庫的問題。
總結(jié)
當(dāng)只讀實(shí)例出現(xiàn)延遲的時(shí)候，排查方向如下：
?控制臺(tái)查看監(jiān)控，檢查只讀實(shí)例的IOPS，確認(rèn)只讀實(shí)例是否存在資源瓶頸。
?控制臺(tái)查看監(jiān)控，檢查只讀實(shí)例的MySQL_COMDML，確認(rèn)主實(shí)例是否TPS過高。
?檢查只讀實(shí)例的binlog增長量，確定是否存在大事務(wù)。
?只讀實(shí)例執(zhí)行show slave status \G，確定是否有元數(shù)據(jù)鎖。
?控制臺(tái)查看SQL慢日志，是否有alter，repair，create等DDL操作。
?檢查只讀實(shí)例是否存在無主鍵表的刪除或者更新操作，可以通過在只讀實(shí)例執(zhí)行show engine innodb status\G查看，或者執(zhí)行show open tables后查看輸出結(jié)果的in_use列里值為1的表。