PGECons 勉強会#2
パラレルクエリ2018/10/18
NECソリューションイノベータ(株)近藤 太樹
1Agenda パラレルクエリとは？
 パラレルクエリはいつ使う？
 パラレルクエリには何が必要？
 パラレルクエリの落とし穴！？
 リソースが足りない場合
 大きいテーブルどうしの結合 パラレルクエリを使用したら思わぬ伏兵が!?

 皆さんからの質問にお答えします!

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

2

パラレルクエリの概要

パラレルクエリとは？

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

3

PostgreSQLでのパラレルクエリとは？
 1つのクエリを複数のプロセスで分担してパラレル処理すること パラレルクエリで効率的に処理可能と判断した場合のみ採用
 PostgreSQLでは、パラレル処理においてはスレッドは使用していない

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

4

PostgreSQLでのパラレルクエリとは？
 PostgreSQL 9.6で採用
 PostgreSQL 10以降も継続して強化中
 PostgreSQLのパラレルクエリはBackground Worker Processの一種として実装
PostgreSQLのプロセス
Background Worker
バックグラウンドプロセス

クライアントBackground Worker
Background Worker

autovacuum

WAL Writer

LoggerCheckpointerpostgres (postmaster)
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

5

PostgreSQLにおけるパラレルクエリの歴史

パラレルクエリ導入までの道のりは長く、基礎作りはPostgreSQL 9.3の頃(2012～2013)から
 9.3 : Background Worker Process 導入
 9.4 : Background Worker Processの動的開始・終了
max_worker_processesパラメータの導入

 9.5 : 「並列実行基盤を改善しました。」

 9.6 : 祝 パラレルクエリ導入
 10 : Gatherノードの拡張により、部分集約が可能に 11 : SELECT文以外のSQL文でのパラレル対応 (後述)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

6

PostgreSQLで対応するパラレルクエリ
 全てのクエリがパラレルに対応しているわけではない パラレル対応しているのは以下の操作
 PostgreSQL 9.6～
 逐次スキャン(SeqScan)、集約(Aggregation)処理、ハッシュ結合、入れ子ループ(Nested Loop)結合 PostgreSQL 10～ マージ結合(Join)処理、B木(B-tree)インデックススキャン、集約(Aggregation)処理の対応範囲拡大
 PostgreSQL 11～ B木インデックスの作成、ハッシュ結合(ハッシュテーブルの共有化)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

7

効果が高いクエリについてパラレルクエリはいつ使う？

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

8

パラレルクエリはいつ使う？
 データベースでの処理量の多い、応答に長時間かかるクエリが、パラレルクエリに対応しているとき
 大量のデータが登録されているテーブルの検索 大量のデータの集計処理

 このようなクエリをパラレル化することで応答時間の短縮が期待可能 BI・DWHなどのOLAP用途でのクエリは、このようなクエリの典型例
 同様の理由から、バッチ処理でも効果が期待可能(リソースがあれば)Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

9

パラレルクエリはいつ使う？
 パラレルクエリに対応しているSQL文は以下の通り
 基本的にはSELECT文

 以下は、v11より対応
 CREATE TABLE … AS SELECT …
 SELECT INTO …
 CREATE MATERIALIZED VIEW
 CREATE INDEX (B木のみ)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

10

パラレルクエリはいつ使う？
 パラレルクエリを使用できない場合もあるので注意 UPDATEやINSERTなどの更新系

 ロックを取得するようなクエリ
 SELECT FOR UPDATE 一時テーブルでの検索
 CREATE TEMPORARY TABLE で作成されたもの

 通常のSELECTでも、場合によっては使用されない
 ただし、かなり複雑なクエリにならないと、陥らない

詳細はマニュアルでご確認ください。
 https://www.postgresql.org/docs/current/static/parallel-safety.html (英語) https://www.postgresql.jp/document/10/html/when-can-parallel-query-beused.html (v10ベース 日本語訳)

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

11

パラレルクエリはいつ使う？
 大量のデータとはどのくらいか？
 パラレル度はテーブルのサイズに依存
ブロック数 サイズ(MB)×3×3×3×3×3

起動Worker数

検索プロセス数

1,024

8

123,07224

2

39,21672

3427,648216

4582,944648

56248,832

1,944

6

7

…

…

…

…

 パラレルでない場合の実行計画よりもコスト推定値が下回った場合に初めて使用される
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

12必要となるリソース

パラレルクエリには何が必要？

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

13

適切なハードウェアリソースが必要 パラレルクエリを有効活用するには、それに応じたハードウェアリソースが必要 パラレル実行する(予定の)プロセス数以上の物理CPUコアを用意する。
 パラレル実行する(予定の)テーブルサイズ以上のメモリを用意する。

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

14適切なパラメータの投入が必要

パラレルクエリ関連の設定が行われていること
 max_worker_processes, max_parallel_workers_per_gather,

max_parallel_workers は 全て1以上である必要がある (v9.6は注意)
パラメータ

デフォルト値 デフォルト値(v9.6)(v10)

解説

max_worker_processes

8

8 BgWorkerの最大数

max_parallel_workers_per_gather﻿

02 Worker最大数/Gather―

max_parallel_workers
﻿
min_parallel_relation_size
﻿8MB

min_parallel_table_scan_size﻿―

min_parallel_index_scan_size﻿―parallel_setup_cost﻿
parallel_tuple_cost﻿

8 Worker最大数/クエリ―

パラレル発動最小サイズ(table)

8MB

パラレル発動最小サイズ(table)

512KB パラレル発動最小サイズ(index)
この3項目だけは、
1000
1000 コスト推定値/Worker
最低限 確認が必要
0.1

0.1 コスト推定値/Tuple

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

15

パラレルクエリ使用の際の考慮点
 パラレルクエリ関連のパラメータでは以下に注意
 max_worker_processes は一種のハードリミットなので使用可能な物理コア数以下に設定
 max_parallel_workers_per_gather,max_parallel_workers はソフトリミットに近い存在でチューニングが必要 max_parallel_workersはクエリ実行につき適用されるので、複数クライアントからクエリを実行されると全体の合計値はこの設定値を超える
 max_parallel_workers_per_gatherはGatherノード毎に適用される
 1クエリ中に複数のGatherノードが現れる場合がありうるので要注意。

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

16

パラレルクエリ使用の際の考慮点
 クエリがどの程度のコアを使用するのか？
 テーブルサイズで(ほぼ)確定する。

 正確を期するなら、実行計画を表示する。

EXPLAIN SELECT …
 Gatherノードの表示を確認->

Gather (cost=4254.90..166694.20 rows=9700 width=8)
Workers Planned: 5

上記の場合は、このGatherノードに対して、Background Workerが5プロセス割り当てられる
 ただし、パラメータ設定値でクリッピングされるので注意

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

17

パラレルクエリ使用の際の考慮点
 適切なメモリを見積もるために psql から以下のようにすればテーブルサイズの概算値が取得可能=# ¥d+
List of relations
Schema |Name| Type | Owner |Size| Description--------+------------------------+-------+-------+------------+------------public | call_center| table | kondo | 8192 bytes |public | catalog_page| table | kondo | 2264 kB|
public | catalog_returns| table | kondo | 65 MB|
public | catalog_sales| table | kondo | 799 MB|
public | customer| table | kondo | 38 MB|
public | customer_address| table | kondo | 13 MB|
public | customer_demographics | table | kondo | 214 MB|
public | date_dim| table | kondo | 15 MB|Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

18

パラレルクエリ使用の際の考慮点
 パラレルクエリを使用する際は、テーブルのデータがメモリに載っているかが肝心 PostgreSQLはOSのディスクキャッシュを活用するが…
 PostgreSQLの共有バッファに全データを載せることも検討する価値が十分にある shared_buffersをテーブルサイズ以上に設定する
 Contributionパッケージ内のpg_prewarmを使用すれば、全データを簡単に共有バッファに載せることが可能

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

19リソースがあれば 理論に近い高速化が可能← Faster 以下の例では、40並列付近まで高速化が認められる

 2016年度活動報告書より抜粋
 物理マシンで測定
 1テーブルのパラレル検索・集約
 テーブルサイズは230GB程度
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

20利用する際に注意すべき点パラレルクエリの落とし穴！？

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

21

① リソースが足りない場合

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

22リソースは絶対に確保すること！
 パラレルのプロセス数以上のCPUコアを確保！
 PostgreSQLはマシンのCPUコア数は見ていない 複数プロセスに処理を割り当てて実行する
 一見動いているように見えるがパフォーマンス悪化
 複数のパラレルクエリが同時実行される場合も同様

 テーブルサイズ以上のメモリを確保！
 共有バッファが足りなければ、データは追い出される
 OSのキャッシュメモリも同様

 そして、ディスクにアクセスが集中する
 I/Oネック発生でパフォーマンス悪化
 CPU使用率が高くならない

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

23

コア数以上のプロセス数になると…
並列度6とCPUコア数

[ms]
16000

14000

← Faster

12000

10000

 仮想マシンで測定
 1テーブルのパラレルスキャン
 テーブルサイズは2GB強 max_parallel_workers_per_gatherは6で固定(パラレルOFFを除く)
 仮想CPU数を変動

8000

6000

4000

2000

0
パラレルOFF

1246[個]vCPU数
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

24

I/Oネックに引っかかると…
 性能が頭打ちになる← Faster 下記の例では、2並列の時点で性能が頭打ちに 2016年度活動報告書より抜粋
 物理マシンで測定
 1テーブルのパラレル検索・集約
 テーブルサイズは230GB程度
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

25

② 大きいテーブルどうしの結合

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

26

大きいテーブルどうしの結合は避ける テーブルの結合にもパラレルクエリは使用される
 しかし、結合対象の2テーブルのうち、片側にしかパラレルクエリが用いられない。
 もう片方のテーブルは必ず全スキャン
GatherHashJoin
Parallel
Seq ScanHashJoin
Parallel
Seq ScanHashテーブル
Seq Scan
全スキャン!

HashJoin

HashParallel
Seq Scanテーブル
Seq Scan
全スキャン!

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

Hash
テーブル
Seq Scan
全スキャン!
27

大きいテーブルどうしの結合をすると…
大きめのテーブルの結合

[ms]
18000

 2テーブルの結合(Hash Join) テーブルサイズは1GB強×216000← Faster

14000

12000

10000

8000

6000

4000

2000

0
パラレルOFF

1246

Worker数
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

28

③ パラレルクエリを使用したら思わぬ伏兵が!?

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

29

『コスト推定値が…』ということは パラレルクエリが選択されるのは…
 パラレルでない場合の実行計画よりもコスト推定値が下回った場合
 同じクエリでもコスト推定値が異なるということを示唆 パラレルクエリを選択した部分のコスト値が低下する

 すると…

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

30

2017年度成果発表会資料より抜粋

検証A パラレルクエリでの処理性能の改善状況
No.10 パラレルクエリの有効化によって劇的(2000倍以上)に高速化

ただし、並列処理で速くなったのではなく、実行計画のうち、実際に実行された部分が変化した結果、高速化。

No.11
 v9.6ではパラレルクエリを選択せず
 v10はパラレルクエリを選択し、8%程度の高速化
「非相関サブクエリのパラレル実行」(v10で新規対応)に該当したか？

パラレルクエリ無効の状態でも、v10がv9.5比で、両クエリともに20%程度高速化していることが判明
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

31

パラレルクエリでNo.10に何が起こったのか？
 実行計画のうち、実際に実行された部分が変化したのが主要因
 この変化はパラレルクエリが絡んでいるのか？

→ 結果的には絡んでいた
 実行計画に表示されているalternativesの部分が鍵(alternatives: SubPlan 1 or hashed SubPlan 2)

 alternatives とは…
 exists句の処理を高速化するため、続けて現れるサブクエリの結果を普通に得るか、ハッシュテーブル化するかを実行時に判断
 ハッシュテーブル化して得るとサブクエリの結果を保持するため大幅に高速化する場合がある Executorのコードの一部である、nodeSubplan.c のExecInitAlternativeSubPlan関数で判断。
 なんと、9年前(v8.4)からあるロジック。(Commit ID : bd3dadda)
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

32

alternatives の判定ロジック
 同一サブクエリに対するParent
AlternativeSubPlan
SubPlan

サブクエリの実行計画 1

HashSubPlan

サブクエリの実行計画 2

実行計画を2種類持つ
 実行時にExecutorが判断する
 EXPLAIN ANALYZE時、実行されなかった方は、必ずNever Executed

判定ロジック
SubPlanとHashSubPlanの初期コストをそれぞれ𝐶𝑠1 , 𝐶𝑠2
SubPlanとHashSubPlanの1実行あたりのコストをそれぞれ𝐶𝑝1 , 𝐶𝑝2
Parentにおける出力行数予測値を𝑅、SubPlanとHashSubPlanの初期コストをそれぞれ𝐶1 , 𝐶2 とすると、

𝐶1 = 𝐶𝑠1 + 𝑅 × 𝐶𝑝1 ,𝐶2 = 𝐶𝑠2 + 𝑅 × 𝐶𝑝2
𝐶1 < 𝐶2 → 𝐷𝑜 𝑆𝑢𝑏𝑃𝑙𝑎𝑛(𝐶1 ≥ 𝐶2 → 𝐷𝑜 𝐻𝑎𝑠ℎ𝑆𝑢𝑏𝑃𝑙𝑎𝑛)
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

33

パラレルクエリでNo.10に何が起こったのか？
 ずばり、HashSubPlan側のコスト推定値の減少
 このクエリでは、HashSubPlan側のみパラレルクエリを選択 結果、SubPlan側のコスト推定値以下となり、HashSubPlan側を実行 では、SubPlan側の実行計画が、HashSubPlan側と同様となるようすれば、パラレルクエリなしでも速くなるのでは？
同様 : Nested Loop結合ではなく、ハッシュ結合を選択させる

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

34

結合方法の制限結果
 結論 : 変わらない、または、遅い[秒]SubPlan側実行計画の結合方式と所要時間

14000← Faster

12000

10000

8000

6000

4000

2000

0
パラレルOFF

パラレルOFF+NestLoop OFF

パラレルOFF+NestLoop OFF+MergeJoin Off

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

35

パラレルクエリでNo.10に何が起こったのか？
 2000倍も高速化したのは、“Hash”SubPlanだったことが主要因であった可能性が高い SubPlanでは、結果を保持しておらず、参照されるたびにサブクエリが再実行された
 HashSubPlanでは、結果をハッシュテーブル化して保持していたため、サブクエリが再実行されることはなかった

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

36

まとめ

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

37

まとめ
 パラレルクエリは大量のデータを持つテーブルの検索・集約における応答時間の短縮が期待可能
 パラレルクエリを有効活用するには適切なハードウェアリソースとパラメータが必要 コスト推定値が従来と異なることによる影響に注意 パラレルクエリ実行により、実行計画の取得だけでは判明しない差異が存在
 EXPLAINではわからない。EXPLAIN ANALYZEが必要。

 パラレルクエリの効果が十分に得られるか、要確認 パラレルクエリ実行の処理時間と、オーバヘッドやパラレルクエリ部分以外の処理時間の割合を算出する、など
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

38

PostgreSQL Enterprise Consortium

POSTGRESQL ENTERPRISECONSORTIUM
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

39