TL; DR
- 普段使ってるLinuxマシンで、PostgreSQL 11〜16を動かして、Star Schema Benchmarkを走行させた。
- PostgreSQL 13から14にかけて、パラレルクエリが高速化している。
- q4.3で使用されるパラレルワーカー数が減る問題については、依然として残ったまま。
- 上記とは別に、q4.3はPostgreSQL 16で遅くなってしまっている(ように見える)
はじめに
Advent Calendar
これは、 PostgreSQL Advent Calendar 2023 シリーズ1の6日目の記事です。
昨日は、 そーだい (@soudai1025) さんの担当でした。
ちなみに、この記事が、私の人生で 初めての Advent Calendar 参加 です。
この記事は
某団体で活動していた頃に、毎年 恒例の 検証に しよう と目論んだが、
ちょうど2年前ぐらいに、ひょんな事から、大いなる神々の逆鱗に触れてしまい1、
結果として恒例化に失敗どころか、某団体を去る羽目に陥り、
無に帰したものを、個人でやってみた、というだけのものになります。
なお、この時の神々の怒り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
- 8 Cores, 16 Threads
- メモリ: 64GB (OCメモリ DDR4-3200相当)
- OS: Debian sid (Kernel 6.5.13 (linux-image-6.5.0-5-amd64))
- CPU: AMD Ryzen 7 2700X
測定に使用したソフトウェアは、以下になります。
- Star Schema Benchmark3
これは、pg_ssb_runのREADME.mdとSSBの実行方法に記載されているものに準拠。- Scale Factor: 70G
- データ生成プログラム: こちら
- ベンチマーク用クエリ: この論文から抜粋
- ベンチマーク走行プログラム:pg_ssb_run
- 検証したPostgreSQLのバージョン毎に5回走行して、平均値を記録とする。
- PostgreSQL
すべて、ソースコードからビルドしたもの- gccのバージョン
gcc (Debian 13.2.0-7) 13.2.0 - バージョン
カッコ内は、各STABLEブランチのコミットIDを示す。
すべて、zipやtar.gzで配布されている地点以降のコミットID。- 11.224
- 12.17相当 (5d40b3c4)
- 13.13相当 (3c49fa2a)
- 14.10相当 (e846fc49)
- 15.5相当 (15d48592)
- 16.1相当 (267f33f6)
- パラメータ
デフォルト値から変更しているもののみ記載
鍵となるパラメータは太字で表記- 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
- gccのバージョン
テーブルにベンチマーク用のデータを載せると、以下のとおりとなります。
中心となるテーブルは、 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 11.22 | PostgreSQL 12.17 | PostgreSQL 13.13 | PostgreSQL 14.10 | PostgreSQL 15.5 | PostgreSQL 16.1 |
|---|---|---|---|---|---|---|
| q1.1 | 10.0159202 | 10.3871654 | 10.509886 | 6.8449454 | 7.4573754 | 6.7561726 |
| q1.2 | 9.2982988 | 9.5381888 | 9.7400842 | 6.1569582 | 6.6007142 | 6.0587188 |
| q1.3 | 9.1693784 | 9.455414 | 9.6518352 | 6.1688138 | 6.579428 | 6.0423814 |
| q2.1 | 17.171151 | 15.60114 | 14.3381634 | 11.2794356 | 11.6440384 | 14.872725 |
| q2.2 | 12.817602 | 12.2763344 | 12.119254 | 9.1027866 | 9.437818 | 10.7207544 |
| q2.3 | 11.3133242 | 11.6404948 | 11.4277444 | 8.6325194 | 8.774327 | 8.5029168 |
| q3.1 | 18.2040074 | 18.7410828 | 18.4156692 | 16.0730464 | 15.9422596 | 15.7884818 |
| q3.2 | 12.9989492 | 13.1027712 | 12.7195964 | 9.9181838 | 11.0652514 | 10.3504942 |
| q3.3 | 11.3351326 | 11.7120452 | 11.274899 | 8.343607 | 8.7596916 | 8.4677206 |
| q3.4 | 11.590048 | 11.7086364 | 11.1546018 | 8.3117564 | 8.748464 | 8.5200344 |
| q4.1 | 18.520675 | 19.0861886 | 18.5636536 | 16.6482334 | 16.2069082 | 16.2724034 |
| q4.2 | 18.423762 | 18.4853132 | 18.2262152 | 15.6313848 | 16.3266934 | 15.4296064 |
| q4.3 | 20.5148144 | 19.9970258 | 18.297215 | 15.6203236 | 16.21476 | 20.6267952 |
また、上記の表をグラフ化したものを以下に示します。
q4.3のパラレルワーカー数
ずっと気になっている、 q4.3でパラレルワーカー数が減る問題が直っているかを確認しましょう。
以下は、実際に実行された実行計画から抜き出した、パラレルワーカー使用予定数(planned)と実際の使用数(launched)を示す表です。
| version | workers_planned | workers_launched |
|---|---|---|
| PostgreSQL 11.22 | 4 | 4 |
| PostgreSQL 12.17 | 4 | 4 |
| PostgreSQL 13.13 | 4 | 4 |
| PostgreSQL 14.10 | 4 | 4 |
| PostgreSQL 15.5 | 4 | 4 |
| PostgreSQL 16.1 | 4 | 4 |
今回の設定の場合、直っていれば、少なくともplannedは 7 になるはずですが、
これは、直ってないですね。
以下は、PostgreSQL 16.1における、q4.3の実行計画を pev2 を用いて図示したものです。
一番左に来てるテーブルが、customerテーブルなので、全体のパラレルワーカー数が、これに引っ張られています。
所感
PostgreSQL 14で高速化している
こうしてみると、PostgreSQL 13から14にかけて、所要時間がグッと減っています。
パラレルクエリ関連で、大きな改善が入ったことが伺えます。
事実、PostgreSQL 14のリリースノートにも、そのような記載があります。
q4.3は魔境かも？
q4.3において、パラレルワーカー数が小さくなる、という問題はPostgreSQL 9.6の頃から観測されています。
直接の原因となるコードも特定してはいますが、データセットによっては再現しないこともあり、
プランナーの精度の問題と捉えることもできます。
なので、この問題をコミュニティに提起しても、単純に「このコードを修正すればOK！」という方向に議論が向くかどうかは、個人的には、ちょっと懐疑的です。
この問題をコミュニティに提起するときは、澤田さんとか藤井さんとかを味方につけてから提起したいと思います。
また、上記の問題とは別に、グラフを見ると、PostgreSQL 16において、q4.3の所要時間が、PostgreSQL 11並みに戻ってしまっています。6
PostgreSQL 16で追加された部分が原因で、遅くなってしまっているのかもしれません。
ここは、追試が必要な部分ですね。
おわりに
今回は、PostgreSQLにおいて、Star Schema Benchmarkを用いて、パラレルクエリの性能評価をやってみました。
ちょっと、突っ込んだ調査をした方が良い点もありましたので、時間があったら調査してみたいと思います。
PostgreSQLを動かせる性能が良さげなマシンを持っている方は、このベンチマークを使って、
PostgreSQLの性能測定や調査に活かしたり、単純に自分のマシンの自慢をしたりしてみてはいかがでしょうか？
明日は、@jun2さんの担当です。