Mysql佔用過高CPU時的優化手段

散盡浮華發表於2016-12-27

 

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設定的連線數(http://www.cnblogs.com/kevingrace/p/6226324.html

下面分享一例遇到過的案例:
網站在高峰時段訪問,點選頁面有點卡。登陸伺服器,發現機器負載有點高,並且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_tmpdir | /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)

 

相關文章