دفع التجميع في PostgreSQL، شرحًا: اختبار group-by على 200 مليون صف

استعلام تقارير كان يُجيب في أقل من ثانية عند 5 ملايين صف، يستغرق الآن 5 ثوانٍ عند 200 مليون. لم يلمس أحد التطبيق.

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

شكل الخطة خلف استعلام group-by بطيء

إليك نوع الاستعلام الذي يظهر في تقريبًا كل عبء عمل تحليلي:

```sql

SELECT c.name, sum(s.amount)

FROM t_sales s

JOIN t_country c ON c.id = s.country_id

GROUP BY c.name;

```

يحتوي t_sales على المعاملات. يحتوي t_country على أربعة صفوف. يخطط PostgreSQL القياسي لهذا في ثلاث حركات:

1. مسح تسلسلي على t_sales.

2. دمج Hash مع t_country، مع بناء جدول hash من الجانب الكبير.

3. HashAggregate أو GroupAggregate على النتيجة المدموجة، مجمّعًا على عمود نصي.

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

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

ما يغيّره دفع التجميع

يعكس دفع التجميع الترتيب. يجمع PGEE على المفتاح الرقمي أثناء المسح، ثم يدمج تلك النتيجة الصغيرة مع جدول البحث.

يدمج الآن مخرجات التجميع، صفًا واحدًا تقريبًا لكل بلد، بدلاً من 200 مليون صف معاملات. التجميع الرقمي أرخص من التجميع النصي. يناسب مسح الفهرس جدول بحث من أربعة صفوف أفضل من دمج hash. وبسبب وصول عمل أقل إلى مرحلة finalize، ينخفض أيضًا عبء تجميع JIT.

تفعيله

دفع التجميع هو تحسين للمُحسّن في PGEE، توزيع PostgreSQL المؤسسات الذي بناه CYBERTEC. ليس ميزة في PostgreSQL المجتمعي. الإعدادات الموثقة هي:

```sql

SET enable_agg_pushdown = on;

SET max_parallel_workers_per_gather = 4;

```

تأكد من وجود المعامل على نسختك قبل التخطيط حوله:

```sql

SHOW enable_agg_pushdown;

```

ما قاسه الاختبار الداخلي

عبء العمل: t_sales مع 200 مليون صف بحجم 10 جيجابايت تقريبًا، مدموج مع جدول بحث t_country من أربعة صفوف، مجمّع حسب البلد.

يضع CYBERTEC مُحسّن استعلامات PGEE بأنه يقدّم تحليلات أسرع بنسبة 50%+. في هذا عبء العمل، وقع قياسنا الخاص في النطاق نفسه.

ما لا يثبته هذا الاختبار

كن حذرًا مع رقم مثل 50.2%، بما في ذلك هذا.

إنه شكل واحد من عبء العمل على مجموعة بيانات واحدة، قُيس في مختبر وليس في الإنتاج. يحتوي جدول البحث على أربعة صفوف، وهو قريب من أفضل حالة لدمج مؤجل. مخرجات التجميع صغيرة مقارنة بجدول الحقائق، وهو أيضًا قريب من أفضل حالة. يدفع دفع التجميع عندما يكون مفتاح التجميع رقميًا وجدول الأبعاد صغيرًا. غيّر أيًا من الشرطين وينكمش التحسّن.

إذا كانت استعلامات تقاريرك تجمع على مفاتيح نصية عالية الكثافة، أو يحتوي جدول البحث على ملايين الصفوف، أو يكون الاستعلام مقيدًا بـ I/O القرص بدلاً من عمل الدمج والتجميع، فلا تتوقع نفس التحسّن. يجب أيضًا تفعيل الإعداد. ليس تلقائيًا، وليس تسريعًا عامًا لكل استعلام على الخادم.

كيف تتحقق من ملاءمة عبء عملك

1. شغّل EXPLAIN (ANALYZE, BUFFERS) على الاستعلام البطيء واعثر على مكان ذهاب الوقت فعليًا. إذا لم يكن التجميع حصة ذات معنى منه، فلن ينقذك دفع التجميع.

2. انظر إلى مفتاح التجميع. تمنح المفاتيح الرقمية المُحسّن أكثر ما يعمل عليه؛ تمنحه المفاتيح النصية الأقل.

3. تحقق من كثافة جدول البحث. جداول الأبعاد الصغيرة هي الحالة التي بُني هذا التحسين من أجلها.

4. اختبر على نسخة من بيانات الإنتاج قبل تغيير أي شيء في الإنتاج. تحركات أعداد الصفوف وتوزيع البيانات للنتيجة أكثر من مواصفات الخادم.

5. قارن الخطط، وليس فقط التوقيتات. خطة تغيّر شكلها تشرح سبب تحرك الأرقام، وخطة لم تتغير تخبرك بالتوقف عن النظر هنا.

أين يقع Worlber

PGEE هو منتج CYBERTEC. يعمل Worlber مع محفظة PostgreSQL المؤسسات لدى CYBERTEC ويتولى العمل حوله: تقييم ما إذا كان عبء العمل يستفيد فعليًا من مُحسّن المؤسسات، هجرة قاعدة البيانات إلى PGEE حيث يكون ذلك القرار الصحيح، وضبط الأداء بعد ذلك.

تلك الجزء الأخير أهم من الترخيص. إعداد مُحسّن لم يضبطه أحد لمزيج الاستعلامات الحقيقي هو علم غير مستخدم على فاتورة.

*أرقام المعايير أعلاه تأتي من اختبار Worlber الداخلي على مجموعة بيانات واحدة وتُقدّم للتوضيح. النتائج على بياناتك ستختلف.*

أحضر لنا استعلام التجميع الأبطأ لديك

أرسل استعلام تقارير واحد وخطة تنفيذه. سيقوم Worlber بالنظر إلى مكان ذهاب الوقت وإخبارك ما إذا كان مُحسّن PGEE يغيّر النتيجة لذلك عبء العمل. إذا لم يساعد، سنقول ذلك، وتحافظ على التحليل في كلتا الحالتين.

تحدث إلى Worlber