高負荷環境でDBが直面する問題とは？PHPとMySQLの TCP TIME-WAIT チューニング(前編)

公開日
更新日

こんにちは。(株)インフィニットループの波多野です。

Linux + Apache + PHP + MySQL の LAMP スタック構成においてシステムを運用している際に、負荷が高くなると、最初は MySQL がボトルネックとなりますが、サーバーのスケールアップや設定、クエリのチューニングを行い性能を向上させていくと、その次に直面するのが Apache (PHP) から MySQL への接続での TCP ソケット枯渇の問題です。

本連載では、前編にて Apache (PHP) から MySQL への接続においてどのように TCP のポート枯渇が発生するのか解説し、また後編では当社(株)インフィニットループで行っているチューニングの手法について紹介します。

サンプルサーバーの構成

本記事では解説のため、さくらのクラウドのサーバーをサンプルとして使用しました。その際に使用したサーバーの設定は以下の通りです。

プラン 1Core-1GB で app, db サーバーの2台
NIC virtio
OS CentOS 7.3
追加 yum リポジトリ EPEL, MySQL, IUS
MySQL 5.7.19
PHP 7.1.7
Apache 2.4.6
IPアドレス 153.127.195.113(app サーバー) , 153.127.203.176(db サーバー)

サンプルサーバーのパケットフィルタは最初は以下の内容で設定し、セキュリティを確保しています。

  • tcp 22 はプライベートLANからの受信のみ許可
  • tcp 3306 は 153.127.195.113 のappサーバーだけに公開
  • tcp 80 は公開
  • tcp, udp の 32768-61000 はアウトバウンド通信の戻りパケット用に許可
  • ストリーミングのフラグメントパケットは公開
  • ip は基本拒否

また IP アドレスを打たずにホスト名でアクセス出来るように /etc/hosts に以下のエントリを追加しました。

153.127.195.113 app
153.127.203.176 db

MySQLクライアントで接続して TCP の状態を観察

ここから実際にサーバーを動かして、その挙動を観察していきます。db サーバーに db1 データベースを作成し、アクセスユーザー user1 を追加しました。また app サーバーに $HOME/.my.cnf ファイルを以下の様に作成し mysql コマンドがユーザー指定なしで使えるようにしておきます。

[mysql]
user=user1
password=****パスワード****

app サーバーから mysql -h db で db サーバーに接続しプロンプトが出たままで、２つのサーバー上でのポート3306 に関する状態を ss コマンド ss -nrt state all '( sport = 3306 or dport = 3306 )' で観察してみます

[knowl@app ~]$ ss -nrt state all '( sport = 3306 or dport = 3306 )'
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
ESTAB       0      0               app:52313                        db:3306

[knowl@db ~]$ ss -nrt state all '( sport =  3306 or dport = 3306 )'
State      Recv-Q Send-Q       Local Address:Port                      Peer Address:Port
LISTEN     0      80                       *:3306                                 *:*
ESTAB      0      0                       db:3306                               app:52313

送信時に送信元側で自動的に割り当てられるポートをエフェメラルポートと言いますが、
app サーバーのエフェメラルポートの 52313 と db サーバーの 3306 で両方向の接続が確立していることがわかります。

この状態で mysql クライアントを終了させ TCP の状態を確認すると、db サーバーは空になった一方 appサーバーでは TIME-WAIT となって残っていることがわかります。

TIME-WAIT   0      0               app:52313                        db:3306

接続が終了してから1分続いた後に TIME-WAIT だったソケットが解放されます。通信のない状態で app サーバー側では1分間 TIME-WAIT 状態でソケットが使用中となります。

RFC793 で定義されている TCP の状態遷移において、最初に接続を切る要求をする方をアクティブクローズと言います。そのアクティブクローズ側は状態遷移の結果 TIME-WAIT に移り、TIME-WAIT は TCP の Max Segment Life (2分）の2倍の時間待機してから CLOSED になる仕様になっています。


https://upload.wikimedia.org/wikipedia/en/5/57/Tcp_state_diagram.png

一方クライアントからの要求を受けて切断に入るパッシブクローズ側の方は TIME-WAIT のように時間待機するフェーズがありませんので次々と遷移して CLOSED になります。これにより MySQL サーバー側ではソケットが残りませんでした。

RFC793 では TIME-WAIT は 2MSL の240秒続くと定義されていますが、実際に測ってみると 60 秒のようです。これは明示的に MSL = 30秒 と定義されている BSD の設定値を Linux カーネルでも採用したためと考えられます。

  • include/net/tcp.h より
#define TCP_TIMEWAIT_LEN (60*HZ) /* how long to wait to destroy TIME-WAIT
                * state, about 60 seconds    */

 

TIME-WAIT によるソケットの枯渇と tcp_fin_timeout の効果を検証

app サーバーから db サーバーに接続する際は app サーバーの送信元ポートとして Linux Kernel によってローカルのエフェメラルポート (32768 - 60999) が新しく割り当てられます。このポートの割り当てが出来ない場合何が起きるでしょうか？実験してみましょう。

app サーバーで 32768 60999 だったものを 32768 32770 の3個だけに変更します。

[knowl@app ~]$ sudo sysctl -w net.ipv4.ip_local_port_range="32768 32770"
net.ipv4.ip_local_port_range = 32768 32770

mysql コマンドを for で 4回 実行してみます

[knowl@app ~]$ for i in `seq 1 4`;do mysql -h db -e ""; done
ERROR 2003 (HY000): Can't connect to MySQL server on 'db' (99)

エラーになりました。ss コマンドで 3306 ポートを調べると

[knowl@app ~]$ ss -nrt state all '( sport = 3306 or dport = 3306 )'
State      Recv-Q Send-Q       Local Address:Port                      Peer Address:Port
TIME-WAIT  0      0                      app:32769                               db:3306
TIME-WAIT  0      0                      app:32768                               db:3306
TIME-WAIT  0      0                      app:32770                               db:3306

3つ全て使い切った上でエラーになっていたことがわかります。

このように、db側ではパッシブクローズのためポートが即時開放されるのに対して、app側ではアクティブクローズのため接続毎にポートが即時開放されず大量のトランザクションが発生する環境においてはエフェメラルポートが枯渇し、ボトルネックとなります。

接続毎に60秒もポートを占有するとあっという間にポートを消費しますので解消したいところです。Linux カーネルのソースを修正してビルドすれば変更可能ですが、クラウド上のサーバーを使って本番環境を構築したいため、リスクとコストを考えると現実的な解決策ではありません。

他の方法を探してみますと net.ipv4.tcp_fin_timeout で調整するという記事がよく見つかります。本当に可能でしょうか？ 10秒に変更してみましょう。

[knowl@app ~]$ sudo sysctl -w net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_fin_timeout = 10

次に mysql クライアントに接続し、15秒待ってポートを確認してみます。

[knowl@app ~]$ mysql -h db -e "";sleep 15;ss -nrt state all '( dport = 3306 )'
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
TIME-WAIT   0      0               app:32768                        db:3306

10秒ではまだ TIME-WAIT で残存したままで、消えたのは変更前と同じ 60 秒後でした。

Kernel Document の ip-sysctl.txt を見ると tcp_fin_timeout はリモートサーバー側のクローズ処理を待つ FIN-WAIT-2 の時間に関係したパラメータで、直接 TIME-WAIT の時間を調整するものではないようです。また実際にカーネルのソースを見てみましょう。

  • include/net/tcp.h より 
    #define TCP_FIN_TIMEOUT    TCP_TIMEWAIT_LEN
                               /* BSD style FIN_WAIT2 deadlock breaker.
                * It used to be 3min, new value is 60sec,
                * to combine FIN-WAIT-2 timeout with
                * TIME-WAIT timer.
                */

tcp_fin_timeout は TIME-WAIT の時間 (TCP_TIMEWAIT_LEN) と同じ値に初期値が定義されていますが、TCP_TIMEWAIT_LEN を変更してくれるものではないようです。残念ながら tcp_fin_timeout の調整で TIME-WAIT によるソケット枯渇を解決するのは難しいようです。

一方、Apache から MySQL への通信はクラウドの内部ネットワークで高速かつ安定して通信出来るため FIN-WAIT-2 の影響は無視出来るものの、Apache の port 80 から外部へのネットワークの通信では FIN-WAIT-2 の時間がありますのでその縮小は効果があります。tcp_fin_timeout チューニングによって Apache が稼働しているサーバーの負荷が軽減される場合があります。

tcp_fin_timeout 変更の注意点ですが、 Web サーバーは内部の DB サーバーだけではなく、Apache から外部のロードバランサーやキャッシュ、ルーターへと接続している構造になっていますので、こちら側のTCP 設定変更によって通信する相手となる外部の機器の方の TCP の挙動も変わることになります。この点を考慮して慎重に TCP のパラメータ設定を試行する必要があります。

以上の tcp_fin_timeout 変更による FIN-WAIT-2 のチューニングですが、本記事では踏み込まず別の機会に解説したいと思います。

PHP のパーシステントと非パーシステント接続

ここまでをまとめると

  • MySQL への接続を切断したのち TCP の TIME-WAIT で 60 秒ソケットを占有したままになる
  • 送信側のエフェメラルポートが枯渇するとソケットを作成できず MySQL と通信出来ない
  • 直接 TIME-WAIT を短縮するには Linux カーネルをビルドするしかない

ということがわかりました。実際のアクセスは mysql クライアントではなく Apache の mod_php で動く php スクリプトになりますので php で再度検証してみましょう。

Apache はデフォルト設定で、テストユーザー knowl のドキュメントルートだけを有効にしてあります。index.php として以下内容のファイルを配置します。

<?php

// $username と$password を別ファイルから読み込む
require_once("../user.php");

error_reporting(E_ALL);
ini_set("display_errors", 1);

try {
$con = new PDO( "mysql:host=db;dbname=db1", $username, $password );
$query = "SELECT 1 AS percent";

foreach ($con->query($query) as $row) {
echo $row["percent"] . "<br>";
}
} catch (PDOException $e) {
echo $e->getMessage();
var_dump(http_response_code(599));
}

unset($query);
unset($con);

PDO のデフォルトの接続方法は mysql クライアントと同じで TCP のアクティブクローズを使っているため、4回目のアクセスで文言は違いますが同じようにエラーになりました。

PHP ではデフォルトの接続方法の他、スクリプト終了時に切断せずキャッシュし、次の接続ではそれを再利用するという方法も使えます。これをパーシステント（持続的な）接続と言います。index.php に PDO の ATTR_PERSISTENT => true を追加した persistent.php を作成しテストしてみましょう。

同じようにブラウザからアクセスしてみますが、何度実行してもエラーにはなりません。またソケットの様子を見ますとアクセスが無くても常時接続済みの ESTAB であることがわかります。

httpd の接続可能数は MaxClient または Apache 2.4 以上での MaxRequestWorkers （デフォルト 256) が上限です。同時に3つ以上のリクエストが来た場合起動された他のプロセスは接続することが出来るのでしょうか？ ブラウザではなく db サーバーから Apache Bench で実行してみます。1 Core メモリ 1GB サーバーのスペックを考慮して 16 並列合計 256 リクエストで試してみます。

[knowl@db ~]$ ab -c 16 -n 256 http://app/~knowl/persistent.php
 ＊＊＊＊省略＊＊＊＊ 
Document Path: /~knowl/persistent.php 
Document Length: 5 bytes 
Concurrency Level: 16 
Time taken for tests: 0.268 seconds 
Complete requests: 256 
Failed requests: 133 (Connect: 0, Receive: 0, Length: 133, Exceptions: 0) 
Write errors: 0 
Non-2xx responses: 133 
Total transferred: 62854 bytes 
HTML transferred: 8994 bytes 
Requests per second: 955.80 [#/sec] (mean) 
Time per request: 16.740 [ms] (mean) 
Time per request: 1.046 [ms] (mean, across all concurrent requests) 
Transfer rate: 229.17 [Kbytes/sec] received

Non-2xx responses: 133 で 113 個のリクエストがエラーになりました。エフェメラルポートの数を 32768 - 32787 の 16個に増やしてみます。

[knowl@app public_html]$ sudo sysctl -w net.ipv4.ip_local_port_range="32768 32784" 
net.ipv4.ip_local_port_range = 32768 32784

リクエストが来なくても ESTAB のままですので、一度全て切るには httpd を再起動し残った TIME-WAIT が消えるまで1分待つ必要があります。ソケットが全て消えたのちに再度 db サーバーから ab でテストしてみます。

Concurrency Level: 16 
Time taken for tests: 0.274 seconds 
Complete requests: 256 
Failed requests: 0 
Write errors: 0 
Total transferred: 52480 bytes 
HTML transferred: 1280 bytes 
Requests per second: 935.00 [#/sec] (mean) 
Time per request: 17.112 [ms] (mean) 
Time per request: 1.070 [ms] (mean, across all concurrent requests) 
Transfer rate: 187.18 [Kbytes/sec] received

今回はエラーは発生せず処理されました。残存しているソケットを確認すると5個使われていました。

[knowl@app public_html]$ ss -nrt state all '( sport = 3306 or dport = 3306 )' 
State Recv-Q Send-Q Local Address:Port Peer Address:Port 
ESTAB 0      0               app:32784           db:3306 
ESTAB 0      0               app:32778           db:3306 
ESTAB 0      0               app:32776           db:3306 
ESTAB 0      0               app:32782           db:3306 
ESTAB 0      0               app:32780           db:3306

Apache の prefork はクライアントからの要求に対して空いているプロセスがあればそれを使って処理してくれるため、同時リクエストと同数のプロセスが起動され都度 MySQL に接続するのではなく、それより少ないプロセス5個にて処理されました。

PDO のパーシステント接続を使っている限り Apache から MySQL への接続は ESTAB のままであり TIME-WAIT なソケットを消費することがありません。最大でも httpd の MaxClient 分のソケットがあれば十分に足りることがわかります。

一方、パーシステント接続は異なる複数のリクエストで接続が共有され続けるため、phpで何らかの異常が発生してトランザクションの途中で処理が終了した場合であっても接続は切断されずそのまま次のリクエストに接続環境が引き継がれてしまいます。クライアントの方の接続は切れていても、PHP からの接続は残っていますので、その処理によって発生したロックが長期間解放されないなどの弊害が起こり得ます。明示的なトランザクションは使わず全て1クエリのオートコミットにすることを採用すればこの弊害に直面する確率を減らせますが、RDBMS を使う利点の一つがトランザクションを使った排他処理や一貫性になりますので、MySQL を使っていて全ての操作をオートコミットなクエリだけで完結する使い方は難しいと考えられます。

これらを考慮すると PHP のパーシステント接続は TIME-WAIT の解決策にはなり得るものの、実際に採用するのは困難です。

パーシステント接続の様々な問題点については php.net の持続的データベース接続 を参照してください。

次回は CentOS 7 を前提に、当社 (株)インフィニットループ で採用している手法を使ったチューニングについて解説致します。

MySQLPHPチューニング
波多野信広のプロフィール画像
波多野信広
商用DBサーバーのサポートエンジニアを経て2012年に札幌へUターン。株式会社インフィニットループでインフラや MySQL の運用に携わり、現在はテクニカルディレクターとして社内の技術調査や教育に取り組んでいます。趣味はSF小説とゲームと数学と庭いじり。1969年北海道札幌生まれ。数学勉強会@札幌と札幌MySQL勉強会に参加。