2006年01月18日

MySQL-DBへの接続・切断を大量に行うと、途中から接続エラーになる

自分が担当しているシステムで、MySQLサーバに接続できなくなる
障害が発生しました。
MySQL-DBに「接続→更新→切断」を行うXML WEBメソッドを
繰り返し数千件コールしていた、という状況です。

今日はひたすらこの調査。
結論としては、
 「MySQLが使えるポートを使い果たしてしまった」
のが原因でした。

直接のヒントになったのが以下のスレッドにある回答です。
 [Insert 毎に接続・切断を繰り返すと不特定な場所でエラーが発生する]
 
MySQLはTCPで接続を行うため、接続時に空いているポートを割り当てるようです。
実際、障害条件となる繰り返し接続中、サーバ上でNETSTATコマンドをかけると
使用済みの TIME WAIT状態となったポートを大量に確認できました。

 (余談)
 私はTCPに詳しくないので、TIME WAIT状態とは何か?からお勉強が必要でした・・・
  [TCPの状態遷移図]
  
Microsoft Windows Server 2003 TCP/IP 実装詳細」のページに
「TCP TIME WAIT遅延」に関して記述があります。

既定値の状況は、以下のようにまとめることができます。

 TCPの仕様上、アプリケーションが生成したソケットが利用できるポートの総数は
 0〜65534までの65535個です。
 ただし、1023以下のポート番号は予約されているため、
 1024以降のポート番号のみが利用されます。
 
 Windowsの場合、同時に利用できるポートの最大数は初期状態で5000までの制限が
 ついています。
 よって、既定値では1024〜5000までの3976個が利用可能なポート番号です。

重要な点は、以下の文です。

 ただし、短時間に多数の発信接続を実行するアプリケーションの場合に
 この既定値を適用すると、ポートの再使用が可能になる前に、
 すべての利用可能なポートを使い尽くしてしまう可能性があります。

 Windows Server 2003 の TCP/IP では、この動作を 2 通りの方法で制御できます。
 まず、"TcpTimedWaitDelay" レジストリ パラメータを使って、この値を変更できます。
 このパラメータの値を 30 秒まで減らすと、ほとんどの環境で問題を回避できます。

 さらに、"MaxUserPorts" レジストリ パラメータを使うと、
 発信接続の開始に使用できるユーザー アクセス可能な一時ポートの数を調整できます。

また、MaxFreeTWTcbsというパラメータで、TIME WAIT状態である(可能性のある)
接続の数を指定できるようですね。
 [Microsoft Windows Server 2003 Service Pack 1 の新しいネットワーク機能]

そして冒頭の記述に戻りますが、解放時間(4分)以内に繰り返し
コールし続けたため、
 「MySQLが使えるポートを使い果たしてしまった」
状態になってしまっていました。

<対応・まとめ>

[提案]
まず言えることは、誰の目にも明らかですが、ソースの見直しです。
極力、接続と切断を減らすこと、この当たり前のことが設計時には重要です。

ただこのシステムの場合、XML WEBサービスとして提供している関係で、
「メソッド1コールで1トランザクション」という大前提があります。
このため、1メソッドで「接続→処理→切断」を行う必要がありました。

今後は、例えば以下のような対応が考えられます。

 ・更新データを配列でまとめて受け取り、一度の接続でまとめて更新する

  (注)
  この仕様にしなかった理由があります。
  現在お客様の事情によりMyISAMテーブルを利用しているため
  トランザクションが使えず、途中で更新エラーになった場合の
  ロールバックが面倒であることを考慮していました

 ・DB接続メソッド、DB切断メソッドを用意して、接続状態をセッションで保持。
  処理メソッドでは接続、切断を行わないようにする

  (注)
  お行儀よく使ってもらえないと、無駄な接続(リソース)が
  サーバ上にしばらく残ります

[環境面での対応]
場当たり的な対応ではありますが、前述した障害原因を補完するための修正を
行うことで、当問題は一応「解決」します。

・利用できるポートの上限を増やす
 レジストリの
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\
   MaxUserPort
 の値を設定します。
 
 既定値は 5000 ですが、レジストリに上記エントリが存在しません
 (WindowsXP, 2003で確認)。

 以下の手順で追加指定が可能です。

 (1) レジストリエディタを起動します
   スタート→ファイル名を指定して実行→regedit

 (2) 以下のキー(ディレクトリ)を選択します
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

   ※Parametersキー内に既に MaxUserPort エントリがある場合は(5)へ

 (3) Parametersフォルダを右クリック→新規→DWORD値を選択します

 (4) 作成したエントリを右クリック→名前の変更で、MaxUserPortにします

 (5) 上記エントリをダブルクリックして、10進を選択、値欄に65534と入力します

 (6) レジストリエディタを閉じて、Windowsを再起動します
   →以下ポート解放時間も指定する場合は、以下の設定を行ってから再起動でOKです

・ソケットがポートを開放するまでの時間を減らす
 レジストリの
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\
   TcpTimedWaitDelay
 の値を設定します。

 既定値は 240(4分)ですが、レジストリに上記エントリが存在しません
 (WindowsXP, 2003で確認)。
 
 以下の手順で追加指定が可能です。

 (1) 前述 MaxUserPort と同様の手順で、以下のキーを選択します
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

 (2) 前述 MaxUserPort と同様の手順で、新規エントリ「TcpTimedWaitDelay」を
   追加します

 (3) 上記エントリをダブルクリックして、10進を選択、値欄に 30 と入力します

 (4) レジストリエディタを閉じて、Windowsを再起動します
   →おそらくネットワーク系のサービスを再起動するだけで良いのだと思いますが、
    よくわからないのでOS再起動としました。
    ちなみに、再起動無しだと設定が効かないことを確認済みです。


プログラミングの知識も勿論日々精進が必要だけど、総合的な知識が
求められてきてるな、とつくづく感じました。


posted by てるとみ at 01:25| Comment(6) | TrackBack(1) | MySQL | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
懐かしいね。もうすっかり分からんが(^_^;)
Posted by マサトっち at 2006年01月18日 19:56
MySQL使ってたんだっけ?

まぁ趣味で使ってみてくれ。
株価のリアルタイム監視システムを作ってみるとかw
Posted by てるとみ at 2006年01月19日 00:27
ちょ、参考になりましハァハァ
Posted by 通りすがりのぎざな人 at 2008年08月28日 19:58
ちょ、いつの間にコメントを(笑
Posted by てるとみ at 2008年11月12日 00:30

月6000万pvのサイトのアクセス解析をする為に
サーバー作りました

アクセス解析の為に、専用サーバーとか高くて合わないので
意を決して余ったXPパソコン使って
初めてのサーバー構築にチャレンジ。
XAMPPで。

いざ回したら3分の1しかログ取れてなくて
そもそも、3回に2回はmysql_connectに失敗する状態でした

そして彷徨っているうちにココへ
試したらめっちゃ改善しました!感謝感謝

ページ保存させてもらいました
Posted by KaZu at 2011年02月17日 18:21
>>KaZuさん
ご丁寧なコメントありがとうございます。
このような記事でも参考になれば、幸いです。

もう5年も前の設定(レジストリ修正)ですけど、
自分の現場では未だに現役な設定だったりします^^;
Posted by てるとみ at 2011年02月17日 23:21
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

認証コード: [必須入力]


※画像の中の文字を半角で入力してください。

この記事へのトラックバック

レジストリのお手入れ
Excerpt: 皆さん、パソコンのお手入れします? 日ごろメンテナンスしないと、動作が鈍くなったりして快適に使うことが出来なくなりますよ!
Weblog: 毎日research日記
Tracked: 2006-03-09 02:29
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。