mysql cpu爆满了,又告警了
步骤: 1、通过show processlist找到耗时最长的 - 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、先杀掉该进程 - mysql> kill 19;
- Query OK, 0 rows affected (0.01 sec)
复制代码
3、通过慢查询日志找到具体的sql语句 开启慢查询: - [mysqld]
- slow_query_log=1 #开启慢查询
- long_query_time=5 #慢查询时间
- log-slow-queries = /var/log/mysql/slowquery.log #需有写入权限
复制代码
引起cpu过高的sql一般集中在order by、group by、批量insert、嵌套子查询等sql语句中
5、调整my.cnf的query_cache_size和tmp_table_size的值
|