如何解決主從資料庫同步延遲問題？

如何解決主庫插入記錄後，無法從從庫中及時讀取的問題,如何從架構上避免這種問題
在網上見過建立一個版本庫的表，然後利用mysql proxy判斷資料是否是最新的，然後路由到主庫或者是從庫，請問這個方案是可行的嗎？具體如何操作？

回複內容：

從你描述的情境來看，你需要在主機寫入之後，保證在備機一定能夠讀取到已經寫入的資料，也就是說，你需要主從架構下的強一致性。

主機與備機之間的物理延遲是不可控的，也是無法避免的。但是如果僅僅需要滿足這種強一致性，是相對簡單的事：只需要在主機寫入時，確認更新已經同步到備機之後，再返回寫操作成功即可。主流資料庫均支援這種完全的同步模式。已經有人提到MySQL的Semi-sync功能（從MySQL5.6開始官方支援，此前的版本可以考慮Google出的非官方補丁），就是基於這種原理。

不過，一般不建議使用這種同步模式。顯而易見，如果寫操作必須等待更新同步完成，肯定會極大地影響效能，除非你不在乎效能。

問題的關鍵在於，主從架構是一種用於資料容錯的高可用性解決方案，而不是一種處理高並發壓力的解決方案。它的目的是主機當機以後備機可以馬上頂上，而不是讓備機來分擔並發壓力。完全同步機制也只是用於保證主機當機以後資料不會丟失，而不是保證從備機讀取資料時的一致性。因此，我根本也不主張你使用從備機讀取資料以分擔並發壓力這種方式。

解決方式是，不要試圖在資料庫層解決並發的讀操作問題，至少不要在主從架構的資料庫層解決。要在資料庫層之上架構一個redis這樣的分布式緩衝來解決，它是專門幹這個的。其效能肯定遠高於從備機讀取資料。

分布式緩衝也存在著一些限制，例如不能完全支援交易處理。這取決於你的應用情境。對於一般的互連網應用，並發壓力大但不要求支援事務，可以考慮分布式緩衝。對於銀行這樣嚴格要求強一致性的應用，對於寫入延遲一般沒什麼要求（延遲幾個小時都可以接受，資料不出錯就行），可以適用完全同步的模式。

另外，不建議你使用“通過版本庫判斷最新版本再分別路由到主機或備機”的山寨版解決方案。這會對應用程式層的代碼造成嚴重汙染。

MySQL replication
我的經驗裡，最重要的是盡量避免讓任何一台mysql伺服器跑滿。
所以真正的解決方案是精準的capacity planning，適時地scale out。盡量不依賴主從同步，比如多主方案。主從同步延遲是必然現象，不是問題。關鍵看具體業務，因同步延遲帶來什麼問題，然後再解決。

舉個簡單的例子

假設某論壇是主從資料庫，我發一個文章後立即重新整理頁面，因為顯示文章是讀，這個時候如果延遲比較厲害，就會提示 404 -———文章不存在，這就有問題了；我們還要假設使用者的容忍度是看見自己的新內容，別人新的內容可以有延遲（實際上延遲是很小的時間單位）。

針對這個假設的問題，可以採取幾種方案：
1、有更新資料後的 讀取相關資料動作，都從預設到主庫；
2、利用緩衝；插入新的資料，會有last_id返回，組裝成資料，緩衝到前端。讀取此 id 資料時，先從緩衝取。題主說的方案感覺非常不靠譜。
不過mysql-proxy本人也幾乎沒怎麼接觸，它能否實現上訴功能有些不大確定，即使它有，也不建議為了這個就用它，官網自己都不推薦用到生產環境。

針對主從延遲，本人的經驗如下：

• 業務量不大的

主庫能處理業務就全放在主庫吧，從庫只做災備，備份，對即時性要求不高的統計報表類工作；

• 已經出現延遲的

一般來說，就慢慢等吧，試圖通過重啟db之類的操作是無法解決的，還會因為大交易回復再重做導致花的時間更長。

• 延遲N天無法解決的

那就重做slave。
為什麼會延遲N天，難道僅僅是因為從庫單線程嗎？
我感覺大部分都是主庫上採用mixed的binlog_format，由於某種限制，無法基於statement，只好row模式複製。
那麼如果當前sql是全表掃描，傳到slave上執行時就是茫茫多次的全表掃描了。
一般來說在slave上show proceslist看查看當前的system user正在執行什麼，那就是問題SQL。如果pos點一直不動，也可以去主庫對應的binlog上查看下執行的是什麼玩意。

• 出現延遲時，查看下當前slave的cpu和磁碟狀況

一般來說如果從庫沒有其他業務，單線程的原因，cpu跑滿一個核已經是極限了。磁碟io滿的話，確認下是否有其他進程或mysql線程影響了它(比如從庫正在dump或者超大的sql在執行)，也可以嘗試調整下slave上關於io的幾個參數

• 從庫raid卡，務必設定成write back的寫策略
  

這點本人深受其害，查了幾個月才發現為什麼我的SSD io效能這麼爛。

• 批量的dml操作
  

批量的dml操作如果不做處理，一般必然會出現延遲，建議業務低峰期執行，並將大量操作做下調整，一次dml 10000行，sleep一會，再dml 10000行。
具體的行數和sleep需要自己根據業務確定，能保證從庫不延遲就好。

• 一點別的tips：
  

1. 如果還是經常性的短時間延遲，那就嘗試加大從庫的硬體設定，比如上sata SSD，pcie等
   
2. 延遲的監控到位，可通過pt-heart-beat來準確監控延遲值，及時發現查看。
   
3. 5.5以後版本的，可以考慮採用半同步複製，能解決少量延遲引起的問題，不過對tps效能損耗較大
   
4. 升級到mysql 5.7吧，多線程複製，幾乎完美解決單線程複製引起的從庫延遲。

完全同步是一個非常昂貴和複雜的操作，負載量大的話幾乎不可能完成。所以聰明的辦法是調整上層的邏輯，避免這種需求。可以採用Redis技術， 新浪微博就大量使用了Redis技術。 區別於Memcached的是，redis會周期性的把更新的資料寫入磁碟或者把修改操作寫入追加的記錄檔案，並且在此基礎上實現了主從同步。這需要假設Redis伺服器了。具體請看 新浪網首席DBA楊海朝的視頻和PPT http://blog.nosqlfan.com/html/2692.html 。中間加個nosql層

可以試一試 採用主動複製的技術來解決MySQL主從之間複製的延遲問題，比如Twitter還專門開發了用於複製和分區的中介軟體gizzard(twitter/gizzard · GitHub ) 。
主動複製雖然解決了被動複製的延遲問題，但也帶來了新的問題，就是資料的一致性問題

可以改善延時,沒法杜絕

你可以看看這個:Percona, http://www.percona.com/ 或許能滿足你的要求...

