詳細介紹Mysql佔用過高CPU時的最佳化手段

下面小編就為大家帶來一篇Mysql佔用過高CPU時的最佳化手段(必看)。小編覺得蠻不錯的，現在就分享給大家，也給大家做個參考。一起跟著小編過來看看吧

Mysql佔用CPU過高的時候，該從哪些方面下手進行最佳化？

佔用CPU過高，可以做如下考慮：

1）一般來講，排除高並發的因素，還是要找到導致你CPU過高的哪幾條在執行的SQL，show processlist語句，查找負載最重的SQL語句，優化該SQL，例如適當建立某字段的索引；

2）開啟慢查詢日誌，將那些執行時間過長且佔用資源過多的SQL拿來進行explain分析，導致CPU過高，多數是GroupBy、OrderBy排序問題所導致，然後慢慢進行最佳化改進。例如最佳化insert語句、最佳化group by語句、最佳化order by語句、最佳化join語句等；

3）考慮定時最佳化檔案及索引；

4）定期分析表，使用optimize table;

5）最佳化資料庫物件；

6）考慮是否為鎖定問題；

7）調整一些MySQL Server參數，例如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等；

8）如果資料量過大，可以考慮使用MySQL叢集或建置高可用環境。

9）可能由於記憶體latch（洩漏）導致資料庫CPU高

10）在多用戶高並發的情況下，任何系統都會hold不住的，所以，使用快取是必須的，使用memcached或redis快取都可以；

11）看看tmp_table_size大小是否偏小，如果允許，適當的增大一點；

12）如果max_heap_table_size配置的過小，增加一點；

13）mysql的sql語句睡眠連接逾時時間設定問題（wait_timeout）

14）使用show processlist查看mysql連接數，看看是否超過了mysql設定的連接數字

下面分享一例遇到過的案例：

網站在高峰時段訪問，點擊頁面有點卡。登陸伺服器，發現機器負載有點高，且mysql佔用了很高的CPU資源，如下圖：

MySQL負載居高不下，如果開啟了慢查詢日誌功能，最好的方法就是針對慢查詢日誌裡執行慢的sql語句進行最佳化，如果sql語句用了大量的group by等語句，union#聯合查詢等肯定會將mysql的佔用率提高。所以就需要最佳化sql語句

除了最佳化sql語句外，也可以做一些設定上的最佳化。在mysql中執行show proceslist;出現下面回顯結果：

1.查詢有大量的Copying to tmp table on disk狀態

明顯是由於臨時表過大導致mysql將臨時表寫入硬碟影響了整體效能。

Mysql中tmp_table_size的預設值僅為16MB，在目前的情況下顯然是不夠用的。

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmp
dir
 | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in 
set
 (0.00 sec)

解決方法：調整臨時表大小

#1）進mysql終端指令修改，加上global,下次進mysql就會生效

mysql> set global tmp_table_size=33554432;
Query OK, 0 rows affected (0.00 sec)

再次登陸mysql

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in set (0.01 sec)

2）my.cnf設定檔修改

[root@www ~]# vim my.cnf
.....
tmp_table_size = 32M

重啟mysql

[root@www ~]# /etc/init.d/mysqld restart

2.show processlist;指令的輸出結果顯示了有哪些執行緒在運行，可以幫助辨識出有問題的查詢語句。例如下面結果：

Id User Host db Command Time State Info 
207 root 192.168.1.25:51718 mytest Sleep 5 
NULL

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库 。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

常见问题：

一般是睡眠连接过多，严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

解决办法 ：

在mysql的配置my.cnf文件中，有一项wait_timeout参数设置.即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止。
wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题。
通常来说，把wait_timeout设置为10小时是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接。

然而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致下面的报错：

The last packet successfully received from the server was 596,688 milliseconds ago.
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

28800seconds，也就是8小时。

如果在wait_timeout秒期间内，数据库连接(java.sql.Connection)一直处于等待状态，mysql就将该连接关闭。这时，你的Java应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时，就碰到上述错误。
可以将mysql全局变量wait_timeout的缺省值改大。

查看mysql手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。

比如将其改成30天

mysql> set global wait_timeout=124800;
Query OK, 0 rows affected (0.00 sec)

以上是詳細介紹Mysql佔用過高CPU時的最佳化手段的詳細內容。更多資訊請關注PHP中文網其他相關文章！