なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLのセッションタイムアウトの制御

timeout系パラメータはいくつかあるけれど

新規接続時のタイムアウト(connect_timeout)ではなく、一度確立した接続がタイムアウトで切断される現象に影響するタイムアウト設定のお話。

ERROR 2013 (HY000): Lost connection to MySQL server during query

にお悩みのアナタのためのヤツです。

「interactive_timeout」「wait_timeout」

・interactive_timeout
サーバーが対話型の接続で、対話型の接続を閉じる前にアクティビティーを待機する秒数。対話型クライアントは、mysql_real_connect() で CLIENT_INTERACTIVE オプションを使用するクライアントと定義されます。wait_timeoutも参照してください。
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_interactive_timeout

・wait_timeout
インタラクティブな接続を閉じる前に、サーバーがその接続上でアクティビティーを待機する秒数。

スレッド開始時に、セッションの wait_timeout 値は、wait_timeout グローバル値または interactive_timeout グローバル値で初期化されますが、いずれになるかはクライアントのタイプ (mysql_real_connect() に対する CLIENT_INTERACTIVE 接続オプションによって定義される) によって決まります。interactive_timeoutも参照してください。
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_wait_timeout


文章で書くと、わりとややこしいんです。
インタラクティブ?非インタラクティブ
スレッド開始時?


スレッド開始後に変えても、影響がない。

「interactive_timeout」の値は、「スレッド開始時」に設定されていた値しか有効になりません。

ログイン後は、グローバル、セッションどちらのレベルでinteractive_timeoutを変更しても、まったく影響がありません。

実際にタイムアウト判定に使用しているのは「wait_timeout」

モードがインタラクティブのときには、global.interactive_timeoutをsession.wait_timeoutに、非インタラクティブのときは、global.interactive_timeoutは一切無視して、global.wait_timeoutをsession.wait_timeoutに反映します。

それによって、実質的にインタラクティブモードのときはinteractive_timeoutに設定しておいた時間でタイムアウトを発生させる、ということを実現しています。

mysqlクライアントでモード切り替え

「-b(--batch)」なし(=デフォルト)だとインタラクティブです。「-b」つけると非インタラクティブです。


いわゆるバッチモードなので、通常はバッチ処理する命令群とセットで使います。つまり「-e "SQL文"」ですね。


ていうか、「-e "SQL文"」つけると「-b」を付けなくてもバッチモードでした。

実験

ログインモードによるsession.wait_timeoutの変化

MySQLに対するrootなユーザでログインし、globalのinteractive_timeout、wait_timeoutの切り替えを行い、インタラクティブモード/バッチモードでログインし直して、sessionに対してどの値が引き継がれているかを確認します。

[root@mysys ~]# mysql -u root -pパスワード -h 127.0.0.1
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 27
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 current_user(),user();
+----------------+----------------+
| current_user() | user()         |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> select @@global.interactive_timeout,@@global.wait_timeout;
+------------------------------+-----------------------+
| @@global.interactive_timeout | @@global.wait_timeout |
+------------------------------+-----------------------+
|                        28800 |                 28800 |
+------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.interactive_timeout,@@session.wait_timeout;
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                         28800 |                  28800 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SET @@global.interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> connect;
Connection id:    28
Current database: *** NONE ***

mysql> select @@global.interactive_timeout,@@global.wait_timeout;
+------------------------------+-----------------------+
| @@global.interactive_timeout | @@global.wait_timeout |
+------------------------------+-----------------------+
|                           10 |                 28800 |
+------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.interactive_timeout,@@session.wait_timeout;
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                            10 |                     10 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@mysys ~]# mysql -u root -pパスワード -h 127.0.0.1 -e "select @@global.interactive_timeout,@@global.wait_timeout;select @@session.interactive_timeout,@@session.wait_timeout;"
Warning: Using a password on the command line interface can be insecure.
+------------------------------+-----------------------+
| @@global.interactive_timeout | @@global.wait_timeout |
+------------------------------+-----------------------+
|                           10 |                 28800 |
+------------------------------+-----------------------+
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                            10 |                  28800 |
+-------------------------------+------------------------+

[root@mysys ~]# mysql -u root -pパスワード -h 127.0.0.1
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 30
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> SET @@global.interactive_timeout = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@global.wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> connect;
Connection id:    31
Current database: *** NONE ***

mysql> select @@global.interactive_timeout,@@global.wait_timeout;
+------------------------------+-----------------------+
| @@global.interactive_timeout | @@global.wait_timeout |
+------------------------------+-----------------------+
|                        28800 |                    10 |
+------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.interactive_timeout,@@session.wait_timeout;
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                         28800 |                  28800 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

[root@mysys ~]# mysql -u root -pパスワード -h 127.0.0.1 -e "select @@global.interactive_timeout,@@global.wait_timeout;select @@session.interactive_timeout,@@session.wait_timeout;"
Warning: Using a password on the command line interface can be insecure.
+------------------------------+-----------------------+
| @@global.interactive_timeout | @@global.wait_timeout |
+------------------------------+-----------------------+
|                        28800 |                    10 |
+------------------------------+-----------------------+
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                         28800 |                     10 |
+-------------------------------+------------------------+

[root@mysys ~]# mysql -u root -pパスワード -h 127.0.0.1
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 33
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> SET @@global.interactive_timeout = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@global.wait_timeout = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.interactive_timeout,@@global.wait_timeout;
+------------------------------+-----------------------+
| @@global.interactive_timeout | @@global.wait_timeout |
+------------------------------+-----------------------+
|                        28800 |                 28800 |
+------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.interactive_timeout,@@session.wait_timeout;
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                         28800 |                  28800 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye


というわけで、

session.wait_timeout初期値
インタラクティブモード global.interactive_timeout
バッチモード global.wait_timeout

となることが確認できました。

ログイン後にsession.wait_timeoutを変えると有効になることの確認

ログインして、session.wait_timeoutを極端に短く変更して、タイムアウトが発生することを確認します。


mysqlクライアントは自動再接続が有効になっているため、タイムアウト時間経過後に投げたSQL発行時点でタイムアウト検出、再接続、SQL実行、という流れになります。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-03-29 15:40:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@session.interactive_timeout,@@session.wait_timeout;
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                         28800 |                  28800 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> set @@session.wait_timeout =5;
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-03-29 15:40:11 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@session.interactive_timeout,@@session.wait_timeout;
+-------------------------------+------------------------+
| @@session.interactive_timeout | @@session.wait_timeout |
+-------------------------------+------------------------+
|                         28800 |                      5 |
+-------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> -- 5秒あける
mysql> select now();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    10
Current database: *** NONE ***

+---------------------+
| now()               |
+---------------------+
| 2017-03-29 15:40:16 |
+---------------------+
1 row in set (0.00 sec)

まとめ

  • タイムアウトは、session.wait_timeoutの値で決まる。
  • session.wait_timeoutの初期値は、ログイン時のモードによって、インタラクティブモードならglobal.interactive_timeoutの値を、バッチモードならglobal.wait_timeoutの値をコピーしてくることによって決まる。
  • ログイン後にsession.wait_timeoutを変更すれば、それは適用される。


なお、wait_timeoutは、あくまで「アイドル状態の待機時間」ですから、ロングクエリー(スロークエリー)がぶった切られる事はありませんし、sleep関数を挟んでも「sleep実行中」となるため、アイドルタイムアウトにはなりません。


ま、大半はyokuさんに教えてもらった話で、それを自分で動かしてまとめてみた、ってネタなんですけどね。


というわけでシメの書籍紹介は、今日のテーマの話とは少し離れるけどyokuさんの本で。