كيفية تسريع الاستعلامات المتكررة باستخدام ميزات PostgreSQL الجديدة
TuBrief 편집팀
2026년 8월 7일
0
Computing/Software원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
커뮤니티의 다른 글
댓글 (0)
Log in to leave a comment
아직 작성된 글이 없습니다
원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
Log in to leave a comment
아직 작성된 글이 없습니다
تنهار استعلامات CTE المتكررة المستخدمة لاستكشاف علاقات الأب والابن أو المسارات المتعددة في الأنظمة القديمة عندما تصبح البيانات عميقة. في اللحظة التي تهتز فيها ت Y fanout الفهارس، ينشئ المحرك جدولاً مؤقتاً في كل خطوة ويكرر عمليات الربط الذاتي. يمتلئ مجموع النتائج بالذاكرة ويحدث تسرب (Spillover) للقرص. يتم حظر الإدخال/الإخراج واستنفاد وحدة المعالجة المركزية. على الرغم من أن تعبير CYCLE الأصلي الذي تم إدخاله في PostgreSQL 14 يقلل من تكلفة بحث المصفوفة، إلا أنه كلما زاد عمق الاستكشاف، تظل تكلفة عمليات البحث المتكررة في الفهرس الثانوي ونسخ ذاكرة البيانات الكبيرة كما هي.
لزيادة سرعة الاستعلام وتوفير وحدة المعالجة المركزية، يجب عليك التبديل إلى بناء الجملة الرسومي الأصلي المستند إلى SQL/PGQ. أولاً، استخدم تعبير CREATE PROPERTY GRAPH مع الاحتفاظ بالهيكل المادي للجداول العلائقية الحالية كما هو. حدد جداول الكيانات الفردية كـ VERTEX TABLES وجداول التقاطع المتعددة كـ EDGE TABLES. ثانياً، نظراً لأن الأعمدة المحددة كمفاتيح لا يتم تضمينها تلقائياً في خصائص الرسم البياني، قم بتضمين الحقول في جملة PROPERTIES لضمان صلاحيات تصفية GRAPH_TABLE. ثالثاً، بالنسبة للاستعلامات التي يتجاوز فيها عمق الاستكشاف 3 مستاعات ويبلغ متوسط معدل التفرع 10 أو أكثر، أو يتجاوز عدد الصفوف 10 ملايين صف، قم بتغييرها إلى نمط GRAPH_TABLE MATCH. يتم تقليص التعليمات البرمجية المتكررة المعقدة إلى نمط واحد سطر، ويتم تقليل استخدام مخزن الذاكرة المؤقت بشكل ملحوظ.
لا يمكن حل أخطاء الإدراج المزدوج التي تحدث عندما تدفع خيوط متعددة البيانات في نفس الوقت في بيئة موزع باستخدام قفل التطبيق. يحدث تجاوز سعة RTT للشبكة وتحدث ظاهرة السباق (Race Condition) في الفجوة المباشرة قبل تأكيد المعاملة. يجب دمج أقفال مستوى الصف مع تعبيرات INSERT ON CONFLICT و MERGE. يقوم تعبير ON CONFLICT بقفل صفحة الفهرس الفريدة باستخدام تقنية الإدراج التخميني ويحاول الإدراج. في حالة حدوث تعارض، فإنه يتفرع مباشرة إلى DO UPDATE أو DO NOTHING لتقليل تكرار جلسات المآزق (Deadlocks).
عند استخدام تعبيرات ذرية، فإن تغيير مستوى عزل المعاملة له تأثير مضاعف كبير. في READ COMMITTED، تنتظر المعاملة اللاحقة حتى تؤكد المعاملة السابقة ثم تقرأ الصفوف الأحدث مرة أخرى للمعالجة الآمنة. ومع ذلك، إذا قمت بزيادته إلى REPEATABLE READ أو SERIALIZABLE، فعند حدوث تعارض مع المعاملة السابقة، فإنه يرسل خطأ فشل التسلسل مباشرة دون انتظار ويتراجع. لتقليل تكلفة التراجع ومنع استنفاد تجمع الاتصالات، يجب تعيين حلقة إعادة محاولة فقط لأكواد الخطأ 40001 و 40P01. أضف قيمة اضطراب عشوائية إلى وقت الانتظار الافتراضي لتشتيت توقيت المحاولة، وربط الحد الأقصى لعدد إعادة المحاولات بين 3 و 5 مرات وإرساله إلى طبقة الأعمال العليا.
يعد التقاط النقطة التي تتدهور فيها كفاءة فحص الفهرس بسبب تراكم تجزئة الكتلة والصفوف الميتة بداية إدارة التخزين. نظراً لخصائص بنية MVCC، لا يتم إرجاع الصفوف الميتة بواسطة UPDATE أو DELETE مباشرة إلى نظام التشغيل ولكنها تظل كتجزئة. عند عرض مؤشر n_dead_tup لعرض pg_stat_user_tables وملحق pgstattuple معاً، إذا تجاوز dead_tuple_ratio 20 بالمائة أو شكلت نسبة free_space 30 بالمائة، يجب استعادة مساحة القرص. إذا ترك الأمر دونดูแล، فسيزداد قراءة كتلة الإدخال/الإخراج وستتعطل كفاءة تجمع المخزن المؤقت.
لاستعادة التخزين في الخلفية دون إيقاف الخدمة، استخدم أداة pg_repack. أولاً، قم بإنشاء جدول ظل للجدول الأصلي المستهدف وإرفاق مشغل تسجيل لتتبع التغييرات. ثانياً، قم بانسخ الصفوف الصالحة بكميات كبيرة إلى جدول الظل، وأعد إنشاء الفهارس بشكل غير متزامن بالتوازي، ثم قم بتغيير كتالوج النظام. ثالثاً، قم بتنفيذ أمر عملية الخلفية مباشرة في المحطة الطرفية.
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
لمنع تنافس القفل، قم بتعيين SET lock_timeout = '3s'; على مستوى الجلسة بحيث إذا تعذر الحصول على القفل في غضون 3 ثوانٍ، فلن يبقى في قائمة الانتظار ويتسبب في خطأ على الفور. أضف خيار --wait-timeout=10 إلى هذا لقطع الموقف الذي يتم فيه حظر الاستعلامات اللاحقة بشكل متسلسل.
إذا تغير مخطط الاستعلام فجأة بسبب عدم تطابق دورة جمع المعلومات الإحصائية، فسؤدي ذلك إلى تعطل الإنتاج. في الأماكن التي تتركز فيها المهام ذات الحجم الكبير، تختلف الإحصاءات وتوزيع البيانات الفعلية، مما يسبب تقلب المخطط. إذا قام المحسن بتحديد ربط حلقة متداخلة بدلاً من فحص الفهرس، فسترتفع وحدة المعالجة المركزية وتحدث اختناقات في الإدخال/الإخراج. من الضروري إدخال وحدة auto_explain في shared_preload_libraries وتعيين log_min_duration على 500 مللي ثانية لتسجيل خطة التنفيذ الفعلية للاستعلامات البطيئة في سجل الخادم للتشخيص.
لتثبيت مسار التنفيذ للاستعلامات المحددة قسراً، استخدم وحدة التلميح. بعد التقاط الاستعلام المشكل من السجلات، قم بتحميل ملحق pg_hint_plan وإضافة تعليق قبل جملة SQL أو تسجيل الاستعلام في جدول كتالوج hint_plan.hints. إذا كان من الصعب نشر التعليمات البرمجية المصدرية، فيمكنك حقن سلسلة التلميح الثابتة مباشرة في مستوى الكتالوج لتثبيت المخطط فوراً دون إعادة نشر. إنها أسرع طريقة لحماية سرعة استجابة الاستعلام في حالات الطوارئ التي تستغرق وقتاً طويلاً لإعادة جمع الإحصاءات أو إعادة إنتاج الفهارس.