您現在的位置: 365建站網 > 365學習 > mysql占用資源CPU超過100%解決方法

mysql占用資源CPU超過100%解決方法

文章來源:365jz.com     點擊數:295    更新時間:2019-12-19 22:59   參與評論

一個Windows服務器,里面帶了不少網站,但是訪問量并不是很大,基本屬于業務集中型。

解決的過程其實也挺easy,這里記錄一下思路。

首先把mySQL服務器升級到最新的版本,盡量避免是mysql本身問題。


然后在my.ini里面mysqld里面配置:


slow-query-log = 1

slow-query-log-file = slow.log

重啟mysql這樣所有'slow'的都會記錄到日志里面。

 

然后再到慢的時候就能看到具體什么sql引發了慢的問題(slow.log在datadir下),最后發現慢的那個表查詢里面username沒有加索引而且并發不小,開始數據不多還沒問題,后來數據多了些達到了十幾萬的時候,并發查沒索引的表就出來問題了,每次比較十幾萬次字符串是挺要命。

 

給這個字段加上索引,馬上cpu就降下來了。


通過以前對mysql的操作經驗,先將mysql的配置問題排除了,查看msyql是否運行正常,通過查看mysql data目錄里面的*.err文件(將擴展名改為.txt)記事本查看即可。如果過大不建議用記事本了,容易死掉,可以用editplus等工具

簡單的分為下面幾個步驟來解決這個問題:

1、mysql運行正常,也有可能是同步設置問題導致

2、如果mysql運行正常,那就是PHP的一些sql語句導致問題發現,用root用戶進入mysql管理

mysql -u root -p
輸入密碼
show full processlist;


查找負荷最重的 SQL 語句,優化該SQL,比如適當建立某字段的索引。

image.png


image.png

可以看到是下面的SQL語句執行耗費了較長時間。

SELECT id,title,most_top,view_count,posttime FROM article 

where status=3 AND catalog_id in (select catalog_id from catalog where catalog_id=17 or parent_id=17)  

order by most_top desc,posttime desc limit 0,8

但是從數據庫設計方面來說,該做的索引都已經做了,SQL語句似乎沒有優化的空間。

直接執行此條SQL,發現速度很慢,需要1-6秒的時間(跟mysql正在并發執行的查詢有關,如果沒有并發的,需要1秒多)。如果把排序依據改為一個,則查詢時間可以縮短至0.01秒(most_top)或者0.001秒(posttime)。

————————————————

通過這個命令我看到原來是有人惡意刷搜索,因為dedecms搜索后面調用搜索最高的詞,導致很多人用工具刷這個,而且是定時有間隔的,所以將這個php程序改名跳轉都方法解決了。


MYSQL CPU 占用 100% 的現象描述

  早上幫朋友一臺服務器解決了 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:\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 占用有輕微下降,以前的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 加上索引:

mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
  建立此索引之后,CPU立刻降了下來,在 50%~70%之間震蕩。

  再次調用 show prosslist,網站A 的sql 調用就很少出現在結果列表中了。但發現此主機運行了幾個 Discuz 的論壇程序, Discuz 論壇的好幾個表也存在著這個問題。于是順手一并解決,cpu占用再次降下來了。(2007.07.09 附注:關于 discuz 論壇的具體優化過程,我后來另寫了一篇文章,詳見:千萬級記錄的 Discuz! 論壇導致 MySQL CPU 100% 的 優化筆記 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)

 解決 MYSQL CPU 占用 100% 的經驗總結

增加 tmp_table_size 值。mysql 的配置文件中,tmp_table_size 的默認大小是 32M。如果一張臨時表超出該大小,MySQL產生一個 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.

對 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的條件判斷中用到的字段,應該根據其建立索引 INDEX。索引被用來快速找出在一個列上用一特定值的行。沒有索引,MySQL不得不首先以第一條記錄開始并然后讀完整個表直到它找出相關的行。表越大,花費時間越多。如果表對于查詢的列有一個索引,MySQL能快速到達一個位置去搜尋到數據文件的中間,沒有必要考慮所有數據。如果一個表有1000行,這比順序讀取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中存儲。

根據 mysql 的開發文檔:

索引 index 用于:

快速找出匹配一個WHERE子句的行
當執行聯結(JOIN)時,從其他表檢索行。
對特定的索引列找出MAX()或MIN()值
如果排序或分組在一個可用鍵的最左面前綴上進行(例如,ORDER BY key_part_1,key_part_2),排序或分組一個表。如果所有鍵值部分跟隨DESC,鍵以倒序被讀取。

在一些情況中,一個查詢能被優化來檢索值,不用咨詢數據文件。如果對某些表的所有使用的列是數字型的并且構成某些鍵的最左面前綴,為了更快,值可以從索引樹被檢索出來。

假定你發出下列SELECT語句:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一個多列索引存在于col1和col2上,適當的行可以直接被取出。如果分開的單行列索引存在于col1和col2上,優化器試圖通過決定哪個索引將找到更少的行并來找出更具限制性的索引并且使用該索引取行。


事后總結

mysql cpu占用率很高,很有可能是因為查詢時死表,或者說大量多表查詢,導致cpu飚高。

另外也有可能是因為tmp_table_size過大,超出了mysql的內存大小使用設定,mysql會將一些table寫入到磁盤中,這樣也會大大引起cpu的使用率增大




如對本文有疑問,請提交到交流論壇,廣大熱心網友會為你解答!! 點擊進入論壇


發表評論 (295人查看0條評論)
請自覺遵守互聯網相關的政策法規,嚴禁發布色情、暴力、反動的言論。
用戶名: 驗證碼: 點擊我更換圖片
最新評論
------分隔線----------------------------
亚洲色欧美图另类综合