MySQLのMAX_EXECUTION_TIMEを使って長時間実行中のクエリを強制的に中断させる

2023-03-27AWS,MySQL

はじめに

多くのサービスでは、MySQL データベースを利用しています。

まれに実行時間の長いクエリが発生します。
いくつか上がった対策の 1 つに実行時間の長い SELECT クエリを強制的に終了させるという案がありました。
案を実現するために MAX_EXECUTION_TIME の設定を利用できます。

ここで挙がった MAX_EXECUTION_TIME とはどのような設定であり、どのように変更するのがよいかを紹介します。

MySQL の MAX_EXECUTION_TIME とはなにか？

MAX_EXECUTION_TIME とはどんな設定項目なのでしょうか。

MySQL 5.7 の公式ドキュメント には以下のように書かれています。

  • MySQL 5.7 から利用できる設定項目である
  • 読み取り専用の SELECT 文にだけ適用される
    • 読み取り専用じゃない SELECT 文とは、更新を行うファンクションを呼び出している SELECT 文のこと ( ex: select my_stored_func_to update )
  • ファンクションの中の SELECT 文には適用されない
  • デフォルト値は 0 であり、この状態では実行タイムアウトは無効 ( 結果が取得できるまで実行される )

検証環境

検証は Docker Compose を使って行います。

$ docker-compose --version

$ docker --version
Docker version 20.10.21, build baeda1f
Bash

利用する MySQL の Docker イメージは MAX_EXECUTION_TIME の設定が利用できる MySQL 5.7 を使用します。

docker-compose.yaml というファイル名で適当な場所に保存しましょう。

version: "3"
services:
  db:
    image: mysql/mysql-server:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=pass
      - MYSQL_DATABASE=timeout_db
      - MYSQL_USER=timeout_user
      - MYSQL_PASSWORD=timeout_pass
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci # あとで使う1 --max-execution-time=1000
    # あとで使う2 volumes:
    # あとで使う2   - $PWD/docker/mysql/etc/my.cnf:/etc/my.cnf
YAML

docker-compose.yaml ファイルができたら起動します。

$ ls docker-compose.yaml
docker-compose.yaml

# 起動
$ docker-compose up -d
Bash

起動した MySQL サーバに接続するためには、以下のコマンドを実行します。

$ docker-compose exec db mysql -u root -ppass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>
Bash

MAX_EXECUTION_TIME 設定を確認

設定を確認するためには、 MySQL データベースに接続し以下のクエリを実行します。

MAX_EXECUTION_TIME 設定用の変数はスコープ別に 2 つ存在します。

  • Global スコープ
  • Session スコープ

MySQL データベースに接続すると、 Global スコープ の MAX_EXECUTION_TIME 設定をデフォルト値として Session スコープ の MAX_EXECUTION_TIME 設定が作られます。
実行クエリに影響を与えるのは Session スコープ の MAX_EXECUTION_TIME 設定値となります。

したがってデータベースに接続した直後は、両者の値が一致しています。

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

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

先に触れたとおり、デフォルト値は 0 です。

この状態では SELECT 文は結果が返ってくるまで実行し続けます。

実行に 3 秒かかるクエリを用意

実行時間の掛かるクエリを発行する方法として、 sleep() 関数を利用します。
引数に指定した数値の秒数分スリープし、正常にスリープできた場合には 0 を、スリープに失敗した場合は 1 を返します。

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.01 sec)
SQL

ここからは、 実行に 3 秒以上かかるクエリ select sleep(3); を強制終了させるかを試していきます。

MAX_EXECUTION_TIME 設定を変更

MAX_EXECUTION_TIME 設定を変更する方法はいくるかあります。

変更方法 1 - オプティマイザヒントを使用

オプティマイザヒント は、MAX_EXECUTION_TIME 同様 MySQL 5.7 から利用できるようになった新機能です。

/*+ ... */ の形式で、プログラミング言語のコメントのようなものの中に命令を記述します。
これによりクエリの挙動を調整できます。

MAX_EXECUTION_TIME の設定を変更したい場合は、 select /*+ max_execution_time(1000) */ のように指定します。
sleep() 関数と異なり、引数は ミリ秒 であることに注意が必要です。
実行タイムアウトを 1000 ms = 1s に設定し挙動を確認してみます。

mysql> -- 3秒スリープ (通常実行)
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.01 sec)

mysql> -- 3秒スリープ (1秒で強制タイムアウト)
mysql> select /*+ max_execution_time(1000) */ sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (1.02 sec)

mysql> -- 3秒スリープ (4秒で強制タイムアウト)
mysql> select /*+ max_execution_time(4000) */ sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.01 sec)
SQL

select /*+ max_execution_time(1000) */ sleep(3); のクエリ実行だけ、結果が 1 となりました。
スリープに失敗し、強制終了されました。
1 row in set (1.02 sec) の行からも 1 秒後にレスポンスが返っていることがわかります。

変更方法 2 - データベース接続後に変数を書き換える

2 つ目に紹介するのが、データベース接続後に MAX_EXECUTION_TIME の値を書き換える方法です。

まずは Session スコープ の設定値を変更してみます。

mysql> -- 設定を変更する ( 実行タイムアウトを1秒 )
mysql> SET max_execution_time = 1000;
Query OK, 0 rows affected (0.01 sec)

mysql> -- 設定を確認
mysql> SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.03 sec)

mysql> -- 3秒スリープ (1秒で強制タイムアウト)
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (1.01 sec)

mysql> -- 0秒スリープ
mysql> select sleep(0);
+----------+
| sleep(0) |
+----------+
|        0 |
+----------+
1 row in set (1.01 sec)
SQL

強制終了されたクエリは戻り値が 1 となりました。

めでたしめでたしと言いたいところですが、この状態で再接続すると MAX_EXECUTION_TIME の設定がもとに戻ってしまいます。

mysql> quit
Bye

$ docker-compose exec db mysql -u root -ppass

mysql> -- Session スコープの値を確認
mysql> SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.02 sec)
SQL

Session スコープ の初期値は、 Global スコープ の設定をもとに決定されるのでした。
そこで Global スコープ の設定を変更してみます。

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

mysql> -- Global スコープの値を確認
mysql> SHOW GLOBAL VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> -- 3秒スリープ
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)
SQL

設定が変更されましたが、 実行タイムアウトは有効になりません。

Session スコープ の設定値を確認すると 0 のままです。

mysql> -- Session スコープの値を確認
mysql> SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.02 sec)
SQL

そこで一度接続し直します。

mysql> quit
Bye

$ docker-compose exec db mysql -u root -ppass

mysql> -- Global スコープの値を確認
mysql> SHOW GLOBAL VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> -- Session スコープの値を確認
mysql> SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> -- 3秒スリープ (1秒で強制タイムアウト)
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (1.01 sec)
SQL

1 秒以上かかるクエリは強制終了されました。

変更方法 3 - 起動時のコマンドラインオプション

Docker 環境ではおすすめの方法です。

my.cnf を用意する必要もないため導入が容易です。

まずは一度、Docker Compose 環境を停止します。

# 任意ですがボリュームも削除しておきます
$ docker-compose down -v
Bash

その後、 docker-compose.yaml ファイルを変更します。
「あとで使う 1」 のコメントを解除し、 --max-execution-time=1000 というオプションを有効にします。

version: "3"
services:
  db:
    image: mysql/mysql-server:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=pass
      - MYSQL_DATABASE=timeout_db
      - MYSQL_USER=timeout_user
      - MYSQL_PASSWORD=timeout_pass
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-execution-time=1000
    # あとで使う2 volumes:
    # あとで使う2   - $PWD/docker/mysql/etc/my.cnf:/etc/my.cnf
YAML

再び Docker Compose を起動しましょう。

# 起動
$ docker-compose up -d
Bash

データベースに接続し、 MAX_EXECUTION_TIME を確認してみます。

$ docker-compose exec db mysql -u root -ppass

mysql> -- Global スコープの値を確認
mysql> SHOW GLOBAL VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.05 sec)

mysql> -- Session スコープの値を確認
mysql> SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.04 sec)
SQL

Global スコープ、Session スコープの設定がともに 1000ms となっています。

実行に 3 秒かかるクエリを発行してみます。

mysql> -- 3秒スリープ (1秒で強制タイムアウト)
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (1.01 sec)

mysql> -- 0秒スリープ
mysql> select sleep(0);
+----------+
| sleep(0) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
SQL

1 秒以上かかるクエリは強制終了されました。

変更方法 4 - /etc/my.cnf

最もベーシックな方法を最後に取り上げます。

my.cnf に設定を記述します。

まずは一度、Docker Compose 環境を停止します。

# 任意ですがボリュームも削除しておきます
$ docker-compose down -v
Bash

その後、 docker-compose.yaml ファイルを変更します。
「あとで使う 2」 のコメントだけを解除します。

version: "3"
services:
  db:
    image: mysql/mysql-server:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=pass
      - MYSQL_DATABASE=timeout_db
      - MYSQL_USER=timeout_user
      - MYSQL_PASSWORD=timeout_pass
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci # あとで使う1 --max-execution-time=1000
    volumes:
      - $PWD/docker/mysql/etc/my.cnf:/etc/my.cnf
YAML

再び Docker Compose を起動しましょう。ただし、起動前に max_execution_time=1000 の設定を記述した my.cnf を作成しておきます。

$ mkdir -p ./docker/mysql/etc

$ cat <<EOF >./docker/mysql/etc/my.cnf
[mysqld]
max_execution_time=1000
EOF

# 起動
$ docker-compose up -d
Bash

接続後、　MAX_EXECUTION_TIME の確認と、3 秒間クエリを実行してみます。

$ docker-compose exec db mysql -u root -ppass

mysql> -- Global スコープの値を確認
mysql> SHOW GLOBAL VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> -- Session スコープの値を確認
mysql> SHOW VARIABLES LIKE 'MAX_EXECUTION_TIME';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1000  |
+--------------------+-------+
1 row in set (0.02 sec)

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (1.01 sec)
SQL

1 秒以上かかるクエリは強制終了されました。

AWS RDS の場合

最後に AWS RDS を使っていた場合について触れておきます。

最近はサービス実行基盤として、クラウドサービス ( AWS、GCP、Azure ) を使うことが多いです。

AWS RDS を利用する場合は my.cnf を触ることができないので MAX_EXECUTION_TIME を パラメータグループ に設定します。
これを反映するためにはインスタンスの再起動が必要となります。

稼働中サービスの RDS を再起動することは難しいので、 「変更方法 2 - 起動後に Global 変数を書き換える」 と組み合わせて対処することで、再起動せずとも設定を反映させることができます。

ただし、すでに接続中のコネクションに対しては設定が反映されない点に注意しましょう。

2023-03-27AWS,MySQL