مراقبة أداء استعلامات PostgreSQL. الجزء الأول - إعداد التقارير

مهندس - مترجم من اللاتينية - مستوحى.
يمكن للمهندس أن يفعل أي شيء. (ج) R. ديزل.
النقوش.

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

مقدمة

تم تغيير جميع الأسماء. المطابقات عشوائية. المادة هي فقط الرأي الشخصي للمؤلف.

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

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

لماذا احتاج كل هذا؟

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

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

لذلك دعونا نبدأ ببطء ...

صياغة المشكلة.

متاح:

PostgreSQL (10.5) ، حمولة مختلطة (OLTP + DSS) ، حمولة متوسطة إلى خفيفة ، مستضافة في سحابة AWS.
لا توجد مراقبة لقاعدة البيانات ، يتم تقديم مراقبة البنية التحتية كأدوات AWS قياسية في الحد الأدنى من التكوين.

يتطلب:

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

مقدمة موجزة أو تحليل الحلول

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

الخيار 1 - "العمل عند الطلب"

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

الخيار 2 - "الرقص مع الدف ، البس الحذاء"

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

الخيار 3 - لا داعي لاختراع دراجة ، فأنت بحاجة لشرائها وركوبها.

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

ماذا تفعل يا تشيرنيشيفسكي؟ سؤالك وثيق الصلة بالموضوع. (مع)

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

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

ما هي إيجابيات وسلبيات هذا الخيار:

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

من لا يخاطر بشيء لا يشرب الشمبانيا.
لذا ، تبدأ المتعة.

فكرة عامة - تخطيطي

(رسم توضيحي مأخوذ من المقال «التوليف كإحدى الطرق لتحسين أداء PostgreSQL»)

التفسير:

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

جداول الخدمة

بادئ ذي بدء ، ERD مبسط تخطيطيًا ، ما حدث في النهاية:

وصف موجز للجداولنقطة النهاية - المضيف ، نقطة الاتصال بالمثيل
قاعدة بيانات - خيارات قاعدة البيانات
pg_stat_history - جدول تاريخي لتخزين اللقطات المؤقتة لعرض pg_stat_statements لقاعدة البيانات الهدف
metric_glossary - قاموس مقاييس الأداء
metric_config - تكوين المقاييس الفردية
متري - مقياس محدد للطلب الذي تتم مراقبته
metric_alert_history - تاريخ تحذيرات الأداء
log_query - جدول خدمة لتخزين السجلات المحللة من ملف سجل PostgreSQL الذي تم تنزيله من AWS
خط الأساس - معلمات الفترة الزمنية المستخدمة كأساس
نقطة تفتيش - تكوين مقاييس للتحقق من حالة قاعدة البيانات
check_alert_history - تاريخ التحذير من مقاييس التحقق من حالة قاعدة البيانات
pg_stat_db_queries - جدول خدمة الطلبات النشطة
سجل النشاطات - جدول خدمة سجل النشاط
trap_oid - جدول خدمة تكوين فخ

المرحلة 1 - جمع إحصائيات الأداء والحصول على التقارير

يستخدم الجدول لتخزين المعلومات الإحصائية. pg_stat_history
pg_stat_history هيكل الجدول

                                          جدول "public.pg_stat_history" العمود | اكتب | المعدلات -------------------- + --------------------- - + ---- -------------------------------- معرف | عدد صحيح | nextval الافتراضي ليس فارغًا ('pg_stat_history_id_seq' :: regclass) snapshot_timestamp | الطابع الزمني بدون المنطقة الزمنية | معرّف_قاعدة البيانات | عدد صحيح | dbid | oid | معرف المستخدم | oid | معرّف الاستعلام | بيجينت | الاستعلام | نص | المكالمات | بيجينت | مجموع_وقت | دقة مزدوجة | min_time | دقة مزدوجة | max_time | دقة مزدوجة | في الوقت نفسه | دقة مزدوجة | stddev_time | دقة مزدوجة | صفوف | بيجينت | Shared_blks_hit | بيجينت | Shared_blks_read | بيجينت | Shared_blks_dirtied | بيجينت | Shared_blks_written | بيجينت | local_blks_hit | بيجينت | local_blks_read | بيجينت | local_blks_dirtied | بيجينت | local_blks_written | بيجينت | temp_blks_read | بيجينت | temp_blks_written | بيجينت | blk_read_time | دقة مزدوجة | blk_write_time | دقة مزدوجة | معرّف_الخط الأساسي | عدد صحيح | الفهارس: "pg_stat_history_pkey" PRIMARY KEY، btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) قيود المفتاح الخارجي: "database_id_fk" FOREIGN KEY (قاعدة البيانات) ) عند حذف الحالة

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

استخدام هذا الجدول بسيط للغاية.

pg_stat_history ستمثل الإحصائيات المتراكمة لتنفيذ الاستعلام لكل ساعة. في بداية كل ساعة بعد ملء الجدول الإحصائيات pg_stat_statements إعادة تعيين مع مجموعة pg_stat_statements_reset ().
ملاحظة: يتم جمع الإحصائيات للطلبات التي تزيد مدتها عن ثانية واحدة.
ملء جدول pg_stat_history

--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
  endpoint_rec record ;
  database_rec record ;
  pg_stat_snapshot record ;
  current_snapshot_timestamp timestamp without time zone;
BEGIN
  current_snapshot_timestamp = date_trunc('minute',now());  
  
  FOR endpoint_rec IN SELECT * FROM endpoint 
  LOOP
    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
	  LOOP
	    
		RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
		
		--Connect to the target DB	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_time double precision , 
	         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
             local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
             temp_blks_read	 bigint ,temp_blks_written bigint ,
             blk_read_time double precision , blk_write_time double precision	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
	
        FOR pg_stat_snapshot IN
          --All queries with max_time greater than 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

	                  )
	        AS t
	         ( dbid oid , userid oid , queryid bigint ,query text , calls bigint , 
  	           total_time double precision ,min_time double precision	 ,max_time double precision	 , mean_time double precision	 ,  stddev_time double precision	 , 
	           rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
               local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
               temp_blks_read	 bigint ,temp_blks_written bigint ,
               blk_read_time double precision , blk_write_time double precision	  
	         )
	    LOOP
		  INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	      VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );
		  
        END LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
    
  END LOOP;

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

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

في الواقع التقارير

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

بيانات مجمعة لفترة زمنية معينة

تحقيق

SELECT 
  database_id , 
  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,
  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,
  SUM(shared_blks_read) AS shared_blks_read ,
  SUM(shared_blks_dirtied) AS shared_blks_dirtied,
  SUM(shared_blks_written) AS shared_blks_written , 
  SUM(local_blks_hit) AS local_blks_hit , 
  SUM(local_blks_read) AS local_blks_read , 
  SUM(local_blks_dirtied) AS local_blks_dirtied , 
  SUM(local_blks_written)  AS local_blks_written,
  SUM(temp_blks_read) AS temp_blks_read, 
  SUM(temp_blks_written) temp_blks_written , 
  SUM(blk_read_time) AS blk_read_time , 
  SUM(blk_write_time) AS blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

دي بي الوقت

to_char (الفاصل الزمني "1 مللي ثانية" * pg_total_stat_history_rec.total_time، "HH24: MI: SS.MS")

I / O Time

to_char (الفاصل الزمني "1 مللي ثانية" * (pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time) ، "HH24: MI: SS.MS")

TOP10 SQL حسب total_time

تحقيق

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(total_time)  AS total_time  	
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT 
GROUP BY queryid 
ORDER BY 3 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------ | TOP10 SQL حسب إجمالي وقت التنفيذ | # | معرّف الاستعلام | المكالمات | المكالمات٪ | total_time (مللي ثانية) | dbtime٪ + ---- + ----------- + ----------- + ----------- + ------ -------------------- + ---------- | 1 | 821760255 | 2 | .00001 | 00: 03: 23.141 (203141.681 مللي ثانية) | 5.42 | 2 | 4152624390 | 2 | .00001 | 00: 03: 13.929 (193929.215 مللي ثانية) | 5.17 | 3 | 1484454471 | 4 | .00001 | 00: 02: 09.129 (129129.057 مللي ثانية) | 3.44 | 4 | 655729273 | 1 | .00000 | 00: 02: 01.869 (121869.981 مللي ثانية) | 3.25 | 5 | 2460318461 | 1 | .00000 | 00: 01: 33.113 (93113.835 مللي ثانية) | 2.48 | 6 | 2194493487 | 4 | .00001 | 00: 00: 17.377 (17377.868 مللي ثانية) | .46 | 7 | 1053044345 | 1 | .00000 | 00: 00: 06.156 (6156.352 مللي ثانية) | .16 | 8 | 3644780286 | 1 | .00000 | 00: 00: 01.063 (1063.830 مللي ثانية) | .03

TOP10 SQL حسب إجمالي وقت الإدخال / الإخراج

تحقيق

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10

-------------------------------------------------- -------------------------------------- | TOP10 SQL حسب إجمالي وقت الإدخال / الإخراج | # | معرّف الاستعلام | المكالمات | المكالمات٪ | وقت الإدخال / الإخراج (مللي ثانية) | db I / O time٪ + ---- + ----------- + ----------- + ------ ----- + -------------------------------- + ----------- - | 1 | 4152624390 | 2 | .00001 | 00: 08: 31.616 (511616.592 مللي ثانية) | 31.06 يونيو | 2 | 821760255 | 2 | .00001 | 00: 08: 27.099 (507099.036 مللي ثانية) | 30.78 | 3 | 655729273 | 1 | .00000 | 00: 05: 02.209 (302209.137 مللي ثانية) | 18.35 | 4 | 2460318461 | 1 | .00000 | 00: 04: 05.981 (245981.117 مللي ثانية) | 14.93 | 5 | 1484454471 | 4 | .00001 | 00: 00: 39.144 (39144.221 مللي ثانية) | 2.38 | 6 | 2194493487 | 4 | .00001 | 00: 00: 18.182 (18182.816 مللي ثانية) | 1.10 | 7 | 1053044345 | 1 | .00000 | 00: 00: 16.611 (16611.722 مللي ثانية) | 1.01 | 8 | 3644780286 | 1 | .00000 | 00: 00: 00.436 (436.205 مللي ثانية) | .03

TOP10 SQL بأقصى وقت للتنفيذ

تحقيق

SELECT 
  id AS snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------ | TOP10 SQL حسب وقت التنفيذ الأقصى | # | لقطة | snapshotID | معرّف الاستعلام | max_time (مللي ثانية) + ---- + ------------------ + ----------- + --------- - + ---------------------------------------- | 1 | 05.04.2019/01/03 4169:655729273 | 00 | 02 | 01.869: 121869.981: 2 (04.04.2019 مللي ثانية) | 17 | 00/4153/821760255 00:01 | 41.570 | 101570.841 | 3: 04.04.2019: 16 (00 مللي ثانية) | 4146 | 821760255/00/01 41.570:101570.841 | 4 | 04.04.2019 | 16: 00: 4144 (4152624390 مللي ثانية) | 00 | 01/36.964/96964.607 5:04.04.2019 | 17 | 00 | 4151: 4152624390: 00 (01 مللي ثانية) | 36.964 | 96964.607/6/05.04.2019 10:00 | 4188 | 1484454471 | 00: 01: 33.452 (93452.150 مللي ثانية) | 7 | 04.04.2019/17/00 4150:2460318461 | 00 | 01 | 33.113: 93113.835: 8 (04.04.2019 مللي ثانية) | 15 | 00/4140/1484454471 00:00 | 11.892 | 11892.302 | 9: 04.04.2019: 16 (00 مللي ثانية) | 4145 | 1484454471/00/00 11.892:11892.302 | 10 | 04.04.2019 | 17: 00: 4152 (1484454471 مللي ثانية) | 00 | 00/11.892/11892.302 XNUMX:XNUMX | XNUMX | XNUMX | XNUMX: XNUMX: XNUMX (XNUMX مللي ثانية) | XNUMX | XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX | XNUMX | XNUMX: XNUMX: XNUMX (XNUMX مللي ثانية)

قراءة / كتابة TOP10 SQL بواسطة المخزن المؤقت المشترك

تحقيق

SELECT 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC  , 5 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------ | TOP10 SQL BY SHARED BUFFER READ / WRITE | # | لقطة | snapshotID | معرّف الاستعلام | قراءة الكتل المشتركة | كتابة الكتل المشتركة + ---- + ------------------ + ----------- + ---------- - + --------------------- + --------------------- | 1 | 04.04.2019/17/00 4153:821760255 | 797308 | 0 | 2 | 04.04.2019 | 16 | 00/4146/821760255 797308:0 | 3 | 05.04.2019 | 01 | 03 | 4169 | 655729273/797158/0 4:04.04.2019 | 16 | 00 | 4144 | 4152624390 | 756514 | 0/5/04.04.2019 17:00 | 4151 | 4152624390 | 756514 | 0 | 6 | 04.04.2019/17/00 4150:2460318461 | 734117 | 0 | 7 | 04.04.2019 | 17 | 00/4155/3644780286 52973:0 | 8 | 05.04.2019 | 01 | 03 | 4168 | 1053044345/52818/0 9:04.04.2019 | 15 | 00 | 4141 | 2194493487 | 52813 | 0/10/04.04.2019 16:00 | 4147 | 2194493487 | 52813 | 0 | XNUMX | XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX | XNUMX | XNUMX | XNUMX | XNUMX | XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX | XNUMX | XNUMX | XNUMX ------------------------------------------------- -------------------------------------------------

رسم بياني لتوزيع الاستعلام حسب وقت التنفيذ الأقصى

طلبات

SELECT  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;

SELECT 
  SUM(calls) AS calls
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id =DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND 
  ( max_time >= hist_current_min AND  max_time < hist_current_max ) ;

| ------------------------------------------------- ---------------------------------------- | MAX_TIME HISTOGRAM | إجمالي المكالمات: 33851920 | MIN TIME: 00: 00: 01.063 | الحد الأقصى للوقت: 00: 02: 01.869 ---------------------------------- -------- ---------------------------- | مدة دقيقة | المدة القصوى | المكالمات + ---------------------------------- + ------------- --------------------- + ---------- | 00: 00: 01.063 (1063.830 مللي ثانية) | 00: 00: 13.144 (13144.445 مللي ثانية) | 9 | 00: 00: 13.144 (13144.445 مللي ثانية) | 00: 00: 25.225 (25225.060 مللي ثانية) | 0 | 00: 00: 25.225 (25225.060 مللي ثانية) | 00: 00: 37.305 (37305.675 مللي ثانية) | 0 | 00: 00: 37.305 (37305.675 مللي ثانية) | 00: 00: 49.386 (49386.290 مللي ثانية) | 0 | 00: 00: 49.386 (49386.290 مللي ثانية) | 00: 01: 01.466 (61466.906 مللي ثانية) | 0 | 00: 01: 01.466 (61466.906 مللي ثانية) | 00: 01: 13.547 (73547.521 مللي ثانية) | 0 | 00: 01: 13.547 (73547.521 مللي ثانية) | 00: 01: 25.628 (85628.136 مللي ثانية) | 0 | 00: 01: 25.628 (85628.136 مللي ثانية) | 00: 01: 37.708 (97708.751 مللي ثانية) | 4 | 00: 01: 37.708 (97708.751 مللي ثانية) | 00: 01: 49.789 (109789.366 مللي ثانية) | 2 | 00: 01: 49.789 (109789.366 مللي ثانية) | 00: 02: 01.869 (121869.981 مللي ثانية) | 0

أعلى 10 لقطات حسب الاستعلام في الثانية

طلبات

--pg_qps.sql
--Calculate Query Per Second 
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
 pg_stat_history_rec record ;
 prev_pg_stat_history_id integer ;
 prev_pg_stat_history_rec record;
 total_seconds double precision ;
 result double precision;
BEGIN 
  result = 0 ;
  
  SELECT *
  INTO pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = pg_stat_history_id ;

  IF pg_stat_history_rec.snapshot_timestamp IS NULL 
  THEN
    RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id < pg_stat_history_rec.id ;

  IF prev_pg_stat_history_id IS NULL 
  THEN
    RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
	RETURN NULL ;
  END IF;
  
  SELECT *
  INTO prev_pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = prev_pg_stat_history_id ;
  
  --RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;    

  total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
  
  --RAISE NOTICE 'total_seconds = % ', total_seconds ;    
  
  --RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;      
  
  IF total_seconds > 0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

| ------------------------------------------------- ---------------------------------------- | أعلى 10 لقطات مرتبة بواسطة أرقام QueryPerSeconds -------------------------------------- ------ -------------------------------------------- ------ ------------------------------------------- | # | لقطة | snapshotID | المكالمات | إجمالي dbtime | QPS | I / O الوقت | I / O time٪ + ----- + ------------------ + ----------- + ------- ---- + ---------------------------------- + ---------- - + ---------------------------------- + ----------- | 1 | 04.04.2019/20/04 4161:5758631 | 00 | 06 | 30.513: 390513.926: 1573.396 (00 مللي ثانية) | 00 | 01.470: 1470.110: 376 (2 مللي ثانية) | .04.04.2019 | 17 | 00/4149/3529197 00:11 | 48.830 | 708830.618 | 980.332: 00: 12 (47.834 مللي ثانية) | 767834.052 | 108.324: 3: 04.04.2019 (16 مللي ثانية) | 00 | 4143 | 3525360/00/10 13.492:613492.351 | 979.267 | 00 | 08: 41.396: 521396.555 (84.988 مللي ثانية) | 4 | 04.04.2019: 21: 03 (4163 مللي ثانية) | 2781536 | 00 | 03/06.470/186470.979 785.745:00 | 00 | 00.249 | 249.865: 134: 5 (04.04.2019 مللي ثانية) | 19 | 03: 4159: 2890362 (00 مللي ثانية) | .03 | 16.784 | 196784.755/776.979/00 00: 01.441 | 1441.386 | 732 | 6: 04.04.2019: 14 (00 مللي ثانية) | 4137 | 2397326: 00: 04 (43.033 مللي ثانية) | .283033.854 | 665.924 | 00/00/00.024 24.505:009 | 7 | 04.04.2019 | 15: 00: 4139 (2394416 مللي ثانية) | 00 | 04: 51.435: 291435.010 (665.116 مللي ثانية) | .00 | 00 | 12.025/12025.895/4.126 8:04.04.2019 | 13 | 00 | 4135: 2373043: 00 (04 مللي ثانية) | 26.791 | 266791.988: 659.179: 00 (00 مللي ثانية) | 00.064 | 64.261 | 024/9/05.04.2019 01:03 | 4167 | 4387191 | 00: 06: 51.380 (411380.293 مللي ثانية) | 609.332 | 00: 05: 18.847 (318847.407 مللي ثانية) | .77.507 | 10 | 04.04.2019/18/01 4157:1145596 | 00 | 01 | 19.217: 79217.372: 313.004 (00 مللي ثانية) | 00 | 01.319: 1319.676: 1.666 (XNUMX مللي ثانية) | XNUMX | XNUMX | XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX | XNUMX | XNUMX: XNUMX: XNUMX (XNUMX مللي ثانية) | XNUMX | XNUMX: XNUMX: XNUMX (XNUMX مللي ثانية) | XNUMX

سجل التنفيذ بالساعة مع QueryPerSeconds و I / O Time

تحقيق

SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2

|-----------------------------------------------------------------------------------------------
| HOURLY EXECUTION HISTORY  WITH QueryPerSeconds and I/O Time
-----------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PER SECOND HISTORY
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O time| I/O time %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 11:00|       4131|       3747|  00:00:00.835(       835.374 ms.)|      1.041|  00:00:00.000(          .000 ms.)|       .000
|    2|  04.04.2019 12:00|       4133|    1002722|  00:01:52.419(    112419.376 ms.)|    278.534|  00:00:00.149(       149.105 ms.)|       .133
|    3|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    4|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    5|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    6|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    7|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    8|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666
|    9|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|   10|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|   11|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|   12|  04.04.2019 23:03|       4165|    1443155|  00:01:34.467(     94467.539 ms.)|    200.438|  00:00:00.015(        15.287 ms.)|       .016
|   13|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   14|  05.04.2019 02:03|       4171|     189852|  00:00:10.989(     10989.899 ms.)|     52.737|  00:00:00.539(       539.110 ms.)|      4.906
|   15|  05.04.2019 03:01|       4173|       3627|  00:00:00.103(       103.000 ms.)|      1.042|  00:00:00.004(         4.131 ms.)|      4.010
|   16|  05.04.2019 04:00|       4175|       3627|  00:00:00.085(        85.235 ms.)|      1.025|  00:00:00.003(         3.811 ms.)|      4.471
|   17|  05.04.2019 05:00|       4177|       3747|  00:00:00.849(       849.454 ms.)|      1.041|  00:00:00.006(         6.124 ms.)|       .721
|   18|  05.04.2019 06:00|       4179|       3747|  00:00:00.849(       849.561 ms.)|      1.041|  00:00:00.000(          .051 ms.)|       .006
|   19|  05.04.2019 07:00|       4181|       3747|  00:00:00.839(       839.416 ms.)|      1.041|  00:00:00.000(          .062 ms.)|       .007
|   20|  05.04.2019 08:00|       4183|       3747|  00:00:00.846(       846.382 ms.)|      1.041|  00:00:00.000(          .007 ms.)|       .001
|   21|  05.04.2019 09:00|       4185|       3747|  00:00:00.855(       855.426 ms.)|      1.041|  00:00:00.000(          .065 ms.)|       .008
|   22|  05.04.2019 10:00|       4187|       3797|  00:01:40.150(    100150.165 ms.)|      1.055|  00:00:21.845(     21845.217 ms.)|     21.812

نص من كل اختيار مزود

تحقيق

SELECT 
  queryid , 
  query 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query

مجموع

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

ملحوظة:إذا قمت بإصلاح معرّف الاستعلام في الاستعلامات ، فسنحصل على السجل لطلب منفصل (من أجل توفير مساحة ، يتم حذف التقارير الخاصة بطلب منفصل).

لذلك ، تتوفر البيانات الإحصائية حول أداء الاستعلام ويتم تجميعها.
اكتملت المرحلة الأولى من "جمع البيانات الإحصائية".

يمكنك المتابعة إلى المرحلة الثانية - "تكوين مقاييس الأداء".


ولكن هذه قصة مختلفة تماما.

يتبع ...

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