mysql服务器cpu爆满解决办法

 

       1、通过show processlist找到耗时最长的

?

1 2 3 4 5 6 7 8

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、先杀掉该进程

?

1 2	mysql> kill 19; Query OK, 0 rows affected (0.01 sec)

3、通过慢查询日志找到具体的sql语句

开启慢查询：

?

1 2 3 4

[mysqld] slow_query_log=1  #开启慢查询 long_query_time=5 #慢查询时间 log-slow-queries = /var/log/mysql/slowquery.log #需有写入权限

4、使用explain 优化sql语句，

引起cpu过高的sql一般集中在order by、group by、批量insert、嵌套子查询等sql语句中

5、调整my.cnf的query_cache_size和tmp_table_size的值