日本でも興味を持つ方がいるかもと思い、オリジナルの著者の方に許可をもらったので翻訳したものを対訳形式で掲載します。How a single PostgreSQL config change improved slow query performance by 50x https://t.co/PdzosNoeYN
— Satoshi Nagayasu 🦃 (@snaga) 2017年11月27日
オリジナル版と併せて、よろしければご覧ください。
■How a single PostgreSQL config change improved slow query performance by 50x
■たった一つの設定変更が如何にしてクエリのパフォーマンスを50倍も改善したか
Pavan Patibandla
At Amplitude, our goal is to provide easy-to-use interactive product analytics, so everyone can find answers to their product questions. In order to provide a great user experience, Amplitude needs to provide these answers quickly. So when one of our customers complained about how long it took to load the event properties dropdown in the Amplitude UI, we started digging into it.
Amplitudeでの我々のゴールは、簡単に使えるインタラクティブなプロダクト分析を提供することです。それによって、すべての人が自分たちのプロダクトについての疑問の答えを得ることができるようになります。素晴らしいユーザエクスペリエンスを提供するために、Amplitudeは答えを迅速に提供する必要があります。ある顧客から Amplitude UI でイベントプロパティのドロップダウンリストのロードに時間がかかると苦情が来たため、その調査を開始しました。
By tracking latency at different levels we figured one particular PostgreSQL query was taking 20 sec to finish. This was a surprise for us, as both tables have indexes on the joined column.
さまざまなレベルで遅延を追跡した結果、ある特定のPostgreSQLのクエリが完了するのに20秒もかかっていることを突き止めました。これは我々にとって驚くべきことでした。というのも、JOINしている両方のテーブルとも、結合しているカラムにインデックスを作成してあったからです。
Slow QueryThe PostgreSQL execution plan for this query was unexpected. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. The sequential scan on a large table contributed to most of the query time.
このクエリの実行プランは想定外のものでした。両方のテーブルにインデックスがあるにも関わらず、PostgreSQLは大きいテーブルに対してシーケンシャルスキャンとハッシュ結合を選択していました。大きなテーブルへのシーケンシャルスキャンは、クエリの実行時間の大部分を占めていました。
Slow Query Execution PlanI initially suspected it could be due to fragmentation. But after inspecting the data, I realized this table was append only and there weren’t many deletions happening on this table. Since reclaiming space using vacuum is not going help much here, I started exploring more. Next, I tried the same query on another customer with good response times. To my surprise the query execution plan looked completely different!
最初に私はフラグメンテーションを疑いました。しかし、データを調査した結果、当該テーブルは追記のみであり、削除はあまり発生していませんでした。領域を解放するVACUUMは助けにならなかったため、さらに深い調査を始めました。次に、同じクエリをレスポンスタイムの良い他の顧客のデータで試してみました。すると驚いたことに、クエリの実行プランは全く違ったものになったのです！
Execution plan of similar query on another AppInterestingly, app A only accessed 10x more data than app B, but the response time was 3000x longer.
興味深いことに、アプリAはアプリBと比べて10倍のデータにアクセスしているだけなのに、レスポンスタイムは3,000倍も長くかかっていました。
To see the alternative query plans PostgreSQL considered before picking Hash Join, I disabled hash join and reran the query.
PostgreSQLがハッシュ結合を選択する前に作成する異なるクエリプランを見てみるために、ハッシュ結合を無効にしてクエリを再度実行してみました。
Alternative execution plan for Slow QueryThere you go! The same query finished 50x faster when using a Nested Loop instead of a Hash Join. So why did PostgreSQL choose a worse plan for app A?
やりました！ ハッシュ結合ではなくネステッドループ結合を使った場合に、同じクエリが50倍も速くなりました。なぜ、PostgreSQLはアプリAで性能の悪い実行プランを選択したのでしょうか？
Looking more closely at the estimated cost and actual run time for both plans, estimated cost to actual runtime ratios were very different. The main culprit for this discrepancy was the sequential scan cost estimation. PostgreSQL estimated that a sequential scan would be better than 4000+ index scans, but in reality index scans were 50x faster.
両方の推定コスト（estimated cost）と実行時間（actual run time）を詳細に見てみると、推定コストと実行時間の比率が大きく異なっていることが分かります。この食い違いの犯人は、シーケンシャルスキャンのコスト推定の部分です。PostgreSQLは、インデックススキャンを4,000回するよりもシーケンシャルスキャンを1回する方がコストが低いと推定しましたが、実際にはインデックススキャンの方が50倍も速かったのです。
That led me to the ‘random_page_cost’ and ‘seq_page_cost’ configuration options. The default PostgreSQL values of 4 and 1 for ‘random_page_cost’, ‘seq_page_cost’ respectively are tuned for HDD, where random access to disk is more expensive than sequential access. However these costs were inaccurate for our deployment using gp2 EBS volume, which are solid state drives. For our deployment random and sequential access is almost the same.
このことから、私は random_page_cost と seq_page_cost の設定について考え始めました。PostgreSQLのデフォルトでは random_page_cost には 4 、seq_page_cost には 1 が設定されています。これはランダムアクセスの方がシーケンシャルアクセスよりもコストが高いハードディスクに合わせてチューニングされています。しかし、これらのコストは、我々がデプロイしている（AWSの）gp2 EBS ボリューム（SSDです）においては正確ではありません。我々のデプロイメント環境では、ランダムアクセスとシーケンシャルアクセスの性能はほぼ同じです。
I changed ‘random_page_cost’ to 1 and retried the query. This time, PostgreSQL used a Nested Loop and the query finished 50x faster. After the change we also noticed a significant drop in max response times from PostgreSQL.
私は random_page_cost を 1 に設定してクエリを再度実行してみました。この時には、PostgreSQLはネステッドループ結合を採用し、クエリは50倍速く実行されました。この設定を変更した後、我々のPostgreSQLの最大のレスポンスタイムは著しく短縮されました。
Overall Slow Query performance improved significantlyIf you are using SSDs and running PostgreSQL with default configuration, I encourage you to try tuning random_page_cost & seq_page_cost. You might be surprised by some huge performance improvements.
もし、あなたがSSDを使っていて、PostgreSQLをデフォルトの設定で使っているのなら、 random_page_cost と seq_page_cost をチューニングしてみることをお勧めします。大きなパフォーマンス改善に驚くかもしれません。
Has any other parameter tuning given you huge gains across the board? Let us know about it in the comments.
他に何か全体的なパフォーマンスの大きな改善につながったパラメータチューニングをご存知でしょうか？ コメント欄で教えていただければと思います。
以上です。
最後で触れられているコメントについては、（英語で）オリジナル版の方にお願いいたします。
Hacker Newsのコメントも面白いと思いますので、併せてどうぞ。
How a single PostgreSQL config change improved slow query performance by 50x | Hacker News https://t.co/QlpjDcWr89
— Satoshi Nagayasu 🦃 (@snaga) 2017年11月28日
では、また。
大変面白い記事の翻訳、ありがとうございます。
返信削除一点気になったのですが、
>シーケンシャルスキャンの方がインデックススキャンよりも4000倍以上もコストが低いと推定
ここは「インデックススキャンを4000回するよりもシーケンシャルスキャンの方がコストが低いと推定」と訳さないと、意味が違ってしまうように思います。ご検討下さいませ～
コメントありがとうございます。
削除確かにそうですね。本文を修正しました。