MySQLでは、さまざまな処理においてタイムアウトオプション値の設定が可能です。それらのタイムアウトオプションは処理ごとに別のオプションを持っている場合が多く、どの処理が対応しているのか迷ってしまうことがあります。

今回は、いくつかのタイムアウトオプションについて紹介したいと思います。MySQLのバージョンは5.7.22を使用しています。

SHOW GLOBAL VARIABLESからtimeoutで部分一致検索すると、以下のようにオプションが確認できます。このうちのいくつかを紹介します。

mysql > > show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 5        |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+

コネクション関連のタイムアウトオプション

connect_timeout

MySQLがクライアントからの接続パケットを待機する時間（秒）となります。デフォルトは10秒です。コネクション生成エラーが頻発する場合はこの値を変更するといいでしょう。set global句を使用したオンラインでの変更可能です。

wait_timeout

アプリケーションなどから接続された非対話型の接続に対してのアイドルタイムアウト時間（秒）となります。コネクションステータスがSleep（アイドル）の状態がこのタイムアウト値を超えると切断されます。デフォルトは28800秒（8時間）で、オンラインでの変更可能です。

interactive_timeout

mysqlクライアントでログインした時などの対話型の接続に対してのアイドルタイムアウト時間（秒）となります（mysqlクライアントは--executeオプションを使用した場合は非対話型となります⁠）⁠。接続が切断される条件やデフォルト値などはwait_timeoutオプションと同様です。

注意として、対話型の接続はスレッド開始時にinteractive_timeoutのグローバル値でwait_timeoutのグローバル値を上書きします。wait_timeoutを10秒、interactive_timeoutを100秒に設定していた場合、対話型クライアントでwait_timeoutの値を確認すると100と表示されます。 。

また、MySQL5.7とそれ以降であればperformance_schema.variables_by_threadテーブルから他のスレッドのオプション値がわかります。たとえば、wait_timeoutのグローバル値ではなくセッションスコープで値を設定された場合、このテーブルからスレッドIDを指定して確認できます。

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28000 |
+---------------+-------+

mysql> SELECT * FROM variables_by_thread WHERE thread_id=48 AND variable_name='wait_timeout';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        48 | wait_timeout  | 1              |
+-----------+---------------+----------------+

スレッドIDの特定方法は第38回　performance_schemaのthreadsテーブルを参照してください。

InnoDBやSQLの操作関連のタイムアウトオプション

innodb_lock_wait_timeout

InnoDBにおける行ロックの待機時間（秒）となります。デフォルトは50秒で、オンラインでの変更可能です。待機時間を経過すると以下エラーが発生して、デフォルト設定ではタイムアウトしたステートメントのみがロールバックされます。トランザクション全体をロールバックするようにしたい場合は、innodb_rollback_on_timeoutをONにしてMySQLを起動する必要があります。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

lock_wait_timeout

メタデータロックを取得するための待機時間（秒）となります。デフォルトは31536000秒（1年）です。

メタデータロックとはデータの一貫性を確保するために使用されます。トランザクションが使用しているテーブルに対してメタデータロックを取得し、他のセッションからそのテーブルに対してAlterやTruncateなどのDDL文を実行するとメタデータロックを取得できず待機します。その時のタイムアウト値になります。詳しくはマニュアル 8.10.4 メタデータのロックをご確認ください。

待機時間を経過するとinnodb_lock_wait_timeoutのタイムアウトと同じく以下エラーが発生します。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

レプリケーション関連のタイムアウトオプション

slave_net_timeout

スレーブがマスターからの後続のデータを待機する時間（秒）となります。デフォルトはMySQL5.7とそれ以降では60秒で、MySQL5.6とそれ以前は3600秒（1時間）です。

デフォルトでは60秒の間にマスターからの更新情報を受け取れていない状態であれば、スレーブはマスターと接続が切断されていると見なし、マスターへ再接続を試行します。この際リレーログがローテーションされます。

ただし、実際にマスターへの更新がないだけで接続は正常であれば、再接続する前にマスターへハートビートを送信することでslave_net_timeoutの値をリセットします。このハートビートはCHANGE MASTER TOステートメントの MASTER_HEARTBEAT_PERIOD オプションで管理されます。このオプションはレプリケーションハートビートの間隔（秒単位）を制御します。値は明示的に指定もできますし、デフォルトではslave_net_timeout/2 が設定されます。

このハートビートに関するステータス情報はMySQL5.6とそれ以前はSHOW GLOBAL STATUS LIKE '%heartbeat%'で確認でき、MySQL5.7とそれ以降はperformance_schema.replication_connection_statusテーブルから確認できます。

また、再接続間隔はCHANGE MASTER TOステートメントの MASTER_CONNECT_RETRY オプション（デフォルト60秒）で制御され、再接続の試行回数は MASTER_RETRY_COUNT オプション（デフォルト86400回）により制限されます。

rpl_stop_slave_timeout

STOP SLAVE の待機時間（秒）となります。デフォルト31536000秒（1年）です。

これは、STOP SLAVE ステートメントと、FLUSH TABLES WITH READ LOCKステートメントなど他のセッションからのステートメントのデッドロックを回避するためにMySQL5.6から追加されました。

まとめ

MySQLにはさまざまなタイムアウトオプションが存在します。エラーメッセージやオプションの内容を把握して、問題に対処できるようにしておきましょう。