🥇PostgreSQL의 병렬 쿼리

최신 CPU에는 많은 코어가 있습니다. 수년 동안 애플리케이션은 데이터베이스에 쿼리를 병렬로 전송해 왔습니다. 테이블의 여러 행에 대한 보고서 쿼리인 경우 여러 CPU를 사용할 때 더 빠르게 실행되며 PostgreSQL은 버전 9.6부터 이를 수행할 수 있었습니다.

병렬 쿼리 기능을 구현하는 데 3년이 걸렸습니다. 쿼리 실행의 여러 단계에서 코드를 다시 작성해야 했습니다. PostgreSQL 9.6은 코드를 더욱 개선하기 위해 인프라를 도입했습니다. 후속 버전에서는 다른 유형의 쿼리가 병렬로 실행됩니다.

제한

모든 코어가 이미 사용 중인 경우 병렬 실행을 활성화하지 마십시오. 그렇지 않으면 다른 요청이 느려집니다.
가장 중요한 것은 WORK_MEM 값이 높은 병렬 처리는 많은 메모리를 사용한다는 것입니다. 각 해시 조인 또는 정렬은 work_mem 메모리를 차지합니다.
대기 시간이 짧은 OLTP 쿼리는 병렬 실행으로 가속화될 수 없습니다. 그리고 쿼리가 하나의 행을 반환하는 경우 병렬 처리로 인해 속도가 느려질 뿐입니다.
개발자는 TPC-H 벤치마크를 사용하는 것을 좋아합니다. 어쩌면 완벽한 병렬 실행을 위해 비슷한 쿼리가 있을 수도 있습니다.
조건자 잠금이 없는 SELECT 쿼리만 병렬로 실행됩니다.
때로는 적절한 인덱싱이 병렬 모드에서 순차적 테이블 스캔보다 더 나을 때도 있습니다.
쿼리 및 커서 일시 중지는 지원되지 않습니다.
창 함수와 순서 집합 집계 함수는 병렬이 아닙니다.
I/O 워크로드에서는 아무 것도 얻을 수 없습니다.
병렬 정렬 알고리즘은 없습니다. 그러나 정렬이 포함된 쿼리는 일부 측면에서 병렬로 실행될 수 있습니다.
병렬 처리를 활성화하려면 CTE (WITH ...)를 중첩된 SELECT로 바꾸세요.
타사 데이터 래퍼는 아직 병렬 처리를 지원하지 않습니다(그러나 가능합니다!).
FULL OUTER JOIN은 지원되지 않습니다.
max_rows는 병렬 처리를 비활성화합니다.
쿼리에 PARALLEL SAFE로 표시되지 않은 함수가 있는 경우 단일 스레드가 됩니다.
SERIALIZABLE 트랜잭션 격리 수준은 병렬 처리를 비활성화합니다.

테스트 환경

PostgreSQL 개발자는 TPC-H 벤치마크 쿼리의 응답 시간을 줄이려고 노력했습니다. 벤치마크를 다운로드하고 PostgreSQL에 적응. 이는 데이터베이스나 하드웨어 비교를 위한 것이 아닌 TPC-H 벤치마크의 비공식적 사용입니다.

TPC-H_Tools_v2.17.3.zip(또는 최신 버전) 다운로드 TPC 오프사이트에서.
makefile.suite의 이름을 Makefile로 바꾸고 여기에 설명된 대로 변경합니다. https://github.com/tvondra/pg_tpch . make 명령을 사용하여 코드를 컴파일합니다.
데이터 생성: ./dbgen -s 10 23GB 데이터베이스를 생성합니다. 이는 병렬 쿼리와 비병렬 쿼리의 성능 차이를 확인하기에 충분합니다.
파일 변환 tbl в csv с for и sed.
저장소 복제 pg_tpch 그리고 파일을 복사하세요 csv в pg_tpch/dss/data.
명령을 사용하여 쿼리 만들기 qgen.
다음 명령을 사용하여 데이터베이스에 데이터를 로드합니다. ./tpch.sh.

병렬 순차 스캐닝

병렬 읽기 때문이 아니라 데이터가 많은 CPU 코어에 분산되어 있기 때문에 더 빠를 수 있습니다. 최신 운영 체제에서는 PostgreSQL 데이터 파일이 잘 캐시됩니다. 미리 읽기를 사용하면 PG 데몬이 요청하는 것보다 스토리지에서 더 큰 블록을 가져올 수 있습니다. 따라서 쿼리 성능은 디스크 I/O에 의해 제한되지 않습니다. 다음을 위해 CPU 주기를 소비합니다.

테이블 페이지에서 한 번에 하나씩 행을 읽습니다.
문자열 값과 조건 비교 WHERE.

간단한 쿼리를 실행해 보겠습니다. select:

tpch=# explain analyze select l_quantity as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineitem (cost=0.00..1964772.00 rows=58856235 width=5) (actual time=0.014..16951.669 rows=58839715 loops=1)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1146337
Planning Time: 0.203 ms
Execution Time: 19035.100 ms

순차 스캔은 집계 없이 너무 많은 행을 생성하므로 쿼리가 단일 CPU 코어에 의해 실행됩니다.

추가하면 SUM(), 두 가지 워크플로가 쿼리 속도를 높이는 데 도움이 된다는 것을 알 수 있습니다.

explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1589702.14..1589702.15 rows=1 width=32) (actual time=8553.365..8553.365 rows=1 loops=1)
-> Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 ms

병렬 집계

병렬 시퀀스 스캔 노드는 부분 집계를 위한 행을 생성합니다. "부분 집계" 노드는 다음을 사용하여 이러한 선을 자릅니다. SUM(). 마지막에는 각 작업자 프로세스의 SUM 카운터가 "Gather" 노드에 의해 수집됩니다.

최종 결과는 "Finalize Aggregate" 노드에 의해 계산됩니다. 자신만의 집계 함수가 있는 경우 이를 "병렬 안전"으로 표시하는 것을 잊지 마세요.

작업자 프로세스 수

서버를 다시 시작하지 않고도 작업자 프로세스 수를 늘릴 수 있습니다.

explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1589702.14..1589702.15 rows=1 width=32) (actual time=8553.365..8553.365 rows=1 loops=1)
-> Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 ms

여기서 무슨 일이 일어나고 있는 걸까요? 작업 프로세스는 2배 늘어났고 요청 속도는 1,6599배 빨라졌습니다. 계산이 흥미롭습니다. 2개의 작업자 프로세스와 1개의 리더가 있었습니다. 변경 후 4+1이 되었습니다.

병렬 처리로 인한 최대 속도 향상: 5/3 = 1,66(6)배.

어떻게 작동합니까?

프로세스

요청 실행은 항상 선행 프로세스에서 시작됩니다. 리더는 모든 비병렬 처리와 일부 병렬 처리를 수행합니다. 동일한 요청을 수행하는 다른 프로세스를 작업자 프로세스라고 합니다. 병렬 처리는 인프라를 사용합니다. 동적 배경 작업자 프로세스 (버전 9.4부터). PostgreSQL의 다른 부분은 스레드가 아닌 프로세스를 사용하므로 3개의 작업자 프로세스가 있는 쿼리는 기존 처리보다 4배 더 빠를 수 있습니다.

상호 작용

작업자 프로세스는 메시지 대기열(공유 메모리 기반)을 통해 리더와 통신합니다. 각 프로세스에는 오류용 대기열과 튜플용 대기열이 2개 있습니다.

얼마나 많은 워크플로가 필요합니까?

최소 한도는 매개변수로 지정됩니다. max_parallel_workers_per_gather. 그런 다음 요청 실행자는 매개변수에 의해 제한된 풀에서 작업자 프로세스를 가져옵니다. max_parallel_workers size. 마지막 제한은 max_worker_processes, 즉 총 백그라운드 프로세스 수입니다.

작업자 프로세스를 할당할 수 없는 경우 처리는 단일 프로세스로 수행됩니다.

쿼리 플래너는 테이블이나 인덱스의 크기에 따라 워크플로를 줄일 수 있습니다. 이에 대한 매개변수가 있습니다. min_parallel_table_scan_size и min_parallel_index_scan_size.

set min_parallel_table_scan_size='8MB'
8MB table => 1 worker
24MB table => 2 workers
72MB table => 3 workers
x => log(x / min_parallel_table_scan_size) / log(3) + 1 worker

테이블이 3배 더 커질 때마다 min_parallel_(index|table)_scan_size, Postgres는 작업자 프로세스를 추가합니다. 워크플로 수는 비용을 기준으로 하지 않습니다. 순환 종속성은 복잡한 구현을 어렵게 만듭니다. 대신 플래너는 간단한 규칙을 사용합니다.

실제로 이러한 규칙은 항상 프로덕션에 적합한 것은 아니므로 특정 테이블에 대한 작업자 프로세스 수를 변경할 수 있습니다. ALTER TABLE ... SET (parallel_workers = N).

병렬 처리를 사용하지 않는 이유는 무엇입니까?

긴 제한 사항 목록 외에도 비용 확인도 있습니다.

parallel_setup_cost - 짧은 요청의 병렬 처리를 방지합니다. 이 매개변수는 메모리 준비, 프로세스 시작 및 초기 데이터 교환에 소요되는 시간을 추정합니다.

parallel_tuple_cost: 작업 프로세스의 튜플 개수에 비례하여 리더와 워커 간의 통신이 지연될 수 있습니다. 이 매개변수는 데이터 교환 비용을 계산합니다.

중첩된 루프 조인

PostgreSQL 9.6+ может выполнять вложенные циклы параллельно — это простая операция.

explain (costs off) select c_custkey, count(o_orderkey)
                from    customer left outer join orders on
                                c_custkey = o_custkey and o_comment not like '%special%deposits%'
                group by c_custkey;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: customer.c_custkey
   ->  Gather Merge
         Workers Planned: 4
         ->  Partial GroupAggregate
               Group Key: customer.c_custkey
               ->  Nested Loop Left Join
                     ->  Parallel Index Only Scan using customer_pkey on customer
                     ->  Index Scan using idx_orders_custkey on orders
                           Index Cond: (customer.c_custkey = o_custkey)
                           Filter: ((o_comment)::text !~~ '%special%deposits%'::text)

수집은 마지막 단계에서 발생하므로 Nested Loop Left Join은 병렬 작업입니다. 병렬 인덱스 전용 스캔은 버전 10에서만 도입되었습니다. 병렬 직렬 스캔과 유사하게 작동합니다. 상태 c_custkey = o_custkey 클라이언트 문자열당 하나의 주문을 읽습니다. 따라서 평행하지 않습니다.

해시 조인

PostgreSQL 11까지 각 작업자 프로세스는 자체 해시 테이블을 생성합니다. 이러한 프로세스가 XNUMX개 이상 있으면 성능이 향상되지 않습니다. 새 버전에서는 해시 테이블이 공유됩니다. 각 작업자 프로세스는 WORK_MEM을 사용하여 해시 테이블을 생성할 수 있습니다.

select
        l_shipmode,
        sum(case
                when o_orderpriority = '1-URGENT'
                        or o_orderpriority = '2-HIGH'
                        then 1
                else 0
        end) as high_line_count,
        sum(case
                when o_orderpriority <> '1-URGENT'
                        and o_orderpriority <> '2-HIGH'
                        then 1
                else 0
        end) as low_line_count
from
        orders,
        lineitem
where
        o_orderkey = l_orderkey
        and l_shipmode in ('MAIL', 'AIR')
        and l_commitdate < l_receiptdate
        and l_shipdate < l_commitdate
        and l_receiptdate >= date '1996-01-01'
        and l_receiptdate < date '1996-01-01' + interval '1' year
group by
        l_shipmode
order by
        l_shipmode
LIMIT 1;
                                                                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1964755.66..1964961.44 rows=1 width=27) (actual time=7579.592..7922.997 rows=1 loops=1)
   ->  Finalize GroupAggregate  (cost=1964755.66..1966196.11 rows=7 width=27) (actual time=7579.590..7579.591 rows=1 loops=1)
         Group Key: lineitem.l_shipmode
         ->  Gather Merge  (cost=1964755.66..1966195.83 rows=28 width=27) (actual time=7559.593..7922.319 rows=6 loops=1)
               Workers Planned: 4
               Workers Launched: 4
               ->  Partial GroupAggregate  (cost=1963755.61..1965192.44 rows=7 width=27) (actual time=7548.103..7564.592 rows=2 loops=5)
                     Group Key: lineitem.l_shipmode
                     ->  Sort  (cost=1963755.61..1963935.20 rows=71838 width=27) (actual time=7530.280..7539.688 rows=62519 loops=5)
                           Sort Key: lineitem.l_shipmode
                           Sort Method: external merge  Disk: 2304kB
                           Worker 0:  Sort Method: external merge  Disk: 2064kB
                           Worker 1:  Sort Method: external merge  Disk: 2384kB
                           Worker 2:  Sort Method: external merge  Disk: 2264kB
                           Worker 3:  Sort Method: external merge  Disk: 2336kB
                           ->  Parallel Hash Join  (cost=382571.01..1957960.99 rows=71838 width=27) (actual time=7036.917..7499.692 rows=62519 loops=5)
                                 Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
                                 ->  Parallel Seq Scan on lineitem  (cost=0.00..1552386.40 rows=71838 width=19) (actual time=0.583..4901.063 rows=62519 loops=5)
                                       Filter: ((l_shipmode = ANY ('{MAIL,AIR}'::bpchar[])) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1996-01-01'::date) AND (l_receiptdate < '1997-01-01 00:00:00'::timestamp without time zone))
                                       Rows Removed by Filter: 11934691
                                 ->  Parallel Hash  (cost=313722.45..313722.45 rows=3750045 width=20) (actual time=2011.518..2011.518 rows=3000000 loops=5)
                                       Buckets: 65536  Batches: 256  Memory Usage: 3840kB
                                       ->  Parallel Seq Scan on orders  (cost=0.00..313722.45 rows=3750045 width=20) (actual time=0.029..995.948 rows=3000000 loops=5)
 Planning Time: 0.977 ms
 Execution Time: 7923.770 ms

TPC-H의 쿼리 12는 병렬 해시 연결을 명확하게 보여줍니다. 각 작업자 프로세스는 공통 해시 테이블 생성에 기여합니다.

병합 조인

병합 조인은 본질적으로 비병렬적입니다. 이것이 쿼리의 마지막 단계이더라도 걱정하지 마세요. 여전히 병렬로 실행될 수 있습니다.

-- Query 2 from TPC-H
explain (costs off) select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from    part, supplier, partsupp, nation, region
where
        p_partkey = ps_partkey
        and s_suppkey = ps_suppkey
        and p_size = 36
        and p_type like '%BRASS'
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'AMERICA'
        and ps_supplycost = (
                select
                        min(ps_supplycost)
                from    partsupp, supplier, nation, region
                where
                        p_partkey = ps_partkey
                        and s_suppkey = ps_suppkey
                        and s_nationkey = n_nationkey
                        and n_regionkey = r_regionkey
                        and r_name = 'AMERICA'
        )
order by s_acctbal desc, n_name, s_name, p_partkey
LIMIT 100;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
         ->  Merge Join
               Merge Cond: (part.p_partkey = partsupp.ps_partkey)
               Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
               ->  Gather Merge
                     Workers Planned: 4
                     ->  Parallel Index Scan using <strong>part_pkey</strong> on part
                           Filter: (((p_type)::text ~~ '%BRASS'::text) AND (p_size = 36))
               ->  Materialize
                     ->  Sort
                           Sort Key: partsupp.ps_partkey
                           ->  Nested Loop
                                 ->  Nested Loop
                                       Join Filter: (nation.n_regionkey = region.r_regionkey)
                                       ->  Seq Scan on region
                                             Filter: (r_name = 'AMERICA'::bpchar)
                                       ->  Hash Join
                                             Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
                                             ->  Seq Scan on supplier
                                             ->  Hash
                                                   ->  Seq Scan on nation
                                 ->  Index Scan using idx_partsupp_suppkey on partsupp
                                       Index Cond: (ps_suppkey = supplier.s_suppkey)
               SubPlan 1
                 ->  Aggregate
                       ->  Nested Loop
                             Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
                             ->  Seq Scan on region region_1
                                   Filter: (r_name = 'AMERICA'::bpchar)
                             ->  Nested Loop
                                   ->  Nested Loop
                                         ->  Index Scan using idx_partsupp_partkey on partsupp partsupp_1
                                               Index Cond: (part.p_partkey = ps_partkey)
                                         ->  Index Scan using supplier_pkey on supplier supplier_1
                                               Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
                                   ->  Index Scan using nation_pkey on nation nation_1
                                         Index Cond: (n_nationkey = supplier_1.s_nationkey)

"Merge Join" 노드는 "Gather Merge" 위에 위치합니다. 따라서 병합에서는 병렬 처리를 사용하지 않습니다. 그러나 "Parallel Index Scan" 노드는 여전히 세그먼트에 도움이 됩니다. part_pkey.

구간별 연결

PostgreSQL 11에서 구간별 연결 기본적으로 비활성화되어 있습니다. 스케줄링 비용이 매우 높습니다. 유사한 파티셔닝을 가진 테이블은 파티션별로 조인될 수 있습니다. 이런 식으로 Postgres는 더 작은 해시 테이블을 사용합니다. 각 섹션의 연결은 평행할 수 있습니다.

tpch=# set enable_partitionwise_join=t;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
                    QUERY PLAN
---------------------------------------------------
 Append
   ->  Hash Join
         Hash Cond: (t2.b = t1.a)
         ->  Seq Scan on prt2_p1 t2
               Filter: ((b >= 0) AND (b <= 10000))
         ->  Hash
               ->  Seq Scan on prt1_p1 t1
                     Filter: (b = 0)
   ->  Hash Join
         Hash Cond: (t2_1.b = t1_1.a)
         ->  Seq Scan on prt2_p2 t2_1
               Filter: ((b >= 0) AND (b <= 10000))
         ->  Hash
               ->  Seq Scan on prt1_p2 t1_1
                     Filter: (b = 0)
tpch=# set parallel_setup_cost = 1;
tpch=# set parallel_tuple_cost = 0.01;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
                        QUERY PLAN
-----------------------------------------------------------
 Gather
   Workers Planned: 4
   ->  Parallel Append
         ->  Parallel Hash Join
               Hash Cond: (t2_1.b = t1_1.a)
               ->  Parallel Seq Scan on prt2_p2 t2_1
                     Filter: ((b >= 0) AND (b <= 10000))
               ->  Parallel Hash
                     ->  Parallel Seq Scan on prt1_p2 t1_1
                           Filter: (b = 0)
         ->  Parallel Hash Join
               Hash Cond: (t2.b = t1.a)
               ->  Parallel Seq Scan on prt2_p1 t2
                     Filter: ((b >= 0) AND (b <= 10000))
               ->  Parallel Hash
                     ->  Parallel Seq Scan on prt1_p1 t1
                           Filter: (b = 0)

가장 중요한 것은 섹션의 연결이 해당 섹션이 충분히 큰 경우에만 평행하다는 것입니다.

병렬 추가

병렬 추가 다양한 워크플로의 다양한 블록 대신 사용할 수 있습니다. 이는 일반적으로 UNION ALL 쿼리에서 발생합니다. 단점은 각 작업자 프로세스가 1개의 요청만 처리하기 때문에 병렬성이 낮다는 것입니다.

여기서는 2개의 작업자 프로세스가 실행 중이지만 4개가 활성화되어 있습니다.

tpch=# explain (costs off) select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day union all select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '2000-12-01' - interval '105' day;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Gather
   Workers Planned: 2
   ->  Parallel Append
         ->  Aggregate
               ->  Seq Scan on lineitem
                     Filter: (l_shipdate <= '2000-08-18 00:00:00'::timestamp without time zone)
         ->  Aggregate
               ->  Seq Scan on lineitem lineitem_1
                     Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)

가장 중요한 변수

WORK_MEM은 쿼리뿐만 아니라 프로세스당 메모리를 제한합니다. 프로세스들 연결 = 많은 메모리.
max_parallel_workers_per_gather — 실행 프로그램이 계획의 병렬 처리에 사용할 작업자 프로세스 수.
max_worker_processes — 총 작업자 프로세스 수를 서버의 CPU 코어 수에 맞게 조정합니다.
max_parallel_workers - 동일하지만 병렬 작업 프로세스의 경우입니다.

결과

버전 9.6부터 병렬 처리는 많은 행이나 인덱스를 검색하는 복잡한 쿼리의 성능을 크게 향상시킬 수 있습니다. PostgreSQL 10에서는 병렬 처리가 기본적으로 활성화되어 있습니다. OLTP 작업량이 많은 서버에서는 이를 비활성화하는 것을 잊지 마십시오. 순차 스캔이나 인덱스 스캔은 많은 리소스를 소모합니다. 전체 데이터 세트에 대한 보고서를 실행하지 않는 경우 누락된 인덱스를 추가하거나 적절한 분할을 사용하여 쿼리 성능을 향상시킬 수 있습니다.

참조

출처 : habr.com

PostgreSQL의 병렬 쿼리

제한