SELECT文をタイムアウト強制終了させる「MAX_EXECUTION_TIME」使ってる？

この記事はMySQL Casual Advent Calendar 2017 - Qiitaの9日目のエントリとなります。

実行が長引いたSELECT文を強制終了させるヤーツ

MySQL5.6まで、正常に処理が進んでいて遅いSELECTをタイムアウトさせるシステム変数はありませんでした。

正常に処理が進んでいない時のパラメータだと

lock_wait_timeout：メタデータロック取得待ち
innodb_lock_wait_timeout：レコードロック取得待ち

がありました。

正常に処理が進んでいるけど、厳密には「処理中」ではないときに効くパラメータだと

net_read_timeout：クライアントからサーバに送り込んだデータの読み込み時間
net_write_timeout：サーバからクライアントへのデータの書き戻し時間

がありました。

他にも、アイドルタイムアウト系で

interactive_timeout
wait_timeout

などもありました。

しかし、MySQLさん、ちょっと重いSQL投げると延々帰ってこないことがあったりするのに、それをタイムアウトさせるサーバー側の設定がなく、クライアントサイドで対応する必要がありました。

例えば、Javaプログラムの場合、JDBCドライバ「Connector/J」に対して「SocketTimeout」で「クライアントから見て、リクエストがｎミリ秒帰ってこなかったら諦めるよ」って形をとる必要がありました。末尾の追記もあわせてお読みください。

使用している言語がJavaじゃなかったら、、、わかりません。ドキュメント漁ってください。

ザクッと読んだ限り、connection_timeout、つまり「新規接続時にサーバーが見つからない/返事がない」ヤツっぽいタイムアウトと、コネクションの確立後に投げたリクエストの返答までの時間が間延びしてるタイムアウトが読み分けにくい。。。

Connector/.NETの「MySqlCommand.CommandTimeout Property」
なんかはイケそうな感じだし、これ以外は。。。。

シェルスクリプトでmysql -e "SQL文"とかやってたら、、、mysqlプログラムのオプションにはSocketTimeOut相当のパラメータはありません。。。

しかし、MySQL 5.7以降（もちろん8.0も）、表題に上げた「MAX_EXECUTION_TIME」によって、実行自体が長引くSELECT文にミリ秒指定でタイムアウト設定をすることが可能になっています。

MAX_EXECUTION_TIMEの使い方

GLOBAL/SESSION VARIABLEで指定する

my.cnfで指定するなり、SET句でしていするなり、今まで通りのサーバーシステム変数の扱いと同じです。

これに限った話ではありませんが、SETでGLOBALだけ設定しても、現在セッションには影響ありません。
GLOBALにSETしたあとにログインするか、SESSIONレベルでSETしましょう。

以下、100万件超、普通に投げると20秒弱かかるSELECT文に対して、「MAX_EXECUTION_TIME」で1000ミリ秒を指定したケースです。

「ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded」で強制終了されますが、これが出るまでに何秒かかったかの表示はないです。。。

mysql> select count(*) from test_a;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (19.07 sec)

mysql> set global max_execution_time=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.max_execution_time,@@session.max_execution_time;
+-----------------------------+------------------------------+
| @@global.max_execution_time | @@session.max_execution_time |
+-----------------------------+------------------------------+
|                        1000 |                            0 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from test_a;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (18.97 sec)

mysql> exit
Bye
[root@mysql57 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@global.max_execution_time,@@session.max_execution_time;
+-----------------------------+------------------------------+
| @@global.max_execution_time | @@session.max_execution_time |
+-----------------------------+------------------------------+
|                        1000 |                         1000 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from test_a;
ERROR 1046 (3D000): No database selected
mysql> use exe_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from test_a;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

オプティマイザ・ヒントで指定する

オプティマイザ・ヒント自体がMySQL 5.7からの新機能で、「/*+ XXXXXXXXX */」の形式で、BKAJやMRRといったアルゴリズム採用をヒント句の形で与えられるものです。
その中で、ちょっと異色なヒントとして、MAX_EXECUTION_TIMEが指定できます。

そのSQLだけに指定できるので、セッションレベルよりも細かい制御ができて便利ですね。

気をつけたいのは、システム変数との違いです。
MAX_EXECUTION_TIMEは「＝ｎミリ秒」ではなく、「(ｎミリ秒)」で指定します。

mysql> select /*+ MAX_EXECUTION_TIME(1000) */ count(*) from test_a;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

なお、オプティマイザ・ヒントの文言を間違うと、ふつーにSELECTの処理が実行され、warningが検出されます。

以下、MAX_EXECUTON_TIMEのスペルミス（Iがない）をした例です。

mysql> select /*+ MAX_EXECUTON_TIME(1000) */ count(*) from test_a;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set, 1 warning (18.93 sec)

注意？点

MAX_EXECUTION_TIMEは

読み取り専用SELECTにしか効きません
ストアドプログラムには効きません

The execution timeout for SELECT statements, in milliseconds. If the value is 0, timeouts are not enabled.
max_execution_time applies as follows:

The global max_execution_time value provides the default for the session value for new connections. The session value applies to SELECT executions executed within the session that include no MAX_EXECUTION_TIME(N) optimizer hint or for which N is 0.

max_execution_time applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

max_execution_time is ignored for SELECT statements in stored programs.

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

ということで。

トランザクションの扱いはどうなる？

「AutoCommit=OFF」の時、START TRANSACTIONを明示的に発行しなくても、最初のSQLを投げるとトランザクションが開始される話は、以前に触れました。

で、MAX_EXECUTION_TIMEでタイムアウトを引き起こした場合、どうなるか。

トランザクションは継続したままでした。
ま、予想通りでしたが。

MySQLのトランザクション、基本的には、エラーでもトランザクションの状態は変わらないですね。
基本的ではないところだと、「デッドロックは、検出したらトランザクションが小さい方（＝更新量の少ない方）を強制ロールバックさせる」です。

まとめ

MySQL 5.7以降、SELECTの実行時間を「MAX_EXECUTION_TIME」でタイムアウト制御することができる
システム変数だけじゃなく、オプティマイザ・ヒント（5.7新機能）により、SELECT文単位でもタイムアウト指定できる。
あくまで、「読み取り専用SELECT文だけ」が対象。
もともとMySQLの持ってるタイムアウト設定パラメータ、多い＆名前が直感的じゃないの、ツラい。

MySQL5.7、GAしてから早2年ですよ。
ネット界隈はMySQL8.0で一喜一憂してますが、レイトマジョリティ以降の現場でも、さすがにMySQL5.7には手を出していいよね。

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

作者: 奥野幹也
出版社/メーカー: 翔泳社
発売日: 2016/08/26
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

ところで

OracleとかPostgreSQLってどうだっけ？

追記

クライアントサイド（JDBC）の件で、指摘頂きました。
SocketTimeoutぢゃなくて、今回の件で対応するのはJDBC仕様のsetQueryTimeout()です! - MeijiKのコメント / はてなブックマーク

SocketTimeoutは、「MySQLサーバーに対して投げたリクエストが、一定時間帰ってこなかったらタイムアウト」という設定で、投げたリクエストの内容は問わないし、そもそも「サーバーが見つからない」といったネットワークレイヤでの問題も一緒くたにして扱うパラメータでした。

今回教えていただいた「setQueryTimeout(int seconds)」は、

タイムアウト処理をするスレッド（モニタースレッド）を別途生成
指定した時間になったら、実行中のクエリをキャンセル（Statement.cancel）させる。

という挙動を取るようです。

なお、setQueryTimeoutを使用するには、Connector/Jのプロパティ
enableQueryTimeoutsがtrueになっている必要がありますが、trueがデフォルトですので、フツーに使う分には問題なさそうです。

しかし、うまくタイムアウトを発生させられないケースもあるようです。
Statement.cancelがネットワークコネクションが正常に確立されていることを前提となっているため、それを時間設定で呼び出すsetQueryTimeoutからの終了も、ネットワークの状態に依存するようです。

また、setQueryTimeoutによってタイムアウトが発生したときに、クエリの強制終了（KILL
QUERY）ではなく、接続そのものを切断させる（KILL
CONNECTION）を発生させるプロパティqueryTimeoutKillsConnectionなんてのもあるようです。

公式ドキュメントでは、このあたりです。
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

まとめると

	SQL種別	N/W正常	備考
MAX_EXECUTION_TIME	SELECTのみ	必要	SQLの中で指定できる（オプティマイザ・ヒント使用時）ので、JDBC使わない時も利用できる。
setQueryTimeout	種別問わず	必要	JDBC標準のメソッド。
SocketTimeout	種別問わず	不要	SQLキャンセルの発行ではなく、諦めて（放置して）呼び出し元に処理を戻す。

となるようです。

ご指摘の通り、MAX_EXECUTION_TIMEのようにSQLの実行タイムアウトにもとづいてキャンセルを発行するのは「setQueryTimeout」です。SocketTimeoutはネットワーク遅延のときなど処理を放置したまま呼び出し元に戻してしまうので、エラーハンドリングとその後の処置も別で考える必要があるでしょう。（MAX_EXECUTION_TIMEやsetQueryTimeoutによるタイムアウト発生時にNW異常があると、発行したキャンセルがサーバーに正しく届かず、エラーだけ発生してキャンセルできない可能性はあります。この場合、キャンセルリクエストのタイムアウトって、どうなるんだろう。）

私も今知った状態なので、もう少しドキュメントを読み込んでから、具体的な挙動検証をやってみたいと思います。