تحتوي وحدات المعالجة المركزية الحديثة على الكثير من النوى. لسنوات ، كانت التطبيقات ترسل استعلامات إلى قواعد البيانات بشكل متوازٍ. إذا كان استعلام تقرير مقابل عدة صفوف في جدول ، فإنه يكون أسرع عندما يستخدم وحدات معالجة مركزية متعددة ، وقد تمكنت PostgreSQL من القيام بذلك منذ الإصدار 9.6.
استغرق الأمر 3 سنوات لتنفيذ ميزة الاستعلام الموازي - اضطررت إلى إعادة كتابة الكود في مراحل مختلفة من تنفيذ الاستعلام. قدمت PostgreSQL 9.6 البنية التحتية لزيادة تحسين الكود. في الإصدارات اللاحقة ، يتم تنفيذ أنواع أخرى من الاستعلامات بشكل متوازٍ.
القيود
- لا تقم بتمكين التنفيذ المتوازي إذا كانت جميع النوى مشغولة بالفعل ، وإلا ستبطئ الطلبات الأخرى.
- والأهم من ذلك ، أن المعالجة المتوازية بقيم WORK_MEM العالية تستهلك قدرًا كبيرًا من الذاكرة - كل ربط تجزئة أو فرز يستهلك الذاكرة في مقدار work_mem.
- لا يمكن تسريع استعلامات OLTP ذات زمن الوصول البطيء عن طريق التنفيذ المتوازي. وإذا أرجع الاستعلام صفًا واحدًا ، فإن المعالجة المتوازية ستبطئه فقط.
- يحب المطورون استخدام معيار TPC-H. ربما لديك استفسارات مماثلة للتنفيذ المتوازي المثالي.
- يتم تنفيذ استعلامات SELECT فقط بدون قفل المسند بالتوازي.
- أحيانًا تكون الفهرسة المناسبة أفضل من عمليات المسح المتسلسلة للجدول بالتوازي.
- لا يتم دعم إيقاف الاستعلام مؤقتًا والمؤشرات.
- وظائف النافذة والوظائف التجميعية للمجموعة المرتبة ليست متوازية.
- لا تكسب شيئًا في عبء عمل الإدخال / الإخراج.
- لا توجد خوارزميات فرز متوازية. ولكن يمكن أن تعمل الاستعلامات ذات الأنواع بالتوازي في بعض الجوانب.
- استبدل 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.
المسح المتسلسل المتوازي
قد يكون أسرع ليس بسبب القراءة المتوازية ، ولكن لأن البيانات مبعثرة عبر العديد من مراكز وحدة المعالجة المركزية. في أنظمة التشغيل الحديثة ، يتم تخزين ملفات بيانات PostgreSQL بشكل جيد. من خلال القراءة المسبقة ، من الممكن الحصول على كتلة أكبر من التخزين من طلبات البرنامج الخفي PG. لذلك ، لا يقتصر أداء الاستعلام على إدخال / إخراج القرص. تستهلك دورات وحدة المعالجة المركزية من أجل:
- قراءة الأسطر واحدة تلو الأخرى من صفحات الجدول ؛
- قارن قيم وشروط السلسلة
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يعطي الفحص المتسلسل عددًا كبيرًا جدًا من الصفوف بدون تجميع ، لذلك يتم تنفيذ الاستعلام بواسطة نواة واحدة لوحدة المعالجة المركزية.
إذا أضفت 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التجميع الموازي
تنتج عقدة "Parallel Seq Scan" صفوفًا للتجميع الجزئي. تقطع العقدة "التجميع الجزئي" هذه الصفوف باستخدام SUM(). في النهاية ، يتم تجميع عداد SUM من كل سير عمل بواسطة عقدة التجميع.
يتم حساب النتيجة النهائية بواسطة عقدة "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.
تسريعنا الأقصى من المعالجة المتوازية: 5/3 = 1,66 (6) مرات.
كيف يعمل؟
العمليات
يبدأ تنفيذ الاستعلام دائمًا بالعملية الرائدة. يقوم القائد بكل شيء غير متوازي وبعض المعالجة المتوازية. تسمى العمليات الأخرى التي تؤدي نفس الطلبات عمليات العمال. تستخدم المعالجة المتوازية البنية التحتية (منذ الإصدار 9.4). نظرًا لأن أجزاء أخرى من PostgreSQL تستخدم العمليات بدلاً من الخيوط ، فإن الاستعلام الذي يحتوي على 3 عمليات عاملة يمكن أن يكون أسرع بأربع مرات من المعالجة التقليدية.
تفاعل
تتواصل عمليات العمال مع القائد عبر قائمة انتظار الرسائل (بناءً على الذاكرة المشتركة). تحتوي كل عملية على قائمتين: للأخطاء وللصفوف.
كم عدد عمليات العمال التي تحتاجها؟
الحد الأدنى يحدد المعلمة . ثم يأخذ منفذ الطلب العمليات العاملة من التجمع ، مقيدًا بالمعامل . القيد الأخير هو ، وهو العدد الإجمالي للعمليات التي تتم في الخلفية.
إذا لم يكن من الممكن تخصيص عملية عاملة ، فستكون المعالجة عملية واحدة.
يمكن لمخطط الاستعلام تقليل مهام سير العمل بناءً على حجم الجدول أو الفهرس. هناك خيارات لهذا. и .
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في كل مرة يكون الجدول أكبر بثلاث مرات من 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)يحدث التجميع في الخطوة الأخيرة ، لذا فإن Nested Loop Left Join هي عملية موازية. تم تقديم المسح الفهرس المتوازي فقط في الإصدار 10. وهو يعمل بشكل مشابه للمسح المتسلسل المتوازي. حالة c_custkey = o_custkey يقرأ طلبًا واحدًا لكل سطر عميل. لذا فهي ليست موازية.
Hash Join - 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)تقع عقدة "دمج الانضمام" فوق "تجميع دمج". لذا لا يستخدم الدمج المعالجة المتوازية. لكن عقدة "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. الجانب السلبي هو أقل التزامن ، حيث تعالج كل عملية عامل طلبًا واحدًا فقط.
هناك عمليتان عاملة تعملان هنا ، على الرغم من تمكين 2.
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 العمليات اتصالات = الكثير من الذاكرة.
- - كم عدد عمليات العمال التي سيستخدمها البرنامج المنفذ للمعالجة المتوازية من الخطة.
- - يضبط العدد الإجمالي للعمليات العاملة على عدد أنوية وحدة المعالجة المركزية على الخادم.
- - نفس الشيء ، ولكن لتدفقات العمل المتوازية.
نتائج
بدءًا من الإصدار 9.6 ، يمكن للمعالجة المتوازية أن تحسن أداء الاستعلامات المعقدة التي تفحص العديد من الصفوف أو الفهارس بشكل كبير. في PostgreSQL 10 ، يتم تمكين المعالجة المتوازية افتراضيًا. لا تنس تعطيله على الخوادم ذات عبء عمل OLTP الثقيل. عمليات المسح المتسلسلة أو الفهرسية كثيفة الاستخدام للموارد. إذا كنت لا تقوم بالإبلاغ عن مجموعة البيانات بأكملها ، فيمكن جعل الاستعلامات أكثر أداءً ببساطة عن طريق إضافة فهارس مفقودة أو باستخدام التقسيم المناسب.
مراجع
المصدر: www.habr.com