[[427065]]

介紹

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

思路

根據(jù)讀者和用戶的反饋，畫了一個(gè)寫作思路圖。通過此圖可以更好的分析出當(dāng)前文章的寫作知識(shí)點(diǎn)?？梢愿斓膸椭x者在最短時(shí)間內(nèi)判斷是否為有效文章!

外部統(tǒng)計(jì)

select 1

正常情況

這里先來聊一下select 1的用法，這個(gè)用法我想大家應(yīng)該都是知道的，因?yàn)榕袛嘁粋€(gè)庫是否還活著，只需要執(zhí)行一條SQL語句不就好了嘛

• 如果庫正常沒有問題，那么就會(huì)返回1，因?yàn)檩敵?肯定是要返回1的呀
• 如果庫掛掉了，輸出1肯定是沒有反應(yīng)的，因?yàn)镸ySQL已經(jīng)無法提供服務(wù)了

mysql在執(zhí)行select1的時(shí)候，往往是用于單機(jī)服務(wù)，我們舉一個(gè)很簡(jiǎn)單的例子，在一個(gè)cmd控制臺(tái)上進(jìn)入mysql，并且執(zhí)行SQL語句，只能得知當(dāng)前庫是否正常。無法得知整個(gè)數(shù)據(jù)庫的集群是否都正常。所以在單機(jī)狀態(tài)下這種方案是比較常用的，一旦上了一些集群規(guī)模一般不會(huì)采用這種方案!

意外情況

首先我們介紹一下配置并發(fā)線程上限的參數(shù) innodb_thread_concurrency 。如果把他設(shè)置 3 一旦并發(fā)線程數(shù)達(dá)到這個(gè)值，InnoDB 在接收到新請(qǐng)求的時(shí)候，就會(huì)進(jìn)入等待狀態(tài)，直到有線程退出。

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

會(huì)執(zhí)行成功的 ! 但是如果測(cè)驗(yàn)完之后這個(gè)用戶再發(fā)送一條查詢表請(qǐng)求，就會(huì)被堵住，因?yàn)榱硗馊齻€(gè)線程的用戶也在查詢表操作，那么這幾個(gè)線程就會(huì)處于等待情況。

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

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

擴(kuò)展 這里的64~128是指并發(fā)查詢的線程，可能有些人會(huì)和并發(fā)連接會(huì)弄混。

show processlist 

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

我們介紹一下熱點(diǎn)更新和死鎖檢測(cè)的時(shí)候吧。如果把 innodb_thread_concurrency設(shè)置為 128 的話，那么出現(xiàn)同一行熱點(diǎn)更新的問題時(shí)，是不是很快就把 128 消耗完了，這樣整個(gè)系統(tǒng)是不是就掛了呢?

不是的， MySQL肯定不會(huì)允許這樣的事情發(fā)生的。所以當(dāng)鎖等待的時(shí)候，并發(fā)線程會(huì)進(jìn)行減一。也就說鎖等待不會(huì)算在線程128中。

特殊情況

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

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

查表判斷

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

• 那么表放在什么地方呢?
• 肯定不能隨便放在一個(gè)數(shù)據(jù)庫中吧!

表的位置是在如下圖的那個(gè)數(shù)據(jù)庫中建立的，我們可以建立一個(gè)health_check，里面只放一行數(shù)據(jù)，然后定期執(zhí)行。

select * from mysql.health_check; 

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

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

更新數(shù)據(jù)也就是記入一個(gè)事務(wù)。記入事務(wù)是要寫binlog日志的，磁盤滿了咋寫?

所以執(zhí)行不成功，但是還能提供讀取的數(shù)據(jù)。顯然兩頭不對(duì)應(yīng)肯定不可以的。

更新判斷

又pass了一個(gè)

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

update mysql.health_check set t_modified=now(); 

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

節(jié)點(diǎn)可用性的檢測(cè)都應(yīng)該包含主庫和備庫。如果用更新來檢測(cè)主庫的話，那么備庫也要進(jìn)行更新檢測(cè)。備庫的檢測(cè)也是要寫 binlog 的。由于我們一般會(huì)把數(shù)據(jù)庫 A 和 B 的主備關(guān)系設(shè)計(jì)為雙 M 結(jié)構(gòu)，所以在備庫 B 上執(zhí)行的檢測(cè)命令，也要發(fā)回給主庫 A。

主庫 A 和備庫 B 都用相同的更新命令，就可能出現(xiàn)行沖突，也就是可能會(huì)導(dǎo)致主備同步停止。所以，現(xiàn)在看來 mysql.health_check 這個(gè)表就不能只有一行數(shù)據(jù)了。

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

MySQL 規(guī)定了主庫和備庫的 server_id 必須不同(否則創(chuàng)建主備關(guān)系的時(shí)候就會(huì)報(bào)錯(cuò))，這樣就可以保證主、備庫各自的檢測(cè)命令不會(huì)發(fā)生沖突。

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

根據(jù)我們前幾篇文章的介紹，當(dāng)更新操作出現(xiàn)慢操作或者失敗。就可以主從切換了，為什么還會(huì)有判定慢的問題呢?

IO資源分配

首先，所有的檢測(cè)邏輯都需要一個(gè)超時(shí)時(shí)間 N。執(zhí)行一條 update 語句，超過 N 秒后還不返回，就認(rèn)為系統(tǒng)不可用。

判定慢是因?yàn)镮O資源分配的問題，日志盤的 IO 利用率已經(jīng)是 100% 的場(chǎng)景。這時(shí)候，整個(gè)系統(tǒng)響應(yīng)非常慢，已經(jīng)需要做主備切換了。

IO 利用率 100% 表示系統(tǒng)的 IO 是在工作的，每個(gè)請(qǐng)求都有機(jī)會(huì)獲得 IO 資源，執(zhí)行自己的任務(wù)。而我們的檢測(cè)使用的 update 命令，需要的資源很少，所以可能在拿到 IO 資源的時(shí)候就可以提交成功，并且在超時(shí)時(shí)間 N 秒未到達(dá)之前就返回給了檢測(cè)系統(tǒng)。

檢測(cè)系統(tǒng)一看，update 命令沒有超時(shí)，于是就得到了 系統(tǒng)正常 的結(jié)論。

IO問題，SQL執(zhí)行很慢，但是這個(gè)時(shí)候系統(tǒng)是正常的肯定是不行的

內(nèi)部統(tǒng)計(jì)

外部統(tǒng)計(jì)無法判斷滿足真實(shí)需求。我們轉(zhuǎn)戰(zhàn)內(nèi)部統(tǒng)計(jì)方案。

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

從performance_schema 庫，就在 file_summary_by_event_name 表里統(tǒng)計(jì)了每次 IO 請(qǐng)求的時(shí)間。

COUNT_STAR：所有 IO 的總次數(shù)

SUM_NUMBER_OF_BYTES_READ :總共從 redo log 里讀了多少個(gè)字節(jié)。

對(duì)上述表中的字段介紹簡(jiǎn)單普及一下最常用的。剩下的用的時(shí)候自行搜索。

排查思路

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

我們每一次操作數(shù)據(jù)庫，performance_schema 都需要額外地統(tǒng)計(jì)這些信息，所以我們打開這個(gè)統(tǒng)計(jì)功能是有性能損耗的。

如果要打開 redo log 的時(shí)間監(jiān)控，你可以執(zhí)行這個(gè)語句：

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

開啟之后，用于實(shí)戰(zhàn)呢

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

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; 

發(fā)現(xiàn)異常后，取到你需要的信息，再通過下面這條語句：

truncate table performance_schema.file_summary_by_event_name; 

把之前的統(tǒng)計(jì)信息清空。這樣如果后面的監(jiān)控中，再次出現(xiàn)這個(gè)異常，就可以加入監(jiān)控累積值了。

總結(jié)

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

于是到了查表判斷，查表判斷涉及到 innodb寫事務(wù)日志的時(shí)候，如果磁盤滿了的話，寫事務(wù)寫不了但是可以讀，導(dǎo)致不一致。

再到更新判斷。IO 利用率 100% 表示系統(tǒng)的 IO 是在工作的，每個(gè)請(qǐng)求都有機(jī)會(huì)獲得 IO 資源。所以u(píng)pdate不會(huì)超時(shí)，系統(tǒng)認(rèn)為是正常情況。所以一邊響應(yīng)不了服務(wù)，一邊又判斷正常，導(dǎo)致不一致。

最后到了內(nèi)部統(tǒng)計(jì)。采用系統(tǒng)庫的方案。通過 event_name 和 MAX_TIMER 字段進(jìn)行判斷是否出問題