Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【MySQL】MySQL-IO-影响应用访问慢

09 Mar 2019 » MySQL

案例分析

查看nginx日志

2019/03/05 00:17:58 [error] 9807#0: *119208865 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 220.181.108.94, server: www.escience.cn, req
uest: "GET /people/dudongsheng/index.html HTTP/1.1", upstream: "http://10.10.1.151:80/people/dudongsheng/index.html", host: "www.escience.cn"

查看负载

[root@rabbit-04-22 logs]# top

从mysql数据库服务器来看,负载比平时高,有io wait 的现象。

查看数据库的SQL语句

mysql> show processlist;
+------+-----------+-------------------+-------+---------+------+-------+------------------+
| Id   | User      | Host              | db    | Command | Time | State | Info             |
+------+-----------+-------------------+-------+---------+------+-------+------------------+
| 1136 | root      | localhost         | dhome | Query   |    0 | NULL  | show processlist | 
| 1184 | dhomeuser | 10.10.1.151:52687 | dhome | Sleep   |   36 |       | NULL             | 
| 1241 | dhomeuser | 10.10.1.151:44597 | dhome | Sleep   |    0 |       | NULL             | 

其中有select * from access_log order by xxx limit xx语句.

解决方法

其中这张表的数据有700多万,对这种表的数据做归档,然后truncate table access_log. 应用恢复正常。