一次数据库查询超时优化问题的实战记录
作者:丽大佬的小跟班 发布时间:2024-01-25 18:03:11
问题发现
在七月份时,经常发现有几个定时任务报错,查看了下异常原因,大概定位是数据库执行异常
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
### The error may exist in class path resource [mapper/XXXXXXXXX-Mapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select t3.cino, t2.sn as orderSn, t2.provider_id as providerId, t4.logistics_no as logisticsSn, t2.`name`,
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
; Unsupported command; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
org.springframework.dao.DataAccessResourceFailureException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
### The error may exist in class path resource [mapper/XXXXXXXXXOMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select t3.cino, t2.sn as orderSn, t2.provider_id as providerId, t4.logistics_no as logisticsSn,
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unsupported command
查找原因
1 和 DBA 排查 mycat(公司使用 mycat ) 和 mysql 的错误日志。发现是应用服务这边会给 MyCat 发送一个 KILL QUERY 命令,而 myCat 不支持该 KILL QUERY 命令。才给应用服务返回了 Unsupported command 异常错误
2021-07-02 10:46:33.567 WARN [$_NIOREACTOR-37-RW] (io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:96)) - Unsupported command:KILL QUERY 2956587
2 上网搜索了下 KILLQUERY 发生的场景,在一篇文章 深入分析JDBC超时机制 找到问题所在,sql 执行超时,jdbc 会向 mysql 服务发送一个kill 命令,从而让 mysql 停止 sql 执行。不过传给了 mycat 服务,mycat 没有处理该命令,而是直接报错返回
3 查看了下应用服务的配置,在 jdbc:url 统一配置的 mysql socket 执行超时时间是 15s。而在 mybatis 单独的 sql 执行语句配置的设置更长,是 20s(注意这里的配置是 xml 加 注解的方式)
3.1 mybatis xml里的 sql 语句我单独在客户端执行,测试的 sql 执行时间大概在 6,7 秒左右,是不会超过 15s 这个限制的,但是从定时任务调度日志看,每次任务总体上就执行了 8 秒左右,说明 mycat 确实是在15秒内收到超时 KILL 命令,从而导致sql 执行失败
4 想着是不是在其他地方配置超时。细看了下 mybatis 的配置,还真有一个统一 sql 超时配置,default-statement-timeout = 5
从mybatis 源码文档上看,单独设置的 mybatis @Options timeout属性是会覆盖掉在 yml 配置的 default-statement-timeout属性的。难道是 @Options 没生效 ?
5 因此决定本地调试一波。发现 @Options 还真的没生效,jdbc的 queryTimeout 取值的是 mybatis在yml的 全局配置
解决问题
上面提到, sql 的 statment 混合使用了 mybatis 的注解和xml混合使用。猜测应该是 mybatis 的 注解和xml 使用方式是不兼容的。因此试着在 xml 的 statment 修改了 timeout配置。设置 timeout = 60。发现 配置生效了,如下图
后面也试着将 sql 放置到 mybatis 的 @Select 注解,去掉 xml 的 statement。@Options 的配置也是可以生效的。mybatis 的@Options 和 xml 是无法同时生效的,可能 mybatis 其他的注解一样和 xml 配置不能同时生效,希望读者以后能避开这个坑
额外话:Transaction Timeout、Statement Timeout、Socket timeout 的区别
上面 mybatis 配置的 timeout 其实就是Statement Timeout。还有就是在jdbc:url 配置的socketTimeout;其实还有是spring定义的一个事务超时: Transaction Timeout
它们三者的关系是在怎样的呢
Statement Timeout
statement timeout 是用来限制一条语句 statement 的执行时长,可通过调用JDBC的java.sql.Statement.setQueryTimeout(int timeout) 进行设置,不过一般是通过ORM框架来进行设置
在 myBatis中,statement timeout 的默认值是通过 defaultStatementTimeout 属性进行设置。同时还可以在 xml 中 select,insert,update标签设置timeout属性,从而对不同 sql 语句配置超时时间
Transaction Timeout
Spring 提供的 transaction timeout 配置也非常简单,它会记录每个事务的开始时间和消耗时间,当超出timeout值时将抛出异常。
假设某个事务中包含 3 个statement,每个statement的执行时间是 100ms,其他业务逻辑的执行时间是 50ms,那么transaction timeout至少应该设置为350ms(100 * 3 + 50)
Socket timeout
JDBC的 socket timeout 在数据库被突然停掉或是发生网络错误时十分重要。由于TCP/IP的结构原因,socket没有办法探测到网络错误,因此应用也无法主动发现数据库连接断开。如果没设置 socket timeout 的话,应用在数据库返回结果前会无期限地等下去,这种连接被称为 dead connection
优先级顺序
Socket timeout 级别优于 Transaction Timeout,而Transaction Timeout 级别优于 Statement Timeout。也就是说如果 Statement Timeout 大于 Transaction Timeout 或者 Socket timeout,则无法生效
不推荐使用socket timeout来限制statement的执行时长,因此socket timeout的值必须要高于statement timeout,否则,socket timeout将会先生效,这样statement timeout就变得毫无意义
参数文章
深入分析JDBC超时机制
如何配置MySQL数据库超时设置
来源:https://www.cnblogs.com/cscw/p/15398573.html
猜你喜欢
- 人们很容易忽视图像img标签的alt属性。然而,它的重要性也无法体现出来,它是有利于网页的accessibility and&nb
- 无论是在小得可怜的免费数据库空间或是大型电子商务网站,合理的设计表结构、充分利用空间是十分必要的。这就要求我们对数据库系统的常用数据类型有充
- 这篇文章主要介绍了python with (as)语句实例详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,
- 1.函数添加import sys sys.pathsys.path.append("c:\\")2.修改pythonpa
- 代码如下:<% Randomize Do While Len(pass)<12 
- 前言用阻塞 API 写同步代码最简单,但一个线程同一时间只能处理一个请求,有限的线程数导致无法实现万级别的并发连接,过多的线程切换也抢走了
- 前言“幸运牛牛套圈圈”套住欢乐,圈住幸福,等你来挑战!哈喽,大家上午好,我是你们的栗子同学,今天来给
- Python错误SyntaxError: unexpected EOF while parsing含义是解释器到底了都没找到它要找到的东西出
- 一定要对用户可能输入的诸如引号,尖括号等特殊字符给予足够重视,它们可能引发严重的安全问题。SQL注入的基本手法之一,就是利用对单引号未加过滤
- 我的环境,Windows10,Python3.6.3查询了很多有关资料,发现都是Python2版本操作Word文件的,所以就写了这篇短小的文
- 1.背景最近使用Pytest中的fixture和conftest时,遇到需要在conftest中的setup和teardown方法里传递参数
- 本文主要研究的是用Python语言建立Map写Excel表的相关代码,具体如下。前言:我们已经能够很熟练的写Excel表相关的脚本了。大致的
- xml_to_csv代码如下:import osimport globimport pandas as pdimport xml.etree
- 当地址栏没有参数"id"时: 代码如下:Request.QueryString["ID"] == n
- ThinkPHP的分组功能是广为开发者使用的一个具有很大实用价值的功能,该功能可以解决中大型项目情况中MVC分层文件过多导致不易管理的问题。
- 如何把[1, 5, 6, [2, 7, [3, [4, 5, 6]]]]变成[1, 5, 6, 2, 7, 3, 4, 5, 6]?思考:-
- 简介tuple1.元组是以圆括号“()”包围的数据集合,不同成员以“,”分隔。通过下标进行访问2.不可变序列,可以看做不可变的列表,与列表不
- 本文实例讲述了python实现查找两个字符串中相同字符并输出的方法。分享给大家供大家参考。具体实现方法如下:seq1 = "spa
- 一、介绍线程是什么?线程有啥用?线程和进程的区别是什么?线程是操作系统能够进行运算调度的最小单位。被包含在进程中,是进程中的实际运作单位。一
- pip是什么其实,pip就是 Python标准库(The Python Standard Library)中的一个包,这个包比较特殊,用它可