مفاجأة الاستعلام جدولة في قاعدة بيانات بوستجرس

الرسوم البيانية والتقارير والتحليلات - كل هذا موجود بطريقة أو بأخرى في المكتب الخلفي لأي ، حتى المشاريع الصغيرة جدًا. عندما تصبح مزدحمة في الجداول العادية في Excel / Numbers / Libre ، لكن البيانات لا تزال كبيرة جدًا ، غالبًا ما يتم تصميم الحلول التقليدية لاحتياجات الشركة الداخلية باستخدام قواعد البيانات العلائقية مثل PostgreSQL أو MySQL أو MariaDB.

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

وضع البداية

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

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

على الرغم من أن العديد من الأشياء التي سيتم مناقشتها قابلة للتطبيق على جميع الإصدارات الحديثة من قاعدة البيانات هذه ، فإن الأمثلة أدناه تعني الإصدار 11.2 ، والأخيرة في الوقت الحالي.
قبل أن نتعمق في التفاصيل ، من المنطقي التنقيب ونقول بضع كلمات عن المكان الذي يمكن أن تأتي منه مشكلات الأداء في قواعد البيانات العلائقية. ما هو بالضبط مشغول قاعدة البيانات عندما "يبطئ"؟ نقص الذاكرة (عدد كبير من الوصول إلى القرص أو الشبكة) ، وهو معالج ضعيف ، كلها مشاكل واضحة مع حلول واضحة ، ولكن ماذا يمكن أن يؤثر على سرعة تنفيذ الاستعلام؟

تعذب الذكريات

لكي تستجيب قاعدة البيانات لاستعلام SQL ، تحتاج إلى إنشاء خطة استعلام (في الجداول والأعمدة لترى ما هي الفهارس المطلوبة ، وما يجب الاختيار من هناك ، وما يمكن مقارنته ، ومقدار الذاكرة المطلوبة ، وما إلى ذلك). تتشكل هذه الخطة في شكل شجرة ، عقدتها ليست سوى بضع عمليات نموذجية ، مع تعقيد حسابي مختلف. فيما يلي بعض منها ، على سبيل المثال (N هو عدد الأسطر التي سيتم بها تنفيذ العملية):

العملية	ما الذي يتم	التكلفة
اختر ... أين ... عمليات جلب البيانات
المسح الضوئي	نقوم بتحميل كل صف من الجدول والتحقق من الحالة.	يا (لا)
مسح مؤشر (مؤشر ب شجرة)	البيانات موجودة مباشرة في الفهرس ، لذلك نحن نبحث حسب الشرط عن العناصر الضرورية للفهرس ونأخذ البيانات منه.	O (log (N)) ، ابحث عن عنصر في شجرة مرتبة.
مسح مؤشر (مؤشر التجزئة)	البيانات موجودة مباشرة في الفهرس ، لذلك نحن نبحث حسب الشرط عن العناصر الضرورية للفهرس ونأخذ البيانات منه.	O (1) ، البحث عن عنصر في جدول تجزئة ، باستثناء تكلفة إنشاء تجزئة
صورة نقطية كومة المسح	نختار أرقام الأسطر اللازمة حسب الفهرس ، ثم نقوم بتحميل الأسطر اللازمة فقط ونجري فحوصات إضافية معهم.	مؤشر المسح الضوئي + المسح الضوئي (M) ، حيث M هو عدد الصفوف الموجودة بعد Index Scan. من المفترض أن M << N ، أي الفهرس أكثر فائدة من Seq Scan.
انضمام العمليات (JOIN ، حدد من جداول متعددة)
حلقة متداخلة	لكل صف من الجدول الأيسر ، ابحث عن صف مناسب في الجدول الأيمن.	يا (ن 2 ). ولكن إذا كان أحد الجداول أصغر بكثير من الآخر (القاموس) ولم ينمو عملياً مع مرور الوقت ، فقد تنخفض التكلفة الفعلية إلى O (N).
تجزئة الانضمام	لكل صف من الجدولين الأيمن والأيسر ، نعتبر التجزئة ، مما يقلل من عدد عمليات البحث عن خيارات الاتصال الممكنة.	O (N) ، ولكن في حالة دالة تجزئة غير فعالة للغاية أو عدد كبير من الحقول المتماثلة للاتصال ، قد يكون هناك O (N 2 )
دمج الانضمام	حسب الحالة ، نقوم بفرز الجدولين الأيمن والأيسر ، وبعد ذلك نجمع بين القائمتين المصنفتين	O (N * log (N)) فرز التكاليف + الذهاب من خلال القائمة.
عمليات التجميع (GROUP BY ، مميزة)
مجموع المجموعة	نقوم بفرز الجدول وفقًا لحالة التجميع ، ثم في القائمة التي تم فرزها نقوم بتجميع الصفوف المجاورة.	O (N * log (N))
تجزئة المجموع	نحن نعتبر التجزئة لشرط التجميع لكل صف. للصفوف مع نفس التجزئة ، ونحن تجميع.	يا (لا)

كما ترون ، تعتمد تكلفة الاستعلام كثيرًا على كيفية تحديد موقع البيانات في الجداول وكيف يتوافق هذا الترتيب مع عمليات التجزئة المستخدمة. حلقة متداخلة ، على الرغم من تكلفتها في O (N ² ) ، يمكن أن تكون أكثر ربحية من Hash Join أو Merge Join عندما يتحول أحد الجداول المرتبطة إلى صف واحد أو عدة صفوف.

بالإضافة إلى موارد وحدة المعالجة المركزية ، تشمل التكلفة أيضًا استخدام الذاكرة. كلاهما موارد محدودة ، لذلك يجب على مخطط الاستعلام إيجاد حل وسط. إذا كان هناك جدولان أكثر ربحًا من الناحية الرياضية للاتصال عبر Hash Join ، لكن ببساطة لا يوجد مكان لمثل هذا الجدول الكبير في الذاكرة ، فقد تضطر قاعدة البيانات إلى استخدام Merge Join ، على سبيل المثال. حلقة متداخلة "بطيئة" بشكل عام لا تتطلب ذاكرة إضافية وجاهزة لإنتاج نتائج مباشرة بعد الإطلاق.

يتم عرض التكلفة النسبية لهذه العمليات بشكل أوضح على الرسم البياني. هذه ليست أرقامًا مطلقة ، بل هي نسبة تقريبية لعمليات مختلفة.



مخطط متداخل حلقة "يبدأ" أدناه ، لأن لا يتطلب إجراء عمليات حسابية إضافية أو تخصيص ذاكرة أو نسخ بيانات وسيطة ، لكن له تكلفة O (N ² ). تتضمن عمليات الدمج بين الانضمام وربط التجزئة تكاليف أولية أعلى ، ومع ذلك ، بعد بعض قيم N ، تبدأ في التغلب على حلقة متداخلة في الوقت المناسب. يحاول المجدول اختيار الخطة بأقل تكلفة وعلى الرسم البياني أعلاه يلتزم بعمليات مختلفة باستخدام N (سهم متقطع أخضر). مع عدد الأسطر التي تصل إلى N1 ، يكون استخدام Nested Loop أكثر ربحية ، من N1 إلى N2 هو أكثر ربحية لـ Merge Join ، ثم بعد N2 يصبح أكثر ربحية لـ Hash Join ، لكن Hash Join تتطلب الذاكرة لإنشاء جداول تجزئة. وعندما تصل إلى N3 ، تصبح هذه الذاكرة غير كافية ، مما يؤدي إلى الاستخدام القسري لـ Merge Join.

عند اختيار الخطة ، يقوم المجدول بتقدير تكلفة كل عملية في الخطة باستخدام مجموعة من التكاليف النسبية لبعض العمليات "الذرية" في قاعدة البيانات. على سبيل المثال ، العمليات الحسابية ، المقارنات ، تحميل الصفحة في الذاكرة ، إلخ. فيما يلي قائمة ببعض هذه المعلمات من التكوين الافتراضي ، لا يوجد الكثير منها:

التكلفة النسبية ثابتة	القيمة الافتراضية
seq_page_cost	1.0
random_page_cost	4.0
cpu_tuple_cost	0.01
cpu_index_tuple_cost	0.005
cpu_operator_cost	0.0025
parallel_tuple_cost	0.1
parallel_setup_cost	1000.0

صحيح أن هذه الثوابت وحدها قليلة ، ولا تزال بحاجة إلى معرفة "N" ، أي بالضبط عدد الصفوف من النتائج السابقة التي سيتعين معالجتها في كل عملية من هذه العمليات. الحد العلوي واضح هنا - قاعدة البيانات "تعرف" كمية البيانات الموجودة في أي جدول ويمكنها حساب "الحد الأقصى" دائمًا. على سبيل المثال ، إذا كان لديك جدولان يحتوي كل منهما على 100 صف ، فيمكن أن ينتج عن انضمامهما من 0 إلى 10000 صف في الإخراج. وفقًا لذلك ، يمكن أن تحتوي عملية الإدخال التالية على 10000 خط.

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

خارج المربع الأمثل

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

خذ على سبيل المثال الاستعلام:

SELECT t1.important_value FROM t1 WHERE t1.a > 100 

افترض أن الرسم البياني للقيم الموجودة في عمود "t1.a" كشف أن القيم التي تزيد عن 100 موجودة في حوالي 1٪ من صفوف الجدول. ثم يمكننا أن نتوقع أن تعود هذه العينة إلى حوالي مائة من جميع الصفوف من الجدول "t1".
تمنحك قاعدة البيانات الفرصة للنظر في التكلفة المتوقعة للخطة من خلال أمر EXPLAIN والوقت الفعلي لتشغيلها - باستخدام EXPLAIN ANALYZE.

يبدو أنه مع الإحصاءات التلقائية ، يجب أن يكون كل شيء على ما يرام الآن ، ولكن يمكن أن تكون هناك صعوبات. يوجد مقال جيد حول هذا الموضوع من Citus Data ، مع مثال على عدم كفاءة الإحصاءات التلقائية ومجموعة من الإحصائيات الإضافية باستخدام إحصائيات CREATE (متوفرة مع PG 10.0).

لذلك ، بالنسبة إلى المجدول ، هناك مصدران للأخطاء في حساب التكاليف:

1. يمكن أن تختلف التكلفة النسبية للعمليات البدائية (seq_page_cost ، cpu_operator_cost ، وما إلى ذلك) بشكل افتراضي عن الواقع (تكلفة وحدة المعالجة المركزية 0.01 ، تكلفة تحميل صفحة srq - 1 أو 4 للتحميل العشوائي للصفحة). بعيدا عن حقيقة أن 100 مقارنات سوف تكون مساوية لتحميل صفحة 1.
2. خطأ في التنبؤ بعدد الصفوف في العمليات المتوسطة. يمكن أن تكون التكلفة الفعلية للعملية في هذه الحالة مختلفة تمامًا عن التوقعات.

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

خطط مفاجئة - المنافسين

إذا سارت الأمور على ما يرام ، فسيتم تلبية طلبك في أسرع وقت ممكن. كلما زادت كمية البيانات ، زادت سرعة تنفيذ الاستعلام في قاعدة البيانات تدريجياً ، وبعد مرور بعض الوقت ، لاحظتها ، يمكنك التنبؤ تقريبًا بموعد زيادة الذاكرة أو عدد نوى وحدة المعالجة المركزية أو توسيع المجموعة ، وما إلى ذلك.

ولكن يجب أن نأخذ في الاعتبار حقيقة أن الخطة المثلى بها منافسين بتكاليف تنفيذ وثيقة ، والتي لا نراها. وإذا غيرت قاعدة البيانات فجأة خطة الاستعلام إلى أخرى ، فستكون هذه مفاجأة. من الجيد أن تنتقل قاعدة البيانات إلى خطة أكثر كفاءة. وإذا لم يكن كذلك؟ دعونا نلقي نظرة على الصورة ، على سبيل المثال. هذا هو التكلفة المتوقعة والوقت الحقيقي لتنفيذ خطتين (الأحمر والأخضر):



هنا ، تظهر خطة واحدة باللون الأخضر وأقرب "منافس لها" باللون الأحمر. يعرض الخط المنقط رسمًا بيانيًا للتكاليف المتوقعة ، والخط الصلب هو الوقت الفعلي. يظهر السهم المتقطع الرمادي اختيار المخطط.

لنفترض أنه في ليلة الجمعة الجميلة ، يصل العدد المتوقع للصفوف في بعض العمليات الوسيطة إلى N1 وأن ​​التنبؤ "الأحمر" يبدأ في التفوق على "الأخضر". المجدول يبدأ في استخدامه. ينتقل وقت تنفيذ الاستعلام الفعلي على الفور (التبديل من خط صلب أخضر إلى خط أحمر) ، أي أن جدول تدهور قاعدة البيانات يأخذ شكل خطوة (أو ربما "جدار"). في الممارسة العملية ، يمكن لهذا "الجدار" أن يزيد من وقت تنفيذ الاستعلام بترتيب من الحجم أو أكثر.

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

كيف نعيش معها؟

السؤال الذي يطرح نفسه: هل كان من الممكن بطريقة ما التنبؤ بهذه الخطط غير المرئية "تحت الماء"؟ بعد كل شيء ، المشكلة ليست أنها ليست الأمثل ، ولكن يمكن أن يحدث التحول إلى خطة أخرى بشكل غير متوقع ، ووفقًا لقانون المعنى ، في هذه اللحظة المؤسفة للغاية.

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

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

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

العمليات المستخدمة	القيمة الافتراضية
enable_bitmapscan enable_hashagg enable_hashjoin enable_indexscan enable_indexonlyscan enable_material enable_mergejoin enable_nestloop enable_parallel_append enable_seqscan enable_sort enable_tidscan enable_parallel_hash enable_partition_pruning	على
enable_partitionwise_join enable_partitionwise_aggregate	بعيدا

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

ذات أهمية خاصة اثنين من العمليات التالية:

• تجزئة الانضمام. تعقيدها هو O (N) ، ولكن مع وجود خطأ في التنبؤ في مقدار النتيجة ، لا يمكنك احتواء الذاكرة وعليك القيام بدمج الانضمام ، بتكلفة O (N * log (N)).
• حلقة متداخلة. تعقيده هو O (N ² ) ، وبالتالي ، فإن الخطأ في حجم التنبؤ يؤثر على سرعة مثل هذا الاتصال.

على سبيل المثال ، دعنا نأخذ بعض الأرقام الحقيقية من الاستعلامات ، والتي شاركنا في تحسينها.

الخطة 1. مع كل العمليات المسموح بها ، كانت التكلفة الإجمالية للخطة المثلى 274962.09 وحدة.

خطة 2. مع حلقة "محظور" المتداخلة ، ارتفعت التكلفة إلى 40000534153.85. هذه ال 40 مليار التي تشكل الجزء الأكبر من التكلفة هي 4 أضعاف حلقة المتداخلة المستخدمة ، على الرغم من الحظر. والباقي 534153.85 - وهذا هو بالضبط توقعات تكلفة جميع العمليات الأخرى في الخطة. كما نرى ، أعلى بحوالي مرتين من تكلفة الخطة المثلى ، أي أنها قريبة بما فيه الكفاية.

خطة 3. مع "محظور" تجزئة الانضمام ، كانت التكلفة 383253.77. تم وضع الخطة بالفعل دون استخدام عملية Hash Join ، نظرًا لأننا لا نرى أي مليارات. ومع ذلك ، فإن تكلفتها أعلى بنسبة 30٪ من التكلفة المثلى ، وهي أيضًا قريبة جدًا.

في الواقع ، كانت أوقات تنفيذ الاستعلام كما يلي:

اكتملت الخطة 1 (جميع العمليات المسموح بها) في حوالي 9 دقائق.
خطة 2 (مع حلقة متداخلة "محظورة") الانتهاء في 1.5 ثانية.
اكتملت الخطة 3 (مع وصلة تجزئة "محظورة") في حوالي 5 دقائق.

السبب ، كما ترون ، هو التنبؤ الخاطئ لتكلفة حلقة متداخلة. في الواقع ، عند مقارنة EXPLAIN مع EXPLAIN ANALYZE ، يتم اكتشاف خطأ فيه بتعريف N المشؤوم في العملية المتوسطة. بدلاً من صف واحد متوقع ، واجهت حلقة متداخلة عدة آلاف من الصفوف ، مما تسبب في زيادة وقت تنفيذ الاستعلام ببضعة أوامر.

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

في الممارسة العملية ، إذا كان طلبك فجأة (بعد ترقية قاعدة بيانات أو فقط بمفرده) بدأ يعمل لفترة أطول من السابق ، فحاول أولاً رفض إما Hash Join أو Nested Loop ونرى كيف يؤثر ذلك على سرعة الاستعلام. في حالة النجاح ، ستتمكن على الأقل من حظر خطة جديدة غير مثالية ، والعودة إلى الخطة السريعة السابقة.

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

 SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … (    ) 

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

أو ، مثال آخر ، التكلفة الشرطية لتشغيل المعالجة المتوازية هي 1000 بشكل افتراضي ، في حين أن تكلفة تحميل الصفحة هي 1.0. من المنطقي أن تبدأ بتغيير واحد فقط من المعلمات في وقت واحد لتحديد ما إذا كان يؤثر على اختيار الخطة. أسهل الطرق هي البدء بتعيين المعلمة على 0 أو على قيمة عالية (1 مليون).

ومع ذلك ، ضع في اعتبارك أنه من خلال تحسين الأداء في طلب واحد ، يمكنك تحطيمه في طلب آخر. بشكل عام ، هناك مجال واسع للتجارب. من الأفضل أن تحاول تغييرها مرة واحدة في كل مرة.

خيارات العلاج البديل

ستكون قصة مجدول غير كاملة دون ذكر ملحقين على الأقل في PostgreSQL.

الأول هو SR_PLAN ، لحفظ الخطة المحسوبة وفرض استخدامها مرة أخرى. هذا يساعد في جعل سلوك قاعدة البيانات أكثر قابلية للتنبؤ من حيث خيارات الخطة.

والثاني هو " مُحسِّن استعلام Adaptive Query Optimizer" ، الذي يقوم بتطبيق الملاحظات على المجدول من خلال التنفيذ الفعلي للاستعلام ، أي أن المجدول يقيس النتائج الفعلية للاستعلام الذي تم تنفيذه ويضبط خططه في المستقبل مع وضع ذلك في الاعتبار. وبالتالي فإن قاعدة البيانات هي "ضبط ذاتي" لبيانات واستعلامات محددة.

ماذا تفعل قاعدة البيانات عندما تبطئ؟

الآن وبعد أن قمنا بتسوية تخطيط الاستعلام بشكل أو بآخر ، دعونا نرى ما الذي يمكن تحسينه في قاعدة البيانات نفسها وفي التطبيقات التي تستخدمها للحصول على أقصى أداء ممكن منها.

افترض أن خطة الاستعلام هي الأمثل بالفعل. إذا استبعدنا أكثر المشكلات وضوحًا (ذاكرة منخفضة أو قرص / شبكة بطيئة) ، فلا تزال هناك تكاليف لحساب التجزئة. من المحتمل أن تكون هناك فرص كبيرة لإدخال تحسينات مستقبلية على PostgreSQL (باستخدام GPU أو حتى تعليمات SSE2 / SSE3 / AVX لوحدة المعالجة المركزية) ، ولكن حتى الآن لم يتم ذلك ولا تستخدم حسابات التجزئة إمكانات الأجهزة تقريبًا أبدًا. يمكنك المساعدة قليلاً في قاعدة البيانات هذه.

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

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

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

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

استفسارات ناجحة ، مع خطط دقيقة وقصيرة.