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 را کاهش دهند. دانلود بنچمارک و . این یک استفاده غیر رسمی از معیار TPC-H است - نه برای مقایسه پایگاه داده یا سخت افزار.
- دانلود TPC-H_Tools_v2.17.3.zip (یا نسخه جدیدتر) .
- نام makefile.suite را به Makefile تغییر دهید و همانطور که در اینجا توضیح داده شده است تغییر دهید: . کد را با دستور make کامپایل کنید.
- تولید داده:
./dbgen -s 10یک پایگاه داده 23 گیگابایتی ایجاد می کند. این کافی است تا تفاوت عملکرد کوئری های موازی و غیر موازی را مشاهده کنید. - تبدیل فایل ها
tblвcsv с forиsed. - مخزن را شبیه سازی کنید
pg_tpchو فایل ها را کپی کنیدcsvвpg_tpch/dss/data. - با یک دستور کوئری ایجاد کنید
qgen. - با دستور داده ها را در پایگاه داده بارگذاری کنید
./tpch.sh.
اسکن متوالی موازی
ممکن است به دلیل خواندن موازی سریعتر نباشد، بلکه به این دلیل است که داده ها در بسیاری از هسته های CPU پخش می شوند. در سیستم عامل های مدرن، فایل های داده PostgreSQL به خوبی کش می شوند. با خواندن پیش رو، می توانید یک بلوک بزرگتر از درخواست دیمون PG از فضای ذخیره سازی دریافت کنید. بنابراین، عملکرد پرس و جو توسط ورودی/خروجی دیسک محدود نمی شود. چرخه های 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تجمع موازی
گره موازی Seq Scan ردیف هایی را برای تجمع جزئی تولید می کند. گره "Partial Aggregate" این خطوط را با استفاده از آن برش می دهد 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 صف است: برای خطاها و برای تاپل ها.
چند گردش کار مورد نیاز است؟
حداقل حد توسط پارامتر مشخص می شود . سپس اجراکننده درخواست، فرآیندهای کارگر را از استخر محدود شده توسط پارامتر می گیرد . آخرین محدودیت این است ، یعنی تعداد کل فرآیندهای پس زمینه.
اگر امکان تخصیص یک فرآیند کارگری وجود نداشت، پردازش تک فرآیندی خواهد بود.
برنامه ریز پرس و جو می تواند گردش کار را بسته به اندازه جدول یا شاخص کاهش دهد. برای این کار پارامترهایی وجود دارد и .
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).
چرا از پردازش موازی استفاده نمی شود؟
علاوه بر لیست طولانی محدودیت ها، بررسی های هزینه نیز وجود دارد:
- برای جلوگیری از پردازش موازی درخواست های کوتاه. این پارامتر زمان آماده سازی حافظه، شروع فرآیند و تبادل اولیه داده ها را تخمین می زند.
: ارتباط بین رهبر و کارگران می تواند به نسبت تعداد دفعات از فرآیندهای کاری به تاخیر بیفتد. این پارامتر هزینه تبادل داده را محاسبه می کند.
پیوندهای حلقه تو در تو
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)این مجموعه در آخرین مرحله انجام می شود، بنابراین پیوستن چپ حلقه تودرتو یک عملیات موازی است. موازی Index Only Scan فقط در نسخه 10 معرفی شد. شبیه اسکن سریال موازی عمل می کند. وضعیت c_custkey = o_custkey یک سفارش را در هر رشته مشتری می خواند. پس موازی نیست.
Hash Join
هر فرآیند کارگر جدول هش خود را تا PostgreSQL 11 ایجاد می کند. و اگر بیش از چهار مورد از این فرآیندها وجود داشته باشد، عملکرد بهبود نخواهد یافت. در نسخه جدید جدول هش به اشتراک گذاشته شده است. هر فرآیند کارگر می‌تواند از 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کوئری 12 از TPC-H به وضوح یک اتصال هش موازی را نشان می دهد. هر فرآیند کارگر به ایجاد یک جدول هش مشترک کمک می کند.
ادغام پیوستن
پیوستن ادغام ماهیت غیر موازی دارد. اگر این آخرین مرحله پرس و جو است، نگران نباشید - همچنان می تواند به صورت موازی اجرا شود.
-- 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 حافظه را در هر فرآیند محدود می کند، نه فقط درخواست ها: work_mem فرایندها اتصالات = حافظه زیاد.
- - برنامه در حال اجرا از چه تعداد پردازش کارگری برای پردازش موازی از طرح استفاده خواهد کرد.
- - تعداد کل فرآیندهای کارگر را با تعداد هسته های CPU روی سرور تنظیم می کند.
- - یکسان است، اما برای فرآیندهای کار موازی.
نمایش نتایج: از
در نسخه 9.6، پردازش موازی می تواند عملکرد پرس و جوهای پیچیده ای را که بسیاری از ردیف ها یا نمایه ها را اسکن می کنند، تا حد زیادی بهبود بخشد. در PostgreSQL 10، پردازش موازی به طور پیش فرض فعال است. به یاد داشته باشید که آن را در سرورهایی با حجم کار زیاد OLTP غیرفعال کنید. اسکن متوالی یا اسکن فهرست منابع زیادی را مصرف می کند. اگر گزارشی را روی کل مجموعه داده اجرا نمی کنید، می توانید عملکرد پرس و جو را به سادگی با افزودن نمایه های از دست رفته یا استفاده از پارتیشن بندی مناسب بهبود بخشید.
مراجع
منبع: www.habr.com