一次數(shù)據(jù)庫查詢超時優(yōu)化問題的實戰(zhàn)記錄
問題發(fā)現(xiàn)
在七月份時,經常發(fā)現(xiàn)有幾個定時任務報錯,查看了下異常原因,大概定位是數(shù)據(jù)庫執(zhí)行異常
### 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 的錯誤日志。發(fā)現(xiàn)是應用服務這邊會給 MyCat 發(fā)送一個 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 上網(wǎng)搜索了下 KILLQUERY 發(fā)生的場景,在一篇文章 深入分析JDBC超時機制 找到問題所在,sql 執(zhí)行超時,jdbc 會向 mysql 服務發(fā)送一個kill 命令,從而讓 mysql 停止 sql 執(zhí)行。不過傳給了 mycat 服務,mycat 沒有處理該命令,而是直接報錯返回
3 查看了下應用服務的配置,在 jdbc:url 統(tǒng)一配置的 mysql socket 執(zhí)行超時時間是 15s。而在 mybatis 單獨的 sql 執(zhí)行語句配置的設置更長,是 20s(注意這里的配置是 xml 加 注解的方式)
3.1 mybatis xml里的 sql 語句我單獨在客戶端執(zhí)行,測試的 sql 執(zhí)行時間大概在 6,7 秒左右,是不會超過 15s 這個限制的,但是從定時任務調度日志看,每次任務總體上就執(zhí)行了 8 秒左右,說明 mycat 確實是在15秒內收到超時 KILL 命令,從而導致sql 執(zhí)行失敗
4 想著是不是在其他地方配置超時。細看了下 mybatis 的配置,還真有一個統(tǒng)一 sql 超時配置,default-statement-timeout = 5
-
從mybatis 源碼文檔上看,單獨設置的 mybatis @Options timeout屬性是會覆蓋掉在 yml 配置的 default-statement-timeout屬性的。難道是 @Options 沒生效 ?
5 因此決定本地調試一波。發(fā)現(xiàn) @Options 還真的沒生效,jdbc的 queryTimeout 取值的是 mybatis在yml的 全局配置
解決問題
上面提到, sql 的 statment 混合使用了 mybatis 的注解和xml混合使用。猜測應該是 mybatis 的 注解和xml 使用方式是不兼容的。因此試著在 xml 的 statment 修改了 timeout配置。設置 timeout = 60。發(fā)現(xiàn) 配置生效了,如下圖
后面也試著將 sql 放置到 mybatis 的 @Select 注解,去掉 xml 的 statement。@Options 的配置也是可以生效的。mybatis 的@Options 和 xml 是無法同時生效的,可能 mybatis 其他的注解一樣和 xml 配置不能同時生效,希望讀者以后能避開這個坑
額外話:Transaction Timeout、Statement Timeout、Socket timeout 的區(qū)別
上面 mybatis 配置的 timeout 其實就是Statement Timeout。還有就是在jdbc:url 配置的socketTimeout;其實還有是spring定義的一個事務超時: Transaction Timeout
它們三者的關系是在怎樣的呢
Statement Timeout
- statement timeout 是用來限制一條語句 statement 的執(zhí)行時長,可通過調用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的執(zhí)行時間是 100ms,其他業(yè)務邏輯的執(zhí)行時間是 50ms,那么transaction timeout至少應該設置為350ms(100 * 3 + 50)
Socket timeout
- JDBC的 socket timeout 在數(shù)據(jù)庫被突然停掉或是發(fā)生網(wǎng)絡錯誤時十分重要。由于TCP/IP的結構原因,socket沒有辦法探測到網(wǎng)絡錯誤,因此應用也無法主動發(fā)現(xiàn)數(shù)據(jù)庫連接斷開。如果沒設置 socket timeout 的話,應用在數(shù)據(jù)庫返回結果前會無期限地等下去,這種連接被稱為 dead connection
優(yōu)先級順序
- Socket timeout 級別優(yōu)于 Transaction Timeout,而Transaction Timeout 級別優(yōu)于 Statement Timeout。也就是說如果 Statement Timeout 大于 Transaction Timeout 或者 Socket timeout,則無法生效
- 不推薦使用socket timeout來限制statement的執(zhí)行時長,因此socket timeout的值必須要高于statement timeout,否則,socket timeout將會先生效,這樣statement timeout就變得毫無意義
總結
到此這篇關于數(shù)據(jù)庫查詢超時優(yōu)化問題的文章就介紹到這了,更多相關數(shù)據(jù)庫查詢超時優(yōu)化內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
參數(shù)文章
相關文章
在SQL SERVER中查詢數(shù)據(jù)庫中第幾條至第幾條之間的數(shù)據(jù)SQL語句寫法
這篇文章主要介紹了在SQL SERVER中查詢數(shù)據(jù)庫中第幾條至第幾條之間的數(shù)據(jù)SQL語句寫法,需要的朋友可以參考下2015-11-11Windows10用Navicat?定時備份報錯80070057的問題解析
這篇文章主要介紹了Windows10用Navicat?定時備份報錯80070057的問題,本文通過圖文并茂的形式給大家分享問題所在原因及解決方案,需要的朋友可以參考下2023-10-10