صحة الفهارس في PostgreSQL من خلال عيون مطور Java

يا.

اسمي فانيا وأنا مطور جافا. لقد حدث أنني أعمل كثيرًا مع PostgreSQL - حيث أقوم بإعداد قاعدة البيانات وتحسين البنية والأداء ولعب القليل من DBA في عطلات نهاية الأسبوع.

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

إخلاء المسئولية

الإصدار الرئيسي من PostgreSQL الذي أعمل معه هو 10. يتم أيضًا اختبار جميع استعلامات SQL التي أستخدمها في الإصدار 11. الحد الأدنى للإصدار المدعوم هو 9.6.

قبل التاريخ

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

المشكلة الأولى - التكوين الافتراضي

ربما سئم الجميع من الاستعارة المتعلقة بـ Postgres، والتي يمكن تشغيلها على ماكينة صنع القهوة، ولكن... التكوين الافتراضي يثير بالفعل عددًا من الأسئلة. على الأقل، الأمر يستحق الاهتمام به Maintenance_work_mem, temp_file_limit, بيان_مهلة и lock_timeout.

في حالتنا، Maintenance_work_mem كان الافتراضي 64 ميغابايت، و temp_file_limit حوالي 2 غيغابايت - ببساطة لم يكن لدينا ذاكرة كافية لإنشاء فهرس على طاولة كبيرة.

لذلك ، في صفحة-مؤشر-الصحة لقد جمعت سلسلة مفتاحفي رأيي، المعلمات التي ينبغي تكوينها لكل قاعدة بيانات.

المشكلة الثانية - فهارس مكررة

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

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

المشكلة الثالثة - المؤشرات المتقاطعة

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

المشكلة الرابعة - المفاتيح الخارجية بدون فهارس

يتيح لك Postgres إنشاء قيود المفاتيح الخارجية دون تحديد فهرس دعم. في كثير من الحالات، لا يمثل هذا مشكلة، وربما لا يظهر نفسه... في الوقت الحالي...

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

delete from <table> where id in (…)

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

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

المشكلة الخامسة - القيمة الخالية في الفهارس

افتراضيًا، يتضمن Postgres قيمًا فارغة في فهارس btree، ولكن عادةً لا تكون هناك حاجة إليها هناك. لذلك، أحاول جاهداً التخلص من هذه القيم الخالية (diagnostics Indexes_with_null_values)، إنشاء فهارس جزئية على أعمدة فارغة حسب النوع where <A> is not null. وبهذه الطريقة تمكنت من تقليل حجم أحد فهارسنا من 1877 ميجا بايت إلى 16 كيلو بايت. وفي إحدى الخدمات، انخفض حجم قاعدة البيانات إجمالاً بنسبة 16% (بنسبة 4.3 جيجابايت بالأرقام المطلقة) بسبب استبعاد القيم الخالية من الفهارس. توفير هائل في مساحة القرص مع تعديلات بسيطة جدًا. 🙂

المشكلة السادسة – عدم وجود المفاتيح الأساسية

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

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

في نسخة المكتبة 0.1.5 تمت إضافة القدرة على جمع البيانات من مجموعة كبيرة من الجداول والفهارس والرد عليها في الوقت المناسب.

المسألتان السابعة والثامنة - فهارس غير كافية وفهارس غير مستخدمة

التشخيصان التاليان هما: table_with_missing_indexes и unused_indexes - ظهرت في شكلها النهائي مؤخرًا نسبيًا. النقطة المهمة هي أنه لا يمكن أخذها وإضافتها فحسب.

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

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

وختاما

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

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

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

أنا آمل حقا أن صفحة-مؤشر-الصحة سيكون مفيدًا وفي الطلب. يمكنك أيضًا المساهمة في تطوير المكتبة من خلال الإبلاغ عن المشكلات التي تجدها واقتراح تشخيصات جديدة.

المصدر: www.habr.com