Go to Qiita Advent Calendar 2024 Top
LoginSignup
18
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

サムザップ #1Advent Calendar 2020

Day 23
@kojima_akirainサムザップ

MySQLのタイムアウトをPHPクライアント側でハンドリングする

Last updated at Posted at 2020-12-23

本記事は、サムザップ #1 Advent Calendar 2020 - Qiita の12/23の記事です。

はじめに

この記事では、PHPとMySQLに関わる様々なタイムアウト設定について確認しつつ、PHPクライアント側でタイムアウトをハンドリングする手法について記載したいと思います。

ことの発端としては、Laravel(Lumen)でバッチ処理している際に、重いクエリを投げた結果数十秒程度でMySQL server has gone awayが発生し、クエリが正常に実行できなかったことでした。

google先生に確認すると、解消方法としてwait_timeoutの設定値を伸ばす方法が散見されました。当方の環境ではデフォルト値の28800だったので該当せず、他の原因を探すことになり、この際なので、各種設定をまとめようと思いました。

検証環境

  • PHP : 7.3.25
  • mysqlnd : 5.0.12-dev
  • mysql server : 5.7.32

タイムアウトの分類

今回はPHP側で検知するクライアントサイドのタイムアウトと、MySQL側で検知するサーバーサイドのタイムアウトに分けて見ていきたいと思います。

冒頭のwait_timeoutはMySQL側の設定なので、サーバーサイドのタイムアウトということになります。

MySQLサーバー側のタイムアウト設定

まずはMySQLのタイムアウトを確認していきたいと思います。
MySQLに接続し、show global variables like '%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    | 50       |
| 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
innodb_lock_wait_timeout
wait_timeout
interactive_timeout

辺りでしょうか。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

connect_timeoutは、接続パケットを受付るのを待つ時間です。
その名の通り、接続時のタイムアウトです。分かりやすい！

innodb_lock_wait_timeoutは、行ロック取得待ちをする時間です。
複数の接続から同じデータを更新したり、for updateでロック取得したりする際に同じみです。
ゲーム系においては、レイドや対戦ゲームにおいてHP等ユーザー間で共有のデータを更新する際にお目にかかった方も多いのではないでしょうか。

続いてwait_timeoutです。
「非インタラクティブな接続を閉じる前に、サーバーがその接続上でアクティビティーを待機する秒数。」だそうです。
要するに、接続もしくはクエリの発行等のアクティビティーが発生してから、次のクエリの発行等のアクティビティーが発生するまでの待ち時間です。

以下のコードで試してみます。
※試す前に、wait_timeoutの時間を5秒に設定しておきます。

<?php

$mysqli = new mysqli('db', 'root', 'secret');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

sleep(10);  // PHP側で10秒待つ

if ($result = $mysqli->query('SELECT table_name, table_type, engine FROM information_schema.tables;')) {
  echo 'successful!!' . PHP_EOL;
} else {
  echo 'failed!!' . PHP_EOL;
}

$mysqli->close();

こちらのコードは、以下の通りエラーになります。

root@ff99dda9cf79:/var/www/html# php test_wait_timeout.php

Warning: mysqli::query(): MySQL server has gone away in /var/www/html/test_wait_timeout.php on line 11

Warning: mysqli::query(): Error reading result set's header in /var/www/html/test_wait_timeout.php on line 11
failed!!

※コード中のsleep(10);を外せば、成功します。

以上より、MySQLから見たクエリ（接続）とクエリの間の待ち時間（クライアント側の処理時間）ということが確認できました。
これはMySQL側から接続を切られた状態（接続から5秒後）であり、PHP側でのエラーの検知のタイミングとしては、次のクエリを投げた時（接続から10秒後）になります。

最後にinteractive_timeoutです。
こちらが勘違いしやすいのですが、「サーバーが対話型の接続で、対話型の接続を閉じる前にアクティビティーを待機する秒数。」となります。

アクティビティーを待機する時間という意味では、wait_timeoutと同じで、接続モードが対話型か、非インタラクティブ型かによって異なるということになります。

対話型の例としては、mysqlクライアントやMySQL Workbench等です。
非インタラクティブ型としては、PHPアプリケーションやmysqlクライアントにリダイレクション等で直接クエリを流し込むパターン等が該当します。

その接続（スレッド）がどのタイムアウト値になっているかは、以下のように確認可能です。

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

mysql> SELECT * FROM performance_schema.variables_by_thread WHERE variable_name='wait_timeout';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        29 | wait_timeout  | 28800          |
+-----------+---------------+----------------+
1 row in set (0.00 sec)

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

mysql> SELECT * FROM performance_schema.variables_by_thread WHERE variable_name='wait_timeout';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        29 | wait_timeout  | 28800          |
+-----------+---------------+----------------+
1 row in set (0.00 sec)

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    | 50       |
| 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                | 5        |
+-----------------------------+----------+
13 rows in set (0.01 sec)

こちらmysqlクライアントで対話型として接続しているため、wati_timeoutを5秒に設定しているにも関わらず、interactive_timeoutの設定値が有効となり、variables_by_threadのwait_timeoutの値が28800となっています。

一方wait_timeoutの説明で確認した通り、PHPアプリケーション（非インタラクティブ型）ではwait_timeoutの5秒が有効になっていたかと思います。

mysqlクライアントのようなツールを使った手動の操作と、アプリケーションによる実行時でそれぞれ設定でき、分かっていると便利ですね！

PHP側のタイムアウト

それでは本題のPHPクライアント側でのタイムアウトのハンドリングです。

その前にPHPのMySQL接続に使えるAPIとドライバの関係について図示しておきます。

php-mysql-api2.png

つまりAPIのレイヤーと、ドライバーのレイヤーで何某かの設定ができそうです。

mysqlndのタイムアウト設定

ドライバとしては、現状デファクトスタンダードのmysqlndを利用します。
設定可能なものは、mysqlnd.net_read_timeout(>=PHP5.3.0)です。

こちらマニュアルを見ると何やら長い説明があるのですが、どうやら長いクエリを実行して、設定した時間を経過すると、タイムアウトを検知し、接続を切ってくれそうです。

以下のコードを実行してみます。

<?php

ini_set('mysqlnd.net_read_timeout', 5);
$mysqli = new mysqli('db', 'root', 'secret');


if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// MySQLサーバー側で10秒待つ
if ($result = $mysqli->query('select sleep(10);')) {
    echo 'successful!!' . PHP_EOL;
} else {
    echo 'failed!!' . PHP_EOL;
}

$mysqli->close();

すると10秒待たずに、以下のような結果になりました。

root@ff99dda9cf79:/var/www/html# php test.php

Warning: mysqli::query(): MySQL server has gone away in /var/www/html/test.php on line 11

Warning: mysqli::query(): Error reading result set's header in /var/www/html/test.php on line 11
failed!!

PHP側で5秒のタイムアウトを設定できたようです。
ちなみに$mysqli->close();する前に、再度クエリを投げようとすると、間髪入れずにクエリが失敗しました。これは接続が切れているためで、エラーの後に再度クエリを投げるためには、再接続が必要です。

PDOのタイムアウト設定

それでは続いてAPI側を見ていきましょう。
Laravel(Lumen)では、MySQL接続の標準のAPIがPDOです。
PDOでは、以下のタイムアウトに関するオプションが設定可能です。

PDO::ATTR_TIMEOUT

マニュアルを見ると、
タイムアウト秒数を指定する。すべてのドライバがこのオプションに対応しているわけではなく、 またドライバによってこのオプションの扱いは異なる。たとえば sqlite は書き込み可能なロックを確保するのをこの秒数まで待ち続けるが、 他のドライバではこの秒数を接続時のタイムアウトや読み込みのタイムアウトとして扱うこともある。 int を渡す。
ドライバのソースを見ない
と正確なことは分からなそうですね。。
とりあえずクエリのタイムアウトに使えるのか確認してみたいと思います。

以下のコードを実行します。

<?php

$options = [
    PDO::ATTR_TIMEOUT => 5
];

try {
    $pdo = new pdo('mysql:host=db', 'root', 'secret', $options);
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

if ($pdo->query('select sleep(10);')) {
    echo 'successful!!' . PHP_EOL;
} else {
    echo 'failed!!' . PHP_EOL;
}

$pdo = null;

結果は…

root@ff99dda9cf79:/var/www/html# php test_pdo.php
successful!!

10秒待って、普通に成功しました。
どうやらクエリのタイムアウトを検知することには使えないようです。
ちなみにPDOの接続を明示的に閉じるためには、nullを代入する必要があるようです。
https://www.php.net/manual/ja/pdo.connections.php

ということは、この設定はどこに効いてくるのでしょうか！？
答えは…接続のタイムアウトのようです。
https://github.com/php/php-src/blob/2c0d56cc150ada2355319c418c0c6e8321ef7b0f/ext/pdo_mysql/mysql_driver.c#L630
やっぱり最後はソースコードですね。。

MySQLiのタイムアウト設定

それではAPI側でクエリのタイムアウトの制御ができないのか！？と言うと、MySQLiには、PHP7.2以降でMYSQLI_OPT_READ_TIMEOUTというオプションが追加されており、こちらで制御できそうです。

では、早速以下のコードを実行してみましょう。

<?php

$mysqli = mysqli_init();
if (!$mysqli) {
    die('mysqli_init failed');
}

if (!$mysqli->options(MYSQLI_OPT_READ_TIMEOUT, 5)) {
    die('Setting MYSQLI_INIT_COMMAND failed');
}

if (!$mysqli->real_connect('db', 'root', 'secret')) {
    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

if ($result = $mysqli->query('select sleep(10);')) {
    echo 'successful!!' . PHP_EOL;
} else {
    echo 'failed!!' . PHP_EOL;
}

$mysqli->close();

すると以下のように5秒程度でエラーが発生しました。
どうやら想定通りに効いているようです。

root@ff99dda9cf79:/var/www/html# php test_mysqli.php

Warning: mysqli::query(): MySQL server has gone away in /var/www/html/test_mysqli.php on line 16

Warning: mysqli::query(): Error reading result set's header in /var/www/html/test_mysqli.php on line 16
failed!!

注意点としては、

mysqli_options() は、 mysqli_init() がコールされた後、 mysqli_real_connect() の前にコールしなければなりません。

ということです。

最後に

以上、様々なPHPとMySQLにおけるタイムアウト設定について検証してみました。
クライアント側でハンドリングが出来れば、誤って非常に時間のかかるクエリを投げたとしても、設定した値でタイムアウトさせることが出来、アプリの安定稼働に有益かと思います。

こういった細かいエラーハンドリングを積み重ねて、より安心・安定のゲーム運営が出来るように引き続き頑張っていきますので、宜しくお願いします！！

明日は、@chrno001さんの記事です。
また、サムザップのアドベントカレンダーは人数の関係上2つあります。
こちらもよろしくお願いいたします。
サムザップ #2 Advent Calendar 2020 - Qiita

18
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
18
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?