تعمق في الإحصاءات الداخلية لـ PostgreSQL. أليكسي ليسوفسكي

نسخ تقرير 2015 بواسطة أليكسي ليسوفسكي "الغوص العميق في الإحصاءات الداخلية لـ PostgreSQL"

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

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

مساء الخير اسمي اليكسي. كما قال إيليا ، سأتحدث عن إحصائيات PostgreSQL.

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

سأخبرك بكيفية استخدام الإحصائيات بفعالية لحل مجموعة متنوعة من المشكلات التي لديك أو قد تكون لديك.

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

ولن تكون هناك مراجعات للأداة ، ولن أقارن منتجًا بآخر. لن يكون هناك اعلان دعونا نسقط هذا.

أريد أن أوضح لك أن استخدام الإحصائيات مفيد. انه ضروري. استخدمه بلا خوف. كل ما نحتاجه هو لغة SQL بسيطة ومعرفة أساسية بـ SQL.

وسنتحدث عن الإحصائيات التي نختارها لحل المشكلات.

إذا نظرنا إلى PostgreSQL وقمنا بتشغيل أمر على نظام التشغيل لعرض العمليات ، فسنرى "الصندوق الأسود". سنرى بعض العمليات التي تقوم بشيء ما ، وبالاسم يمكننا أن نتخيل تقريبًا ما يفعلونه هناك ، وماذا يفعلون. لكن ، في الواقع ، هذا صندوق أسود ، لا يمكننا النظر إلى الداخل.

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

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

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

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

ما هي مشاكل الإحصاء؟

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

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

وتحتاج إلى معرفة أساسية بـ SQL. للحصول على بعض البيانات من الإحصائيات ، تحتاج إلى إجراء استعلامات SQL ، أي أنك بحاجة إلى معرفة كيفية الاختيار والانضمام.

تخبرنا الإحصائيات بأشياء عديدة. يمكن تقسيمها إلى فئات.

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

ترد مصادر الإحصاءات على النحو التالي:

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

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

ومع ذلك ، إذا أخذنا الصورة السابقة Как тратится время на PostgreSQL ومتوافقة مع هذه القائمة ، نحصل على هذه الصورة. كل عرض (مشاهدات) ، أو كل وظيفة ، يمكننا استخدامها لغرض أو لآخر للحصول على الإحصائيات المناسبة عندما يكون لدينا PostgreSQL قيد التشغيل. ويمكننا بالفعل الحصول على بعض المعلومات حول تشغيل النظام الفرعي.

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

ماذا يمكننا أن نأخذ من هناك؟ لنبدأ بأبسط الأشياء.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

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

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

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

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

يمكننا استخدام هذا الطلب. هذا SQL بسيط جدًا. ويمكننا رؤية هذه البيانات بأنفسنا.

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

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

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

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

pg_stat_bgwriter - يصف هذا العرض تشغيل نظامين فرعيين في الخلفية PostgreSQL: checkpointer и background writer.

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

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

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

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

وهناك ثلاث معاملات لضبط نقطة التفتيش:

• сheckpoint_segments.

• сheckpoint_timeout.

• сheckpoint_competion_target.

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

ملاحظة: النسخة 9.4 التي تم النظر فيها في التقرير لم تعد ذات صلة. في الإصدارات الحديثة من PostgreSQL ، فإن المعلمة checkpoint_segments استبدالها بالمعلمات min_wal_size и max_wal_size.

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

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

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

ملاحظة: _يصف النص التالي وجهات النظر الإحصائية المرتبطة بالنسخ المتماثل. تمت إعادة تسمية معظم أسماء العروض والوظائف في Postgres 10. كان جوهر إعادة التسمية هو استبدالها xlog في wal и location في lsn في أسماء الوظائف / العرض ، إلخ. مثال خاص ، وظيفة pg_xlog_location_diff() تمت إعادة تسميته إلى pg_wal_lsn_diff()._

هنا أيضًا لدينا الكثير. لكننا نحتاج فقط إلى العناصر المتعلقة بالموقع.

إذا رأينا أن جميع القيم متساوية ، فهذا مثالي والنسخة المتماثلة لا تتخلف عن المستوى الرئيسي.

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

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

إذا كانت هذه الأشياء مختلفة ، فهناك نوع من التأخير. التأخير هو تأخر النسخة المتماثلة من الرئيسي ، أي أن البيانات تختلف بين الخوادم.

هناك ثلاثة أسباب للتأخير:

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

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

ملاحظة: _بدلاً من pg_xlog_locationdiff () ، يمكنك استخدام عامل الطرح وطرح موقع واحد من آخر. مريح.

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

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

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

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

ومع ذلك ، هناك مقياس ثان - seq_tup_read. هذا هو عدد الصفوف التي تم إرجاعها من الفحص المتسلسل. إذا تجاوز متوسط ​​العدد 1 ، 000 ، 10 ، 000 ، فهذا مؤشر بالفعل قد تحتاج إلى إنشاء فهرس في مكان ما بحيث تكون عمليات الوصول حسب الفهرس ، أو من الممكن تحسين الاستعلامات التي تستخدم عمليات المسح المتسلسلة بحيث هذا لا يحدث.

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

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

يمكن أيضًا الحصول على أحجام الجدول باستخدام هذا الجدول واستخدام وظائف إضافية pg_total_relation_size(), pg_relation_size().

بشكل عام ، هناك metacommands dt и di، والتي يمكنك استخدامها في PSQL وكذلك الاطلاع على أحجام الجدول والفهرس.

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

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

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

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

وبسبب تصميمها ، فإن UPDATE هي عملية ثقيلة الوزن. لكن يمكن جعلها أسهل. يأكل hot updates. ظهرت في PostgreSQL الإصدار 8.3. وما هذا؟ هذا تحديث خفيف لا يتسبب في إعادة بناء الفهارس. أي أننا قمنا بتحديث السجل ، ولكن تم تحديث السجل الموجود في الصفحة (الذي ينتمي إلى الجدول) فقط ، ولا تزال الفهارس تشير إلى نفس السجل في الصفحة. هناك القليل من منطق العمل المثير للاهتمام ، فعندما يأتي الفراغ ، يكون لديه هذه السلاسل hot يعيد البناء ويستمر كل شيء في العمل دون تحديث الفهارس ، ويحدث كل شيء مع إهدار أقل للموارد.

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

ALTER TABLE table_name SET (fillfactor = 70);

كيفية زيادة الحجم hot updateاوف؟ يمكننا ان نستخدم fillfactor. يحدد حجم المساحة الخالية المحجوزة عند ملء صفحة في جدول باستخدام الإدخالات. عندما تذهب الإدخالات إلى الجدول ، فإنها تملأ الصفحة بالكامل ، ولا تترك مساحة فارغة فيها. ثم يتم تمييز صفحة جديدة. يتم ملء البيانات مرة أخرى. وهذا هو السلوك الافتراضي ، fillfactor = 100٪.

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

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

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

ملاحظة: _ منذ Postgres 10 ، تحسن الموقف مع تتبع فراغ الفراغ كثيرًا - ظهر عرض pg_stat_progressالفراغ ، مما يبسط إلى حد كبير مسألة مراقبة الفراغ الذاتي.

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

وكيف يتم حساب هذه العتبة؟ هذه نسبة مئوية محددة للغاية من إجمالي عدد الصفوف في الجدول. هناك معلمة autovacuum_vacuum_scale_factor. يحدد النسبة المئوية. لنفترض أن 10٪ + هناك حد أساسي إضافي قدره 50 سطرًا. وماذا يحدث؟ عندما يكون لدينا صفوف ميتة أكثر من "10٪ + 50" من كل الصفوف في الجدول ، فإننا نضع الجدول في فراغ تلقائي.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

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

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

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

pg_stat_all_indexes هي إحصاءات عن الفهارس. هي ليست كبيرة. ويمكننا الحصول على معلومات حول استخدام الفهارس منه. وعلى سبيل المثال ، يمكننا تحديد الفهارس التي لدينا إضافية.

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

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

رابطان:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

هذه أمثلة استعلام أكثر تقدمًا حول كيفية البحث عن الفهارس غير المستخدمة.

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

ما الذي يجب تلخيصه أيضًا بواسطة الفهارس؟

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

• يشغلون مساحة.

• إبطاء عمليات التحديث.

• عمل إضافي للفراغ.

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

الرأي التالي هو pg_stat_activity. هذا هو التناظرية للمنفعة ps، فقط في PostgreSQL. لو psثم تراقب العمليات في نظام التشغيل pg_stat_activity سيُظهر لك النشاط داخل PostgreSQL.

ماذا يمكننا أن نأخذ من هناك؟

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

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

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

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

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

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

يرجى ملاحظة: مع مثل هذا الطلب ، يمكننا تحديد الطلبات والمعاملات الطويلة. نستخدم الوظيفة clock_timestamp() لتحديد وقت العمل. الطلبات الطويلة التي وجدناها ، يمكننا تذكرها وتنفيذها explain، انظر إلى الخطط وقم بالتحسين بطريقة ما. نطلق النار على الطلبات الطويلة الحالية ونعيش.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

المعاملات السيئة خامدة في المعاملة وخاملة في معاملات المعاملات (المجهضة).

ماذا يعني ذلك؟ المعاملات لها حالات متعددة. ويمكن لإحدى هذه الدول أن تأخذ في أي وقت. هناك مجال لتعريف الدول state في هذا الرأي. ونستخدمها لتحديد الدولة.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

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

إذا رأيت أن لديك أكثر من 5-10-20 منهم في قاعدة البيانات الخاصة بك ، فأنت بحاجة إلى القلق والبدء في فعل شيء معهم.

هنا نستخدم أيضًا وقت الحساب clock_timestamp(). نحن نطلق المعاملات ، ونحسن التطبيق.

كما قلت أعلاه ، تكون الأقفال عندما تتنافس معاملتان أو أكثر على واحد أو مجموعة من الموارد. لهذا لدينا مجال waiting ذات قيمة منطقية true أو false.

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

ملاحظة: _بدءًا من Postgres 9.6 ، المجال waiting تمت إزالتها واستبدالها بحقلين إخباريين آخرين wait_event_type и wait_event._

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

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

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

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

والرابط الأول هو نص الطلب نفسه. إنها طويلة جدًا.

والرابط الثاني مقال عن الأقفال. من المفيد أن تقرأ ، إنه ممتع للغاية.

فماذا نرى؟ نرى طلبين. التعامل مع ALTER TABLE هي معاملة محظورة. لقد بدأت ، لكنها لم تنته ، والتطبيق الذي نشر هذه المعاملة يقوم بأشياء أخرى في مكان ما. والطلب الثاني هو التحديث. ينتظر انتهاء الجدول البديل قبل مواصلة عمله.

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

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

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

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

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

ويمكننا فقط إلقاء نظرة على الإحصائيات المختلفة لهذه الطلبات.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

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

ماذا نفعل؟ نحسب الإحصائيات الإجمالية لجميع الاستفسارات. بعد ذلك ، لكل استعلام ، نحسب مساهمته الفردية في هذه الإحصائية الإجمالية.

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

ماذا لدينا وراء الكواليس؟ لا يزال هناك عدد قليل من الطلبات التي لم أفكر فيها ، لأن الوقت محدود.

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

المساهمة التالية هي pg_buffercache. يسمح لك بفحص المخازن المؤقتة المشتركة: كيف يتم استخدام صفحات المخزن المؤقت بشكل مكثف ولأي جداول. ويسمح لك فقط بالنظر في المخازن المؤقتة المشتركة وتقييم ما يحدث هناك.

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

الوحدة التالية هي pg_stat_kcache. كما أنه يستخدم استدعاء النظام getrusage(). وتقوم بتنفيذه قبل وبعد تنفيذ الطلب. وفي الإحصائيات التي تم الحصول عليها ، يسمح لنا بتقدير مقدار ما أنفقه طلبنا على القرص I / O ، أي العمليات مع نظام الملفات والنظر في استخدام المعالج. ومع ذلك ، فإن الوحدة صغيرة (khe-khe) وتتطلب لعملها PostgreSQL 9.4 و pg_stat_statements ، والتي ذكرتها سابقًا.

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

• استخدام الإحصائيات سهل ، إنه SQL عادي. لقد جمعت طلبًا ، جمعته ، أرسلته ، نظرت فيه.

• الإحصائيات تساعد في الإجابة على الأسئلة. إذا كانت لديك أسئلة ، فانتقل إلى الإحصائيات - انظر واستخلص النتائج وحلل النتائج.

• وجرب. الكثير من الطلبات ، الكثير من البيانات. يمكنك دائما تحسين بعض الاستعلام الموجود. يمكنك عمل نسختك الخاصة من الطلب التي تناسبك أكثر من النسخة الأصلية واستخدامها.

مراجع

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

المؤلف يكتب أكثر
https://dataegret.com/news-blog (المهندس)

جامع الإحصائيات
https://www.postgresql.org/docs/current/monitoring-stats.html

وظائف إدارة النظام
https://www.postgresql.org/docs/current/functions-admin.html

وحدات المساهمة
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

أدوات SQL وأمثلة تعليمات SQL
https://github.com/dataegret/pg-utils

شكرا لكم جميعا على اهتمامكم!

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