CPU hiện đại có rất nhiều lõi. Trong nhiều năm, các ứng dụng đã gửi các truy vấn đến cơ sở dữ liệu một cách song song. Nếu đó là truy vấn báo cáo trên nhiều hàng trong bảng, nó sẽ chạy nhanh hơn khi sử dụng nhiều CPU và PostgreSQL đã có thể thực hiện việc này kể từ phiên bản 9.6.
Phải mất 3 năm để triển khai tính năng truy vấn song song - chúng tôi phải viết lại mã ở các giai đoạn thực hiện truy vấn khác nhau. PostgreSQL 9.6 đã giới thiệu cơ sở hạ tầng để cải thiện mã hơn nữa. Trong các phiên bản tiếp theo, các loại truy vấn khác được thực hiện song song.
Hạn chế
- Không kích hoạt thực thi song song nếu tất cả các lõi đều đang bận, nếu không các yêu cầu khác sẽ chậm lại.
- Quan trọng nhất, việc xử lý song song với giá trị WORK_MEM cao sử dụng nhiều bộ nhớ - mỗi phép nối hoặc sắp xếp băm sẽ chiếm bộ nhớ work_mem.
- Không thể tăng tốc các truy vấn OLTP có độ trễ thấp bằng cách thực thi song song. Và nếu truy vấn trả về một hàng, việc xử lý song song sẽ chỉ làm chậm truy vấn.
- Các nhà phát triển thích sử dụng điểm chuẩn TPC-H. Có thể bạn có các truy vấn tương tự để thực hiện song song hoàn hảo.
- Chỉ các truy vấn SELECT không có khóa vị từ mới được thực thi song song.
- Đôi khi việc lập chỉ mục phù hợp sẽ tốt hơn việc quét bảng tuần tự ở chế độ song song.
- Việc tạm dừng truy vấn và con trỏ không được hỗ trợ.
- Các hàm cửa sổ và các hàm tổng hợp tập hợp có thứ tự không song song.
- Bạn không đạt được gì trong khối lượng công việc I/O.
- Không có thuật toán sắp xếp song song. Nhưng các truy vấn có sắp xếp có thể được thực hiện song song ở một số khía cạnh.
- Thay thế CTE (VỚ ...) bằng CHỌN lồng nhau để cho phép xử lý song song.
- Trình bao bọc dữ liệu của bên thứ ba chưa hỗ trợ xử lý song song (nhưng họ có thể!)
- FULL OUTER THAM GIA không được hỗ trợ.
- max_rows vô hiệu hóa xử lý song song.
- Nếu một truy vấn có hàm không được đánh dấu AN TOÀN SONG SONG thì truy vấn đó sẽ là một luồng đơn.
- Mức cô lập giao dịch SERIALIZABLE vô hiệu hóa xử lý song song.
Môi trường thử nghiệm
Các nhà phát triển PostgreSQL đã cố gắng giảm thời gian phản hồi của các truy vấn điểm chuẩn TPC-H. Tải xuống điểm chuẩn và . Đây là cách sử dụng không chính thức của điểm chuẩn TPC-H - không phải để so sánh cơ sở dữ liệu hoặc phần cứng.
- Tải xuống TPC-H_Tools_v2.17.3.zip (hoặc phiên bản mới hơn) .
- Đổi tên makefile.suite thành Makefile và thay đổi như mô tả ở đây: . Biên dịch mã bằng lệnh make.
- Tạo dữ liệu:
./dbgen -s 10tạo cơ sở dữ liệu 23 GB. Điều này đủ để thấy sự khác biệt về hiệu suất của các truy vấn song song và không song song. - Chuyển đổi tệp tin
tblвcsv с forиsed. - Sao chép kho lưu trữ
pg_tpchvà sao chép các tập tincsvвpg_tpch/dss/data. - Tạo truy vấn bằng lệnh
qgen. - Tải dữ liệu vào cơ sở dữ liệu bằng lệnh
./tpch.sh.
Quét tuần tự song song
Nó có thể nhanh hơn không phải vì đọc song song mà vì dữ liệu được trải rộng trên nhiều lõi CPU. Trong các hệ điều hành hiện đại, các tệp dữ liệu PostgreSQL được lưu trữ tốt. Với tính năng đọc trước, có thể nhận được khối lớn hơn từ bộ lưu trữ so với yêu cầu daemon PG. Do đó, hiệu suất truy vấn không bị giới hạn bởi I/O đĩa. Nó tiêu thụ chu kỳ CPU để:
- đọc từng hàng một từ các trang trong bảng;
- so sánh giá trị và điều kiện chuỗi
WHERE.
Hãy chạy một truy vấn đơn giản 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 msQuá trình quét tuần tự tạo ra quá nhiều hàng không được tổng hợp, do đó truy vấn được thực thi bởi một lõi CPU.
Nếu bạn thêm SUM(), bạn có thể thấy hai quy trình công việc sẽ giúp tăng tốc truy vấn:
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 msTổng hợp song song
Nút Quét tuần tự song song tạo ra các hàng để tổng hợp một phần. Nút "Tổng hợp một phần" cắt các dòng này bằng cách sử dụng SUM(). Cuối cùng, bộ đếm SUM từ mỗi quy trình công nhân được nút “Gather” thu thập.
Kết quả cuối cùng được tính toán bởi nút “Hoàn thiện tổng hợp”. Nếu bạn có các hàm tổng hợp của riêng mình, đừng quên đánh dấu chúng là “an toàn song song”.
Số lượng quy trình công nhân
Số lượng quy trình công nhân có thể tăng lên mà không cần khởi động lại máy chủ:
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 msNhững gì đang xảy ra ở đây? Quy trình làm việc tăng gấp 2 lần và yêu cầu chỉ nhanh hơn 1,6599 lần. Các tính toán thật thú vị. Chúng tôi có 2 quy trình công nhân và 1 người lãnh đạo. Sau khi thay đổi nó trở thành 4+1.
Tăng tốc tối đa của chúng tôi từ xử lý song song: 5/3 = 1,66(6) lần.
Nó hoạt động như thế nào?
Процессы
Việc thực hiện yêu cầu luôn bắt đầu với quy trình hàng đầu. Người lãnh đạo thực hiện mọi việc không song song và một số xử lý song song. Các quy trình khác thực hiện các yêu cầu tương tự được gọi là quy trình công nhân. Xử lý song song sử dụng cơ sở hạ tầng (từ phiên bản 9.4). Vì các phần khác của PostgreSQL sử dụng các quy trình thay vì các luồng, nên một truy vấn có 3 quy trình công nhân có thể nhanh hơn 4 lần so với xử lý truyền thống.
Tương tác
Các quy trình công nhân giao tiếp với người lãnh đạo thông qua hàng đợi tin nhắn (dựa trên bộ nhớ dùng chung). Mỗi tiến trình có 2 hàng đợi: hàng đợi lỗi và hàng đợi bộ dữ liệu.
Cần bao nhiêu quy trình công việc?
Giới hạn tối thiểu được xác định bởi tham số . Sau đó, trình chạy yêu cầu sẽ nhận các quy trình công nhân từ nhóm bị giới hạn bởi tham số . Hạn chế cuối cùng là , tức là tổng số tiến trình nền.
Nếu không thể phân bổ một quy trình công nhân, việc xử lý sẽ là một quy trình.
Trình lập kế hoạch truy vấn có thể giảm bớt quy trình công việc tùy thuộc vào kích thước của bảng hoặc chỉ mục. Có các thông số cho việc này и .
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 workerMỗi lần bảng lớn gấp 3 lần min_parallel_(index|table)_scan_size, Postgres thêm một quy trình công nhân. Số lượng quy trình công việc không dựa trên chi phí. Sự phụ thuộc vòng tròn làm cho việc triển khai phức tạp trở nên khó khăn. Thay vào đó, người lập kế hoạch sử dụng các quy tắc đơn giản.
Trong thực tế, những quy tắc này không phải lúc nào cũng phù hợp cho sản xuất, vì vậy bạn có thể thay đổi số lượng quy trình công nhân cho một bảng cụ thể: ALTER TABLE ... SET (parallel_workers = N).
Tại sao xử lý song song không được sử dụng?
Ngoài danh sách dài các hạn chế, còn có các biện pháp kiểm tra chi phí:
- để tránh xử lý song song các yêu cầu ngắn. Tham số này ước tính thời gian chuẩn bị bộ nhớ, bắt đầu quá trình và trao đổi dữ liệu ban đầu.
: giao tiếp giữa người lãnh đạo và người lao động có thể bị trì hoãn tỷ lệ với số lượng bộ dữ liệu từ các quy trình công việc. Tham số này tính toán chi phí trao đổi dữ liệu.
Tham gia vòng lặp lồng nhau
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)Việc thu thập diễn ra ở giai đoạn cuối, vì vậy Nested Loop Left Join là một hoạt động song song. Quét chỉ mục song song chỉ được giới thiệu trong phiên bản 10. Nó hoạt động tương tự như quét nối tiếp song song. Tình trạng c_custkey = o_custkey đọc một đơn hàng trên mỗi chuỗi khách hàng. Vì vậy, nó không song song.
Băm tham gia
Mỗi quy trình công nhân tạo bảng băm riêng cho đến PostgreSQL 11. Và nếu có nhiều hơn bốn quy trình này, hiệu suất sẽ không được cải thiện. Trong phiên bản mới, bảng băm được chia sẻ. Mỗi quy trình công nhân có thể sử dụng WORK_MEM để tạo bảng băm.
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 msTruy vấn 12 từ TPC-H hiển thị rõ ràng kết nối băm song song. Mỗi quy trình công nhân góp phần tạo ra một bảng băm chung.
Hợp nhất Tham gia
Một phép nối hợp nhất có bản chất không song song. Đừng lo lắng nếu đây là bước cuối cùng của truy vấn - nó vẫn có thể chạy song song.
-- 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)Nút "Hợp nhất tham gia" nằm phía trên "Tập hợp hợp nhất". Vì vậy việc hợp nhất không sử dụng xử lý song song. Nhưng nút "Quét chỉ mục song song" vẫn giúp phân đoạn part_pkey.
Kết nối theo từng phần
Trong PostgreSQL 11 bị tắt theo mặc định: nó có lịch trình rất tốn kém. Các bảng có cách phân vùng tương tự có thể được nối theo từng phân vùng. Bằng cách này Postgres sẽ sử dụng các bảng băm nhỏ hơn. Mỗi kết nối của các phần có thể song song.
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)Điều chính là kết nối trong các phần chỉ song song nếu các phần này đủ lớn.
Nối song song
có thể được sử dụng thay cho các khối khác nhau trong các quy trình công việc khác nhau. Điều này thường xảy ra với các truy vấn UNION ALL. Nhược điểm là ít song song hơn vì mỗi tiến trình công nhân chỉ xử lý 1 yêu cầu.
Có 2 quy trình công nhân đang chạy ở đây, mặc dù 4 quy trình đã được bật.
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)Các biến quan trọng nhất
- WORK_MEM giới hạn bộ nhớ cho mỗi tiến trình, không chỉ các truy vấn: work_mem các quy trình kết nối = rất nhiều bộ nhớ.
- — có bao nhiêu quy trình công nhân mà chương trình thực thi sẽ sử dụng để xử lý song song từ kế hoạch.
- — điều chỉnh tổng số quy trình công nhân theo số lõi CPU trên máy chủ.
- - tương tự, nhưng dành cho các quy trình làm việc song song.
Kết quả
Kể từ phiên bản 9.6, xử lý song song có thể cải thiện đáng kể hiệu suất của các truy vấn phức tạp quét nhiều hàng hoặc chỉ mục. Trong PostgreSQL 10, xử lý song song được bật theo mặc định. Hãy nhớ tắt nó trên các máy chủ có khối lượng công việc OLTP lớn. Quét tuần tự hoặc quét chỉ mục tiêu tốn rất nhiều tài nguyên. Nếu bạn không chạy báo cáo trên toàn bộ tập dữ liệu, bạn có thể cải thiện hiệu suất truy vấn bằng cách thêm các chỉ mục bị thiếu hoặc sử dụng phân vùng thích hợp.
tài liệu tham khảo
Nguồn: www.habr.com