原文《MySQL實戰(zhàn)45講》

前言

? 我第一次使用 select 1 ，是在項目里面引入 Druid 時看到了連接的有效性判斷配置，部分配置如下：

······
<property name="validationQuery" value="select 1" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
······

? 在 Druid 的參考配置中，我們可以看到，通過 “select 1”，連接池就可以判斷連接是否有效了。但是 "select 1 " 返回了，就表示主庫沒問題嗎？ 經過學習，發(fā)現還真不是這么簡單。

select 1 判斷

? 實際上，select 1 成功返回，只能說明這個庫的進程還存在，并不能說明數據庫沒有問題，以一下場景為例。

# 設置 innodb 的并發(fā)線程數
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)

img

? 設置 innodb_thread_concurrency 是為了控制 InnoDB 的并發(fā)線程上限。也就是說，一旦并發(fā)線程數達到這個值，InnoDB就不會馬上執(zhí)行新的請求，而是等待線程資源，直到當前正在執(zhí)行的線程退出。InnoDB中，innodb_thread_concurrency 默認為0，表示不限制并發(fā)線程數。但是不設置并發(fā)線程數肯定是不行的，因為機器的CPU處理能力有限，大量線程切換處理的過程中，上下文切換的成本太高。

? 在 session D 中，select 1；是可以 執(zhí)行成功的，但實際查詢語句卻被 blocked 。也就是說，這個場景下，使用 select 1；并不能檢測到問題。

不要混淆并發(fā)連接和并發(fā)查詢

? show processlist 的結果里，看到的是并發(fā)連接，并發(fā)連接多只是多占用一些內存，實際消耗CPU資源的是并發(fā)查詢。

? 而“當前正在執(zhí)行”的語句，才是我們所說的并發(fā)查詢。

在線程進入鎖等待后，并發(fā)線程會減一

? 熱點數據更新和死鎖檢測的時候，如果 innodb_thread_concurrency 設置的太小，同時該熱點行更新過程中，發(fā)生了死鎖等問題，那等待更新該行的線程豈不是很快就達到 innodb_thread_concurrency 了，導致數據庫沒有空閑的資源可以執(zhí)行其他請求？

? 實際上，等待行鎖的（也包括間隙鎖）的線程是不算會算在 innodb_thread_concurrency 里面的。MySQL 為什么要這么設計呢？ 因為，進入鎖等待的線程已經不再消耗CPU了，更重要的是，這么設計可以避免整個數據系統(tǒng)被鎖死。

? 舉個例子：innodb_thread_concurrency 設置為 128 ，此時有128個線程等待同一個行鎖；但是這個時候還是可以繼續(xù)處理新的請求的，因為等待行鎖的線程并不會占用 innodb_thread_concurrency 。當然，但等待中的線程真正的執(zhí)行查詢，就會占用 innodb_thread_concurrency 了。

查表判斷

為了能夠檢測InnoDB并發(fā)線程數過多而導致系統(tǒng)不可用的情況，我們需要找一個訪問InnoDB的場景。常見的做法是，在系統(tǒng)庫（mysql庫）里創(chuàng)建一個表，比如命名為 health_check, 里面放一行數據，然后定期執(zhí)行。

mysql> select * from mysql.health_check; 

? 使用這個方法，我們可以檢測出由于并發(fā)線程過多導致的數據庫不可用的情況。但是，我們馬上還會碰到下一個問題，即：磁盤空間滿了以后，這個方法就無能為力了。

? 對于更新操作的事務，提交后是需要寫binlog的，如果binlog所在的磁盤占用達到了100%了，那么所有的更新語句提交的commit語句都會被堵住。但是，此時系統(tǒng)還是可以正常執(zhí)行查詢請求的。

? 所以，我們需要把查詢語句改成更新語句后，才能檢測到該問題。

更新判斷

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

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

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

? 但，備庫的檢測也是要寫 binlog 的。由于我們一般會把數據庫 A 和 B 的主備關系設計為雙 M 結構，所以在備庫 B 上執(zhí)行的檢測命令，也要發(fā)回給主庫 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 規(guī)定了主庫和備庫的 server_id 必須不同（否則創(chuàng)建主備關系的時候就會報錯），這樣就可以保證主、備庫各自的檢測命令不會發(fā)生沖突。

? 更新判斷是一個相對比較常用的方案了，不過依然存在一些問題。其中，“判定慢”一直是讓 DBA 頭疼的問題。你一定會疑惑，更新語句，如果失敗或者超時，就可以發(fā)起主備切換了，為什么還會有判定慢的問題呢？

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

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

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

內部統(tǒng)計

? 我們上面說的所有方法，都是基于外部檢測的。外部檢測天然有一個問題，就是隨機性。因為，外部檢測都需要定時輪詢，所以系統(tǒng)可能已經出問題了，但是卻需要等到下一個檢測發(fā)起執(zhí)行語句的時候，我們才有可能發(fā)現問題。而且，如果你的運氣不夠好的話，可能第一次輪詢還不能發(fā)現，這就會導致切換慢的問題。

? 針對磁盤利用率這個問題，如果 MySQL 可以告訴我們，內部每一次 IO 請求的時間，那我們判斷數據庫是否出問題的方法就可靠得多了。MySQL 5.6 版本以后提供的 performance_schema 庫，就在 file_summary_by_event_name 表里統(tǒng)計了每次 IO 請求的時間。

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

img

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

? 接下來的三組數據，顯示的是 redo log 操作的時間統(tǒng)計。

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

? 第二組六列，是讀操作的統(tǒng)計。最后一列 SUM_NUMBER_OF_BYTES_READ 統(tǒng)計的是，總共從 redo log 里讀了多少個字節(jié)。

? 第三組六列，統(tǒng)計的是寫操作。

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

? 除了對 'wait/io/file/innodb/innodb_log_file’ 的統(tǒng)計，還有其他總共有46行統(tǒng)計信息，需要用到的時候再詳細了解吧；

image.png

? 因為我們每一次操作數據庫，performance_schema 都需要額外地統(tǒng)計這些信息，所以我們打開這個統(tǒng)計功能是有性能損耗的。如果打開所有的 performance_schema 項，性能大概會下降 10% 左右。所以，我建議你只打開自己需要的項進行統(tǒng)計。你可以通過下面的方法打開或者關閉某個具體項的統(tǒng)計。

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

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

? 假設，現在你已經開啟了 redo log 和 binlog 這兩個統(tǒng)計信息，那要怎么把這個信息用在實例狀態(tài)診斷上呢？很簡單，你可以通過 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;

? 發(fā)現異常后，取到你需要的信息，再通過下面這條語句把之前的統(tǒng)計信息清空。這樣如果后面的監(jiān)控中，再次出現這個異常，就可以加入監(jiān)控累積值了。

mysql> truncate table performance_schema.file_summary_by_event_name;