[[427065]]

介紹

大家好，我是Leo，目前在常州從事Java后端工程師。上篇文章我們介紹了讀寫分離那些問題，主要從概念，目的，單到多的演變，安全性演變以及六個解決方案為敘述。今天我們聊聊一主多從，如果掛了你會如何快速定位。贈送算法，MySQL書籍，劍指offer

思路

根據讀者和用戶的反饋，畫了一個寫作思路圖。通過此圖可以更好的分析出當前文章的寫作知識點。可以更快的幫助讀者在最短時間內判斷是否為有效文章!

外部統計

select 1

正常情況

這里先來聊一下select 1的用法，這個用法我想大家應該都是知道的，因為判斷一個庫是否還活著，只需要執行一條SQL語句不就好了嘛

• 如果庫正常沒有問題，那么就會返回1，因為輸出1肯定是要返回1的呀
• 如果庫掛掉了，輸出1肯定是沒有反應的，因為MySQL已經無法提供服務了

mysql在執行select1的時候，往往是用于單機服務，我們舉一個很簡單的例子，在一個cmd控制臺上進入mysql，并且執行SQL語句，只能得知當前庫是否正常。無法得知整個數據庫的集群是否都正常。所以在單機狀態下這種方案是比較常用的，一旦上了一些集群規模一般不會采用這種方案!

意外情況

首先我們介紹一下配置并發線程上限的參數 innodb_thread_concurrency 。如果把他設置 3 一旦并發線程數達到這個值，InnoDB 在接收到新請求的時候，就會進入等待狀態，直到有線程退出。

這里我們可以模擬一下最壞的情況，如果這時有三個線程正常訪問數據庫執行一個大數據量的查詢操作。如果這時來一個select 1 是否能執行成功呢?

會執行成功的 ! 但是如果測驗完之后這個用戶再發送一條查詢表請求，就會被堵住，因為另外三個線程的用戶也在查詢表操作，那么這幾個線程就會處于等待情況。

問題來了 select 1執行成功了，真實的查詢語句出問題了，那么這個方案可行嗎，肯定是不行的。

innodb_thread_concurrency 這個參數默認是0。代表著不限制上限并發線程。這個肯定是不行，考慮到整體性能的考慮，如果并發線程過于會影響MySQL的整體性能。所以我們一般建議64~128。

擴展 這里的64~128是指并發查詢的線程，可能有些人會和并發連接會弄混。

show processlist 

執行上述SQL，以下是Command列中的Query是屬于并發查詢，并發連接是屬于與數據庫發起連接，但是掛在那個界面不做任何操作。并發連接只是浪費一些內存而已，而并發查詢是浪費MySQL限制的并發線程數的。

我們介紹一下熱點更新和死鎖檢測的時候吧。如果把 innodb_thread_concurrency設置為 128 的話，那么出現同一行熱點更新的問題時，是不是很快就把 128 消耗完了，這樣整個系統是不是就掛了呢?

不是的， MySQL肯定不會允許這樣的事情發生的。所以當鎖等待的時候，并發線程會進行減一。也就說鎖等待不會算在線程128中。

特殊情況

一些鎖等待肯定是不算在并發線程中的，那么如果像我們上述那種消耗時間比較大的查詢，如何處置呢?

如果真的干到了128，再使用select 1 豈不是會出問題嗎，所以下一個方案就誕生了

查表判斷

select 1 的弊端出來了，逐漸演變成查表判斷

• 那么表放在什么地方呢?
• 肯定不能隨便放在一個數據庫中吧!

表的位置是在如下圖的那個數據庫中建立的，我們可以建立一個health_check，里面只放一行數據，然后定期執行。

select * from mysql.health_check; 

這樣的確可以從innodb這邊解決當前的數據庫的狀態，那么問題來了，innodb是要寫日志的，也就是寫binlog，所以當磁盤空間占用率達到100%。所有的更新語句和事務提交的 commit 語句就都會被堵住。但是，系統這時候還是可以正常讀數據的。

上面的查詢判斷，顯然是不行的。

更新數據也就是記入一個事務。記入事務是要寫binlog日志的，磁盤滿了咋寫?

所以執行不成功，但是還能提供讀取的數據。顯然兩頭不對應肯定不可以的。

更新判斷

又pass了一個

既然要更新，就要放個有意義的字段，常見做法是放一個 timestamp 字段，用來表示最后一次執行檢測的時間。這條更新語句類似于：

update mysql.health_check set t_modified=now(); 

所有主從庫涉及到更新操作的話，肯定是要處理同步問題的

節點可用性的檢測都應該包含主庫和備庫。如果用更新來檢測主庫的話，那么備庫也要進行更新檢測。備庫的檢測也是要寫 binlog 的。由于我們一般會把數據庫 A 和 B 的主備關系設計為雙 M 結構，所以在備庫 B 上執行的檢測命令，也要發回給主庫 A。

主庫 A 和備庫 B 都用相同的更新命令，就可能出現行沖突，也就是可能會導致主備同步停止。所以，現在看來 mysql.health_check 這個表就不能只有一行數據了。

如果存放多行的話，在一主多從中就要考慮server_id的問題啦

MySQL 規定了主庫和備庫的 server_id 必須不同(否則創建主備關系的時候就會報錯)，這樣就可以保證主、備庫各自的檢測命令不會發生沖突。

更新判斷是一個相對比較常用的方案了，不過依然存在一些問題。比如 “判定慢”

根據我們前幾篇文章的介紹，當更新操作出現慢操作或者失敗。就可以主從切換了，為什么還會有判定慢的問題呢?

IO資源分配

首先，所有的檢測邏輯都需要一個超時時間 N。執行一條 update 語句，超過 N 秒后還不返回，就認為系統不可用。

判定慢是因為IO資源分配的問題，日志盤的 IO 利用率已經是 100% 的場景。這時候，整個系統響應非常慢，已經需要做主備切換了。

IO 利用率 100% 表示系統的 IO 是在工作的，每個請求都有機會獲得 IO 資源，執行自己的任務。而我們的檢測使用的 update 命令，需要的資源很少，所以可能在拿到 IO 資源的時候就可以提交成功，并且在超時時間 N 秒未到達之前就返回給了檢測系統。

檢測系統一看，update 命令沒有超時，于是就得到了 系統正常 的結論。

IO問題，SQL執行很慢，但是這個時候系統是正常的肯定是不行的

內部統計

外部統計無法判斷滿足真實需求。我們轉戰內部統計方案。

上一種方案的更細判斷，會有寫入binlog IO磁盤的問題，那么方案優化，如果MySQL可以提供這類數據豈不是可靠多了嘛!

從performance_schema 庫，就在 file_summary_by_event_name 表里統計了每次 IO 請求的時間。

COUNT_STAR：所有 IO 的總次數

SUM_NUMBER_OF_BYTES_READ :總共從 redo log 里讀了多少個字節。

對上述表中的字段介紹簡單普及一下最常用的。剩下的用的時候自行搜索。

排查思路

找到這個表之后，我們只需要event_name = "wait/io/file/sql/binlog"這一行就OK了。

我們每一次操作數據庫，performance_schema 都需要額外地統計這些信息，所以我們打開這個統計功能是有性能損耗的。

如果要打開 redo log 的時間監控，你可以執行這個語句：

update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%'; 

開啟之后，用于實戰呢

可以通過 MAX_TIMER 的值來判斷數據庫是否出問題了。比如，你可以設定閾值，單次 IO 請求時間超過 200 毫秒屬于異常，然后使用類似下面這條語句作為檢測邏輯。

select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000; 

發現異常后，取到你需要的信息，再通過下面這條語句：

truncate table performance_schema.file_summary_by_event_name; 

把之前的統計信息清空。這樣如果后面的監控中，再次出現這個異常，就可以加入監控累積值了。

總結

大概介紹了從最基礎的 select 1 方法開始，這種方法應用與單機MySQL是再好不過了，但是一主多從集群之后就不行了。

于是到了查表判斷，查表判斷涉及到 innodb寫事務日志的時候，如果磁盤滿了的話，寫事務寫不了但是可以讀，導致不一致。

再到更新判斷。IO 利用率 100% 表示系統的 IO 是在工作的，每個請求都有機會獲得 IO 資源。所以update不會超時，系統認為是正常情況。所以一邊響應不了服務，一邊又判斷正常，導致不一致。

最后到了內部統計。采用系統庫的方案。通過 event_name 和 MAX_TIMER 字段進行判斷是否出問題