客戶方數(shù)據(jù)庫(kù)服務(wù)器CPU負(fù)載高優(yōu)化案例

背景

上周線上服務(wù)出現(xiàn)一個(gè)問(wèn)題，打開某個(gè)頁(yè)面，會(huì)導(dǎo)致其它接口請(qǐng)求響應(yīng)超時(shí)，排查后發(fā)現(xiàn)數(shù)據(jù)庫(kù)響應(yīng)超400s，之前1s就可查到數(shù)據(jù)。

具體原因是有個(gè)大屏統(tǒng)計(jì)頁(yè)面，會(huì)實(shí)時(shí)查看各業(yè)務(wù)服務(wù)近幾個(gè)月的統(tǒng)計(jì)數(shù)據(jù)，根據(jù)多個(gè)指標(biāo)統(tǒng)計(jì)，最近導(dǎo)入了幾千萬(wàn)數(shù)據(jù)，數(shù)據(jù)量變大了，之前沒(méi)數(shù)據(jù)。

前端頁(yè)面設(shè)置的是5s刷新一次。每次刷新會(huì)有十幾個(gè)SQL發(fā)給Mysql執(zhí)行，導(dǎo)致SQL查詢?nèi)蝿?wù)在排隊(duì)執(zhí)行，所以后面系統(tǒng)其它頁(yè)面全部不可用，SQL等待超時(shí)。

本篇不討論通過(guò)SQL來(lái)做統(tǒng)計(jì)的合理性。只介紹Mysql診斷分析思路。

診斷分析

客戶方Mysql版本8.0.16。

登錄數(shù)據(jù)庫(kù)服務(wù)器

• 登錄服務(wù)器后 top 命令查詢系統(tǒng)負(fù)載，發(fā)現(xiàn) Load Average 第一個(gè)數(shù)值達(dá)25，說(shuō)明系統(tǒng)在過(guò)去一分鐘超過(guò)25個(gè)任務(wù)在等待執(zhí)行。

• Mysql進(jìn)程當(dāng)前占用cpu達(dá)260%（linux服務(wù)器有12核cpu）。

• 登陸Mysql實(shí)例，執(zhí)行 show processlist 查看活動(dòng)連接，看到有400多個(gè)線程（大部分是 sleep 狀態(tài)），其中幾十個(gè)線程在等待執(zhí)行 SQL，有的連接 Time 達(dá)到了1400多秒。

• show global status like '%Thread%'; 查看同時(shí)有40多個(gè)活躍線程正在執(zhí)行。

• 捕獲問(wèn)題 SQL 語(yǔ)句。通過(guò) explain 分析 SQL 發(fā)現(xiàn)基本走的全表掃描。對(duì)一張業(yè)務(wù)表通過(guò) A 業(yè)務(wù)字段 去分組統(tǒng)計(jì)每天的值、每個(gè)月的值，這個(gè) A 存儲(chǔ)的值離散度很低。所以查詢很慢，請(qǐng)求多了，就阻塞了。

解決方案：

1. kill 掉耗時(shí)長(zhǎng)的 SQL 線程，等待一會(huì)后再次通過(guò) top 命令查看 cpu 使用率恢復(fù)正常；

2. 針對(duì)問(wèn)題 SQL 中 某些字段加索引（離散度低，全表統(tǒng)計(jì)，所以效果不明顯）；

3. 和產(chǎn)品確定相關(guān)業(yè)務(wù)，定下來(lái)是代碼加緩存，統(tǒng)計(jì)數(shù)據(jù)延遲更新，不實(shí)時(shí)加載；

4. 通過(guò)慢日志定位慢 sql,找到時(shí)間長(zhǎng)的 sql，逐個(gè)優(yōu)化。

Mysql 參數(shù)調(diào)優(yōu)

參數(shù)優(yōu)化對(duì) Mysql 性能也非常重要，合理配置參數(shù)，可以提升 Mysql 的性能和穩(wěn)定性。

1. 調(diào)大緩存池（InnoDB Buffer Pool）：默認(rèn)128M，通常設(shè)置機(jī)器內(nèi)存的50-80%，假如機(jī)器內(nèi)存是10個(gè)G，設(shè)置5G-8G是合理的，根據(jù)機(jī)器負(fù)載及運(yùn)行的服務(wù)來(lái)定。

2. 調(diào)整 innodb_log_file_size 參數(shù)：該參數(shù)表示redo log的日志大小，此值太小會(huì)造成日志的頻繁切換；值太大，數(shù)據(jù)庫(kù)恢復(fù)時(shí)，會(huì)占用更多時(shí)間。推薦256M ~ 1GB。

3. 設(shè)置 max_connections 值：該值用于設(shè)置數(shù)據(jù)庫(kù)服務(wù)器同時(shí)允許的最大連接數(shù)，我通常設(shè)置在1500左右；

4. wait_timeout 參數(shù)配置：表示連接最長(zhǎng)空閑時(shí)間，默認(rèn)8小時(shí)，超過(guò)8小時(shí) Mysql 則關(guān)閉該連接。建議設(shè)置 1800秒。interactive_timeout 和 wait_timeout要設(shè)置一致，分別代表交互式等待時(shí)間和非交互式等待時(shí)間。

命令詳解

show processlist;

查看活動(dòng)連接和查詢執(zhí)行的命令。

show full processlist命令可以看到完整的SQL語(yǔ)句信息。

返回的信息：

1. Id: 連接的唯一標(biāo)識(shí)符。

2. User: 連接的數(shù)據(jù)庫(kù)用戶。

3. Host: 連接的客戶端主機(jī)名或 IP 地址。

4. db: 連接正在使用的數(shù)據(jù)庫(kù)。

5. Command: 連接當(dāng)前正在執(zhí)行的命令類型，如 Query、Sleep、Connect 等。

6. Time: 查詢已經(jīng)執(zhí)行的時(shí)間（秒），用于判斷是否有長(zhǎng)時(shí)間運(yùn)行的查詢。

7. State: 表示連接狀態(tài)，例如 Sending data、Waiting for table lock 等。

8. Info: 正在執(zhí)行的查詢文本，可以看到具體的 SQL 查詢。

Command 狀態(tài)及含義：

• Sleep：連接處于空閑狀態(tài)，沒(méi)有正在執(zhí)行的查詢。

• Query：連接正在執(zhí)行一個(gè)查詢語(yǔ)句。

• Execute：連接正在執(zhí)行一個(gè)準(zhǔn)備好的語(yǔ)句（prepared statement）。

• Connect：正在與數(shù)據(jù)庫(kù)建立連接。

• Init DB：正在初始化數(shù)據(jù)庫(kù)連接。

• Quit：連接正在關(guān)閉。

• Statistics：正在收集數(shù)據(jù)庫(kù)統(tǒng)計(jì)信息。

• Binlog Dump：正在復(fù)制二進(jìn)制日志。

• Table Dump：正在導(dǎo)出表數(shù)據(jù)。

• Close：連接正在關(guān)閉。

• Change User：正在更改連接的用戶。

• Ping：連接正在執(zhí)行心跳檢測(cè)。

• Kill：正在終止一個(gè)連接。

• Delayed insert：正在執(zhí)行延遲插入。

• Change master：正在更改主服務(wù)器信息。

• Prepare：連接正在準(zhǔn)備一個(gè)語(yǔ)句。

• Daemon：MySQL 服務(wù)器內(nèi)部的守護(hù)進(jìn)程或后臺(tái)線程。

State 狀態(tài)及含義：

• Sending data：連接正在發(fā)送數(shù)據(jù)到客戶端，通常意味著查詢正在執(zhí)行，并且結(jié)果集正在被發(fā)送。

• Waiting for table lock：連接正在等待獲取某個(gè)表的鎖，可能由于其他并發(fā)查詢正在使用該表而導(dǎo)致阻塞。

• Locked：連接被鎖定，可能由于其他并發(fā)操作或事務(wù)而導(dǎo)致。

• Copying to tmp table：連接正在將數(shù)據(jù)復(fù)制到臨時(shí)表，通常在使用臨時(shí)表進(jìn)行排序或分組時(shí)出現(xiàn)。

• Repair by sorting：連接正在執(zhí)行表的修復(fù)操作，并使用排序算法進(jìn)行修復(fù)。

• Repair with keycache：連接正在執(zhí)行表的修復(fù)操作，并使用鍵緩存進(jìn)行修復(fù)。

• Sorting result：連接正在對(duì)結(jié)果集進(jìn)行排序。

• Creating sort index：連接正在創(chuàng)建用于排序的索引。

• Sending cached result：連接正在發(fā)送緩存的結(jié)果集。

• Converting HEAP to MyISAM：連接正在將 HEAP 表轉(zhuǎn)換為 MyISAM 表。

• Waiting for tables：連接正在等待所有表被鎖定，通常在執(zhí)行復(fù)雜查詢時(shí)出現(xiàn)。

• Opening tables：連接正在打開表，通常在查詢開始時(shí)出現(xiàn)。

• Init：連接剛剛創(chuàng)建，還沒(méi)有開始執(zhí)行任何操作。

• Killed：連接的查詢被管理員或其他進(jìn)程終止。

• starting：連接開始的狀態(tài)（瞬時(shí)的，表示連接準(zhǔn)備好執(zhí)行查詢或操作）。

• Waiting on empty queue：表示一個(gè)等待連接的線程在等待事件隊(duì)列為空。（通常會(huì)后臺(tái)線程完成任務(wù)后進(jìn)入此狀態(tài)）

• Sending to client：是一個(gè)連接線程的狀態(tài)，表示該連接正在將查詢結(jié)果數(shù)據(jù)發(fā)送給客戶端。

Top命令

前五行是系統(tǒng)整體的統(tǒng)計(jì)。

第一行：top表頭

• top：顯示 top 命令當(dāng)前的運(yùn)行時(shí)間。

• up：表示系統(tǒng)的運(yùn)行時(shí)間。格式為：天-小時(shí):分鐘。

• 3 users — 當(dāng)前有3個(gè)用戶登錄系統(tǒng)。

• load average：平均負(fù)載。分別表示系統(tǒng)在過(guò)去 1 分鐘、5 分鐘和 15 分鐘內(nèi)的平均負(fù)載。

Load Average 表示系統(tǒng)在不同時(shí)間間隔內(nèi)的平均負(fù)載情況。

第一個(gè)數(shù)值（0.01）：在過(guò)去1分鐘內(nèi)，系統(tǒng)的平均進(jìn)程等待數(shù)為0.01。 第二個(gè)數(shù)值（0.03）：在過(guò)去5分鐘內(nèi)，系統(tǒng)的平均進(jìn)程等待數(shù)為0.01。 第三個(gè)數(shù)值（0.08）：在過(guò)去15分鐘內(nèi)，系統(tǒng)的平均進(jìn)程等待數(shù)為0.05。

之前SQL排隊(duì)的問(wèn)題情況下，第一個(gè)數(shù)字到了33，意味著在過(guò)去1分鐘內(nèi)，系統(tǒng)的平均進(jìn)程等待數(shù)為33。這樣的負(fù)載是非常高的，表示系統(tǒng)在這段時(shí)間內(nèi)有大量的進(jìn)程在等待CPU資源，系統(tǒng)可能會(huì)響應(yīng)緩慢甚至出現(xiàn)性能問(wèn)題。

第二行：任務(wù)（進(jìn)程）

• total：系統(tǒng)中的總進(jìn)程數(shù)。

• running：當(dāng)前正在運(yùn)行的進(jìn)程數(shù)。

• sleeping：當(dāng)前休眠的進(jìn)程數(shù)。

• stopped：停止的進(jìn)程數(shù)。

• zombie：僵尸進(jìn)程數(shù)。

系統(tǒng)共有244個(gè)進(jìn)程，其中處于運(yùn)行中的有1個(gè)，242個(gè)在休眠（sleep），stoped狀態(tài)的有1個(gè)，zombie狀態(tài)（僵尸）的有0個(gè)。

第三行：CPU 使用情況

• us：用戶態(tài)使用 CPU 的百分比。

• sy：系統(tǒng)態(tài)使用 CPU 的百分比。

• ni：修改過(guò)優(yōu)先級(jí)的進(jìn)程使用 CPU 的百分比。

• id：空閑 CPU 百分比。

• wa：等待磁盤 I/O 的 CPU 百分比。

• hi：硬中斷（Hardware IRQ）占用CPU的百分比。

• si：軟中斷（Software Interrupts）占用CPU的百分比。

• st：虛擬機(jī)等待CPU時(shí)間的百分比（也就是宿主機(jī)從當(dāng)前虛擬機(jī)偷取的CPU時(shí)間）。

第四行：KiB Mem（內(nèi)存使用情況）

• total：總內(nèi)存量。

• used：已使用的內(nèi)存量。

• free：空閑內(nèi)存量。

• buffers：用于緩沖的內(nèi)存量。

• cached：用于緩存的內(nèi)存量。

24521472k total — 物理內(nèi)存總量（23.38GB）。
4434672k used — 使用中的內(nèi)存總量（4.23GB）。
10843452k free — 空閑內(nèi)存總量（10.34G）。
9243348k buffers — 緩存的內(nèi)存量（8.81GB）。

第五行：KiB Swap（交換空間使用情況）

• total：總交換空間大小。

• used：已使用的交換空間大小。

• free：剩余的交換空間大小。

• available：可用的交換空間大小。

5242876k total — 交換區(qū)總量（5GB）。
4615932k used — 使用的交換區(qū)總量（4.40GB）。
626944k free — 空閑交換區(qū)總量（612MB） 13232772k cached — 緩沖的交換區(qū)總量（12.61GB）

各進(jìn)程（任務(wù)）的狀態(tài)監(jiān)控

• PID：進(jìn)程ID。

• USER：進(jìn)程所有者。

• PR：進(jìn)程優(yōu)先級(jí)。

• NI：nice值。負(fù)值表示高優(yōu)先級(jí)，正值表示低優(yōu)先級(jí)。

• VIRT：進(jìn)程使用的虛擬內(nèi)存總量，單位kb。

• RES：進(jìn)程使用的、未被換出的物理內(nèi)存大小，單位kb。

• SHR：共享內(nèi)存大小，單位kb。

• S：進(jìn)程狀態(tài)。D（不可中斷的睡眠狀態(tài)）、R（運(yùn)行）、S（睡眠）、T（跟蹤/停止）、Z（僵尸進(jìn)程）。

• %CPU：進(jìn)程當(dāng)前占用CPU資源的百分比。

• %MEM：進(jìn)程當(dāng)前使用的物理內(nèi)存百分比。

• TIME+：進(jìn)程使用的CPU時(shí)間總計(jì)。

• COMMAND：進(jìn)程名稱（命令名/命令行）。

圖中 mysql進(jìn)程 TIME+ 列顯示：140129:37 ，表示進(jìn)程在CPU上的累計(jì)運(yùn)行時(shí)間為 140129 分鐘 37 秒。

總結(jié)

本篇只介紹了 top 命令，我們還可以用 vmstat 和 mnon 來(lái)監(jiān)控系統(tǒng)性能。

vmstat：

用于查看系統(tǒng)虛擬內(nèi)存和系統(tǒng)資源使用情況的命令。它提供了對(duì)系統(tǒng)內(nèi)存、CPU、交換空間等性能指標(biāo)的實(shí)時(shí)監(jiān)控。

mnon：

是一個(gè)虛擬內(nèi)存性能監(jiān)控工具，它用于監(jiān)控和診斷 Linux 系統(tǒng)上的內(nèi)存和交換空間使用情況。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-612810.html

到了這里，關(guān)于客戶方數(shù)據(jù)庫(kù)服務(wù)器CPU負(fù)載高優(yōu)化案例的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！