Mysql慢查询导致db卡 diskIO高

来自linux中国网wiki
跳到导航 跳到搜索

CPU过度处理


注意  除了慢查询  可能 SQL的查询频率极高 也是常见的

#别人的
由于之前出现过由于用户的使用过于频繁,而导致的表数据量过大引起的数据查询缓慢的问题,所以在此CPU彪高之前,已经频繁使用 show processlist 查看过执行时间过长的SQL,并且皆已经做了相对应的处理(相关的业务皆已经分表分层,以及都增加了相关的表索引),所以,我在人工观察 show processlist的过程当中,的确没有再出现过 Query过程中Time占用时间很长的情况;
 
并且查看了对应的 mysql-slow.log 日志(在最初部署Mysql的同时,MySql的配置中已经开启过了慢SQL记录的日志功能),查询了对应的慢SQL的记录日志后,的确是不存在在当前CPU彪高的时间点中存在慢SQL查询的情况;
 
但始终就一直没怀疑,是否是并发过高,而导致的MySQL CPU问题,其实LZ最初也怀疑过,难道是应用程序的并发过高而导致的吗?但简单想了一下,当前的应用程序并还没有到达完全的负载状态,理论上MySQL这样一个成熟的DB,不至于如此便随意彪高了,虽然是在简单的回想,但手上的动作还是敲起了熟悉的命令:show full processlist ,因为如果并发高的情况下,采取每1秒获取一次正在运行的线程数量的方式(采用SQL脚本for输出),可以判断出是否存在运行线程递增的情况。

此时则发现了一个新的问题,我每一次不管任何时候查询当前正在运行的线程,都会看到这样一个SQL在执行中过程中,尽管对应的Time查询时间很短,该线程的ID便直接结束了,但意外的是,每一次查看时,都能看到该SQL在运行中;换句话理解则是,该SQL查询结果是很快的,但是不管在任何一个时间查看线程时都能看到该SQL正在执行中,这只能说明一个问题,,那就是该SQL的查询频率极高!高到每毫秒不间断的都一直在重新查询该SQL;

iotop  htop 

MySql CPU彪高到百分之1000的排查思路

2017故障

磁盘I/O很高无非是下面几个原因引起:

磁盘子系统设备性能差,或采用ext2/ext3之类文件系统,或采用cfq之类的io scheduler,所以IOPS提上不去;
SQL效率不高,比如没有索引,或者一次性读取大量数据,所以需要更多的I/O;
可用内存太小,内存中能缓存/缓冲的数据不多,所以需要更多的I/O。

处理过程无非就是  iotop ,打开 slow query log 然后 show full processlist

mysql(root (none))>show variables like '%slow%';
+---------------------+----------------------------------------------------------+
| Variable_name       | Value                                                    |
+---------------------+----------------------------------------------------------+
| log_slow_queries    | OFF                                                      |
| slow_launch_time    | 2                                                        |
| slow_query_log      | OFF                                                      |
| slow_query_log_file | /mysql/data/VM_slow.log |
+---------------------+----------------------------------------------------------+
##由上面可见 没打开 ,自己临时打开一下 
 set global slow_query_log='ON';

##最长的那些一般就是了 这里为了安全我 省略了 sorry 
mysql(root (none))>show  full processlist;
+---------+-----------------+--------------------+----------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------------------------------
| Id      | User            | Host               | db       | Command | Time    | State                  | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------+-----------------+--------------------+----------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------------------------------
|       1 | event_scheduler | localhost          | NULL     | Daemon  | 5602073 | Waiting on empty queue | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 2062319 | root            | 127.0.0.1:69    | games | Sleep   |      65 |                        | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
+---------+-----------------+--------------------+----------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------------------------------

以前的

mysql慢查询导致db卡 diskIO高
今天海岛反映有几个服卡
前提知识

mysql> show variables like '%slow%';
+——————+——-+
| Variable_name | Value |
+——————+——-+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+——————+——-+
2 rows in set (0.00 sec)

show variables like ‘%log%’;

#########
1、开启慢查询
找到 MySQL 的配置文件 ,my.cnf (Windows 为 my.ini ),在 MySQL 下增加下面几行:
添加在 [mysqld]后面
long_query_time = 0.01
#long_query_time = 1
slow_query_log=1
slow_query_log_file = /var/log/mysql/slow.log
# 这个在 mysql5.1测试过不行
#log-slow-queries= /usr/var/slowquery.log
上面的 2 是查询的时间,即当一条 SQL 执行时间超过2秒的时候才记录,/usr/var/slowquery.log 是日志记录的位置。
然后重新启动MySQL服务

或者临时打开 
set global slow_query_log='ON';

2、 MySQL 配置文件的位置
Windows:Windows 的配置文件为 my.ini,一般在 MySQL 的安装目录下或者 c:Windows 下。
Linux:Linux 的配置文件为 my.cnf ,一般在 /etc 下。
####################

处理
mysql -uroot -p -P3330 -h 10.142.30.67 -e 'show full processlist' >s11_processlist.txt
mysql -uroot -p -P3330 -h 10.142.30.67 -e 'show processlist' >s11min_processlist.txt
这样就可以得知哪些慢的东西 了

数据中心采集的问题,有可能会导致全服都卡的。
s11 s70 s97 s128 s135 db卡
后来 下午也卡 但没有lock 什么 的 就判定是网络喽

reference

mysql show processlist

Mysql慢查询slow query log

mysql占用CPU超过100%解决过程

导致MySQL数据库服务器磁盘I/O高

一次服务器IO占用率高的定位分析

MySql语句性能问题定位--从sql语句到磁盘IO检查

MySQL抓包工具:MySQL Sniffer【转

MySQL占用IO过高解决方案 http://www.111cn.net/database/mysql/116614.htm

MySQL慢查询日志与磁盘IO http://blog.csdn.net/qq_33290787/article/details/51916366

mysql 慢查询测试 http://blog.linuxchina.net/?p=1574

Mysql 慢查询和慢查询日志分析 http://www.cnblogs.com/wrmfw/archive/2011/09/05/2166929.html

reference 2022

MySql CPU彪高到百分之1000的排查思路