OVERVIEW
試験概要

catch-img

第4回　パラレルクエリ

PostgreSQL新機能

パラレルクエリはPostgreSQL 9.6で導入された機能です。 9.6での導入以降、メジャーバージョンアップごとに改善がなされ、対象となる処理は増加しています。
今回のコラムではバージョン12でのパラレルクエリを前提に概説します。

1. パラレルクエリとは

パラレルクエリとは、クエリの実行を並列化することで複数のCPUを活用し、性能向上を図ることを目指す仕組みです。パラレルクエリは、大量のデータに対して集計/分析を行うような場合に効果が期待できます。

近年のサーバは複数のCPUを搭載したものが一般的になっていますが、パラレルクエリでは単一のクエリでも複数のプロセスにわたって処理を並列化できるため、これらのCPUを有効活用することができます。なお、PostgreSQLでは、スレッドではなくプロセス単位で並列実行を行います。本記事では並列実行のために起動されるプロセスをワーカー、ワーカーが並列処理した結果の集約など並列実行する部分以外も担うプロセスをリーダーと呼びます。


2. パラレルクエリの使い方

パラレルクエリ専用の構文はありません※。PostgreSQLが実行計画を立てる際に、パラレルクエリが最も効率的な方法であると判断した場合に、パラレルクエリが選択されます。アプリケーションレベルで処理を並列化しようとした場合、マルチプロセス/マルチスレッドで動作させることを意識して処理を記述する必要がありますが、パラレルクエリではユーザはそのような手間をかけることなく、並列処理の恩恵を受けることができます。

※例外として、pg_hint_planという実行計画を制御するエクステンションを利用すると、クエリ中にヒントを指定することでパラレルクエリの実行有無や並列数にヒントを与えることができます。

実行するクエリがパラレルクエリになるか調べるためには、実行計画を確認します。

まず、パラレルクエリを利用しない場合について見てみます。データはPostgreSQL同梱のベンチマークツールであるpgbenchのテーブルを利用します。

 -- 親テーブル定義
 $ # pgbenchを利用してデータを作成
 $ pgbench -i -s 50
 $ psql
 =# --パラレルクエリを無効にする。max_parallel_workers_per_gatherについては後述。
 =# SET max_parallel_workers_per_gather=0;
 =# EXPLAIN ANALYZE VERBOSE SELECT MAX(bid) FROM pgbench_accounts;
                                                                    QUERY PLAN
  
    ----------------------------------------------------------------------------------------------------------------------
     Aggregate  (cost=144468.00..144468.01 rows=1 width=4) (actual time=1421.764..1421.764 rows=1 loops=1)
       Output: max(bid)
       ->  Seq Scan on public.pgbench_accounts  (cost=0.00..131968.00 rows=5000000 width=4) (actual time=0.968..931.840 rows=5000000 loops=1)
             Output: aid, bid, abalance, filler
     Planning Time: 0.291 ms
     Execution Time: 1421.859 ms
postgres=# EXPLAIN ANALYZE VERBOSE SELECT MAX(bid) FROM pgbench_accounts ;

pgbench_accountsテーブルをシーケンシャルスキャンし、その結果の最大値を計算しています。特に変わったところはないシンプルな実行計画です。

続いて、パラレルクエリを利用してみましょう。

    =#  --max_parallel_workers_per_gatherをデフォルトの2に設定
    =# SET max_parallel_workers_per_gather=2;
    =# EXPLAIN ANALYZE VERBOSE SELECT MAX(bid) FROM pgbench_accounts;
    PLAN
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
     Finalize Aggregate  (cost=109009.88..109009.89 rows=1 width=4) (actual time=923.846..923.846 rows=1 loops=1) ..
       Output: max(bid) ..(エ)
       ->  Gather  (cost=109009.67..109009.88 rows=2 width=4) (actual time=923.836..924.041 rows=3 loops=1) ..(ウ)
             Output: (PARTIAL max(bid))
             Workers Planned: 2
             Workers Launched: 2
             ->  Partial Aggregate  (cost=108009.67..108009.68 rows=1 width=4) (actual time=918.758..918.759 rows=1 loops=3) ..(イ)
                   Output: PARTIAL max(bid)
                   Worker 0: actual time=916.668..916.668 rows=1 loops=1
                   Worker 1: actual time=916.115..916.115 rows=1 loops=1
                   ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..102 801.33 rows=2083333 width=4) (actual time=0.930..618.785 rows=1666667 loops=3)  ..(ア)
                         Output: aid, bid, abalance, filler
                         Worker 0: actual time=0.822..635.588 rows=1836418 loops=1 ..(オ)
                         Worker 1: actual time=0.051..616.901 rows=1598505 loops=1 ..(オ)
     Planning Time: 0.108 ms
     Execution Time: 924.110 ms

実行計画がやや複雑になりましたが、'Worker 0'などの出力があり、パラレルクエリ用のワーカーが起動していることが推測できるかと思います。今回並列に実行されているのは、pgbench_accountsテーブルを並列にスキャンする部分 (ア)、とそれぞれのプロセス単位での最大値を計算する部分(イ)です。その後この実行計画では、リーダーが2の結果を集めた(ウ)上で、全体としての最大値を計算(エ)しています。 なお、並列に実行されている1と2の処理には、リーダーも参加しています。つまり今回の例ではワーカー2つに加えリーダー1つで並列実行されるため、実際の並列度は3になります※。

※スキャンした行数をみると、リーダーが並列実行に参加していることがわかります。(オ)をみると、Worker 0は1,836,418、Worker 1は1,598,505なので、合計3,434,923ですが、pgbench_accountsテーブルの行数は5,000,000なので1,565,077行差異があります。この差分がリーダーがスキャンした行数になります。


3. パラレルクエリが実行できる処理

PostgreSQL 12では、以下の処理が並列実行可能です。

(1)スキャン

  • シーケンシャルスキャン
  • インデックススキャン
  • インデックスオンリースキャン
  • ビットマップヒープスキャン

(2)結合

  • ネストループ結合
  • マージ結合
  • ハッシュ結合

(3)アペンド

  • Append

(4)CREATE文

  • CREATE INDEX
  • CREATE TABLE ... AS
  • CREATE MATERIALIZED VIEW

(5)SELECT文

  • SELECT INTO

逆に以下のような操作はパラレルクエリとしては実行できないため、通常のクエリとして実行します。以下主要なものを記載しますが、詳細を把握したい場合はマニュアルの「第15章 パラレルクエリ」を参照してください。

(1)データを書き込んだり、行のロックを取得するクエリ
たとえばUPDATE文やDELETE文などはパラレルクエリとして実行できません。上のCREATE文、SELECT文は例外的にパラレルクエリとして実行できます。

(2)クエリ実行中にサスペンドされる可能性のあるクエリ
たとえば、カーソルを使った操作や、PL/pgSQLのループはパラレルクエリとしては実行できません。

(3)並列に実行ができない関数
関数は並列に実行可能かどうかが定義されています。利用したい関数が並列に実行できるかどうかは、pg_procというシステムカタログの proparallel列から確認できます。

表.1 proparallel列


proparallel

説明

s

parallel safe。並列実行可能

r

parallel restricted。リーダーでは実行可能だがワーカーでは実行不可

u

parallel unsafe。並列実行不可

(4)parallel restrictedな処理
以下のような処理はparallel restrictedな処理なので、リーダーでは実行可能ですがワーカーでは実行できません。

  • 共通テーブル式(CTE)のスキャン
  • 一時テーブルのスキャン


4. パラレルクエリが実行されるための条件

パラレルクエリが実行できる処理でも、必ずパラレルクエリとなるわけではなりません。主な条件を列挙します。

(1) max_parallel_workers_per_gatherが1以上であること
先程パラレルクエリの実行計画を確認した際に、(ウ)にGatherというノードが現れていました。Gatherより下の部分が並列に実行されるのですが、max_parallel_workers_per_gatherは1つのGatherに起動できる最大のワーカー数を設定します。 max_parallel_workers_per_gatherを0に設定すると1つもワーカーを起動できなくなるため、1以上に設定する必要があります。冒頭のクエリではパラレルクエリを無効にした実行計画を確認するために、このパラメータを0に変更していました。

(2)パラレルクエリのワーカーが起動できること
PostgreSQLインスタンスが1時点で起動できるパラレルクエリのワーカー数はmax_parallel_workersというパラメータで上限がかけられています。​​​​​​​同様に、パラレルクエリを含むワーカ一プロセス一般についてもmax_worker_processesというパラメータで上限がかけられています※。このため、これらのパラメータが1以上に設定されており、まだこれらのパラメータの上限に達していないことがパラレル化の必要条件となります。

※パラレルクエリのワーカーは、バックグラウンドワーカーというPostgreSQLからユーザなどが提供するコードを別のプロセスで実行できる仕組みを利用しています。このバックグラウンドワーカーの起動できるプロセスの上限数を指定するのがmax_worker_processesです。

(3)パラレルクエリを実行できるテーブルサイズであること
あまりにも小さいテーブルに対してパラレルクエリを実行しても、相対的に処理の分割損が大きくなり、並列実行の恩恵を受けることが難しいと考えられます。
​​​​​​​PostgreSQLでは min_prallel_table_scan_sizeというパラメータでパラレルクエリを実行できる最小のテーブルサイズを設定しており、この値より大きいテーブルサイズである必要があります。

(4)パラレルクエリを実行するのが効率的であること
PostgreSQLは実行計画を作成する際に、並列実行しない方法とコスト比較し、並列実行するのが最も効率が高いと見積もった場合にパラレルクエリを選択します。このコストを計算する基礎値を与えるパラメータにparallel_setup_costとparallel_tuple_costがあります。 それぞれパラレルクエリを実行する初期コスト、パラレルワーカーから他のプロセスへ1行を渡すコストです。



5. 並列数の決定

パラレルクエリを実行する場合、PostgreSQLは起動するワーカーの数をスキャンするテーブルの大きさを元に決定します。具体的にはテーブルの大きさが3倍になるとワーカー数が1増えます。実行するマシンのCPUコア数などは考慮しません。このため、大規模なテーブルをスキャンするクエリが複数ある場合などは、物理的なコア数以上にプロセスを起動してしまう可能性がありますが、この場合並列実行は性能向上に寄与しないでしょう。 並列数を指定したい場合、テーブルのパラメータであるparallel_workersを変更します。※

※前述のとおりpg_hint_planを利用している場合、クエリ中のヒントでテーブルの並列度を指定することが可能です。

以下の例では、PostgreSQLがテーブルの大きさからワーカー数を4としているクエリについて、parallel_workersを変更することでワーカー数を5に変更しています。

まず、PostgreSQLにワーカー数を決めさせてみます。
Workers 0 ~ 3まで起動されており、合計4つのワーカーが起動したことが確認できます。

  =# --まずmax_parallel_workers_per_gatherによる制限を受けないようにする
  =# SET max_parallel_workers_per_gather=8;
  
  =# テーブルの大きさからPostgreSQLに自動で並列数を決めさせると、ワーカー数は4となる
  =# EXPLAIN ANALYZE VERBOSE  SELECT MAX(bid) FROM pgbench_accounts ;
                                                                            QUERY PLAN
  
    ----------------------------------------------------------------------------------------------------------------------
     Finalize Aggregate  (cost=98593.42..98593.43 rows=1 width=4) (actual time=1031.122..1031.123 rows=1 loops=1)
       Output: max(bid)
       ->  Gather  (cost=98593.00..98593.41 rows=4 width=4) (actual time=1028.404..1031.313 rows=5 loops=1)
             Output: (PARTIAL max(bid))
             Workers Planned: 4
             Workers Launched: 4
             ->  Partial Aggregate  (cost=97593.00..97593.01 rows=1 width=4) (actual time=1011.296..1011.297 rows=1 loops=5)
                   Output: PARTIAL max(bid)
                   Worker 0: actual time=987.859..987.860 rows=1 loops=1
                   Worker 1: actual time=1021.843..1021.844 rows=1 loops=1
                   Worker 2: actual time=1005.188..1005.188 rows=1 loops=1
                   Worker 3: actual time=1013.722..1013.723 rows=1 loops=1
                   ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..94468.00 rows=1250000 width=4) (actual time=0.312..750.101 rows=1000000 loops=5)
                         Output: aid, bid, abalance, filler
                         Worker 0: actual time=0.071..790.200 rows=1063914 loops=1
                         Worker 1: actual time=0.075..701.452 rows=943975 loops=1
                         Worker 2: actual time=0.102..749.747 rows=1045418 loops=1
                         Worker 3: actual time=0.093..697.032 rows=909266 loops=1

続いて、parallel_workersを指定してみます。
Workers 0 ~ 4まで起動されており、合計5つのワーカーが起動したことが確認できます。

  =# --pgbench_accountsテーブルのparallel_workersを5に指定する
  =# ALTER TABLE pgbench_accounts SET (parallel_workers = 5);
  
  =# --ワーカー数が5となる
  =# EXPLAIN ANALYZE VERBOSE  SELECT MAX(bid) FROM pgbench_accounts ;
  
  ----------------------------------------------------------------------------------------------------------------------
   Finalize Aggregate  (cost=95468.52..95468.53 rows=1 width=4) (actual time=1066.255..1066.255 rows=1 loops=1)
     Output: max(bid)
     ->  Gather  (cost=95468.00..95468.51 rows=5 width=4) (actual time=1066.243..1068.636 rows=6 loops=1)
           Output: (PARTIAL max(bid))
           Workers Planned: 5
           Workers Launched: 5
           ->  Partial Aggregate  (cost=94468.00..94468.01 rows=1 width=4) (actual time=1047.896..1047.896 rows=1 loops= 6)
                 Output: PARTIAL max(bid)
                 Worker 0: actual time=1054.588..1054.588 rows=1 loops=1
                 Worker 1: actual time=1033.509..1033.510 rows=1 loops=1
                 Worker 2: actual time=1030.231..1030.231 rows=1 loops=1
                 Worker 3: actual time=1044.894..1044.894 rows=1 loops=1
                 Worker 4: actual time=1058.312..1058.312 rows=1 loops=1
                 ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..91968.00 rows=1000000 width=4) (actual time=0.561..809.169 rows=833333 loops=6)
                       Output: aid, bid, abalance, filler
                       Worker 0: actual time=0.730..799.688 rows=799845 loops=1
                       Worker 1: actual time=0.089..882.398 rows=966789 loops=1
                       Worker 2: actual time=0.948..672.450 rows=775371 loops=1
                       Worker 3: actual time=0.059..804.914 rows=838323 loops=1
                       Worker 4: actual time=0.066..810.470 rows=718458 loops=1
   Planning Time: 0.218 ms
   Execution Time: 1068.700 ms

なお、パラレル数を2倍にしても一般に性能は2倍になりません。これは並列化できない部分の存在や分割によるオーバーヘッド増加などによるものです。このため、過度に並列数を増やしても、通常あまり効果は期待できないです。テーブルの大きさが3倍になると1ワーカーを増やすというPostgreSQLの方針は、テーブルが大きくなってもあまり並列数を上げないことになるため、この並列処理の性質と適合しています。

今回は、PostgreSQLのパラレルクエリの概要から対象となる処理、実際にパラレルクエリが実行されるための条件や並列数の決め方について概説しました。うまくパラレルクエリが動作した場合、性能が劇的に改善することも少なくないので、性能改善の手段の1つとして押さえておくとよいと思います。


<参考資料>

(1)PostgreSQL マニュアル 

(2)MasahikoSawada: Parallel Queryの概要
(3)PGECons: 勉強会#2 パラレルクエリ



鳥越　淳（著者）

 2008年頃からオープンソースソフトウェアの技術調査や案件導入に従事。PostgreSQLについては、PGConf.ASIA,PostgresConf USなどに登壇。共著に『PostgreSQL徹底入門 第4版』(翔泳社)がある。


前の記事

prev-article-image

第3回　宣言的パーティション-2

English

© EDUCO All Rights Reserved.