ファイアウォールの背後にある PostgreSQL: クエリに時間がかかりすぎる

ファイアウォールの背後にある PostgreSQL: クエリに時間がかかりすぎる

これが私のセットアップです。VMWare ESXi 4.0 上の CentOS 5.2 ボックス 2 台。最初のボックスの IP アドレスは eth0 では 192.168.22.52、eth1 では 192.168.99.1 です。2 番目のボックスは eth0 の IP アドレス 192.168.99.2 で PostgreSQL 8.3 を実行しています。iptables は次のとおりです。ボックス1ボックス2については下記のコメントを参照してください。

私は、box1 にポート 5432 転送を設定し、Vista ノートブック (192.168.22.1、このサブネットには他のボックスはなく、独自のスイッチがあり、物理的に分離されています) から pgAdminIII または psql を介して box2 の PostgreSQL に接続できます。接続先のデータベースには 2 つのスキーマがあり、1 つは「小さい」スキーマ (基本的に 1 つのテーブルのみ)、もう 1 つは大きいスキーマ (約 30 のテーブル、100 の関数など) です。そのため、小さいスキーマで作業 (テーブルを参照するなど) できますが、大きいスキーマを拡張しようとすると、pgAdminIII が 20 分ほどフリーズします。

PostgreSQL ログには、時間がかかりすぎるクエリがあることが示されています。

2009-06-04 21:04:46 EEST LOG:  00000: duration: 493578.874 ms  statement: 
SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, 
typns.nspname AS typnsp, lanname, proargnames, proconfig,
        pg_get_userbyid(proowner) as funcowner, description
              FROM pg_proc pr
              JOIN pg_type typ ON typ.oid=prorettype
              JOIN pg_namespace typns ON typns.oid=typ.typnamespace
              JOIN pg_language lng ON lng.oid=prolang
              LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid
             WHERE proisagg = FALSE AND pronamespace = 2200::oid
               AND typname <> 'trigger'
             ORDER BY proname

box1 と box2 はどちらも開発ボックスのクローンであり、元のネットワーク構造は異なっていました。box2 はポート転送なしで直接アクセスでき、データベースへのアクセスにまったく問題はありませんでした。

ここで、box2 または「元の」マシン上の psql 経由で、または box2 に接続している box1 から上記のクエリを実行すると、すぐに実行されます。

クエリの実行中、box2 の tcpdump は定期的に次のメッセージを表示します。

12:45:39.770609 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 8760:10220(1460) ack 1 win 54
12:45:39.968496 IP 192.168.22.1.49484 > 192.168.99.2.postgres: . ack 10220 win 16425
12:45:39.968541 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 10220:11680(1460) ack 1 win 54
12:45:39.968574 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 11680:13140(1460) ack 1 win 54
12:45:39.969250 IP 192.168.22.1.49484 > 192.168.99.2.postgres: . ack 13140 win 16425
12:45:39.969275 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 13140:17520(4380) ack 1 win 54
12:45:39.969408 IP 192.168.22.52 > 192.168.99.2: ICMP 192.168.22.1 unreachable - need to frag (mtu 1500), length 556

それ以外では、トラフィックはあまり見られません。すべての ethN インターフェイスの MTU は 1500 です。ノートブックからの ping -l 1472 -f 192.168.99.1 は問題なく通過します。

iptables またはネットワーク設定について何か見落としているのではないかと思いますので、アドバイスをいただければ幸いです。

答え1

試してみるべきこと:

  1. まず、ネットワークが正常に動作していることを確認します。管理対象スイッチがある場合は、インターフェイス統計で速度/デュプレックスの不一致または MTU の不一致を調べます。エラーが発生している場合は、ケーブルの確認/交換を検討してください (例: Cat5e ではなく Cat5 で GigE を実行しようとすると、問題が発生する可能性があります)。

  2. 2 台のマシン間および外部マシンとの間でワイヤー スピード転送が可能であることを証明するために、いくつかのテストを実行します。netcat、ftp、または http 転送は、ここでは良いスタートになります (scp は CPU にバインドされる可能性があるため、最適なテストではない可能性があります)。

  3. 同じクエリを Postgres サーバー上でローカルにテストします。適切な時間枠で完了した場合、データベースに問題がないことがわかります。完了しないか、時間がかかりすぎる場合は、デバッグする必要があるクエリが間違っているか、データベースに他の問題があることになります。ストレージ I/O 側を必ず考慮してください。ディスクが提供できる容量が飽和状態になっている可能性があります。VMware のパフォーマンス グラフをチェックして、確認または否定してください。

  4. それが機能すると仮定して、ファイアウォールを無効にし、「box1」から postgres サーバーに対して同じクエリを実行します。それが機能する場合、VM 間接続はおそらく正常です。

  5. それが機能すると仮定して、ファイアウォールを再起動して再度テストします。それが機能する場合、問題はそのホストの外部にある可能性が高いため、スイッチまたは外部ホストをデバッグする必要があります。

幸運を。

答え2

MTU の問題が発生していますが、その理由はわかりません。ここでは仮想トポロジについて理解しようとしています。

それで、Windows Vista ノートブックは「ローカル」ネットワークに接続されていますか、それともインターネット ネットワークに接続されていますか?

Windows Vista ノートブックがインターネットに接続されており、ポート 5432 のポート転送を使用して「ボックス 2」にアクセスするために「ボックス 1」の外部側 IP アドレスにアクセスしていると想定しています。その場合、次の操作を実行すると何が返されますか。

ping -l 1472 -f <ボックス1のIPアドレス>

編集: わかりました。非常に良いです。よろしければ、「box 1」と「box 2」の両方で「ifconfig」を実行し、各イーサネット インターフェイスの MTU 値を調べてください。すべて 1500 になっているはずです。(「box 1」が「box 2」に、ノートブック宛ての 556 バイト データグラムをフラグメント化できないと伝えた理由を理解しようとしているだけです...)

編集: うわー。すごいですね。

お願いしすぎでなければ、iptables 構成の内容 (またはそのリンク) を質問に投稿していただけますか? (ここで行き詰まってきています。あなたが説明していることは私が頻繁に行っていることですが、どのように故障しているのかよくわかりません。)

編集: 戻ってきました。わかりました。これで困惑してしまいました。iptables の設定は、問題を引き起こすようなものではありません。UDP 5432 を「ボックス 2」に転送しているのがわかります。これを転送する必要はありません。Postgres は TCP のみを使用します。ただし、これによって何か問題が発生することはありません。

20 分間待機している間に、Vista ノートブックと「ボックス 2」の間でトラフィックが移動しているのを確認しましたか? 接続するたびにその状態を再現できますか?

大きな違いがあるわけではありませんが、「ボックス 1」の FORWARD チェーンでは、通常、RELATED、ESTABLISHED が設定されたパケットを ACCEPT するルールをチェーンの最初のルールにします (処理を短縮するため)。ただし、これがパフォーマンスに大きな影響を与えるとは思えません。

問題の答えが分からないのは嫌だ。これでは夜も眠れなくなる。

答え3

これらのマシンの 1 つが IPv6 を不適切に使用しようとしている可能性はありますか? つまり、IPv6 が使用されるべきでないすべての場所で IPv6 がオフになっていること、また、使用されている場合は正しく構成されていることを確認しましたか?

関連情報