Q&A
メモリなど資源の割り当て状況を知りたい。参考：https://www.postgresql.jp/document/9.5/html/runtime-config-resource.html たぶん16GBを全然活用できていないんじゃないかなって。
初投稿になり、ご回答いただく上で必要な情報などが欠落している場合にはご指摘ください。
前提・実現したいこと
- PostgreSQLにおいて、外部ネットワークに存在するDB対してselect文を実行した際に、ネットワーク帯域をフルで使ってデータの取得を行いたい
発生している問題・エラーメッセージ
- postgresqlでselect文実行時に外部ネットワークにあるクライアントから実行すると、結果が返ってくるのに時間がかかる。
- DBと同一第3セグメントに置かれるクライアントから同一クエリを実行した場合に比べて、15倍以上時間がかかる。
該当のクエリ
SELECT * FROM tableName
対象となるテーブル
- テーブルサイズ:1.5GB
- 行数:約860000
- 列数:4(A,B,C,D)
- インデックス：各列ごとにbtreeインデックスを使用
CREATE INDEX idx_A ON public.A USING btree;
試したこと
- ネットワーク帯域が不足していることを疑って、同じクエリを並列で走らせたが、**1クエリ実行時の速度(bps)×並列数の速度(bps)**となるため、帯域不足ではない。
- 外部からアクセスする場合、FWなどの制限があるため、FWの設定をすべて無効化して実行したが、ネットワーク速度に変化はなかった。
- レコードを一度に取得するのではなく、limit句を追加することで1000行ずつ取得するようにしたが処理速度に変化はなかった(86400行で100minかかるとすれば1000行ならその100min/864かかった)
- Select時のクエリでEXPLAIN ANLYZEを実行した結果は以下
Seq Scan on tableName (cost=0.00..50622.81 rows=854081 width=359)
- pg_stat_activityを利用し、クエリを受け取ってから処理完了までの時間を計測したところ、外部ネットワークのクライアントから投げたほうが、同一第3セグメントのクライアントから投げた場合に比べ15倍以上長くかかっていた。
お伺いしたいこと
- ネットワーク帯域に余裕があるにもかかわらず、外部ネットワークのクライアントからpostgresqlのDBにSelect文を投げると処理に時間がかかってしまう理由を知りたいです。
補足情報（FW/ツールのバージョンなど）
- DBが置かれているPCのスペック
OS: windows Embeded Standard 7
CPU:Core-i7-5700EQ（2.6GHz,Broadwell）
メモリ:DDR3 16GB
- postgreSQL
ver:9.5.3
- クエリ実行時の読出しは同一セグメントでは50Mbps、外部からだと3～5Mbpsのパフォーマンスとなっています。 設備と設定上は、同一セグメントでは１Gbps、外部は100Mbpsまで対応しています。
原因がわかる方、ほかに調査したほうがいいことをご存知の方いらっしゃいましたらご回答いただけますと幸いです。
よろしくお願いいたします。
資源割り当てに関して設定しているものは以下になります
shared_buffers = 512MB
work_mem = 32MB
maintenance_work_mem = 512MB
上記以外の項目に関しては設定していないためデフォルト値になります。
同一第3セグメントと外部セグメントで単純に大きめのファイルをコピーしてみる、pingを打ってみるで差が生じるのでしょうか？
外部ネットワークと「同一第3セグメントのクライアント」のネットワークの遅延の差はどの程度ありますか？
またその遅延を考慮してクエリを行うと、さらにどの程度遅くなりますか？
クエリがselect 1だとどうなりますか？
>>nandy様
それぞれに関して計測しましたので、以下に記載いたします。
DBが置かれているPC→同一第三セグメントのクライアントへデータ転送：80Mbps
DBが置かれているPC→同一外部セグメントのクライアントへデータ転送：50Mbps
転送した際のデータ：1.0GBの単一ファイル
DBが置かれているPC→外部セグメントのクライアントへping：
最小 = 23ms、最大 = 28ms、平均 = 25ms TTL =122
DBが置かれているPC→同一第三セグメントのクライアントへping：
Minimum = 0ms, Maximum = 0ms, Average = 0ms TTL=126
(time<1msのため0ms表記)
pingの応答時間にはかなりの違いが見られました。
bpsよりもping応答時間にかなりの差がありますが、外部セグメントからpingでも平均25msなためそれほど致命的に遅くはないように感じます。
>>tktkt様
読出しは同一セグメントでは50Mbps、外部からだと3～5Mbpsのパフォーマンスとなっています。
設備と設定上は、同一セグメントでは１Gbps、外部は100Mbpsまで対応しています。
単純なデータコピーの際の結果は、nandy様への返信に記載してあります。
「その遅延を考慮してクエリを行うと」
遅延を考慮したクエリというものが分かりません。
何か具体的なクエリを示していただくことはできますでしょうか。
「クエリがselect 1だとどうなりますか？」
これは１record当たりの取得時間のことになりますでしょうか。
書いてありましたね、失礼しました。また、間違えましたので、以前のコメントも無視してください。
2019/10/10 14:21のコメントで、「外部セグメントからpingでも平均25ms」ということは、
「DBが置かれているPC→同一第三セグメントのクライアントへping：」は
DBが置かれているPC→外部セグメントからのクライアントへping：」ということですか？
おっしゃる通りで、私の記載が間違っておりまったく逆の記載をしておりました。
申し訳ありません。
元コメントを修正いたしました。
また、問題はネットワークだと思いますが、なぜpostgresqlが問題だと思いますか？
中継地点にrichなproxyはありませんか？
単純なデータコピーを同じ経路上で行った際に、転送時の速度に大幅な違いが見られず、postgreSQlのクエリ実行時に大きな遅延が見られたためpostgresqlに問題があると思いました。
>>中継地点にrichなproxy
すいません、”richな”とはどのような意味でしょうか。
また、m6u様への回答へのコメントにそれぞれのノードからのtracertの結果を記載してあります。
3～4の経路において、ping応答速度の低下がみられます。
ここが問題ということでしょうか？
> 単純なデータコピー~
かしこまりました。
> richな~
L7まで読みだすようなものをイメージしていました。違っていたらごめんなさい。
>ここが問題ということでしょうか？
私も詳しくないので、わかりません。protocolの違いが影響を及ぼしそうな気がします。
>>tktkt様
すいません、初学者なためL7というものがすぐには理解できませんでしたが、調べたところアプリケーション層の中身まで解析して分散先を決定するもののようですね。
おそらくですが、３～４の経路でプロキシを経由しているため、プロキシ周りの設定がどのようになっているのか確認したほうがいいかもしれませんね。
ここに関しては、私の担当を超える範囲になってしまいますのですぐに回答できませんが、優先して確認するようにいたします。
外部ネットワークとの接続にSSHは使ってますか？
tacsheavenさんの回答へのコメントからの引用です。
> どちらも帯域に対して余裕はあるため、帯域が不足している思っていませんがこの考え方は正しくないでしょうか。
TCPプロトコルによってはプロトコル自体のオーバーヘッドのせいでパフォーマンスが劇落ちするものもあります。古いFTPとか。逆に、よくチューニングされたプロトコルだと、データ量が多くても古いものより速いものがあります。HTTPに対する最近のHTTPS/HTTP2とか。帯域幅に比例していないのは、postgresが使っているプロトコルが双方向の送受信を細かく、多く行うようなもので、外部セグメント間のやり取りでハブやルーティングのオーバーヘッドの影響を受け易い、なんてことは考えられませんか。
外部・内部のpostgreSQLに接続するクライアントは同じもの(バージョンも含めて)でしょうか？
postgreSQLのクライアントはよく知らないのですが、CLIで同じshellを流してみてどの部分で差が生じているか、地道に調べる必要があると思います。
・Diskの書き込み速度などを排除するために出力は/dev/nullにするとか。。。
psqlコマンドには「\timing」コマンドがあるようです。
# \timing
Timing is on.
# SELECT * FROM table_name
Time: 91.594 ms
#
外部からのDBアクセスが遅かったので行った事、プログラムに改変が入るのでご参考です。
・通信をhttpsに変えましたRESTにしたのでプログラムを改変、sql文を受信しJSONで返信
・ネットワーク上httpパケットを圧縮しました。
・大きなsql文はサーバ側に移しました。ロジックで可能なものはストアドにしました。
回答4件
0
自己解決
解決するに至りませんでしたので、クローズいたします。
投稿2020/01/23 06:02
総合スコア6
0
ノートPCをDBサーバーにしているんですね。
接続の経路が違って遅いのならネットワーク環境だと思います。
LANケーブルが低速だったり、低スペックなハブだったりしてないでしょうか。
追記
クエリ実行時の読出しは同一セグメントでは50Mbps、外部からだと3～5Mbpsのパフォーマンスとなっています。
設備と設定上は、同一セグメントでは１Gbps、外部は100Mbpsまで対応しています。
設定上１０分の１で、実行時も１０分の１程度。
だけど、最低の100Mbpsには達していないから、余裕があるはずということですか？
外部分で帯域保証されていないなら、文句言えない類の話な気がします。
投稿2019/10/10 05:09
編集2019/10/10 07:23
総合スコア25327
ルーターで優先QoS設定するとかどうでしょう？
ノートPCではなく組み込み型のデスクトップPCを使用しています。
ハブのスペックに関しては、単一データコピー時にある程度の速度が出ていることを確認しているため、ボトルネックにはなっていないと考えています。
>>外部分で帯域保証されていないなら
1.0GBの単一ファイルを単純コピーで転送した際には以下のパフォーマンスになっています。
DBが置かれているPC→同一第三セグメントのクライアントへデータ転送：80Mbps
DBが置かれているPC→同一外部セグメントのクライアントへデータ転送：50Mbps
帯域保証はわかりかねますが、クエリ実行時にもこれに匹敵するような速度が出てほしいというのが正直なところです。
ベストエフォートの契約の場合、帯域は上限値ですので下回っても文句言えないだったと思います。
0
単純に同一セグメントは 1Gbps で、外部からだと 100Mbps でつながってるとかではないのですかね。
投稿2019/10/10 04:47
総合スコア13703
申し訳ありません、情報が不足しておりました。
読出しは同一セグメントでは50Mbps、外部からだと3～5Mbpsのパフォーマンスとなっています。
設備と設定上は、同一セグメントでは１Gbps、外部は100Mbpsまで対応しています。
どちらも帯域に対して余裕はあるため、帯域が不足している思っていませんがこの考え方は正しくないでしょうか。
0
テーブルの全データを読み出すっていうクエリーでそれだけ待たされるっていうのは、
サーバーとしてのチューニングが疎かになっているのではないかと察します。
PostgreSQLのメモリ設計 - Qiita
PostgreSQL のパフォーマンスチューニング - Qiita
あたりを参考に、あるいはpgTuneを参考にまずは設定を変更してみてから、
調整してみてはいかがでしょうか。
投稿2019/10/10 04:33
退会済みユーザー
総合スコア0
ご回答ありがとうございます。
pgTuneのツールは初めて拝見しました、参考にいたします。
全データを呼び出すことは、同一セグメントにおいては高速なのですが、外部ネットワークからのアクセスに対しては低速となります。
メモリ設定を改善することで、Select文を実行するクライアントの場所が外部ネットワークでも速度が改善することがあるのでしょうか？
同一セグメント下においての読出しは高速なため、データベース自体のメモリ設定が不十分であることが原因ではないように感じています。
同一セグメントで十分高速なのであれば、当てはまらないかもですね。読み違えました。
ネットワーク経路上でボトルネックがないか探し出すことになるでしょうか。
traceroute(tracert.exe)でDB側から探索してみてはいかがでしょうか。
外部ネットワークからアクセスした場合のTracertの結果は以下です。
1 1 ms 1 ms 1 ms 10.A.D
2 1 ms 2 ms 2 ms 10.A.E
3 2 ms 2 ms 3 ms 10.A.E
4 22 ms 22 ms 23 ms 10.B.F
5 22 ms 22 ms 23 ms 10.C.F
6 24 ms 23 ms 24 ms 10.C.G
7 22 ms 22 ms 24 ms 10.C.H
セキュリティ対策のため、第2セグメント以下はマスクしています。
可読性が悪いかもしれませんが英数字はそれぞれ同一の数値が入ることを意味しています。
同一セグメントからアクセスした場合のTracertの結果は以下です。
1 1 ms 1 ms 1 ms 10.A.B.
2 1 ms <1 ms <1 ms 10.A.C
3 <1 ms <1 ms <1 ms 10.A.D
外部においては3～4の経路で応答時間が遅延していました。
ここがボトルネックの可能性がありますが、応答時間だけでなく速度(bps)も見ればはっきりしてきますでしょうか。
あなたの回答
tips
プレビュー
