PostgreSQL sorğularının performansının monitorinqi. 1-ci hissə - hesabat

Mühəndis - latın dilindən tərcümə - ilham.
Mühəndis hər şeyi edə bilər. (c) R. Dizel.
Epiqraflar.

Və ya verilənlər bazası administratorunun öz proqramlaşdırma keçmişini niyə xatırlamalı olduğu haqqında hekayə.

Müqəddimə

Bütün adlar dəyişdirilib. Matçlar təsadüfi olur. Material yalnız müəllifin şəxsi fikridir.

Zəmanətlərdən imtina: planlaşdırılan məqalələr seriyasında istifadə olunan cədvəllərin və skriptlərin ətraflı və dəqiq təsviri olmayacaqdır. Materiallar dərhal "OLDUĞU KİMİ" istifadə edilə bilməz.
Birincisi, materialın çoxluğuna görə,
ikincisi, real müştərinin istehsal bazası ilə kəskinliyinə görə.
Buna görə də məqalələrdə yalnız ən ümumi formada fikir və təsvirlər veriləcəkdir.
Ola bilsin ki, gələcəkdə sistem GitHub-da yerləşdirmə səviyyəsinə yüksəlsin, ya da yox. Zaman göstərəcək.

Hekayənin başlanğıcı -Hər şeyin necə başladığını xatırlayırsınız.
Nəticədə baş verənlər, ən ümumi mənada - "Sintez PostgreSQL performansını yaxşılaşdırmaq üsullarından biri kimi»

Bütün bunlar mənə niyə lazımdır?

Yaxşı, ilk növbədə, təqaüddəki şərəfli günləri xatırlayaraq, özünüzü unutmamaq üçün.
İkincisi, yazılanları sistemləşdirmək. Artıq özüm üçün bəzən çaşmağa başlayıram və ayrı-ayrı hissələri unuduram.

Yaxşı və ən əsası - birdən kimsə üçün lazımlı ola bilər və təkəri yenidən icad etməməyə və dırmıq toplamamağa kömək edə bilər. Başqa sözlə, karmanızı yaxşılaşdırın (Xabrovski deyil). Çünki bu dünyada ən dəyərli şey ideyalardır. Əsas odur ki, ideya tapmaqdır. İdeyanı reallığa çevirmək isə artıq sırf texniki məsələdir.

Beləliklə, yavaş-yavaş başlayaq ...

Problemin formalaşdırılması.

Mövcuddur:

PostgreSQL(10.5), qarışıq yük (OLTP+DSS), orta və yüngül yük, AWS buludunda yerləşdirilir.
Verilənlər bazası monitorinqi yoxdur, infrastruktur monitorinqi minimal konfiqurasiyada standart AWS alətləri kimi təqdim olunur.

Lazım:

Verilənlər bazasının performansına və statusuna nəzarət edin, ağır verilənlər bazası sorğularını optimallaşdırmaq üçün ilkin məlumatı tapın və əldə edin.

Qısa giriş və ya həllərin təhlili

Başlamaq üçün, problemin həlli variantlarını mühəndis üçün fayda və problemlərin müqayisəli təhlili baxımından təhlil etməyə çalışaq və ştat siyahısında olması lazım olanlar fayda və zərərlə məşğul olsunlar. menecmentin.

Seçim 1 - "Tələb əsasında işləmək"

Hər şeyi olduğu kimi buraxırıq. Müştəri sağlamlığında, verilənlər bazasında və ya tətbiqetmədə bir şeydən razı qalmazsa, o, DBA mühəndislərinə elektron poçtla və ya bilet qutusunda insident yaradaraq məlumat verəcəkdir.
Mühəndis bildiriş aldıqdan sonra problemi başa düşəcək, həll yolu təklif edəcək və ya hər şeyin öz-özünə həll olunacağına ümid edərək problemi həll edəcək və hər halda hər şey tezliklə unudulacaq.
Gingerbread və donuts, qançırlar və qabarGingerbread və donuts:
1. Ediləcək əlavə heç nə yoxdur
2. Çıxıb çirklənmək imkanı həmişə var.
3. Özünüzə sərf edə biləcəyiniz çox vaxt.
Çürüklər və şişlər:
1. Müştəri gec-tez bu dünyada varlığın mahiyyəti və ümumbəşəri ədalət haqqında düşünəcək və bir daha özünə sual verəcək - mən niyə pulumu onlara ödəyirəm? Nəticə həmişə eynidir - yeganə sual müştərinin cansıxıcı olması və vidalaşdığı zamandır. Və qidalandırıcı boşdur. Bu kədərlidir.
2. Mühəndisin inkişafı sıfıra bərabərdir.
3. İşin planlaşdırılmasında və yüklənmədə çətinliklər

Seçim 2 - "Dəflərlə rəqs et, ayaqqabı geyin və geyin"

1-ci bənd-Niyə bizə monitorinq sistemi lazımdır, bütün müraciətləri alacağıq. Biz məlumat lüğətinə və dinamik görünüşlərə hər cür sorğu göndəririk, hər cür sayğacları işə salırıq, hər şeyi cədvəllərə gətiririk, siyahıları və cədvəlləri olduğu kimi vaxtaşırı təhlil edirik. Nəticədə gözəl və ya çox olmayan qrafiklər, cədvəllər, hesabatlarımız var. Əsas odur ki, daha çox, daha çox olardı.
2-ci bənd-Fəaliyyət yaradın-bütün bunların təhlilini aparın.
3-ci bənd-Biz müəyyən bir sənəd hazırlayırıq, bu sənədə deyirik, sadəcə olaraq - “baza bazasını necə təchiz edirik”.
4-ci bənd- Qrafiklərin və rəqəmlərin bütün bu möhtəşəmliyini görən müştəri uşaqcasına sadəlövh inamdadır - indi hər şey bizim üçün işləyəcək, tezliklə. Və asanlıqla və ağrısız şəkildə maliyyə resurslarından ayrılın. Rəhbərlik də əmindir ki, bizim mühəndislər çox çalışırlar. Maksimum yükləmə.
5-ci bənd- 1-ci addımı müntəzəm olaraq təkrarlayın.
Gingerbread və donuts, qançırlar və qabarGingerbread və donuts:
1. Menecerlərin və mühəndislərin həyatı sadə, proqnozlaşdırıla bilən və fəaliyyətlə doludur. Hər şey uğuldayır, hamı məşğuldur.
2. Müştərinin həyatı da pis deyil - o, həmişə əmindir ki, bir az səbr etmək lazımdır və hər şey düzələcək. Yaxşılaşmır, yaxşı, yaxşı - bu dünya ədalətsizdir, sonrakı həyatda - bəxtiniz gətirəcək.
Çürüklər və şişlər:
1. Gec və ya tez, eyni şeyi edəcək, lakin bir az daha ucuz olan oxşar xidmətin daha ağıllı bir provayderi olacaq. Nəticə eyni olarsa, niyə daha çox pul ödəməlisiniz. Bu yenə qidalandırıcının yox olmasına səbəb olacaq.
2. Darıxdırıcıdır. Hər hansı bir kiçik mənalı fəaliyyət nə qədər darıxdırıcıdır.
3. Əvvəlki versiyada olduğu kimi - inkişaf yoxdur. Ancaq bir mühəndis üçün mənfi cəhət odur ki, birinci variantdan fərqli olaraq burada daim İİB yaratmaq lazımdır. Və bu vaxt tələb edir. Hansı ki, sevdiyiniz insanın xeyrinə xərclənə bilər. Özünə baxa bilmirsən, hamı səninlə maraqlanır.

Variant 3-Velosiped icad etməyə ehtiyac yoxdur, onu alıb sürmək lazımdır.

Başqa şirkətlərin mühəndisləri bilərəkdən pivə ilə pizza yeyirlər (ah, 90-cı illərdə Sankt-Peterburqun şanlı vaxtları). Hazırlanmış, sazlanmış və işləyən monitorinq sistemlərindən istifadə edək və ümumiyyətlə desək, onlar fayda gətirir (yaxşı, ən azı yaradıcılarına).
Gingerbread və donuts, qançırlar və qabarGingerbread və donuts:
1. Artıq icad edilmiş şeyi icad etməklə vaxt itirməyə ehtiyac yoxdur. Alın və istifadə edin.
2. Monitorinq sistemləri axmaqlar tərəfindən yazılmır və təbii ki, faydalıdır.
3. İşləyən monitorinq sistemləri adətən faydalı süzülmüş məlumat verir.
Çürüklər və şişlər:
1. Mühəndis bu halda mühəndis deyil, sadəcə başqasının məhsulunun istifadəçisidir.Yaxud istifadəçidir.
2. Müştəri ümumiyyətlə başa düşmək istəmədiyi, almamalı olduğu bir şeyi almaq zərurətinə əmin olmalıdır və ümumiyyətlə, ilin büdcəsi təsdiqlənib və dəyişməyəcək. Sonra ayrıca bir resurs ayırmalı, onu müəyyən bir sistem üçün konfiqurasiya etməlisiniz. Bunlar. Əvvəlcə ödəmək, ödəmək və yenidən ödəmək lazımdır. Müştəri isə xəsisdir. Bu həyatın normasıdır.

Nə etməli, Çernışevski? Sualınız çox yerlidir. (ilə)

Bu xüsusi vəziyyətdə və mövcud vəziyyətdə bir az fərqli edə bilərsiniz - öz monitorinq sistemimizi yaradaq.

Yaxşı, bir sistem deyil, əlbəttə ki, sözün tam mənasında, bu, çox yüksək və təkəbbürlüdür, lakin heç olmasa bir şəkildə özünüzü asanlaşdırın və performans insidentlərini həll etmək üçün daha çox məlumat toplayın. Özünüzü bir vəziyyətdə tapmamaq üçün - "ora get, bilmirəm haradan tap, nə olduğunu bilmirəm."

Bu seçimin müsbət və mənfi cəhətləri nələrdir:

Pros:
1. Maraqlıdır. Ən azı daimi "məlumat faylını kiçil, cədvəl sahəsini dəyişdir və s."dən daha maraqlıdır.
2. Bunlar yeni bacarıqlar və yeni inkişaflardır. Hansı gələcəkdə gec-tez layiqli gingerbread və donuts verəcəkdir.
Eksiler:
1. İşləmək lazımdır. Çox çalış.
2. Siz müntəzəm olaraq bütün fəaliyyətin mənasını və perspektivlərini izah etməli olacaqsınız.
3. Nəyisə qurban vermək lazım gələcək, çünki mühəndisin əlində olan yeganə resurs - zaman Kainat tərəfindən məhduddur.
4. Ən pis və ən xoşagəlməz - nəticədə "Siçan deyil, qurbağa deyil, naməlum balaca heyvan" kimi zibillər çıxa bilər.

Bir şeyi risk etməyən şampan içməz.
Beləliklə, əyləncə başlayır.

Ümumi fikir - sxematik

(İllüstrasiya məqalədən götürülmüşdür «Sintez PostgreSQL performansını yaxşılaşdırmaq üsullarından biri kimi")

Şərhlər:

• Hədəf verilənlər bazası standart PostgreSQL “pg_stat_statements” uzantısı ilə quraşdırılmışdır.
• Monitorinq verilənlər bazasında biz pg_stat_statements tarixçəsini ilkin mərhələdə saxlamaq və gələcəkdə ölçüləri və monitorinqi konfiqurasiya etmək üçün bir sıra xidmət cədvəlləri yaradırıq.
• Monitorinq hostunda biz bilet sistemində insident yaratmaq üçün olanlar da daxil olmaqla bir sıra bash skriptləri yaradırıq.

Xidmət masaları

Başlamaq üçün, sxematik olaraq sadələşdirilmiş ERD, sonunda nə oldu:

Cədvəllərin qısa təsvirison nöqtə - host, instansiyaya qoşulma nöqtəsi
Verilənlər bazası - verilənlər bazası seçimləri
pg_stat_history - hədəf verilənlər bazasının pg_stat_statements görünüşünün müvəqqəti anlıq görüntülərini saxlamaq üçün tarixi cədvəl
metrik_glossary - Performans göstəriciləri lüğəti
metrik_konfiqurasiya - fərdi ölçülərin konfiqurasiyası
metrik - nəzarət edilən sorğu üçün xüsusi metrik
metric_alert_history - performans xəbərdarlıqlarının tarixi
log_sorğu - AWS-dən endirilmiş PostgreSQL log faylından təhlil edilmiş qeydləri saxlamaq üçün xidmət cədvəli
ilkin - əsas kimi istifadə olunan müddətin parametrləri
nəzarət məntəqəsi - verilənlər bazasının vəziyyətini yoxlamaq üçün ölçülərin konfiqurasiyası
checkpoint_alert_history - verilənlər bazası statusu yoxlama metriklərinin xəbərdarlıq tarixi
pg_stat_db_queries — aktiv sorğuların xidmət cədvəli
fəaliyyət_loqu — fəaliyyət jurnalının xidmət cədvəli
trap_oid - tələ konfiqurasiyası xidmət cədvəli

Mərhələ 1 - performans statistikasını toplayın və hesabatlar əldə edin

Statistik məlumatları saxlamaq üçün cədvəldən istifadə olunur. pg_stat_history
pg_stat_history cədvəl strukturu

                                          Cədvəl "public.pg_stat_history" Sütun | növü | Dəyişdiricilər-------------------+--------------------- --+---- --------------------------------- id | tam | null default default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | saat qurşağı olmadan vaxt damgası | verilənlər bazası_id | tam | dbid | oid | userid | oid | queryid | bigint | sorğu | mətn | zənglər | bigint | ümumi_zaman | ikiqat dəqiqlik | min_zaman | ikiqat dəqiqlik | maksimum_zaman | ikiqat dəqiqlik | orta_zaman | ikiqat dəqiqlik | stddev_time | ikiqat dəqiqlik | sıralar | bigint | shared_blks_hit | bigint | paylaşılan_blks_oxu | 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 | ikiqat dəqiqlik | blk_write_time | ikiqat dəqiqlik | baza_id | tam | İndekslər: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (verilənlər bazası_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Xarici açar məhdudiyyətləri "REYK"tabaseid: İSTİFADƏLƏR verilənlər bazası (id ) KASKADI SİLİN

Gördüyünüz kimi, cədvəl sadəcə məcmu görünüş məlumatıdır pg_stat_statements hədəf verilənlər bazasında.

Bu cədvəlin istifadəsi çox sadədir.

pg_stat_history hər saat üçün sorğunun icrasının yığılmış statistikasını əks etdirəcək. Hər saatın əvvəlində cədvəli doldurduqdan sonra statistika pg_stat_statements ilə sıfırlayın pg_stat_statements_reset().
Qeyd: statistik məlumatlar 1 saniyədən çox davam edən sorğular üçün toplanır.
pg_stat_history cədvəlinin doldurulması

--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;

Nəticədə cədvəldə müəyyən bir müddətdən sonra pg_stat_history Cədvəlin məzmununun bir sıra şəkillərinə sahib olacağıq pg_stat_statements hədəf verilənlər bazası.

Əslində hesabat verir

Sadə sorğulardan istifadə etməklə kifayət qədər faydalı və maraqlı hesabatlar əldə edə bilərsiniz.

Müəyyən bir müddət üçün ümumiləşdirilmiş məlumatlar

İstək

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 ;

D.B. Vaxt

to_char(interval '1 millisaniyə' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

G/Ç vaxtı

üçün_char(interval '1 millisaniyə' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

Ümumi_zaman üzrə TOP10 SQL

İstək

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

------------------------------------------------- ----------------------------------- | ÜMUMİ İCRA MÜDDƏTİNƏ GÖRƏ TOP10 SQL | #| queryid| zənglər| zənglər %| ümumi_vaxt (ms) | dbtime % +----+-----------+-----------+-----------+------ -------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 ms.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113( 93113.835 ms.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 ms.)| .03

Ümumi I/O vaxtına görə TOP10 SQL

İstək

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 ÜMUMİ GİRİŞ/ÇIXIŞ ZAMANI | #| queryid| zənglər| zənglər %| I/O time (ms)|db I/O time % +----+-----------+-----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 iyun | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35 | 4| 2460318461| 1| .00000|00:04:05.981( 245981.117 ms.)| 14.93 | 5| 1484454471| 4| .00001|00:00:39.144( 39144.221 ms.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 ms.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 ms.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 ms.)| .03

Maksimum icra müddətinə görə TOP10 SQL

İstək

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

------------------------------------------------- ----------------------------------- | MAKSİMUM İCRA MÜDDƏTİNƏ GÖRƏ TOP10 SQL | #| snapshot| snapshotID| queryid| maksimum_zaman (ms) +----+------------------+-----------+--------- --+--------------------------------------- | 1| 05.04.2019 01:03| 4169| 655729273| 00:02:01.869( 121869.981 ms.) | 2| 04.04.2019 17:00| 4153| 821760255| 00:01:41.570( 101570.841 ms.) | 3| 04.04.2019 16:00| 4146| 821760255| 00:01:41.570( 101570.841 ms.) | 4| 04.04.2019 16:00| 4144| 4152624390| 00:01:36.964( 96964.607 ms.) | 5| 04.04.2019 17:00| 4151| 4152624390| 00:01:36.964( 96964.607 ms.) | 6| 05.04.2019 10:00 | 4188| 1484454471| 00:01:33.452( 93452.150 ms.) | 7| 04.04.2019 17:00| 4150| 2460318461| 00:01:33.113( 93113.835 ms.) | 8| 04.04.2019 15:00| 4140| 1484454471| 00:00:11.892( 11892.302 ms.) | 9| 04.04.2019 16:00| 4145| 1484454471| 00:00:11.892( 11892.302 ms.) | 10| 04.04.2019 17:00| 4152| 1484454471| 00:00:11.892( 11892.302 ms.)

TOP10 SQL tərəfindən SHARED bufer oxumaq/yazmaq

İstək

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

------------------------------------------------- ----------------------------------- | PAYLAŞILMIŞ BUFER OXUMA/YAZMA İLƏ TOP10 SQL | #| snapshot| snapshotID| queryid| paylaşılan bloklar oxundu| paylaşılan bloklar yazmaq +----+------------------+-----------+---------- -+--------------------+--------------------- | 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 ------------------------------------------------- -------------------------------------------------

Maksimum icra müddətinə görə sorğunun paylanması histoqramı

İstək

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 HİSTOQRAM | ÜMUMİ ZƏNGLƏR: 33851920 | MIN VAXT : 00:00:01.063 | MAX VAXT : 00:02:01.869 ---------------------------------- -------- -------------------------------------- | minimum müddət| maksimum müddət| zənglər +---------------------------------+---------------------- --------------------+---------- | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0

Saniyədə Sorğu üzrə TOP10 Snapshot

İstək

--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

|------------------------------------------------ --------------------------------------- | QueryPerSeconds nömrələri ilə sifariş edilən TOP10 Snapshot --------------------------------------------- --------------------------------------------- ------------------------------------------- | #| snapshot| snapshotID| zənglər| ümumi dbtime| QPS | I/O vaxtı | G/Ç vaxtı % +-----+------------------+-----------+------- ----+---------------------------------+---------- -+---------------------------------+----------- | 1| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 2| 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 | 3| 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 | 4| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 5| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 6| 04.04.2019 14:00 | 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 7| 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 | 8| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 9| 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 | 10| 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

QueryPerSeconds və I/O Time ilə Saatlıq İcra Tarixi

İstək

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

Bütün SQL seçimlərinin mətni

İstək

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

Ümumi

Gördüyünüz kimi, kifayət qədər sadə vasitələrlə iş yükü və verilənlər bazasının vəziyyəti haqqında çoxlu faydalı məlumatlar əldə edə bilərsiniz.

Qeyd:Sorğularda sorğu kodunu düzəltsəniz, biz ayrıca sorğu üçün tarixçə əldə edəcəyik (yerə qənaət etmək üçün ayrıca sorğu üçün hesabatlar buraxılır).

Beləliklə, sorğunun icrasına dair statistik məlumatlar mövcuddur və toplanır.
Birinci mərhələ “statistik məlumatların toplanması” tamamlandı.

İkinci mərhələyə keçə bilərsiniz - "performans ölçülərini konfiqurasiya etmək".


Ancaq bu tamamilə fərqli bir hekayədir.

Davam etmək üçün ...

Mənbə: www.habr.com