我在第25和27篇文章中，和你介紹了主備切換流程。通過這些內容的講解，你應該已經很清楚了：在一主一備的雙M架構里，主備切換只需要把客戶端流量切到備庫；而在一主多從架構里，主備切換除了要把客戶端流量切到備庫外，還需要把從庫接到新主庫上。

主備切換有兩種場景，一種是主動切換，一種是被動切換。而其中被動切換，往往是因為主庫出問題了，由HA系統發起的。

這也就引出了我們今天要討論的問題：怎么判斷一個主庫出問題了？

你一定會說，這很簡單啊，連上MySQL，執行個select 1就好了。但是select 1成功返回了，就表示主庫沒問題嗎？

select 1判斷

實際上，select 1成功返回，只能說明這個庫的進程還在，并不能說明主庫沒問題。現在，我們來看一下這個場景。

set global innodb_thread_concurrency=3;CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;insert into t values(1,1)

圖1 查詢blocked

我們設置innodb_thread_concurrency參數的目的是，控制InnoDB的并發線程上限。也就是說，一旦并發線程數達到這個值，InnoDB在接收到新請求的時候，就會進入等待狀態，直到有線程退出。

這里，我把innodb_thread_concurrency設置成3，表示InnoDB只允許3個線程并行執行。而在我們的例子中，前三個session 中的sleep(100)，使得這三個語句都處于“執行”狀態，以此來模擬大查詢。

你看到了， session D里面，select 1是能執行成功的，但是查詢表t的語句會被堵住。也就是說，如果這時候我們用select 1來檢測實例是否正常的話，是檢測不出問題的。

在InnoDB中，innodb_thread_concurrency這個參數的默認值是0，表示不限制并發線程數量。但是，不限制并發線程數肯定是不行的。因為，一個機器的CPU核數有限，線程全沖進來，上下文切換的成本就會太高。

所以，通常情況下，我們建議把innodb_thread_concurrency設置為64~128之間的值。這時，你一定會有疑問，并發線程上限數設置為128夠干啥，線上的并發連接數動不動就上千了。

產生這個疑問的原因，是搞混了并發連接和并發查詢。

并發連接和并發查詢，并不是同一個概念。你在show processlist的結果里，看到的幾千個連接，指的就是并發連接。而“當前正在執行”的語句，才是我們所說的并發查詢。

并發連接數達到幾千個影響并不大，就是多占一些內存而已。我們應該關注的是并發查詢，因為并發查詢太高才是CPU殺手。這也是為什么我們需要設置innodb_thread_concurrency參數的原因。

然后，你可能還會想起我們在第7篇文章中講到的熱點更新和死鎖檢測的時候，如果把innodb_thread_concurrency設置為128的話，那么出現同一行熱點更新的問題時，是不是很快就把128消耗完了，這樣整個系統是不是就掛了呢？

實際上，在線程進入鎖等待以后，并發線程的計數會減一，也就是說等行鎖（也包括間隙鎖）的線程是不算在128里面的。

MySQL這樣設計是非常有意義的。因為，進入鎖等待的線程已經不吃CPU了；更重要的是，必須這么設計，才能避免整個系統鎖死。

為什么呢？假設處于鎖等待的線程也占并發線程的計數，你可以設想一下這個場景：

線程1執行begin; update t set c=c+1 where id=1, 啟動了事務trx1， 然后保持這個狀態。這時候，線程處于空閑狀態，不算在并發線程里面。

線程2到線程129都執行 update t set c=c+1 where id=1; 由于等行鎖，進入等待狀態。這樣就有128個線程處于等待狀態；

如果處于鎖等待狀態的線程計數不減一，InnoDB就會認為線程數用滿了，會阻止其他語句進入引擎執行，這樣線程1不能提交事務。而另外的128個線程又處于鎖等待狀態，整個系統就堵住了。

下圖2顯示的就是這個狀態。

圖2 系統鎖死狀態（假設等行鎖的語句占用并發計數）

這時候InnoDB不能響應任何請求，整個系統被鎖死。而且，由于所有線程都處于等待狀態，此時占用的CPU卻是0，而這明顯不合理。所以，我們說InnoDB在設計時，遇到進程進入鎖等待的情況時，將并發線程的計數減1的設計，是合理而且是必要的。

雖然說等鎖的線程不算在并發線程計數里，但如果它在真正地執行查詢，就比如我們上面例子中前三個事務中的select sleep(100) from t，還是要算進并發線程的計數的。

在這個例子中，同時在執行的語句超過了設置的innodb_thread_concurrency的值，這時候系統其實已經不行了，但是通過select 1來檢測系統，會認為系統還是正常的。

因此，我們使用select 1的判斷邏輯要修改一下。

查表判斷

為了能夠檢測InnoDB并發線程數過多導致的系統不可用情況，我們需要找一個訪問InnoDB的場景。一般的做法是，在系統庫（mysql庫）里創建一個表，比如命名為health_check，里面只放一行數據，然后定期執行：

mysql> select * from mysql.health_check;

使用這個方法，我們可以檢測出由于并發線程過多導致的數據庫不可用的情況。

但是，我們馬上還會碰到下一個問題，即：空間滿了以后，這種方法又會變得不好使。

我們知道，更新事務要寫binlog，而一旦binlog所在磁盤的空間占用率達到100%，那么所有的更新語句和事務提交的commit語句就都會被堵住。但是，系統這時候還是可以正常讀數據的。

因此，我們還是把這條監控語句再改進一下。接下來，我們就看看把查詢語句改成更新語句后的效果。

更新判斷

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

mysql> update mysql.health_check set t_modified=now();

節點可用性的檢測都應該包含主庫和備庫。如果用更新來檢測主庫的話，那么備庫也要進行更新檢測。

但，備庫的檢測也是要寫binlog的。由于我們一般會把數據庫A和B的主備關系設計為雙M結構，所以在備庫B上執行的檢測命令，也要發回給主庫A。

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

為了讓主備之間的更新不產生沖突，我們可以在mysql.health_check表上存入多行數據，并用A、B的server_id做主鍵。

mysql> CREATE TABLE `health_check` ( `id` int(11) NOT NULL, `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB;/* 檢測命令 */ insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

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

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

你一定會疑惑，更新語句，如果失敗或者超時，就可以發起主備切換了，為什么還會有判定慢的問題呢？

其實，這里涉及到的是服務器IO資源分配的問題。

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

你可以設想一個日志盤的IO利用率已經是100%的場景。這時候，整個系統響應非常慢，已經需要做主備切換了。

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

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

也就是說，這時候在業務系統上正常的SQL語句已經執行得很慢了，但是DBA上去一看，HA系統還在正常工作，并且認為主庫現在處于可用狀態。

之所以會出現這個現象，根本原因是我們上面說的所有方法，都是基于外部檢測的。外部檢測天然有一個問題，就是隨機性。

因為，外部檢測都需要定時輪詢，所以系統可能已經出問題了，但是卻需要等到下一個檢測發起執行語句的時候，我們才有可能發現問題。而且，如果你的運氣不夠好的話，可能第一次輪詢還不能發現，這就會導致切換慢的問題。

所以，接下來我要再和你介紹一種在MySQL內部發現數據庫問題的方法。

內部統計

針對磁盤利用率這個問題，如果MySQL可以告訴我們，內部每一次IO請求的時間，那我們判斷數據庫是否出問題的方法就可靠得多了。

其實，MySQL 5.6版本以后提供的performance_schema庫，就在file_summary_by_event_name表里統計了每次IO請求的時間。

file_summary_by_event_name表里有很多行數據，我們先來看看event_name='wait/io/file/innodb/innodb_log_file’這一行。

圖3 performance_schema.file_summary_by_event_name的一行

圖中這一行表示統計的是redo log的寫入時間，第一列EVENT_NAME 表示統計的類型。

接下來的三組數據，顯示的是redo log操作的時間統計。

第一組五列，是所有IO類型的統計。其中，COUNT_STAR是所有IO的總次數，接下來四列是具體的統計項， 單位是皮秒；前綴SUM、MIN、AVG、MAX，顧名思義指的就是總和、最小值、平均值和最大值。

第二組六列，是讀操作的統計。最后一列SUM_NUMBER_OF_BYTES_READ統計的是，總共從redo log里讀了多少個字節。

第三組六列，統計的是寫操作。

最后的第四組數據，是對其他類型數據的統計。在redo log里，你可以認為它們就是對fsync的統計。

在performance_schema庫的file_summary_by_event_name表里，binlog對應的是event_name = "wait/io/file/sql/binlog"這一行。各個字段的統計邏輯，與redo log的各個字段完全相同。這里，我就不再贅述了。

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

我的測試結果是，如果打開所有的performance_schema項，性能大概會下降10%左右。所以，我建議你只打開自己需要的項進行統計。你可以通過下面的方法打開或者關閉某個具體項的統計。

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

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

假設，現在你已經開啟了redo log和binlog這兩個統計信息，那要怎么把這個信息用在實例狀態診斷上呢？

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

mysql> 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;

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

mysql> truncate table performance_schema.file_summary_by_event_name;

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

小結

今天，我和你介紹了檢測一個MySQL實例健康狀態的幾種方法，以及各種方法存在的問題和演進的邏輯。

你看完后可能會覺得，select 1這樣的方法是不是已經被淘汰了呢，但實際上使用非常廣泛的MHA（Master High Availability），默認使用的就是這個方法。

MHA中的另一個可選方法是只做連接，就是 “如果連接成功就認為主庫沒問題”。不過據我所知，選擇這個方法的很少。

其實，每個改進的方案，都會增加額外損耗，并不能用“對錯”做直接判斷，需要你根據業務實際情況去做權衡。

我個人比較傾向的方案，是優先考慮update系統表，然后再配合增加檢測performance_schema的信息。

最后，又到了我們的思考題時間。

今天，我想問你的是：業務系統一般也有高可用的需求，在你開發和維護過的服務中，你是怎么判斷服務有沒有出問題的呢？

你可以把你用到的方法和分析寫在留言區，我會在下一篇文章中選取有趣的方案一起來分享和分析。感謝你的收聽，也歡迎你把這篇文章分享給更多的朋友一起閱讀。

上期問題時間

上期的問題是，如果使用GTID等位點的方案做讀寫分離，在對大表做DDL的時候會怎么樣。

假設，這條語句在主庫上要執行10分鐘，提交后傳到備庫就要10分鐘（典型的大事務）。那么，在主庫DDL之后再提交的事務的GTID，去備庫查的時候，就會等10分鐘才出現。

這樣，這個讀寫分離機制在這10分鐘之內都會超時，然后走主庫。

這種預期內的操作，應該在業務低峰期的時候，確保主庫能夠支持所有業務查詢，然后把讀請求都切到主庫，再在主庫上做DDL。等備庫延遲追上以后，再把讀請求切回備庫。

通過這個思考題，我主要想讓關注的是，大事務對等位點方案的影響。

當然了，使用gh-ost方案來解決這個問題也是不錯的選擇。

?

轉載于:https://www.cnblogs.com/a-phper/p/10313969.html

總結

以上是生活随笔為你收集整理的29 | 如何判断一个数据库是不是出问题了？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。