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