データベースクエリタイムアウト最適化問題の実践記録

2022-01-22 20:44:21

問題の発見

7月に入ってから、いくつかの定型タスクがエラーを報告しているのをよく見かけ、例外の原因を調べた結果、おそらくデータベース実行例外を突き止めました。

### 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
MySQLNonTransientConnectionException: Unsupported command; Unsupported command; nested exception is com.mysql.jdbc.exceptions.jdbc4.
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 exception errorを返します。

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サービスに渡されました。

3 アプリケーションサービスの構成を確認すると、jdbc:url構成のmysqlソケット実行タイムアウトは15s、mybatisスタンドアロンSQL実行文構成は20sと長い（ここの構成は注釈付きxmlであることに注意）。

3.1 mybatis xmlのsql文はクライアント側だけで実行され、テストしたsql実行時間は6、7秒程度で、15秒の制限を超えていません。しかし、タスクスケジューリングのタイムドログから、各タスクは全体で8秒程度実行されており、15秒以内にタイムアウトKILLコマンドを受信したmycatはsql実行に失敗していることになります。

4 タイムアウトをどこかに設定するか考える。mybatisの設定を見ると、default-statement-timeout = 5 という一律のsqlタイムアウトの設定がある。

mybatis のソースドキュメントを見ると、別途設定した mybatis @Options のタイムアウトプロパティが、yml で設定した default-statement-timeout プロパティを上書きしているようです。もしかして、@Optionsが効いていないのでしょうか？

5 そこで、ローカルでデバッグしてみることにしました。jdbcのqueryTimeoutはymlのmybatisのグローバル設定の値を取るので、@Optionsは本当に機能しないことがわかった。

問題を解決する

上記のように、sqlのstatmentはmybatisアノテーションとxmlを混在して使用しています。mybatisアノテーションとxmlの使い方に互換性がないためだと思われます。そこで、xmlのstatmentのタイムアウトの設定を変えてみました。設定は以下のように有効になりました。

また、@Optionsの設定も有効にすることができます。mybatisの@Optionsとxmlの設定は同時に有効にすることができないので、もしかしたらmybatisの他のアノテーションとxmlの設定は同時に有効にすることができないので、今後、読者がこの落とし穴を回避できるようになればいいと思います

おまけ：トランザクションタイムアウト、ステートメントタイムアウト、ソケットタイムアウトの違いについて

上記のmybatisで設定されているタイムアウトは、実はStatement Timeoutです。また、jdbc:urlで設定されているsocketTimeoutや、springで定義されているtransaction timeoutも存在します。トランザクションタイムアウト

で、この3つはどのように関係しているのでしょうか？

ステートメントタイムアウト

ステートメントタイムアウトは、ステートメント文の実行時間を制限するために使用されます。これは、JDBCのjava.sql.Statement.setQueryTimeout（int timeout）を呼び出して設定できますが、通常はORMフレームワークを介して設定されます。
myBatisでは、ステートメントタイムアウトのデフォルト値は、defaultStatementTimeoutプロパティによって設定されます。また、xml の select, insert, update タグに timeout 属性を設定し、異なる sql ステートメントのタイムアウトを設定することもできます。

トランザクションタイムアウト

Springが提供するトランザクションタイムアウトの設定も非常にシンプルで、各トランザクションの開始時刻と消費時刻を記録し、タイムアウト値を超えると例外をスローします。
トランザクションに3つのステートメントが含まれ、それぞれの実行に100ms、残りのビジネスロジックに50msかかるとすると、トランザクションのタイムアウトは少なくとも350ms（100 * 3 + 50）に設定する必要があります。

ソケットタイムアウト

JDBCのソケットタイムアウトは、データベースが突然停止したときやネットワークエラーが発生したときに重要です。TCP/IP の構造上、ソケットにはネットワーク・エラーを検出する方法がないため、アプリケーションはデータベース接続が停止したことを積極的に検出することができません。ソケットタイムアウトを設定しない場合、アプリケーションはデータベースが結果を返すまで無限に待つことになり、この接続はデッドコネクション（dead connection）と呼ばれます。

優先順位

ソケットタイムアウトはトランザクションタイムアウトより、トランザクションタイムアウトはステートメントタイムアウトより優れており、ステートメントタイムアウトがトランザクションタイムアウトまたはソケットタイムアウトより大きい場合、その効果は発揮されないことを意味する
ソケットタイムアウトを使用してステートメントの長さを制限することは推奨されないため、ソケットタイムアウトはステートメントタイムアウトよりも高くなければならず、さもなければソケットタイムアウトが先に有効になり、ステートメントタイムアウトは意味をなさなくなります。

概要

データベースクエリのタイムアウトの最適化の問題については、この記事はここで紹介されて、より関連するデータベースクエリのタイムアウトの最適化の内容は、スクリプトの家の前の記事を検索してくださいまたは次の関連記事を閲覧し続けるあなたは、将来的にはよりスクリプトハウスをサポートしています願っています！この記事では、データベースクエリのタイムアウトの最適化の問題については、ここで紹介されています。