mysql服務(wù)器cpu爆滿解決辦法
點(diǎn)擊上方藍(lán)字關(guān)注我們
1、通過show processlist找到耗時(shí)最長的
mysql>?show?processlist;
+----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
|?Id?|?User?|?Host????????????|?db???|?Command?|?Time?|?State????????|?Info?????????????????????????????????????????????????????????????????????????????????????????????????|
+----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
|?19?|?root?|?localhost:60604?|?big??|?Query???|?1533?|?Sending?data?|?SELECT?count(*)?num,city?FROM?`ih_user_temp`?where?city?in?(select?city?from?ih_user_temp?where?city?|
|?25?|?root?|?localhost???????|?NULL?|?Query???|????0?|?NULL?????????|?show?processlist?????????????????????????????????????????????????????????????????????????????????????|
+----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
2?rows?in?set?(0.00?sec)
2、先殺掉該進(jìn)程
mysql>?kill?19;
Query?OK,?0?rows?affected?(0.01?sec)
[mysqld]
slow_query_log=1??#開啟慢查詢
long_query_time=5?#慢查詢時(shí)間
log-slow-queries?=?/var/log/mysql/slowquery.log?#需有寫入權(quán)限
4、使用explain 優(yōu)化sql語句，
引起cpu過高的sql一般集中在order by、group by、批量insert、嵌套子查詢等sql語句中
5、調(diào)整my.cnf的query_cache_size和tmp_table_size的值
往期推薦
評(píng)論
圖片
表情