LoginSignup
4
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@taiki-k

PostgreSQL パラレルクエリ 性能検証 (2024年版)

Posted at

TL; DR

  • 普段使ってるLinuxマシンで、PostgreSQL 16〜17を動かして、Star Schema Benchmarkを走行させた
  • パラレルクエリ有効の場合、v16よりv17の方が多少速い
    • パラレルクエリ無効だと、性能がばらつく
    • JITは、昔と比べると、進化しているかもしれない
  • q4.3で使用されるパラレルワーカー数が減る問題については、依然として残ったまま

はじめに

Advent Calendar

これは、 PostgreSQL Advent Calendar 2024 シリーズ1の24日目の記事です。
クリスマス・イブですね！
昨日は、 @tom-sato さんの担当でした。

この記事は

某団体で活動していた頃に、毎年 恒例の 検証に しよう と目論んだが、
ちょうど3年前ぐらいに、ひょんな事から、大いなる神々の逆鱗に触れてしまい1、
結果として恒例化に失敗どころか、某団体を去る羽目に陥り、
無に帰したものを、 昨年に引き続き 、個人でやってみた、というだけのものになります。

パラレルクエリ とは？

パラレルクエリとは、1つのクエリを複数のスレッドまたはプロセスで分担して並列処理することで、
単一スレッド/プロセスでの処理よりも高速にクエリを実行する処理方式です。

PostgreSQL におけるパラレルクエリ

PostgreSQLにおけるパラレルクエリは、マルチプロセスによる並列処理となります。
パラレルクエリが選択されるのは、 対象のテーブルサイズが大きく、実行計画が(PostgreSQL 15の場合)このページに記載されている処理のみで構成される場合、かつ、
パラレルクエリが使用できないパターンが出現していない場合です。

基本的には、 パラレルクエリが発動する可能性があるのは、大きいサイズのテーブルに対するSELECT文の時で、使用するインデックスがないか、B-Treeの場合のいずれか、と思ってください。

この場合でも、パラレルクエリと通常(パラレルでない)の両方でコストを算出して検討し、
コストが一番小さい、と判断された場合にパラレルクエリとして実行されます。

どの程度の並列度で実行されるかは、検索に関係するテーブルのサイズによります。
この点は、某団体の成果物に記載がありますので参照してみてください。2

性能検証

Star Schema Benchmark とは？

今回の検証には、Star Schema Benchmarkを使用しています。

Star Schema Benchmarkは、Business Intelligenceを含むOLAP用途でのRDBMSの性能を測定するために考案されたベンチマーク手法です。
POSデータやEコマース等における売上データ(巨大なデータ量のテーブル)を中心に置き、分析のための顧客情報や店舗情報(比較的小さなデータ量)を周囲において、中心テーブルと適宜Joinして結果を導きます。
おそらく、これを図示すると星型に見えることから、この構造を持つものをStar Schemaと呼んでいるものと思います。
詳細については、某団体の成果物中にあるStar Schema Benchmarkの説明を読んでください。2

測定条件

測定対象マシンは、以下になります。

  • 自作マシン (aka ゲーミングPostgreSQLサーバ)
    • CPU: AMD Ryzen 7 2700X 3
      • 8 Cores, 16 Threads
    • メモリ: 64GB (OCメモリ DDR4-3200相当)
    • OS: Debian sid (Kernel 6.11.10 (linux-image-6.11.10-amd64))

測定に使用したソフトウェアは、以下になります。

  • Star Schema Benchmark4
    これは、pg_ssb_runのREADME.mdとSSBの実行方法に記載されているものに準拠。
    • Scale Factor: 70G
    • データ生成プログラム: こちら
    • ベンチマーク用クエリ: この論文から抜粋
    • ベンチマーク走行プログラム:pg_ssb_run
      • 検証したPostgreSQLのバージョン毎に5回走行して、平均値を記録とする。
  • PostgreSQL
    すべて、ソースコードからビルドしたもの
    • コンパイラのバージョン
      • gcc (Debian 14.2.0-11) 14.2.0
      • clang version 19.1.6 (1) (LLVM 19.1.6)
    • バージョン
      カッコ内は、各STABLEブランチのコミットIDを示す。
      すべて、zipやtar.gzで配布されている地点以降のコミットID。
      • 16.6相当 (1f47e7b5)
      • 17.2相当 (6304632e)
    • パラメータ
      デフォルト値から変更しているもののみ記載
      鍵となるパラメータは太字で表記
      • shared_buffers = 48GB
      • effective_cache_size = 48GB
      • work_mem = 8GB
      • maintenance_work_mem = 2GB
      • wal_buffers = 16MB
      • max_wal_size = 16GB
      • min_wal_size = 4GB
      • checkpoint_completion_target = 0.9
      • max_worker_processes = 12
      • max_parallel_maintenance_workers = 2
      • max_parallel_workers_per_gather = 7 5
      • max_parallel_workers = 7 5
      • jit = off (or on)

テーブルにベンチマーク用のデータを載せると、以下のとおりとなります。
中心となるテーブルは、 lineorder テーブルで、 サイズは41GBです。

=# \d+
                                   リレーション一覧
 スキーマ |   名前    |  タイプ  | 所有者 | 永続性 | アクセスメソッド | サイズ | 説明 
----------+-----------+----------+--------+--------+------------------+--------+------
 public   | customer  | テーブル | taiki  | 永続   | heap             | 250 MB | 
 public   | date      | テーブル | taiki  | 永続   | heap             | 336 kB | 
 public   | lineorder | テーブル | taiki  | 永続   | heap             | 41 GB  | 
 public   | part      | テーブル | taiki  | 永続   | heap             | 157 MB | 
 public   | supplier  | テーブル | taiki  | 永続   | heap             | 76 MB  | 
(5 行)

測定に際しては、事前に pg_prewarm で、上記の全テーブルの全データをPostgreSQLの共有バッファに載せてから測定を行っています。

測定結果

通常実行(パラレルクエリなし)での所要時間

まず、各バージョン間の性能傾向を掴むため、
パラレルクエリなしの状態での所要時間の測定結果を以下に示します。
単位は 秒 です。(平均値のみの掲載です)

query_no PostgreSQL 16.6 PostgreSQL 17.2
q1.1 50.56971 47.2563134
q1.2 44.787725 40.6629014
q1.3 44.610307 40.9170858
q2.1 81.0005978 93.8137494
q2.2 58.7538506 63.8835716
q2.3 52.7438524 52.3968052
q3.1 131.3919634 101.4759128
q3.2 64.6795248 91.1327222
q3.3 58.1525614 62.213891
q3.4 57.3286502 63.9588432
q4.1 130.7543524 130.7524122
q4.2 126.701344 128.097293
q4.3 80.2257102 88.6793928

上記の表をグラフ化したものと、平均値と測定最大/最小値の関係(値域)をグラフ化したものを以下に示します。

parallel_ssb_norm.png

parallel_ssb_norm_range.png

こうして見てみると、q1.xが少し速くなった一方で、q2.x、q3.x、q4.xは、
だいたい遅くなってしまっています。
ただ、q3.x、q4.xは、17.2における測定値の値域が16.6のそれと比較して
大きめであることが多そうです。

パラレルクエリでの所要時間

この記事のメインである、パラレルクエリ実行時の所要時間の測定結果を以下に示します。
単位は 秒 です。(平均値のみの掲載です)

query_no PostgreSQL 16.6 PostgreSQL 17.2
q1.1 9.5745272 8.7301526
q1.2 8.9868042 8.159054
q1.3 9.0334 8.1480444
q2.1 14.0367158 14.078033
q2.2 10.8607924 10.7213842
q2.3 10.2163592 10.1150794
q3.1 17.5912014 17.0293366
q3.2 11.7349086 11.4104674
q3.3 9.980247 9.8747862
q3.4 9.9434514 9.764173
q4.1 17.5124954 17.3236074
q4.2 17.1754764 17.067793
q4.3 18.8749474 18.916888

上記の表をグラフ化したものと、平均値と測定最大/最小値の関係(値域)をグラフ化したものを以下に示します。

parallel_ssb_para.png

parallel_ssb_para_range.png

パラレルクエリなしの場合と比較して、全体的に17.2の方が少し速い、という傾向が見て取れます。
また、測定値の値域がパラレルクエリなしの場合と比較しても小さそうです。

JITもONにしてみる

パラレルクエリに追加して、JITも有効にしてみました。
パラレルクエリ+JIT実行時の所要時間の測定結果を以下に示します。
単位は 秒 です。(平均値のみの掲載です)

query_no PostgreSQL 16.6 PostgreSQL 17.2
q1.1 6.8412658 6.6653028
q1.2 6.1870382 6.023434
q1.3 6.1916298 6.0629124
q2.1 12.9881872 13.1024486
q2.2 10.7622862 10.9954388
q2.3 10.01731 10.1614568
q3.1 16.188616 16.2469172
q3.2 11.2618384 11.2461124
q3.3 9.7331572 9.7440572
q3.4 9.7106514 9.706675
q4.1 16.584265 16.5854232
q4.2 16.3078134 16.2286842
q4.3 17.8932338 17.804396

上記の表をグラフ化したものと、平均値と測定最大/最小値の関係(値域)をグラフ化したものを以下に示します。

parallel_ssb_para_jit.png

parallel_ssb_para_jit_range.png

パラレルクエリのみ有効の場合と比較して、全体的に速くなりました。(特にq1.x、q3.1、q4.3)
ただ、正直言って、効果は限定的のような気がします。

q4.3のパラレルワーカー数

ずっと気になっている、 q4.3でパラレルワーカー数が減る問題が直っているかを確認しましょう。

以下は、実際に実行された実行計画から抜き出した、パラレルワーカー使用予定数(planned)と実際の使用数(launched)を示す表です。

version workers_planned workers_launched
PostgreSQL 16.6 4 4
PostgreSQL 17.2 4 4

今回の設定の場合、直っていれば、少なくともplannedは 7 になるはずですが、
これは、やっぱり直ってないですね。

以下は、PostgreSQL 17.2における、q4.3の実行計画を pev2 を用いて図示したものです。

ssb_q43_pg17.png

ルートノード(一番上)から辿っていって、一番左に来てるテーブルが、customerテーブルなので、
全体のパラレルワーカー数が、これに引っ張られています。

所感

パラレルクエリを動かすと性能は安定する

(設定変更が面倒なので)CPUのクロックはシステムの判断によって、動的に変化させています。
パラレルクエリなしの場合、1コアしか動かないため、システム全体のCPU使用率は 6%程度(≒100/16)でした。
このためなのか、CPUのクロックが2.2GHz近辺であることが多く、時々3.7GHz〜4.2GHzになるぐらいでした。3
これに対して、パラレルクエリあり(JIT有効を含む)の場合、q4.3を除いて、おおむね4.2GHz〜4.3GHzで推移していました。6

当然の帰結だと思いますが、クロックの変動が少なければ、性能が安定しますので、
結果としてパラレルクエリありの方が、測定値の値域が狭い傾向にありました。

なお、q4.3は以下の問題が解決していないことから、5コアの動作にとどまり、
システム全体のCPU使用率は 30%程度(≒100/16×5)で推移し、CPUのクロックは、
割と2.2GHz近辺であることが多かったです。
結果、やはり、測定値がばらついていました。

正直、『各バージョン間の性能傾向を掴むため』にパラレルクエリなしの性能を測定しましたが、
測定値のばらつきが大きかったので、今回の平均値での比較では、正しい性能傾向が出てきていないかもしれません。

パラレルクエリ有効の場合、v16よりもv17の方が速い

パラレルクエリありの場合、v17の方が多少速かったですね。

新しいバージョンの方が速い、というのは素晴らしいことだと思います。(たいがい遅くなるので)
ただ、昨年の記事でv15からv16でq4.3の応答速度が元に戻ってしまったところは、v17でもそのままのようです。

これは、何故なんでしょうね…

パラレルクエリ+JITは、得意・不得意があるかもしれない

JITを有効にした場合では、q1.xは全クエリで高速化した一方で、
q1.x以外はq3.1とq4.3が高速化した以外は、大した効果が出ませんでした。
ただ、某団体での以前の検証ではあえなく撃沈していたことを考えると、
JIT(というより、LLVM、Clangか？)の改良が進んだものと思います。

q1.xは、単にテーブルのスキャンと結合だけのクエリですが、
q2.x、q3.x、q4.xはテーブルのスキャン、結合、集約(sum())、ソートが含まれます。
また、q2.x、q3.x、q4.xの順でスキャンするテーブル数が多く、
また結合条件の数も多くなります。

これらから考えると、クエリが複雑なものは、実行コードも複雑になるため、
JITコンパイルにかかる時間が大きくなったり、JITコードの実行時間も長くなったりするのかな、
と思います。(たぶん、コンパイル時間の方が、影響が大きいでしょう) 7

パラレルクエリが使えるときは、パラレルクエリに身を任せてしまったほうが、
良いのかもしれません。

q4.3は魔境かも？

q4.3において、パラレルワーカー数が小さくなる、という問題はPostgreSQL 9.6の頃から観測されています。
直接の原因となるコードも特定してはいますが、データセットによっては再現しないこともあり、
プランナーの精度の問題と捉えることもできます。

なので、この問題をコミュニティに提起しても、単純に「このコードを修正すればOK！」という方向に議論が向くかどうかは、個人的には、ちょっと懐疑的です。
この問題をコミュニティに提起するときは、澤田さんとか藤井さんとかを味方につけてから提起したいと思います。

おわりに

今回は、PostgreSQLにおいて、Star Schema Benchmarkを用いて、パラレルクエリの性能評価をやってみました。
ちょっと、突っ込んだ調査をした方が良い点もありましたので、時間があったら調査してみたいと思います。

PostgreSQLを動かせる性能が良さげなマシンを持っている方は、このベンチマークを使って、
PostgreSQLの性能測定や調査に活かしたり、単純に自分のマシンの自慢をしたりしてみてはいかがでしょうか？

明日は、ついにクリスマス当日ですね！
担当は、@Ha-kunさんになります。

  1. 神々の怒りは、その某団体とは無関係ですので、関係者はご心配なさらずに。 ↩

  2. この文書のパラレルクエリに関する部分は概ね私が書いたものですが、査読ありなので、より正確だと思います。 ↩ ↩2

  3. 定格クロック 3.7GHz、Boost最大クロック 4.3GHz です。 ↩ ↩2

  4. 基本的に某団体の成果物での条件と同じ。 ↩

  5. 物理コア数 - 1。8コアなので、7に設定。 ↩ ↩2

  6. 熱容量に余裕があったのでしょう。部屋が寒かったので。 ↩

  7. 本来、複雑なクエリにおける処理の最適化・高速化を目的としていたはずなので、これでは本末転倒な気が… ↩

PostgreSQLAdvent Calendar 2024

Day 24
4
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?