mysql 服務(wù)器CPU占用過高，如何調(diào)優(yōu)，求助

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

站在用戶的角度思考問題，與客戶深入溝通，找到內(nèi)鄉(xiāng)網(wǎng)站設(shè)計與內(nèi)鄉(xiāng)網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網(wǎng)站設(shè)計、成都網(wǎng)站設(shè)計、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、空間域名、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋內(nèi)鄉(xiāng)地區(qū)。

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

1、mysql運行正常，也有可能是同步設(shè)置問題導(dǎo)致

2、如果mysql運行正常，那就是php的一些sql語句導(dǎo)致問題發(fā)現(xiàn)，用root用戶進入mysql管理

mysql -u root -p

輸入密碼

mysql:show processlist 語句，查找負荷最重的 SQL 語句，優(yōu)化該SQL，比如適當(dāng)建立某字段的索引。

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

當(dāng)然如果你的確實是sql語句用了大量的group by等語句，union聯(lián)合查詢等肯定會將mysql的占用率提高。所以就需要優(yōu)化sql語句，網(wǎng)站盡量生成靜態(tài)的，一般4W ip的靜態(tài)網(wǎng)站，mysql占用率幾乎為0的。所以這對于程序員的經(jīng)驗是個考慮。盡量提高mysql性能 (MySQL 性能優(yōu)化的最佳20多條經(jīng)驗分享)

下面是豆芽收集的文章，大家都可以參考下

MYSQL CPU 占用 100% 的現(xiàn)象描述

早上幫朋友一臺服務(wù)器解決了 Mysql cpu 占用 100% 的問題。稍整理了一下，將經(jīng)驗記錄在這篇文章里

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

MYSQL CPU 占用 100% 的解決過程

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

于是在服務(wù)器上運行命令，將 mysql 當(dāng)前的環(huán)境變量輸出到文件 output.txt：

d:\web\mysql mysqld.exe --help output.txt

發(fā)現(xiàn) tmp_table_size 的值是默認的 32M，于是修改 My.ini, 將 tmp_table_size 賦值到 200M:

d:\web\mysql notepad c:\windows\my.ini

[mysqld]

tmp_table_size=200M

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

于是進入 mysql 的 shell 命令行，調(diào)用 show processlist, 查看當(dāng)前 mysql 使用頻繁的 sql 語句：

mysql show processlist;

反復(fù)調(diào)用此命令，發(fā)現(xiàn)網(wǎng)站 A 的兩個 SQL 語句經(jīng)常在 process list 中出現(xiàn)，其語法如下：

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

調(diào)用 show columns 檢查這三個表的結(jié)構(gòu) :

mysql show columns from _myuser;

mysql show columns from _mydata;

mysql show columns from _mydata_body;

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

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

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

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

建立此索引之后，CPU 馬上降到了 80% 左右?？吹秸业搅藛栴}所在，于是檢查另一個反復(fù)出現(xiàn)在 show processlist 中的 sql 語句：

SELECT COUNT(*)

FROM _mydata AS t1, _mydata_key AS t2

WHERE t1.pid=t2.pid and t2.keywords = '孔雀'

經(jīng)檢查 _mydata_key 表的結(jié)構(gòu)，發(fā)現(xiàn)它只為 pid 建了了 primary key, 沒有為 keywords 建立 index。_mydata_key 目前有 33 萬條記錄，在沒有索引的情況下對33萬條記錄進行文本檢索匹配，不耗費大量的 cpu 時間才怪?？磥砭褪轻槍@個表的檢索出問題了。于是同樣為 _mydata_key 表根據(jù)字段 keywords 加上索引:

mysql ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )

建立此索引之后，CPU立刻降了下來，在 50%~70%之間震蕩。

再次調(diào)用 show prosslist，網(wǎng)站A 的sql 調(diào)用就很少出現(xiàn)在結(jié)果列表中了。但發(fā)現(xiàn)此主機運行了幾個 Discuz 的論壇程序， Discuz 論壇的好幾個表也存在著這個問題。于是順手一并解決，cpu占用再次降下來了。(2007.07.09 附注：關(guān)于 discuz 論壇的具體優(yōu)化過程，我后來另寫了一篇文章，詳見：千萬級記錄的 Discuz! 論壇導(dǎo)致 MySQL CPU 100% 的 優(yōu)化筆記 )

解決 MYSQL CPU 占用 100% 的經(jīng)驗總結(jié)

增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默認大小是 32M。如果一張臨時表超出該大小，MySQL產(chǎn)生一個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，增加 tmp_table_size 值。

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

根據(jù) mysql 的開發(fā)文檔:

索引 index 用于：

快速找出匹配一個WHERE子句的行

當(dāng)執(zhí)行聯(lián)結(jié)(JOIN)時，從其他表檢索行。

對特定的索引列找出MAX()或MIN()值

如果排序或分組在一個可用鍵的最左面前綴上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。

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

假定你發(fā)出下列SELECT語句：

mysql SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2；

如果一個多列索引存在于col1和col2上，適當(dāng)?shù)男锌梢灾苯颖蝗〕?。如果分開的單行列索引存在于col1和col2上，優(yōu)化器試圖通過決定哪個索引將找到更少的行并來找出更具限制性的索引并且使用該索引取行。

Mysql占用CPU過高如何優(yōu)化

CPU占用過高診斷思路

mpstat -P ALL 1，查看cpu使用情況，主要消耗在sys即os系統(tǒng)調(diào)用上

perf top，cpu主要消耗在_spin_lock

生成perf report查看詳細情況

CPU主要消耗在mutex爭用上，說明有鎖熱點。

采用pt-pmp跟蹤mysqld執(zhí)行情況，熱點主要集中在mem_heap_alloc和mem_heap_free上。

Pstack提供更詳細的API調(diào)用棧

Innodb在讀取數(shù)據(jù)記錄時的API路徑為

row_search_for_mysql --》row_vers_build_for_consistent_read --》mem_heap_create_block_func --》mem_area_alloc --》malloc --》 ?_L_unlock_10151 --》__lll_unlock_wait_private

row_vers_build_for_consistent_read會陷入一個死循環(huán)，跳出條件是該條記錄不需要快照讀或者已經(jīng)從undo中找出對應(yīng)的快照版本，每次循環(huán)都會調(diào)用mem_heap_alloc/free。

而該表的記錄更改很頻繁，導(dǎo)致其undo history list比較長，搜索快照版本的代價更大，就會頻繁的申請和釋放堆內(nèi)存。

Linux原生的內(nèi)存庫函數(shù)為ptmalloc，malloc/free調(diào)用過多時很容易產(chǎn)生鎖熱點。

當(dāng)多條 SQL 并發(fā)執(zhí)行時，會最終觸發(fā)os層面的spinlock，導(dǎo)致上述情形。

解決方案

將mysqld的內(nèi)存庫函數(shù)替換成tcmalloc，相比ptmalloc，tcmalloc可以更好的支持高并發(fā)調(diào)用。

修改my.cnf，添加如下參數(shù)并重啟

[mysqld_safe]malloc-lib=tcmalloc

上周五早上7點執(zhí)行的操作，到現(xiàn)在超過72小時，期間該實例沒有再出現(xiàn)cpu長期飆高的情形。

以下是修改前后cpu使用率對比

mysqlcpu平均使用率

MySQL是一款常用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，它的 CPU 平均使用率指的是 MySQL 進程在某一時間段內(nèi) CPU 的平均使用率。通常情況下，MySQL CPU 平均使用率應(yīng)該保持在合理的范圍內(nèi)，過高的 CPU 使用率可能會導(dǎo)致 MySQL 性能下降。

如果你想要查看 MySQL CPU 平均使用率，可以使用以下方法：

使用 MySQL 的狀態(tài)監(jiān)控工具：MySQL 提供了一些狀態(tài)監(jiān)控工具，如 mysqladmin、mysqltuner 等，這些工具可以幫助你查看 MySQL 的 CPU 使用情況。