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を指定して確認できます。
スレッドID:48がwait_timeoutを1秒にしている例
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にはさまざまなタイムアウトオプションが存在します。エラーメッセージやオプションの内容を把握して、問題に対処できるようにしておきましょう。