【小編提醒】本文內(nèi)容源于互聯(lián)網(wǎng)收集整理，不代表YINGSOO觀點(diǎn)！若有咨詢“在mysQL數(shù)據(jù)庫中如何判斷一個(gè)數(shù)據(jù)庫是否出問題的方法”等有關(guān)服務(wù)器、云主機(jī)租用、托管、配置、價(jià)格問題，請(qǐng)?jiān)诰€咨詢YINGSOO客服，獲取專業(yè)解答！

　　【新品優(yōu)惠】美國云服務(wù)器特價(jià) | 新加坡CN2服務(wù)器特價(jià) | 美國物理服務(wù)器特價(jià)

　　【熱門文章】云服務(wù)器適合用來做哪些業(yè)務(wù)?

　　第一次使用 select 1 ，是在項(xiàng)目里面引入 druid 時(shí)看到了連接的有效性判斷配置，部分配置如下：

　　······

　　······

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

　　select 1 判斷

　　實(shí)際上，select 1 成功返回，只能說明這個(gè)庫的進(jìn)程還存在，并不能說明數(shù)據(jù)庫沒有問題，以一下場(chǎng)景為例。

　　# 設(shè)置 innodb 的并發(fā)線程數(shù)

　　set global innodb_thread_concurrency=3;

　　# 建表語句

　　cReAte tAbLe `t` (

　　`id` int(11) not nULL,

　　`c` int(11) deFAULt nULL,

　　pRimARY KeY (`id`)

　　) engine=innodb;

　　# 數(shù)據(jù)初始化

　　insert into t values(1,1)

　　設(shè)置 innodb_thread_concurrency 是為了控制 innodb 的并發(fā)線程上限。也就是說，一旦并發(fā)線程數(shù)達(dá)到這個(gè)值，innodb就不會(huì)馬上執(zhí)行新的請(qǐng)求，而是等待線程資源，直到當(dāng)前正在執(zhí)行的線程退出。innodb中，innodb_thread_concurrency 默認(rèn)為0，表示不限制并發(fā)線程數(shù)。但是不設(shè)置并發(fā)線程數(shù)肯定是不行的，因?yàn)闄C(jī)器的cpU處理能力有限，大量線程切換處理的過程中，上下文切換的成本太高。

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

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

　　show processlist 的結(jié)果里，看到的是并發(fā)連接，并發(fā)連接多只是多占用一些內(nèi)存，實(shí)際消耗cpU資源的是并發(fā)查詢。

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

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

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

　　實(shí)際上，等待行鎖的（也包括間隙鎖）的線程是不算會(huì)算在 innodb_thread_concurrency 里面的。mysQL 為什么要這么設(shè)計(jì)呢？ 因?yàn)椋M(jìn)入鎖等待的線程已經(jīng)不再消耗cpU了，更重要的是，這么設(shè)計(jì)可以避免整個(gè)數(shù)據(jù)系統(tǒng)被鎖死。

　　舉個(gè)例子：innodb_thread_concurrency 設(shè)置為 128 ，此時(shí)有128個(gè)線程等待同一個(gè)行鎖；但是這個(gè)時(shí)候還是可以繼續(xù)處理新的請(qǐng)求的，因?yàn)榈却墟i的線程并不會(huì)占用 innodb_thread_concurrency 。當(dāng)然，但等待中的線程真正的執(zhí)行查詢，就會(huì)占用 innodb_thread_concurrency 了。

　　查表判斷

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

　　mysql> select * from mysql.health_check;

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

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

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

　　更新判斷

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

　　mysql> 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ù)了。

　　為了讓主備之間的更新不產(chǎn)生沖突，我們可以在 mysql.health_check 表上存入多行數(shù)據(jù)，并用 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;

　　/* 檢測(cè)命令 */

　　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)建主備關(guān)系的時(shí)候就會(huì)報(bào)錯(cuò)），這樣就可以保證主、備庫各自的檢測(cè)命令不會(huì)發(fā)生沖突。

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

　　其實(shí)，這里涉及到的是服務(wù)器 io 資源分配的問題。

　　你可以設(shè)想一個(gè)日志盤的 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)。

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

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

　　針對(duì)磁盤利用率這個(gè)問題，如果 mysQL 可以告訴我們，內(nèi)部每一次 io 請(qǐng)求的時(shí)間，那我們判斷數(shù)據(jù)庫是否出問題的方法就可靠得多了。mysQL 5.6 版本以后提供的 performance_schema 庫，就在 file_summary_by_event_name 表里統(tǒng)計(jì)了每次 io 請(qǐng)求的時(shí)間。

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

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

　　第一組五列，是所有 io 類型的統(tǒng)計(jì)。其中，coUnt_stAR 是所有 io 的總次數(shù)，接下來四列是具體的統(tǒng)計(jì)項(xiàng)， 單位是皮秒；前綴 sUm、min、AVg、mAX，顧名思義指的就是總和、最小值、平均值和最大值。

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

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

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

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

　　因?yàn)槲覀兠恳淮尾僮鲾?shù)據(jù)庫，performance_schema 都需要額外地統(tǒng)計(jì)這些信息，所以我們打開這個(gè)統(tǒng)計(jì)功能是有性能損耗的。如果打開所有的 performance_schema 項(xiàng)，性能大概會(huì)下降 10% 左右。所以，我建議你只打開自己需要的項(xiàng)進(jìn)行統(tǒng)計(jì)。你可以通過下面的方法打開或者關(guān)閉某個(gè)具體項(xiàng)的統(tǒng)計(jì)。

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

　　mysql> update setup_instruments set enAbLed='Yes', timed='Yes' where name like '%wait/io/file/innodb/innodb_log_file%';

　　假設(shè)，現(xiàn)在你已經(jīng)開啟了 redo log 和 binlog 這兩個(gè)統(tǒng)計(jì)信息，那要怎么把這個(gè)信息用在實(shí)例狀態(tài)診斷上呢？很簡單，你可以通過 mAX_timeR 的值來判斷數(shù)據(jù)庫是否出問題了。比如，你可以設(shè)定閾值，單次 io 請(qǐng)求時(shí)間超過 200 毫秒屬于異常，然后使用類似下面這條語句作為檢測(cè)邏輯。

　　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ā)現(xiàn)異常后，取到你需要的信息，再通過下面這條語句把之前的統(tǒng)計(jì)信息清空。這樣如果后面的監(jiān)控中，再次出現(xiàn)這個(gè)異常，就可以加入監(jiān)控累積值了。

　　mysql> truncate table performance_schema.file_summary_by_event_name;

　　YINGSOO專業(yè)提供海外云服務(wù)器，包括亞洲、歐美等全球范圍內(nèi)的服務(wù)器租用、托管業(yè)務(wù)，云主機(jī)節(jié)點(diǎn)覆蓋全球，現(xiàn)擁有20多個(gè)節(jié)點(diǎn)并持續(xù)增加中，1對(duì)1客服專屬服務(wù)、瞬時(shí)響應(yīng)，365*7*24h售后保障。客服熱線：400-630-3752

　　熱門文章：【排序字段】【香港系統(tǒng)】【制表簡寫】【數(shù)據(jù)庫指定】【輸入設(shè)備】【密碼錯(cuò)誤】【存儲(chǔ)過程參數(shù)】【商標(biāo)注冊(cè)】【重置密碼】【服務(wù)器租用】【事件系統(tǒng)】【網(wǎng)站支持】【文件配置】【項(xiàng)目鏡像】【優(yōu)化網(wǎng)站】【游標(biāo)臨時(shí)】【數(shù)據(jù)提交】【通信文件】【文件掃描】【這類提供商】【指引虛擬主機(jī)】【模板文件】【光學(xué)元件】【搭載都是】【死鎖事務(wù)】【羅馬尼亞服務(wù)】【代碼服務(wù)器】【流光掃描器】【紅星朝鮮】【香港主機(jī)】

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。