一次MySQL慢查询导致的故障

我们知道分析MySQL语句查询性能的方法除了使用EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。

一、 起因
研发反应某台数据库僵死，后面的会话要么连接不上，要么要花费大量的时间返回结果，哪怕是一个简单的查询。

二、 处理
首先去监控平台查看服务器以及数据库状态，发现这台数据库有大量的慢查询。继续看服务器监控，CPU 平均使用率较高，IO 读写平均值正常。登录到 MySQL，使用 SHOW PROCESSLIST 查看会话状态，总数居然有 600+，这是很不正常的。查看慢查询日志，发现出问题的 SQL 主要集中在几个，有 SUM、有 COUNT、有等值操作等等。这台 MySQL 服务器的 long_query_time 设置为 3秒，而一个简单的查询却要几十秒，这显然是有问题的。写脚本试着 kill 掉相关的会话，发现于事无补，仍然有大量的连接进来。此时使用 top 查看服务器状态，mysqld 进程占用内存和 CPU 居高不下。

故障期间的慢查询数，如图：

CPU 平均使用率，如图：

接着使用 SHOW FULL PROCESSLIST 查看完整状态，在最上面居然发现几条 SQL。这些 SQL 操作使用子查询实现，TIME 列居然达到了 30000 秒，折算过来差不多 10 小时。EXPLAIN 这些语句，居然出现了 USING TEMPORY 和 USING FILESORT，可以看出这些语句是很糟糕的。于是跟开发确认，紧急把这些会话 kill 掉。稍等片刻，会话数立马降下来，只有 100+，top 查看 mysqld 进程，内存和 CPU 都呈现下降的趋势。接着分析开发说上午 9 时写了这些 SQL，发现有问题，注释掉了。新的代码虽然没有此类 SQL，但之前建立的连接并不会释放。解决问题和出现问题的时间差刚好可以和添加子查询的时间对应，就可以确认子查询是此次故障的罪魁祸首。

三、 总结
通过这个故障，总结如下几点：

  • MySQL 应该尽量避免使用子查询，即使使用，也要搞清楚大表和小表的关系；
  • 出现这类问题的排查步骤：

第一，查看服务器监控和 MySQL 监控，分析服务器以及 MySQL 性能，找出异常；
第二，如果是慢查询导致，查看慢查询日志，找出出现问题的 SQL，试着优化，或者把结果缓存；
第三，分清主次，先解决大块问题，后解决细小问题。 把大块的异常解决，小问题就迎刃而解了。比如本文中的例子，把耗费时间长的会话 kill 掉后，后面的连接就正常了；
第四，总结分析。

  • 高效的沟通会事半功倍；
  • DBA 需要定期给出 Top N SQL（类 Oracle 的说法），提供给开发，并协助优化；
  • 查看监控时，不管是服务器监控还是 MySQL 监控，需要做对比，比如和昨天甚至前天的同一时间对比，这会更加快速地定位问题。

四、 技巧
最后，附上一个快速kill 掉 MySQL 会话的方法：

首先使用如下语句分析出有问题的 SQL：

/usr/local/mysql/bin/mysql -uroot -p'XXX' \ -e "SHOW FULL PROCESSLIST;" | more

然后将 SHOW FULL PROCESSLIST 的结果保存到一个文件：

/usr/local/mysql/bin/mysql -uroot -p'XXX' \
-e "SHOW FULL PROCESSLIST;" | \
grep "XXX" | awk '{print $1}' > mysql_slow.txt

最后使用如下简单的 Shell 脚本 kill 掉相关会话：

SELECT concat('kill ',id,';')
FROM information_schema.processlist
WHERE info like 'XXX';

当然也可以使用如下 SQL 拼接 kill 语句：

SELECT concat('kill ',id,';')
FROM information_schema.processlist
WHERE info like 'XXX';

本文对MySQL慢查询导致故障的起因，处理方法，所需的技巧进行了全面分析，希望可以让大家更好的了解MySQL慢查询，对大家的。

赞 (0)

相关推荐

  • mysqldumpslow用法示例(慢查询)

    mysqldumpslow用法示例(慢查询)

    mysqldumpslow是mysql自带的用来分析慢查询的工具 经常使用几个命令 -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default -t NUM just show the top n queries -g PATTERN grep: only consider stmts that include this string -r Reverse the sort order -s,是order的顺序,主要有 c

  • mysql5.7.10开启慢查询详解

    如下所示: #在/etc/my.cnf中的[mysqld]中加入如下代码: slow-query-log=On slow_query_log_file=/data/mysql/log/mysql_slow_query.log long_query_time=2 log_queries_not_using_indexes = ON 第一句是开启慢查询 第二句是用来定义慢查询日志的路径 第三句是用来定义查过多少秒的查询算是慢查询,我这里定义的是2秒 第四句就是记录下没有使用索引的query 以上这篇

  • MySQL启用慢查询日志记录方法

    MySQL启用慢查询日志记录方法

    在MySQL中,慢查询的界定时间是由MySQL内置参数变量long_query_time来指定的,其默认值为10(单位:秒),我们可以通过show variables like 'long_query_time';指令来查看该参数变量的信息: long_query_time的默认值为10秒 不过,在程序开发过程中,我们认为慢速查询的界定时间并没有10秒这么长,依据不同项目的不同需求,我们一般将慢查询的界定时间设定为1~5秒之间.我们可以使用指令set long_query_time = 秒数来设

  • MySQL如何清空慢查询文件

    MySQL如何清空慢查询文件

    一.概述 本章主要写当慢查询文件很大的时候怎样在线生成一个新的慢查询文件. 测试环境:mysql 5.6.21 二.步骤 配置慢查询 默认的my.cnf文件在/etc/目录下 vim /etc/my.cnf #slow_query slow_query_log=1#开启慢查询 slow_query_log_file=/var/lib/mysql/mysql-slow.log #设置慢查询路径 long_query_time=0.01#设置慢查询时间 配置完要重启mysql服务才生效 查询慢查询配

  • 详解mysql数据库如何开启慢查询日志

    数据库开启慢查询日志 修改配置文件 在配置文件my.ini中加上下面两句话 log-slow-queries = C:\xampp\mysql_slow_query.log long_query_time=3 第一句使用来定义慢查询日志的路径(若是linux系统,会涉及权限问题) 第二句使用来定义用时超过过多少秒的查询是慢查询,单位:秒. 查看配置验证是否配置成功: //查看慢查询时间,单位:s show variables like "long_query_time"; //查看慢查

  • Mysql慢查询操作梳理总结

    Mysql慢查询解释 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中.long_query_time的默认值为10,意思是运行10S以上的语句.默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响.慢查询日志支持将日志记录写入文件,也支持将

  • MySQL慢查询之开启慢查询

    一.慢查询有什么用? 它能记录下所有执行超过long_query_time时间的SQL语句, 帮你找到执行慢的SQL, 方便我们对这些SQL进行优化. 二.参数说明 slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录) long_query_time 查询超过多少秒才记录 三.设置步骤 1.查看慢查询相关参数 mysql> show variables like

  • 一次MySQL慢查询导致的故障

    一次MySQL慢查询导致的故障

    我们知道分析MySQL语句查询性能的方法除了使用EXPLAIN 输出执行计划,还可以让MySQL记录下查询超过指定时间的语句,我们将超过指定时间的SQL语句查询称为"慢查询". 一. 起因 研发反应某台数据库僵死,后面的会话要么连接不上,要么要花费大量的时间返回结果,哪怕是一个简单的查询. 二. 处理 首先去监控平台查看服务器以及数据库状态,发现这台数据库有大量的慢查询.继续看服务器监控,CPU 平均使用率较高,IO 读写平均值正常.登录到 MySQL,使用 SHOW PROCESSL

  • MYSQL子查询和嵌套查询优化实例解析

    查询游戏历史成绩最高分前100 Sql代码 SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits) FROM cdb_playsgame ps1 where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3' GROUP BY ps.uid order by ps.credits desc LIMIT 100; Sql代码 SEL

  • MySQL子查询用法实例分析

    本文实例讲述了MySQL子查询用法.分享给大家供大家参考,具体如下: 假设表my_tbl包含三个字段a,b,c:现在需要查询表中列a的每个不同值下的列b为最小值的记录量. 比如表记录为: a  b  c 1  3  'cd' 2  3  'nhd' 1  5  'bg' 2  6  'cds' 1  7  'kiy' 3  7  'vsd' 3  8  'ndf' 希望得到结果为: a  b  c 1  3  'cd' 2  3  'nhd' 3  7  'vsd' (1) 其中一个做法:先查出

  • 查找MySQL中查询慢的SQL语句方法

    查找MySQL中查询慢的SQL语句方法

    如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL .下面介绍MySQL中如何查询慢的SQL语句 一.MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句 1,slow_query_log 这

  • 详解使用navicat连接远程linux mysql数据库出现10061未知故障

    使用使用navicat连接远程linux mysql数据库出现10061未知故障,设置使用ssh连接后出现2013故障 本机环境:win10 navicat premium mysql数据库主机环境:Linux version 4.15.0-42-generic (buildd@lgw01-amd64-023) (gcc version 7.3.0 (Ubuntu 7.3.0-16ubuntu3)) #45-Ubuntu SMP Thu Nov 15 19:32:57 UTC 2018 mysq

  • 详解MySQL子查询（嵌套查询）、联结表、组合查询

    一.子查询 MySQL 4.1版本及以上支持子查询 子查询:嵌套在其他查询中的查询. 子查询的作用: 1.进行过滤: 实例1:检索订购物品TNT2的所有客户的ID = + 一般,在WHERE子句中对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询. 注意:列必须匹配 --在WHERE子句中使用子查询(如这里所示),应该保证SELECT语句具有与WHERE子句中相同数目的列.通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列. 示例2:返回订购

  • MySQL 如何查询当前最新事务ID

    写在前面:在个别时候可能需要查看当前最新的事务 ID,以便做一些业务逻辑上的判断(例如利用事务 ID 变化以及前后时差,统计每次事务的响应时长等用途). 通常地,我们有两种方法可以查看当前的事务 ID: 1.执行 SHOW ENGINE INNODB STATUS,查看事务相关信息 ===================================== 150303 17:16:11 INNODB MONITOR OUTPUT ================================

  • MySQL 分组查询和聚合函数

    概述 相信我们经常会遇到这样的场景:想要了解双十一天猫购买化妆品的人员中平均消费额度是多少(这可能有利于对商品价格区间的定位):或者不同年龄段的化妆品消费占比是多少(这可能有助于对商品备货量的预估). 这个时候就要用到分组查询,分组查询的目的是为了把数据分成多个逻辑组(购买化妆品的人员是一个组,不同年龄段购买化妆品的人员也是组),并对每个组进行聚合计算的过程:. 分组查询的语法格式如下: select cname, group_fun,... from tname [where conditio

  • MySQL慢查询如何定位详解

    前言 相信大家在平时工作中都有过 SQL 优化经历,那么在优化前就必须找到慢 SQL 方可进行分析.这篇文章就介绍下如何定位到慢查询. 慢查询日志是 MySQL 内置的一项功能,可以记录执行超过指定时间的 SQL 语句. 以下是慢查询的相关参数,大家感兴趣的可以看下: 参数 含义 log_output 日志输出位置,默认为 FILE,即保存为文件,若设置为 TABLE,则将日志记录到 mysql.show_log 表中,支持设置多种格式 slow_query_log_file 指定慢查询日志文件

  • 解决MySQL读写分离导致insert后select不到数据的问题

    MySQL设置独写分离,在代码中按照如下写法,可能会出现问题 // 先录入 this.insert(obj); // 再查询 Object res = this.selectById(obj.getId()); res: null; 线上的一个坑,做了读写分离以后,有一个场景因为想方法复用,只传入一个ID就好,直接去库里查出一个对象再做后续处理,结果查不出来,事务隔离级别各种也都排查了,最后发现是读写分离的问题,所以换个思路去实现吧. 补充知识:MySQL INSERT插入条件判断:如果不存在则

随机推荐