Amplitudeの目標は、使いやすいインタラクティブな製品分析を提供して、誰もが製品に関する質問への回答を見つけられるようにすることです。 使いやすさを確保するために、Amplitudeはこれらの回答を迅速に提供する必要があります。 そのため、Amplitudeユーザーインターフェイスでイベントプロパティのドロップダウンリストをロードするのにかかる時間について顧客から苦情があったとき、問題の詳細な調査を開始しました。
さまざまなレベルで遅延を追跡することにより、特定のPostgreSQLクエリが完了するまでに20秒かかることがわかりました。 両方のテーブルの結合列にインデックスがあるため、これは驚きでした。
遅いリクエスト
このクエリのPostgreSQL実行計画は、私たちにとっては予想外のものでした。 両方のテーブルにインデックスがあるという事実にもかかわらず、PostgreSQLは大きなテーブルの順次スキャンでハッシュ結合を実行することにしました。 大規模なテーブルを順次スキャンすると、クエリ時間の大部分を占めました。
遅いクエリ実行プラン
私はこれが断片化によるものであると最初は疑った。 しかし、データをチェックした後、データはこのテーブルにのみ追加され、実際にはそこから削除されないことに気付きました。 ここでVACUUMで場所をクリアしてもあまり役に立ちませんので、さらに掘り始めました。 次に、別のクライアントで同じリクエストを、良い応答時間で試しました。 驚いたことに、クエリ実行計画はまったく異なって見えました！
別のクライアントでの同じリクエストの実行計画
興味深いことに、アプリケーションAはアプリケーションBの10倍のデータにしかアクセスできませんでしたが、応答時間は3,000倍長くなりました。
別のPostgreSQLクエリプランを見るために、ハッシュ接続をオフにしてクエリを再起動しました。
遅いクエリの代替実行計画
さてここ！ ハッシュ結合の代わりにネストされたループを使用すると、同じ要求が50倍速く完了します。 では、なぜPostgreSQLはアプリケーションAの最悪の計画を選択したのですか？
両方のプランの推定コストと実際のリードタイムを詳しく見ると、コストと実際のリードタイムの推定比率は大きく異なっていました。 この矛盾の主な原因は、順次スキャンのコスト見積もりです。 PostgreSQLは、シーケンシャルスキャンは4000以上のインデックススキャンよりも優れていると推定していますが、実際には、インデックススキャンは50倍高速でした。
これにより、 random_page_costおよびseq_page_cost構成オプションに導かれました。 デフォルトのPostgreSQL値は、HDD用に設定されたrandom_page_cost 、 seq_page_costの 4および1です。ディスクへのランダムアクセスは、シーケンシャルアクセスよりも高価です。 ただし、これらのコストは、ソリッドステートドライブであるgp2 EBSボリュームを使用した展開では不正確でした。 この展開では、ランダムアクセスとシーケンシャルアクセスはほぼ同じです。
random_page_costの値を1に変更して、リクエストを再試行しました 。 今回、PostgreSQLはネストされたループを使用し、クエリは50倍速く実行されました。 変更後、PostgreSQLからの最大応答時間の大幅な短縮にも気付きました。
低速な要求の全体的なパフォーマンスが大幅に向上しました。
SSDを使用し、デフォルト設定でPostgreSQLを使用する場合、 random_page_costとseq_page_costを 設定することをお勧めします。 劇的なパフォーマンスの改善に驚くかもしれません。
PostgreSQLに大量のRAMを割り当てているため（RAMのサイズはディスク上のデータベースのサイズを超えているため）、最小パラメーターseq_page_cost = random_page_cost = 0.1を設定して、プロセッサー操作よりもメモリー（キャッシュ）のデータを優先します。 postgresコミュニティが、最新のサーバーではなく、少量のRAMとHDDを備えたサーバーに関連するデフォルト設定をまだ使用する理由はあまり明確ではありません。 これがすぐに修正されることを願っています。