主備切換有兩種場(chǎng)景: 主動(dòng)切換 和 被動(dòng)切換. 被動(dòng)切換大多是主庫出了問題、由HA系統(tǒng)引發(fā)的. 那么: 如何判定主庫出了問題呢 ?

一、 select 1 判定
實(shí)際上, select 1 成功返回、只能說明這個(gè)庫的進(jìn)程還在、并不能說明主庫沒問題. eg. 以下場(chǎng)景:

set global innodb_thread_concurrency=3; // 控制innodb并發(fā)線程上限. 超過、進(jìn)入等待. 來避免線程數(shù)過多、上下文切換的成本過高.

create table `t`(
 `id` int(11) not null,
`c` int(11) default null
) engine=innodb;

insert into t values(1,1);

image.png

在session D里、select 1是可執(zhí)行成功的, 但查詢表t會(huì)被堵住.
注意:

并發(fā)連接和并發(fā)查詢不是一個(gè)概念. show processlist看到的幾千個(gè)連接、指的就是并發(fā)連接.
當(dāng)前正在執(zhí)行的語句、才是并發(fā)查詢.
連接多頂多占一些內(nèi)存、而并發(fā)查詢多、才是造成CPU壓力的根本. 在線程進(jìn)入鎖等待之后、并發(fā)線程的計(jì)數(shù)會(huì)-1, 行鎖(包括間隙鎖)不占用CPU資源.

思考:

Mysql會(huì)什么設(shè)計(jì)鎖等待不計(jì)入并發(fā)線程數(shù)呢 ?

假設(shè)以下場(chǎng)景:
1. 線程1 執(zhí)行begin; update t set c=c+1 where id=1; 啟動(dòng)事務(wù)trx1, 然后保持、此時(shí)線程處于空閑
狀態(tài)、不計(jì)入并發(fā)線程
2.  線程2~129執(zhí)行 update t set c=c+1 where id=1; 由于等行鎖、進(jìn)入等待、這樣就有128個(gè)
線程處于等待狀態(tài).
3. 若處于等待狀態(tài)線程計(jì)數(shù)不-1, innodb會(huì)認(rèn)為線程用滿、阻止其他查詢語句進(jìn)入引擎執(zhí)行、線程1不能
提交、而另外的128個(gè)線程又處于鎖等待狀態(tài)、整個(gè)系統(tǒng)就阻塞了. 如下圖:
此時(shí)innodb不能響應(yīng)任何請(qǐng)求、且所有線程都處于等待狀態(tài)、此時(shí)占用CPU為0, 很不合理、所以 
設(shè)計(jì)進(jìn)入鎖等待、將并發(fā)線程的計(jì)數(shù)器-1, 是合理的.

但: 若真的執(zhí)行查詢、還是計(jì)入并發(fā)線程的. eg. select sleep(100) from t;

等待并發(fā)線程不減1, 系統(tǒng)鎖死.png

二、查表判斷
在系統(tǒng)庫(mysql)新建一個(gè)表, eg. 命名為 health_check, 里邊只放一行數(shù)據(jù)、定期檢測(cè), 可以發(fā)現(xiàn)由于并發(fā)線程數(shù)過多導(dǎo)致的db不可用.

select * from mysql.health_check;

但: 若空間滿了、這種方法又不好使.
更新事務(wù)要寫binlog、而一旦binlog所在磁盤空間占用率達(dá)到100%, 那所有的更新和事務(wù)提交的commit語句都會(huì)被堵住, 但可以正常讀取.

三、更新判斷

update mysql.headlth_check set t_modified=now();

節(jié)點(diǎn)可用性的檢測(cè)都應(yīng)該包含主庫和備庫、若用來檢測(cè)主庫的話、備庫也要進(jìn)行更新檢測(cè). 但: 備庫的檢測(cè)也是要寫binlog的、一般會(huì)把A和B的主備關(guān)系、設(shè)計(jì)為雙M架構(gòu)、所以在備庫B上執(zhí)行的檢測(cè)命令、也會(huì)發(fā)回給主庫A.
但若A、B更新同一條數(shù)據(jù)、就可能發(fā)生行沖突、導(dǎo)致主備同步停止. 可以插入多條數(shù)據(jù)、使用A、B的server_id做主鍵.

create table `headlth_check`(
 `id` int(11) not null,
`t_modified` timestamp not null default current_timestamp,
primary key(`id`)
) engine=innodb;
# 檢測(cè)命令
insert into mysql.health_check(id, t_modified) values(@@server_id, now()) on duplicate

這是一個(gè)比較常見的方案、但還存在一些問題, 判定慢.
eg. 所有的檢測(cè)邏輯都需要一個(gè)超時(shí)時(shí)間N、執(zhí)行update、超過Ns不返回、認(rèn)為系統(tǒng)不可用.
但: 假設(shè)日志盤IO利用率已經(jīng)100%, 這個(gè)時(shí)候系統(tǒng)響應(yīng)很慢、需要主備切換. 但檢測(cè)使用的update需要的資源很少、拿到io就可以提交成功、超時(shí)之前就返回了、于是得到了系統(tǒng)正常的結(jié)論, 顯然這是不合理的判定.

四、內(nèi)部統(tǒng)計(jì)
針對(duì)磁盤利用率、若Mysql 可以告知每次請(qǐng)求的io時(shí)間、就靠譜多了.
5.6版本以后的performance_schema庫、file_summay_by_event_name表里就統(tǒng)計(jì)了每次IO請(qǐng)求時(shí)間.
event_name='wait/io/file/innodb/innodb_log_file' 統(tǒng)計(jì)的是redo log的寫入時(shí)間. 第一列 event_name表示統(tǒng)計(jì)的類型.
接下來3組、統(tǒng)計(jì)的是redo log操作時(shí)間

第一組5列、是所有IO類型的統(tǒng)計(jì):
count_star 是所有IO總次數(shù)
sum、min、avg、max是統(tǒng)計(jì)的總和、最小、平均和最大值.(單位ps)

第二組6列、是讀操作的統(tǒng)計(jì)
最后一列 sum_number_of_bytes_read統(tǒng)計(jì)的是、總共從redo log讀多少個(gè)字節(jié)

第三組6列, 是寫操作統(tǒng)計(jì)

最后四組時(shí)間、是對(duì)其它類型數(shù)據(jù)的統(tǒng)計(jì)、在redo log里、可以認(rèn)為是對(duì) fsync的統(tǒng)計(jì).

binlog對(duì)應(yīng)的event_name是: wait/io/file/sql/binlog 這行、統(tǒng)計(jì)邏輯同 redo log. 額外的統(tǒng)計(jì)這些信息、是有性能耗損的、大概在10%左右. 建議只開啟需要的項(xiàng).

打開統(tǒng)計(jì)項(xiàng)之后、可以通過判斷MAX_TIMER的值來判斷數(shù)據(jù)庫是否有問題.
eg. 設(shè)定閾值、單次IO超過200ms屬于異常、出現(xiàn)異常把之前的統(tǒng)計(jì)值清空、再次出現(xiàn)這個(gè)異常就可以加入監(jiān)控累計(jì)值了.