Geekly articles weekly

استفسارات موازية في بوستجرس


هناك الكثير من النوى في وحدات المعالجة المركزية الحديثة. لعدة سنوات ، أرسلت التطبيقات استعلامات إلى قواعد البيانات بشكل متواز. إذا كان هذا استعلامًا للإبلاغ عن العديد من الصفوف في الجدول ، فسيتم تشغيله بشكل أسرع عندما يستخدم وحدات معالجة مركزية متعددة ، وفي PostgreSQL يكون من الممكن البدء من الإصدار 9.6.


استغرق الأمر 3 سنوات لتنفيذ وظيفة الاستعلام الموازي - واضطررت إلى إعادة كتابة التعليمات البرمجية في مراحل مختلفة من تنفيذ الاستعلام. قدم PostgreSQL 9.6 بنية تحتية لزيادة تحسين الرمز. في الإصدارات اللاحقة ، يتم تنفيذ أنواع أخرى من الاستعلامات بالتوازي.


قيود


  • لا تقم بتمكين التنفيذ الموازي إذا كانت جميع النوى مأخوذة بالفعل ، وإلا ستتباطأ الطلبات الأخرى.
  • الأهم من ذلك ، تستهلك المعالجة المتوازية مع قيم WORK_MEM عالية الكثير من الذاكرة - كل رابط التجزئة أو الفرز يأخذ الذاكرة في مقدار العمل.
  • لا يمكن تسريع طلبات OLTP منخفضة زمن الوصول عن طريق التنفيذ المتوازي. وإذا أرجع الاستعلام صفًا واحدًا ، فستعمل المعالجة المتوازية على إبطائه.
  • يحب المطورون استخدام معيار TPC-H. ربما لديك استفسارات مماثلة للتنفيذ المتوازي المثالي.
  • يتم تنفيذ استعلامات SELECT فقط بدون أقفال مساوية بالتوازي.
  • تكون الفهرسة الصحيحة في بعض الأحيان أفضل من عمليات المسح المتسلسلة للجدول بالتوازي.
  • تعليق الاستعلامات والمؤشرات غير مدعوم.
  • وظائف النافذة والوظائف الإجمالية للمجموعات المطلوبة ليست متوازية.
  • لا تكسب شيئًا في عبء العمل I / O.
  • لا توجد خوارزميات فرز متوازية. لكن يمكن تنفيذ الاستعلامات المصنفة بالتوازي في بعض الجوانب.
  • استبدل CTE (WITH ...) بـ SELECT المتداخل لتمكين المعالجة المتوازية.
  • لا تدعم مغلفات البيانات التابعة لجهات أخرى المعالجة المتوازية (لكن يمكنها!)
  • JOIN FULL OUTER غير مدعوم.
  • max_rows يعطل المعالجة المتوازية.
  • إذا كان للطلب وظيفة لم يتم تمييزها على أنها PARALLEL SAFE ، فستكون مترابطة.
  • مستوى عزل المعاملة SERIALIZABLE يعطل المعالجة المتوازية.

بيئة الاختبار


حاول مطورو PostgreSQL تقليل وقت استجابة استعلامات اختبار TPC-H. قم بتنزيل المعيار وتكييفه مع PostgreSQL . هذا استخدام غير رسمي لمعيار TPC-H - وليس لمقارنة قواعد البيانات أو الأجهزة.


  1. قم بتنزيل TPC-H_Tools_v2.17.3.zip (أو إصدار أحدث) من TPC خارج الموقع .
  2. إعادة تسمية makefile.suite إلى Makefile وتغييرها كما هو موضح هنا: https://github.com/tvondra/pg_tpch . ترجمة التعليمات البرمجية باستخدام الأمر make.
  3. توليد البيانات: ./dbgen -s 10 تنشئ قاعدة بيانات 23 جيجابايت. هذا يكفي لمعرفة الفرق في أداء الاستعلامات المتوازية وغير المتوازية.
  4. تحويل ملفات tbl إلى csv for و sed .
  5. قم بنسخ مستودع pg_tpch وانسخ csv إلى pg_tpch/dss/data .
  6. إنشاء استعلامات باستخدام الأمر qgen .
  7. قم بتحميل البيانات إلى قاعدة البيانات باستخدام الأمر ./tpch.sh .

المسح المتسلسل الموازي


قد يكون أسرع ليس بسبب القراءة المتوازية ، ولكن بسبب تناثر البيانات عبر العديد من مراكز وحدة المعالجة المركزية. في أنظمة التشغيل الحديثة ، يتم تخزين ملفات بيانات 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

يعطي الفحص المتسلسل عددًا كبيرًا جدًا من الصفوف دون تجميع ، لذلك يتم تنفيذ الطلب بواسطة وحدة معالجة مركزية واحدة.


إذا أضفت 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 من كل سير عمل بواسطة عقدة Gather.


يتم احتساب النتيجة النهائية بواسطة العقدة "Finalize Aggregate". إذا كان لديك وظائف التجميع الخاصة بك ، فتأكد من تمييزها على أنها "آمنة موازية".


عدد من سير العمل


يمكن زيادة عدد مهام سير العمل دون إعادة تشغيل الخادم:


 alter system set max_parallel_workers_per_gather=4; select * from pg_reload_conf();

الآن نرى 4 عمال في شرح المخرجات:


 tpch=# 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=1440213.58..1440213.59 rows=1 width=32) (actual time=5152.072..5152.072 rows=1 loops=1) -> Gather (cost=1440213.15..1440213.56 rows=4 width=32) (actual time=5151.807..5153.900 rows=5 loops=1) Workers Planned: 4 Workers Launched: 4 -> Partial Aggregate (cost=1439213.15..1439213.16 rows=1 width=32) (actual time=5147.238..5147.239 rows=1 loops=5) -> Parallel Seq Scan on lineitem (cost=0.00..1402428.00 rows=14714059 width=5) (actual time=0.037..3601.882 rows=11767943 loops=5) Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone) Rows Removed by Filter: 229267 Planning Time: 0.218 ms Execution Time: 5153.967 ms

ما الذي يحدث هنا؟ كان هناك المزيد من سير العمل مرتين ، وكان الطلب أسرع 1.6599 مرة. الحسابات مثيرة للاهتمام. كان لدينا 2 عمليات العمل والقائد 1. بعد التغيير ، أصبح 4 + 1.


أقصى تسارع لدينا من المعالجة المتوازية: 5/3 = 1.66 (6) مرات.


كيف يعمل؟


العمليات


يبدأ تنفيذ الطلب دائمًا بعملية رائدة. يقوم الزعيم بكل شيء غير متوازٍ وجزء من المعالجة المتوازية. تسمى العمليات الأخرى التي تؤدي نفس الطلبات سير العمل. تستخدم المعالجة المتوازية بنية أساسية لسير عمل الخلفية الديناميكية (منذ الإصدار 9.4). نظرًا لأن أجزاء أخرى من PostgreSQL تستخدم العمليات بدلاً من الخيوط ، فقد يكون الاستعلام الذي يحتوي على 3 مهام سير عمل أسرع بـ 4 مرات من المعالجة التقليدية.


تفاعل


تتواصل مهام سير العمل مع القائد من خلال قائمة انتظار الرسائل (بناءً على الذاكرة المشتركة). تحتوي كل عملية على طابورتين: للأخطاء وللمجموعات.


كم عدد عمليات العمل التي تحتاجها؟


يتم تعيين الحد الأدنى بواسطة المعلمة 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

في كل مرة يكون فيها الجدول أكبر بثلاث مرات من 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 هي عملية موازية. يظهر Parallel Index Only Scan في الإصدار 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)

تقع عقدة دمج الدمج أعلى Gather Merge. لذلك الدمج لا يستخدم المعالجة المتوازية. لكن العقدة Parallel Index Scan لا تزال تساعد في الجزء part_pkey .


قسم الاتصال


في PostgreSQL 11 ، يتم تعطيل التقسيم بشكل افتراضي: له جدولة باهظة الثمن. يمكن ربط الجداول مع التقسيم المماثل قسمًا تلو الآخر. لذلك سوف تستخدم بوستجرس الجداول التجزئة أصغر. كل اتصال القسم يمكن أن تكون متوازية.


 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 سير العمل تعمل هنا ، على الرغم من 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 = الكثير من الذاكرة.
  • max_parallel_workers_per_gather - عدد عمليات العمل التي سيستخدمها البرنامج المنفذ للمعالجة المتوازية من الخطة.
  • max_worker_processes - يضبط إجمالي عدد عمليات العمل على عدد مراكز وحدة المعالجة المركزية على الخادم.
  • max_parallel_workers هو نفسه ، ولكن بالنسبة لسير العمل المتوازي.

النتائج


بدءًا من الإصدار 9.6 ، يمكن للمعالجة المتوازية أن تحسن بشكل كبير أداء الاستعلامات المعقدة التي تفحص العديد من الصفوف أو الفهارس. في PostgreSQL 10 ، يتم تمكين المعالجة المتوازية افتراضيًا. تذكر أن تقوم بتعطيله على الخوادم ذات عبء عمل كبير على OLTP. عمليات المسح المتسلسلة أو عمليات المسح الضوئي للفهرس تستهلك الكثير من الموارد. إذا لم تكن تقوم بالإبلاغ عبر مجموعة البيانات بأكملها ، فيمكن جعل الاستعلامات أكثر فاعلية عن طريق إضافة فهارس مفقودة أو استخدام التقسيم الصحيح.


مراجع


Source: https://habr.com/ru/post/ar446706/

More articles:


All Articles