通常の運用時には正常なMySQLでも、レポート作成や調査のために実施されるクエリが異常に長く実行されつづけて放置されている、という場面に出くわしたことがある方もいるかも知れません。

MySQLではmax_execution_timeというシステム変数を利用することで、実行するクエリ時間を制限することができます。このシステム変数はバージョン5.7で追加されているため、利用したことがある読者もいらっしゃるかもしれません。今回はmax_execution_timeシステム数を利用して、SELECT構文の実行時間を制限する方法を紹介したいと思います。なお、今回使用しているMySQLのバージョンは8.0.31になります。

max_execution_time

max_execution_timeは、SELECT構文の実行タイムアウト時間を設定する変数です。設定項目は、実行するミリ秒時間を設定します。デフォルトでは０が設定されており、0の場合はタイムアウトされることはありません。

mysql> SHOW VARIABLES LIKE 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.00 sec)

max_execution_timeが設定されると、設定された値以上に時間がかかるSELECT構文は、以下のエラーを履いてタイムアウトします。

mysql> SET SESSION max_execution_time = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * FROM dummy;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

この変数は読み取り専用のSELECT構文にのみ適用されます。更新を行うようなFunctionやストアドプログラムには効かないようです。

max_execution_timeの設定

max_execution_timeは、GLOBAL、SESSIONどちらにも設定することが可能です。

SET SESSIONで設定した場合には自身のSESSION、GLOBALで設定した場合には、それ以降に新規に作成されたSESSIONに対してmax_execution_timeを設定します。そのため、SET GLOBALで全体のクエリの実行時間を制御し、SET SESSIONで特定のクエリを除外するといった使い方も可能です。

オプティマイザヒント句に埋め込む

max_execution_timeは、オプティマイザヒント句に埋め込むことで、特定のクエリにのみ動作させることも可能です。

オプティマイザヒント句に埋め込む場合は、以下のようにSELECTの中に /*+ max_execution_time(N) */を埋め込むことで、実行時間をNミリ秒以内に制限することができます。これによりセッション単位ではなくクエリ単位で制御することも可能です。

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

mysql> SELECT /*+ MAX_EXECUTION_TIME(10) */ * FROM dummy;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

メタデータロックで待たされているとき

SELECT構文が詰まるものとして、他のトランザクション実行中に対象のテーブルにALTER文を実行して、後続のクエリが対象のテーブルにSELECT構文を実行すると、メタデータロック状態で詰まるといった現象があります。

この場合、通常はlock_wait_timeoutまで待たされますが、max_execution_timeが設定されているとどうなるのでしょうか？実はlock_wait_timeoutとmax_execution_timeの短い方で制限されます。そのため、lock_wait_timeoutが十分に長く設定されていたとしても、max_execution_timeで後続のクエリをエラーとさせることが可能です（ただしエラーにできるのはSELECT構文に限ります⁠）⁠。

lock_wait_timeoutについては以下で紹介していますので、こちらもご確認ください。

• 第75回　MySQLのさまざまなタイムアウトオプションについて | gihyo.jp

trx1> begin; SELECT count(1) FROM dummy  ←トランザクションを開始
trx2> ALTER TABLE dummy ENGINE=InnoDB;   ←ALTER文がメタデータロック待ち状態になる
trx3> > SELECT COUNT(1) FROM dummy;

-- max_execution_timeによってタイムアウトした場合
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

-- lock_wait_timeoutによってタイムアウトした場合
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction)

関連するステータス

max_execution_timeにはMax_execution_time_exeeded、Max_execution_time_set、Max_execution_time_failedという3つのステータスがあります。max_execution_timeによってエラーとなったクエリの数は、Max_execution_time_exeededによって計測することができます。

• Max_execution_time_exeeded
  • max_execution_timeを超えたSELECT構文の数
• Max_execution_time_set
  • 0以外のmax_execution_timeが設定されているときにその値を設定したSELECT構文の数
• Max_execution_time_failed
  • max_execution_timeを設定しようとして失敗したSELECT構文の数

mysql> SHOW STATUS LIKE '%Max_exe%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Max_execution_time_exceeded   | 6     |
| Max_execution_time_set        | 6     |
| Max_execution_time_set_failed | 0     |
+-------------------------------+-------+
3 rows in set (0.01 sec)

まとめ

今回は、max_execution_timeシステム変数について紹介しました。デフォルトは0で設定されてるため、あまり意識することは少ないかもしれませんが、長時間実行を許可したくない場合などにはこの設定は有効かもしれません。もし、長時間実行されると困るようなデータベースを管理している場合は、この設定を有効にすることも考慮してみてください。また、ピークタイムまでに時間がかかりそうなときにクエリをキャンセルしたい場合などでも、事前にSET SESSIONで設定したり、オプティマイザヒント句で設定しておくのも良いでしょう。