(Windows 2003 + IIS + PHP + MYSQL )近來 MySQL 服務進程 (mysqld-nt.exe) CPU 占用率總爲 100% 高居不下(xià)。此主機有10個左右的 database, 分(fēn)别給十個網站調用。據朋友測試，導緻 mysqld-nt.exe cpu 占用奇高的是網站A，一(yī)旦在 IIS 中(zhōng)将此網站停止服務，CPU 占用就降下(xià)來了。一(yī)啓用，則馬上上升。
MYSQL CPU 占用 100% 的解決過程
今天早上仔細檢查了一(yī)下(xià)。目前此網站的七日平均日 IP 爲2000，PageView 爲 3萬左右。網站A 用的 database 目前有39個表，記錄數 60.1萬條，占空間 45MB。按這個數據，MySQL 不可能占用這麽高的資(zī)源。
于是在服務器上運行命令，将 mysql 當前的環境變量輸出到文件 output.txt：
d:\web\mysql> mysqld.exe --help >output.txt
發現 tmp_table_size 的值是默認的 32M，于是修改 My.ini, 将 tmp_table_size 賦值到 200M:
d:\web\mysql> notepad c:\windows\my.ini [mysqld] tmp_table_size=200M
然後重啓 MySQL 服務。CPU 占用有輕微下(xià)降，以前的CPU 占用波形圖是 100% 一(yī)根直線，現在則在 97%~100%之間起伏。這表明調整 tmp_table_size 參數對 MYSQL 性能提升有改善作用。但問題還沒有完全解決。
于是進入 mysql 的 shell 命令行，調用 show processlist, 查看當前 mysql 使用頻(pín)繁的 sql 語句：
mysql> show processlist; 反複調用此命令(每秒刷兩次），發現網站 A 的兩個 SQL 語句經常在 process list 中(zhōng)出現，其語法如下(xià)：
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 建立了一(yī)個 primary key，但并沒有爲 userid 建立索引。而在這個 SQL 語句的第一(yī)個 LEFT JOIN ON 子句中(zhōng)：
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata 的 userid 被參與了條件比較運算。于是我(wǒ)(wǒ)爲給 _mydata 表根據字段 userid 建立了一(yī)個索引：
mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` ) 建立此索引之後，CPU 馬上降到了 80% 左右。看到找到了問題所在，于是檢查另一(yī)個反複出現在 show processlist 中(zhōng)的 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 萬條記錄，在沒有索引的情況下(xià)對33萬條記錄進行文本檢索匹配，不耗費(fèi)大(dà)量的 cpu 時間才怪。看來就是針對這個表的檢索出問題了。于是同樣爲 _mydata_key 表根據字段 keywords 加上索引:
mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` ) 建立此索引之後，CPU立刻降了下(xià)來，在 50%~70%之間震蕩。
再次調用 show prosslist，網站A 的sql 調用就很少出現在結果列表中(zhōng)了。但發現此主機運行了幾個 Discuz 的論壇程序， Discuz論壇的好幾個表也存在着這個問題。于是順手一(yī)并解決，cpu占用再次降下(xià)來了。
至此，問題解決。
----------------------------------------------------------------------------------------
解決 MYSQL CPU 占用 100% 的經驗總結
1. 增加 tmp_table_size 值。mysql 的配置文件中(zhōng)，tmp_table_size 的默認大(dà)小(xiǎo)是 32M。如果一(yī)張臨時表超出該大(dà)小(xiǎo)，MySQL産生(shēng)一(yī)個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，增加 tmp_table_size 值。 這是 mysql 官方關于此選項的解釋：
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
2. 對 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中(zhōng)的條件判斷中(zhōng)用到的字段,應該根據其建立索引 INDEX。
索引被用來快速找出在一(yī)個列上用一(yī)特定值的行。沒有索引，MySQL不得不首先以第一(yī)條記錄開(kāi)始并然後讀完整個表直到它找出相關的行。表越大(dà)，花費(fèi)時間越多。如果表對于查詢的列有一(yī)個索引，MySQL能快速到達一(yī)個位置去(qù)搜尋到數據文件的中(zhōng)間，沒有必要考慮所有數據。如果一(yī)個表有1000行，這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹(shù)中(zhōng)存儲。
根據 mysql 的開(kāi)發文檔:
索引 index 用于：
o 快速找出匹配一(yī)個WHERE子句的行
o 當執行聯結(JOIN)時，從其他表檢索行。
o 對特定的索引列找出MAX()或MIN()值
o 如果排序或分(fēn)組在一(yī)個可用鍵的最左面前綴上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分(fēn)組一(yī)個表。如果所有鍵值部分(fēn)跟随DESC，鍵以倒序被讀取。
o 在一(yī)些情況中(zhōng)，一(yī)個查詢能被優化來檢索值，不用咨詢數據文件。如果對某些表的所有使用的列是數字型的并且構成某些鍵的最左面前綴，爲了更快，值可以從索引樹(shù)被檢索出來。
假定你發出下(xià)列SELECT語句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一(yī)個多列索引存在于col1和col2上，适當的行可以直接被取出。如果分(fēn)開(kāi)的單行列索引存在于col1和col2上，優化器試圖通過決定哪個索引将找到更少的行并來找出更具限制性的索引并且使用該索引取行。
開(kāi)發人員(yuán)做 SQL 數據表設計的時候，一(yī)定要通盤考慮清楚。
|
