解決一個 MySQL 服務器進程 CPU 占用 100%的技術筆記

2024-07-24 12:43:29

字體：大中小

來源：轉載

供稿：網友

早上幫朋友一臺服務器解決了 Mysql cpu 占用 100% 的問題。稍整理了一下：

朋友主機(Windows 2003 + IIS + PHP + MYSQL )近來 MySQL 服務進程 (mysqld-nt.exe) CPU 占用率總為 100% 高居不下。此主機有10個左右的 database, 分別給十個網站調用。據朋友測試，導致 mysqld-nt.exe cpu 占用奇高的是網站A，一旦在 IIS 中將此網站停止服務，CPU 占用就降下來了。一啟用，則馬上上升。

MYSQL CPU 占用 100% 的解決過程

今天早上仔細檢查了一下。目前此網站的七日平均日 IP 為2000，PageView 為 3萬左右。網站A 用的 database 目前有39個表，記錄數 60.1萬條，占空間 45MB。按這個數據，MySQL 不可能占用這么高的資源。

于是在服務器上運行命令，將 mysql 當前的環境變量輸出到文件 output.txt：

d:webmysql> mysqld.exe --help >output.txt

發現 tmp_table_size 的值是默認的 32M，于是修改 My.ini, 將 tmp_table_size 賦值到 200M:

d:webmysql> notepad c:windowsmy.ini
[mysqld]
tmp_table_size=200M

然后重啟 MySQL 服務。CPU 占用有輕微下降，以前的CPU 占用波形圖是 100% 一根直線，現在則在 97%~100%之間起伏。這表明調整 tmp_table_size 參數對 MYSQL 性能提升有改善作用。但問題還沒有完全解決。

于是進入 mysql 的 shell 命令行，調用 show processlist, 查看當前 mysql 使用頻繁的 sql 語句：

mysql> show processlist;

反復調用此命令，發現網站 A 的兩個 SQL 語句經常在 process list 中出現，其語法如下：

SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid
ORDER BY t1.pid
LIMIT 0,15

調用 show columns 檢查這三個表的結構 :

mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;

終于發現了問題所在：_mydata 表，只根據 pid 建立了一個 primary key，但并沒有為 userid 建立索引。而在這個 SQL 語句的第一個LEFT JOIN ON 子句中：

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

_mydata 的 userid 被參與了條件比較運算。于是我為給 _mydata 表根據字段 userid 建立了一個索引：

mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )

建立此索引之后，CPU 馬上降到了 80% 左右。看到找到了問題所在，于是檢查另一個反復出現在 show processlist 中的 sql 語句：

SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid=t2.pid and t2.keywords = '孔雀'

經檢查 _mydata_key 表的結構，發現它只為 pid 建了了 primary key, 沒有為 keywords 建立 index。_mydata_key 目前有 33 萬條記錄，在沒有索引的情況下對33萬條記錄進行文本檢索匹配，不耗費大量的 cpu 時間才怪。看來就是針對這個表的檢索出問題了。于是同樣為 _mydata_key 表根據字段 keywords 加上索引: