ضبط أداء الاستعلام في PostgreSQL

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

البحث عن استفسارات بطيئة

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

pg_stats_statements

وحدة pg_stats_statements هي مكان رائع للبدء. إنها فقط تتبع إحصائيات تنفيذ عبارات SQL ويمكن أن تكون وسيلة سهلة للعثور على استفسارات غير فعالة.

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

SELECT * FROM pg_stat_statements ORDER BY total_time DESC; 

auto_explain

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

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

ضبط مؤشر

استراتيجية ضبط أخرى مهمة هي التأكد من أن يتم استخدام الفهارس بشكل صحيح. كشرط أساسي ، نحتاج إلى تضمين "جامع الإحصاء".

Postgres Statistics Collector هو نظام فرعي من الدرجة الأولى يجمع كل أنواع إحصاءات الأداء المفيدة.

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

فهارس مفقودة

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

 SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC; 

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

الفهارس غير المستخدمة

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

 SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false; 

ملاحظة حول إحصائيات بيئة التطوير

يمكن أن يكون الاعتماد على الإحصاءات من قاعدة بيانات التنمية المحلية مشكلة. من الناحية المثالية ، يمكنك الحصول على الإحصاءات المذكورة أعلاه من جهاز العمل الخاص بك أو توليدها من نسخة احتياطية عاملة مستعادة. لماذا؟ يمكن للعوامل البيئية تغيير سلوك مُحسِّن استعلام Postgres. مثالان:

• عندما يكون الجهاز ذا ذاكرة أقل ، قد لا تتمكن PostgreSQL من تنفيذ Hash Join ، وإلا فإنها ستفعل ذلك وستفعله بشكل أسرع.
• إذا لم يكن هناك العديد من الصفوف في الجدول (كما في قاعدة بيانات التطوير) ، فقد تفضل PostgresSQL إجراء مسح تسلسلي للجدول بدلاً من استخدام فهرس متاح. عندما تكون أحجام الجدول صغيرة ، يمكن أن يكون Seq Scan أسرع. (ملاحظة: يمكنك تشغيل

   SET enable_seqscan = OFF 

  في جلسة بحيث يختار المُحسِّن استخدام الفهارس ، حتى لو كانت عمليات المسح المتسلسل أسرع. هذا مفيد عند العمل مع قواعد بيانات التطوير التي لا تحتوي على الكثير من البيانات)

فهم خطط التنفيذ

الآن وقد وجدت بعض الاستعلامات البطيئة ، فقد حان الوقت لبدء المرح.

شرح

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

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

دعنا نراجع ونبدأ في فهم إخراج EXPLAIN . هنا مثال:

عقدة

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

تكلفة

 cost=146.63..148.65 

الرقم الأول هو التكلفة الأولية (تكلفة الحصول على السجل الأول) ، والرقم الثاني هو تكلفة معالجة العقدة بأكملها (التكلفة الإجمالية من البداية إلى النهاية).

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

الوقت الفعلي

 actual time=55.009..55.012 

في المللي ثانية ، يكون الرقم الأول هو وقت البدء (الوقت لاسترداد السجل الأول) ، والرقم الثاني هو الوقت اللازم لمعالجة العقدة بأكملها (إجمالي الوقت من البداية إلى النهاية). من السهل أن نفهم ، أليس كذلك؟

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

تعرف على المزيد حول خطط التنفيذ.

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

• http://www.depesz.com/2013/04/16/explaining-the-unexplainable/
• https://wiki.postgresql.org/images/4/45/Explaining_EXPLAIN.pdf

طلب ضبط

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

ذاكرة التخزين المؤقت للبيانات ومذكرة التكلفة

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

الأشياء التي تعلمتها يمكن أن تساعد في تحسين خطط التنفيذ:

• مؤشرات
  
  • استبعاد المسح المتسلسل (Seq Scan) عن طريق إضافة فهارس (إذا لم يكن حجم الجدول صغيرًا)
  • عند استخدام فهرس متعدد الأعمدة ، تأكد من الانتباه إلى الترتيب الذي تحدد به الأعمدة المضمنة - مزيد من المعلومات
  • جرب الفهارس التي تكون انتقائية للغاية للبيانات المستخدمة بشكل متكرر. وهذا سيجعل استخدامها أكثر كفاءة.
• حالة أين
  
  
  • تجنب مثل
  • تجنب استدعاءات الوظائف في جملة WHERE
  • تجنب الظروف الكبيرة في ()
• JOINy
  
  
  • عند الانضمام إلى الجداول ، حاول استخدام تعبير المساواة البسيط في جملة ON (أي a.id = b.person_id). يتيح لك ذلك استخدام طرق ربط أكثر فاعلية (على سبيل المثال ، Hash Join ، وليس Nested Loop Join)
  • قم بتحويل الاستعلامات الفرعية إلى عبارات JOIN عندما يكون ذلك ممكنًا ، حيث يتيح ذلك عادة للمحسن فهم الهدف وربما اختيار أفضل خطة.
  • استخدم المركبات المركبة بشكل صحيح: هل تستخدم GROUP BY أو مميزة لمجرد حصولك على نتائج مكررة؟ يشير هذا عادةً إلى الاستخدام غير الصحيح لـ JOINs وقد يؤدي إلى ارتفاع التكاليف.
  • إذا كانت خطة التنفيذ تستخدم Hash Join ، فقد تكون بطيئة جدًا إذا كانت تقديرات حجم الجدول غير صحيحة. لذلك ، تأكد من دقة إحصائيات الجدول الخاصة بك عن طريق مراجعة استراتيجية التنظيف.
  • تجنب الاستعلامات الفرعية المرتبطة كلما أمكن ذلك ؛ يمكنهم زيادة كبيرة في تكلفة الطلب
  • استخدم EXISTS عند التحقق من وجود سلاسل بناءً على معيار ، لأنه يشبه الدائرة القصيرة (يتوقف عن المعالجة عندما يجد تطابقًا واحدًا على الأقل)
• توصيات عامة
  
  
  • بذل المزيد من الجهد مع أقل. المعالج أسرع من الإدخال / الإخراج (I / O)
  • استخدم تعبيرات الجدول الشائعة والجداول المؤقتة عندما تحتاج إلى تنفيذ استعلامات متسلسلة.
  • تجنب عبارات LOOP وفضل عمليات SET
  • تجنب COUNT (*) نظرًا لأن PostgresSQL يقوم بمسح الجداول لهذا ( فقط للإصدارات <= 9.1 )
  • تجنب الطلب حسب ، التمييز ، التجميع ، الاتحاد كلما كان ذلك ممكنًا ، لأن هذا يؤدي إلى ارتفاع التكاليف الأولية.
  • ابحث عن الفرق الكبير بين الأسطر المقدرة والفعلية في تعبير EXPLAIN . إذا كانت العداد مختلفة تمامًا ، فقد تكون إحصائيات الجدول قديمة ، ويقدر PostgreSQL التكلفة باستخدام إحصائيات غير دقيقة. على سبيل المثال:

     Limit (cost=282.37..302.01 rows=93 width=22) (actual time=34.35..49.59 rows=2203 loops=1) 

    كان العدد المقدر للخطوط 93 ، والخط الفعلي - 2203. لذلك ، على الأرجح ، هذا هو قرار سيء للخطة. يجب عليك مراجعة إستراتيجية كنسك والتأكد من تنفيذ ANALYZE في كثير من الأحيان بما فيه الكفاية.