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

01-02

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

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

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

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

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

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

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

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

MySQL replication

我的經驗里，最重要的是盡量避免讓任何一台mysql伺服器跑滿。

所以真正的解決方案是精準的capacity planning，適時地scale out。

題主說的方案感覺非常不靠譜。

不過mysql-proxy本人也幾乎沒怎麼接觸，它能否實現上訴功能有些不大確定，即使它有，也不建議為了這個就用它，官網自己都不推薦用到生產環境。

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

業務量不大的

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

已經出現延遲的

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

延遲N天無法解決的

那就重做slave。

為什麼會延遲N天，難道僅僅是因為從庫單線程嗎？

我感覺大部分都是主庫上採用mixed的binlog_format，由於某種限制，無法基於statement，只好row模式複製。

那麼如果當前sql是全表掃描，傳到slave上執行時就是茫茫多次的全表掃描了。

下面是官網給出的在mixed下，自動從statement轉換成row模式的條件

When running in MIXED logging format, the server automatically switches from statement-based to rowbased logging under the following conditions:

? When a function contains UUID().

? When one or more tables withAUTO_INCREMENT columns are updated and a trigger or stored

function is invoked. Like all other unsafe statements, this generates a warning ifbinlog_format =

STATEMENT.

? When the body of a view requires row-based replication, the statement creating the view also uses it. For

example, this occurs when the statement creating a view uses theUUID() function.

? When a call to a UDF is involved.

? When any INSERT DELAYED is executed for a nontransactional table.

? If a statement is logged by row and the session that executed the statement has any temporary tables,

logging by row is used for all subsequent statements (except for those accessing temporary tables) until

all temporary tables in use by that session are dropped.

This is true whether or not any temporary tables are actually logged.

Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all

subsequent statements using that table are unsafe. The server approximates this condition by treating

all statements executed during the session as unsafe until the session no longer holds any temporary

tables.

? When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)

? When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)

? When a statement refers to one or more system variables. (Bug #31168)

Exception. The following system variables, when used with session scope (only), do not cause the

logging format to switch:

? auto_increment_increment

? auto_increment_offset

? character_set_client

? character_set_connection

? character_set_database

? character_set_server

? collation_connection

? collation_database

? collation_server

? foreign_key_checks

? identity

? last_insert_id

? lc_time_names

? pseudo_thread_id

? sql_auto_is_null

? time_zone

? timestamp

? unique_checks

For information about determining system variable scope, seeSection 5.1.5, 「Using System Variables」.

For information about how replication treats sql_mode, see Section 17.4.1.34, 「Replication and

Variables」.

? When one of the tables involved is a log table in themysql database.

? When the LOAD_FILE() function is used. (Bug #39701)

一般來說在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：

如果還是經常性的短時間延遲，那就嘗試加大從庫的硬體配置，比如上sata SSD，pcie等
延遲的監控到位，可通過pt-heart-beat來準確監控延遲值，及時發現查看。
5.5以後版本的，可以考慮採用半同步複製，能解決少量延遲引起的問題，不過對tps性能損耗較大
升級到mysql 5.7吧，多線程複製，幾乎完美解決單線程複製引起的從庫延遲。
給出幾個同步延遲的case，平時做的小記錄故障案例：slave延遲很大

主從同步延遲是必然現象，不是問題。關鍵看具體業務，因同步延遲帶來什麼問題，然後再解決。

舉個簡單的例子

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

針對這個假設的問題，可以採取幾種方案：

1、有更新數據後的讀取相關數據動作，都從默認到主庫；

2、利用緩存；插入新的數據，會有last_id返回，組裝成數據，緩存到前端。讀取此 id 數據時，先從緩存取。

完全同步是一個非常昂貴和複雜的操作，負載量大的話幾乎不可能完成。所以聰明的辦法是調整上層的邏輯，避免這種需求。

可以採用Redis技術， 新浪微博就大量使用了Redis技術。區別於Memcached的是，redis會周期性的把更新的數據寫入磁碟或者把修改操作寫入追加的記錄文件，並且在此基礎上實現了主從同步。這需要假設Redis伺服器了。具體請看新浪網首席DBA楊海朝的視頻和PPT http://blog.nosqlfan.com/html/2692.html。

首先你要看主備之間的同步方式。

如果是強同步的，隨便你怎麼讀備庫都不會有問題，因為在任何情況下數據跟主庫都是一樣的。

如果是半同步或者非同步，那麼在某些場景下會時不時有延遲。如果不能接受就把強一致性讀的請求發主庫，把弱一致性讀的請求發備庫。

一個好的proxy不僅要判斷主備延遲自動分發流量，還要提供hint讓用戶可以強制指定流量。

可以改善延時,沒法杜絕

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

既然說明是複製，必然存在一段時間內數據不能完全同步的問題，就算是全扔內存里也是一樣，時間長短取決於伺服器負載，配置以及配備。既然不能完全避免，可嘗試通過數據緩存，優化程序邏輯等方式降低新數據立即被讀取的可能性，來盡量避免類似問題的發生。

直接使用強一致性協議的集群方案唄

在資料庫架構上來說，沒有完全的同步。如果你需要完全的同步，除非你可以接受犧牲你的性能。對於主從，首先要分清楚對其的定義：從的存在主要是避免主宕機導致數據丟失。所以，主業務建議盡量在主上操作。非同步是避免不了的。當然，隨業務而定，如果你需要主從的業務時，你可以在中間層加個分散式緩存如redis，對於主從數據延時，在架構上，適當的延時是可以接受的

中間加個nosql層

沒有完全的同步

可以開啟MySQL的 Semi-sync（半同步），從庫讀取數據的時候就不會有延時，當然這個會影響寫入的速度。
網路和從庫硬體配置不錯的話，延時一般在1秒之內，這個值對於絕大部分應用是可以接受的。同時，可以在前端和資料庫之間增加緩存層，來讀取剛剛寫入的數據。

通常少量延遲不是問題。如果要做到完全同步，對主資料庫性能勢必有影響。只要保證從資料庫是主資料庫在某個時間點的快照就成了。

如果要更具體分析，需要詳細描述你的應用場景

主從同步問題永遠都是一致性和性能的權衡，得看實際的應用場景，我們的主從不是強一致性場景，所以就允許了從庫數據的相對延遲，5s的時間窗口保證主庫有修改操作(增、刪、改)同步到從庫，從庫只讀

持續關注中……

把延遲部分用內存資料庫蓋了

如果這個影響很大，可以在沒讀取到數據後，去主庫再讀一次。