PostgreSQLオプティマイザの10年間の改善

 (rmarcus.info)
2 ポイント 投稿者 GN⁺ 2024-04-19 | 1件のコメント | WhatsAppで共有

PostgreSQLオプティマイザの10年間の改善点

  • クエリ最適化の研究者として、この10年間 PostgreSQL の高度なオープンソースクエリオプティマイザを活用して研究してきた
  • データベースに取り組み始めてからの10年間で、PostgreSQL がどれほど改善されたのか気になった
  • 変更履歴や意見は多くあったが、強力な実証的比較が見当たらなかったため、自ら PostgreSQL 8 から 16 までで Join Order Benchmark(JOB) を実行することにした
  • 各データベースバージョンについて、90パーセンタイルのクエリ遅延時間を記録した

テスト環境の構成

  • Arch Linux の Docker コンテナ内で GCC 13.2 を使用して、各バージョンの PostgreSQL をビルドした
  • クエリオプティマイザの品質を測定するため、shared_buffers を 8GB に設定した（データベース全体を保持できるほど十分に大きい）
  • すべてのバージョンで work_mem を 8MB に設定した
  • 各クエリはキャッシュを温めるために一度実行した後、さらに5回実行し、その中央値の遅延時間を記録した

全体的な性能改善

  • PostgreSQL のテール性能は大きく向上したが、13〜16 バージョンはおおむね安定していた
  • バージョン 8 と 16 を比較すると、PostgreSQL のオプティマイザはこの10年間でテール遅延時間をほぼ半減させた
  • クエリ分布全体を調べることができる（対数スケールを参照）

回帰分析による改善の定量化

  • 回帰分析を用いて、遅延時間の低下傾向が有意かどうかを確認し、各 PostgreSQL バージョンがどれだけの改善をもたらしたかを定量化できる
  • PostgreSQL のメジャーバージョン番号をクエリ遅延時間に対して回帰分析すると、新しい各メジャーバージョンの PostgreSQL は平均して Join Order Benchmark で 15% の性能向上をもたらす
  • ただし、線形モデルは変化を測る指標としては必ずしも適切とは言えない

追加の考慮事項

  • もちろん、これらすべての改善がクエリオプティマイザによるものではない。並列ワーカーから just-in-time(JIT) コンパイルに至るまで、実行エンジンの改善も寄与している
  • JOB の各クエリプランが年ごとにどのように変化したかを調べるのも興味深いだろう

主なポイント

  • データベースをアップグレードしよう！ PostgreSQL 8 から 16 へ移行すると、ワークロードのテール遅延時間を大きく改善できる
  • 研究者は PostgreSQL が動く目標であることに注意すべき
    • 学習型クエリ最適化の研究は、時間の経過とともに PostgreSQL の異なるバージョンと比較してきた
    • 以前の技術が PostgreSQL を 30% 改善し、最新の技術が PostgreSQL を 25% 改善するとしても、最新の技術はより強力になった PostgreSQL と比較している可能性がある

GN⁺の意見

  • PostgreSQL は継続的に性能を改善してきたが、最近のバージョンでは改善幅が小さくなっている。これはすでに相当な最適化が行われているためかもしれない。今後の改善はより細かな領域に集中するとみられる

  • 単にクエリオプティマイザだけでなく、実行エンジンの改善も性能向上に寄与している。並列処理や JIT コンパイルなど、さまざまな側面で最適化が進んでいる

  • この実験は Join Order Benchmark に限定されたものであり、実際の業務における性能改善効果はワークロードによって異なる可能性がある。自分の業務特性に合ったベンチマークを実施してみるとよい

  • 研究者は PostgreSQL のバージョン変化を考慮すべきだ。同じアルゴリズムでも、比較対象となる PostgreSQL のバージョンによって相対的な性能向上幅が変わりうるため

  • 古いバージョンの PostgreSQL を使っているなら、アップグレードを積極的に検討する価値がある。10年前のバージョンと比べて、最新バージョンでは著しい性能改善が実現されている。もちろん、アップグレードに伴う互換性などの問題は考慮する必要がある

関連記事

1件のコメント

 
GN⁺ 2024-04-19
Hacker Newsの意見

要約:

  • 最適化の問題をうまく解決するには、コストに関するデータが重要である。PostgreSQL には改善の余地が大きい。特に syscall latency のデータや foreign key の統計などが不足している。
  • 大規模なクエリでは、実行中に計画を修正できる deferred planning のような手法の導入が必要である。
  • 機械学習はコスト予測モデルの改善に活用するのが適切である。機械学習で直接クエリ計画を立てるのは適切ではない。
  • shared buffer を大きく取ってデータをすべてメモリに載せてベンチマークするのでは、オプティマイザの実際の性能を適切に評価しにくい。
  • JIT コンパイラは、まだ性能低下を招くだけの場合が多い。
  • PostgreSQL のバージョン番号付けは 10 から変わっているため、8.x、9.x を major バージョンとみなして性能推移を分析するのも興味深そうである。
  • 提示されたグラフだけでは、性能改善の傾向を明確に確認するのは難しい。Tail latency は改善しているように見えるが、ほかはそれぞれ異なる可能性がある。
  • 優れたオプティマイザを作るのはかなり難しい課題である。
  • クエリ最適化が SQL レベルなのか、アルゴリズムレベルなのか気になる。