Praćenje performansi upita PostgreSQL. Dio 1 — izvještavanje

Inženjer — na latinskom jeziku — nadahnut.
Inženjer može sve. (c) R.Dizel.
Epigrafi.

Ili priča o tome zašto bi administrator baza podataka trebao da se seti svoje programske prošlosti.

Predgovor

Sva imena su promenjena. Podudarnosti su slučajne. Materijal predstavlja isključivo lično mišljenje autora.

Odricanje od garancija: u planiranom ciklusu članaka neće biti detaljnog i tačnog opisa korišćenih tabela i skripti. Materijali se ne mogu odmah koristiti "BAŠ TAKO".
Prvo, zbog velike količine materijala,
drugo, zbog prilagođenosti produkcijskoj bazi stvarnog klijenta.
S toga će članci sadržati samo ideje i opise u najopštijem smislu.
Možda će u budućnosti sistem sazreti do nivoa postavljanja na GitHub, a možda i neće. Vreme će pokazati.

Početak priče — "Sećaš li se kako je sve počelo».
Šta je rezultat, u najopštijim crtama — "Sintez kao jedna od metoda poboljšanja performansi PostgreSQL-a»

Zašto mi je sve ovo?

Pa, prvenstveno, da ne zaboravim, setivši se u penziji lepih dana.
Drugo, da sistematizujem napisano. Jer se već i sam, ponekad, počinjem zbunjivati i zaboravljati pojedine delove.

I najvažnije — možda će nekome zatrebati i pomoći da ne izmišlja bicikl i ne sakuplja lopate. Drugim rečima, poboljšati svoju karmu (ne Habr-ovu). Jer, najvrednije u ovom svetu su ideje. Bitno je pronaći ideju. A realizovati ideju u stvarnost to je već isključivo tehničko pitanje.

Dakle, počnimo polako…

Postavljanje zadatka.

Postoji:

Baza podataka PostgreSQL (10.5), mešovitog tipa opterećenja (OLTP+DSS), srednje-male opterećenosti, smeštena u oblaku AWS.
Nadzor baze podataka je odsutan, nadzor infrastrukture predstavljen je u obliku standardnih sredstava AWS u minimalnoj konfiguraciji.

Potrebno:

Nadzor performansi i stanja baze podataka, pronalaženje i posedovanje osnovnih informacija za optimizaciju teških upita ka BDP-u.

Kratko uvod ili analiza mogućnosti rešenja

Za početak, pokušajmo da razložimo mogućnosti rešenja problema sa stanovišta komparativne analize koristi i neugodnosti za inženjera, a koristi i gubici menadžmenta neka se bave oni kojima je to povereno prema radnom nalogu.

Opcija 1 — "Rad na zahtev"

Ostavljamo sve kako jest. Ako naručitelja nešto ne zadovoljava u radu, performansama baze podataka ili aplikacije, obavijestit će DBA inženjere putem e-maila ili otvaranjem incidenta u sustavu za podršku.
Inženjer koji primi obavijest će razmotriti problem, predložiti rješenje ili odložiti problem nadajući se da će se sve samo riješiti, a i svejedno, uskoro će se sve zaboraviti.
Kolačići i peciva, modrice i kvrgeKolačići i peciva:
1. Nema potrebe za dodatnim akcijama
2. Uvijek postoji prilika za izbjegavanje odgovornosti i izležavanje.
3. Puno vremena koje možete provesti prema vlastitom nahođenju.
Modrice i kvrge:
1. Pr sooner ili kasnije, naručitelj će se zapitati o suštini postojanja i univerzalnoj pravdi u ovom svijetu i ponovno će postaviti pitanje — zašto plaćam svoje novce? Posljedica je uvijek ista — pitanje je samo kada će se naručitelj umoriti i mahati rukom na oproštaj. A hranilica će opustjeti. To je tužno.
2. Razvoj inženjera — nula.
3. Teškoće u planiranju rada i opterećenja

Opcija 2 - "Igramo se s bubnjem, prodajemo i obuvamo"

Točka 1-Zašto nam treba sustav nadzora, sve ćemo primati putem zahtjeva. Pokrećemo hrpu različitih upita prema rječniku podataka i dinamičkim prikazima, uključujemo različite brojače, sve svodimo u tablice, povremeno analiziramo popise i tablice. Kao rezultat toga imamo lijepe ili ne tako lijepe grafikone, tablice, izvještaje. Glavno je da bude što više, što više.
Točka 2-Generiramo aktivnost - pokrećemo analizu svega toga.
Točka 3-Pripremamo neki dokument, nazivamo ga jednostavno – „kako urediti bazu podataka“.
Točka 4-Naručitelj, videći svu tu ljepotu grafikona i brojeva, ostaje u dječjoj naivnoj uvjerenosti – sad će sve raditi, uskoro. I lako i bezbolno se odriče svojih novčanih sredstava. Menadžment također vjeruje – naši inženjeri rade fenomenalno. Opterećenje je na maksimumu.
Točka 5-Redovito ponavljanje točke 1.
Kolačići i peciva, modrice i kvrgeKolačići i peciva:
1. Život menadžera i inženjera – jednostavan, predvidljiv i ispunjen aktivnošću. Sve zuji, svi su zaposleni.
2. Život naručitelja također nije loš – uvijek je siguran da treba malo pričekati i sve će se srediti. Ne ide, pa što – svijet je nepravedan, u sljedećem životu – bit će sreće.
Modrice i kvrge:
1. Pr sooner ili kasnije, pronaći će se brži pružatelj slične usluge koji će raditi isto, ali malo jeftinije. A ako je rezultat isti, zašto plaćati više. To će opet dovesti do nestanka hrane.
2. To je dosadno. Kao što je dosadna svaka malo smislenija aktivnost.
3. Kao i u prethodnoj verziji - nema nikakvog razvoja. No za inženjere je minus to što, za razliku od prve verzije, ovdje treba stalno generirati BDD. A to oduzima vrijeme. Koje se može korisno potrošiti na sebe. Jer ako se sam ne brineš o sebi, svima je svejedno.

Opcija 3 - Ne treba osmišljavati bicikl, treba ga kupiti i voziti.

Inženjeri drugih kompanija ne jedu pizzu bez razloga, zalivajući je pivom (ah, slavni dani Peterburga 90-ih). Hajdemo koristiti sustave praćenja koji su napravljeni, podešeni i rade, i donose korist (barem svojim tvorcima).
Kolačići i peciva, modrice i kvrgeKolačići i peciva:
1. Ne treba gubiti vrijeme na izmišljanje onoga što je već izmišljeno. Uzmi i koristi.
2. Sustave praćenja ne pišu budale i oni su naravno korisni.
3. Radni sustavi praćenja obično daju korisne filtrirane informacije.
Modrice i kvrge:
1. Inženjer u ovom slučaju nije inženjer, već samo korisnik tuđeg proizvoda. Ili korisnik.
2. Naručitelja treba uvjeriti u potrebu kupnje nečega o čemu se ne razumije, i ne treba, a budžet za godinu je odobren i ne će se mijenjati. Potom treba odvojiti poseban resurs, prilagoditi ga konkretnoj sustavu. To jest, prvo treba platiti, plaćati i opet platiti. A naručitelj je škrt. To je normalno u ovom životu.

Što učiniti - Černyševski? Tvoje pitanje je doista na mjestu. (c)

U ovom konkretnom slučaju i postojećoj situaciji može se postupiti malo drugačije - ajmo napraviti naš vlastiti sustav praćenja.

No nije to sustav naravno, u punom smislu te riječi, to je previše neutemeljena tvrdnja, ali barem si olakšati zadatak i prikupiti više informacija za rješavanje problema s performansama. Da ne budemo u situaciji - "idi tamo, ne znam gdje, pronađi to, ne znam što".

Koje su prednosti i nedostaci ove opcije:

Prednosti:
1. To je zanimljivo. Pa barem zanimljivije nego stalni „smanji datafile, promijeni tabelarni prostor, itd.”
2. Ovo su nove vještine i novi razvoj. Što će na duži put donijeti zaslužene nagrade i komplimente.
Mane:
1. Morat ćete raditi. Raditi puno.
2. Morat ćete redovito objašnjavati smisao i perspektive svih aktivnosti.
3. Nešto ćete morati žrtvovati, jer je jedini dostupni resurs za inženjera — vrijeme — ograničeno Univerzumom.
4. Najgore i najneugodnije — na kraju može ispasti nešto poput 'Nije mišić, nije žaba, već nepoznata zvijer'.

Tko ne riskira, ne pije šampanjac.
Dakle — počinje najzanimljivije.

Opća ideja — shematski

(Ilustracija preuzeta iz članka «Sintez kao jedna od metoda poboljšanja performansi PostgreSQL-a»)

Objašnjenje:

• Na ciljnoj bazi se postavlja standardno proširenje PostgreSQL — “pg_stat_statements”.
• U bazi podataka za praćenje stvaramo set servisnih tablica za pohranu povijesti pg_stat_statements u početnoj fazi i za postavljanje metrike i praćenje u budućnosti.
• Na hostu za praćenje stvaramo set bash skripti, uključujući i one za generiranje incidenata u sustavu za tikete.

Servisne tablice

Za početak shematski pojednostavljena ERD, što smo na kraju dobili:

Kratki opis tablicaendpoint — host, točka povezivanja s instancom
database — parametri baze podataka
pg_stat_history — povijesna tablica za pohranu vremenskih preslika prikaza pg_stat_statements ciljne baze podataka
metric_glossary — rječnik performansnih metrika
metric_config — konfiguracija pojedinačnih metrika
metric — konkretna metrika za praćenje zahtjeva
metric_alert_history — povijest upozorenja performansi
log_query — servisna tablica za pohranu raščlanjenih zapisa iz log fajla PostgreSQL učitanog s AWS-a
baseline — parametri vremenskog razdoblja korištenog kao osnovni
checkpoint — konfiguracija metrika provjere stanja baze podataka
checkpoint_alert_history — povijest upozorenja metrika provjere stanja baze podataka
pg_stat_db_queries — servisna tablica aktivnih zahtjeva
activity_log — servisna tablica dnevnika aktivnosti
trap_oid — servisna tablica konfiguracije trap

Faza 1 — prikupljanje statističkih informacija o performansama i dobivanje izvješća

Za pohranu statističkih informacija služi tablica pg_stat_history
Struktura tablice pg_stat_history

                                          Tablica "public.pg_stat_history"
       Stupac        |            Tip             |                          Modifikatori
---------------------+-----------------------------+-------------------------------------------
 id                  | cijeli broj                 | nije null, zadana vrijednost nextval('pg_stat_history_id_seq'::regclass)
 snapshot_timestamp  | vremenska oznaka bez vremenske zone |
 database_id         | cijeli broj                 |
 dbid                | oid                         |
 userid              | oid                         |
 queryid             | veliki broj                 |
 query               | tekst                       |
 calls               | veliki broj                 |
 total_time          | dvostruka preciznost        |
 min_time            | dvostruka preciznost        |
 max_time            | dvostruka preciznost        |
 mean_time           | dvostruka preciznost        |
 stddev_time         | dvostruka preciznost        |
 rows                | veliki broj                 |
 shared_blks_hit     | veliki broj                 |
 shared_blks_read    | veliki broj                 |
 shared_blks_dirtied | veliki broj                 |
 shared_blks_written | veliki broj                 |
 local_blks_hit      | veliki broj                 |
 local_blks_read     | veliki broj                 |
 local_blks_dirtied  | veliki broj                 |
 local_blks_written  | veliki broj                 |
 temp_blks_read      | veliki broj                 |
 temp_blks_written   | veliki broj                 |
 blk_read_time       | dvostruka preciznost        |
 blk_write_time      | dvostruka preciznost        |
 baseline_id         | cijeli broj                 |
Indeksi:
    "pg_stat_history_pkey" PRIMARNI KLJUČ, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Strane ključne ograničenja:
    "database_id_fk" STRANI KLJUČ (database_id) REFERENCIRA database(id) PRI BRISANJU KASKADNO

Kao što se može vidjeti, tablica predstavlja samo kumulativne podatke prikaza pg_stat_statements u ciljanom databazom.

Korištenje ove tablice je vrlo jednostavno

pg_stat_history predstavljat će akumuliranu statistiku izvršenja upita za svaki sat. Na početku svakog sata, nakon popunjavanja tablice, statistika pg_stat_statements se resetira pomoću pg_stat_statements_reset().
Napomena: statistika se prikuplja za upite s vremenom izvršenja većim od 1 sekunde.
Popunjavanje tablice 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 'NOVI SNIMAK SE KREIRA';
		
		--Spojite se s ciljanom bazom podataka	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % i dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Kreiranje snimka pg_stat_statements za bazu %',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 'Kreiranje snimka pg_stat_statements za upite s min_time većim od 1000ms';
	
        FOR pg_stat_snapshot IN
          --Svi upiti s max_time većim od 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;

Kao rezultat, nakon određenog vremenskog perioda u tablici pg_stat_history imat ćemo set snimaka sadržaja tablice pg_stat_statements ciljane baze podataka.

Sam izveštaj

Koristeći jednostavne upite, možemo dobiti prilično korisne i zanimljive izveštaje.

Agregirani podaci za određeni vremenski period

Upit

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 ;

DB Vrijeme

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

I/O Vrijeme

to_char(interval '1 millisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

TOP10 SQL po ukupnom vremenu

Upit

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 PO UKUPNOM VREMENU IZVRŠENJA
|   #|    queryid|      calls|    % poziva|                ukupno vreme (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

TOP10 SQL po ukupnom I/O vremenu

Upit

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 PO UKUPNOM I/O VREMENU
|   #|    queryid|      pozivi|    % poziva|                   I/O vrijeme (ms)|% vrijeme db I/O
+----+-----------+-----------+-----------+--------------------------------+-------------
|   1| 4152624390|          2|     .00001|00:08:31.616(    511616.592 ms.)|        31.06
|   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

TOP10 SQL po maksimalnom vremenu izvršenja

Upit

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 PO MAKSIMALNOM VREMENU IZVRŠENJA
|   #|          snapshot| snapshotID|    queryid|                           max_time (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 po ISKORISTI dijeljenih bafera za čitanje/zapisivanje

Upit

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 PREMA DELJENJU/BROJENJU U MEMORIJI
|   #|          snimka| snapshotID|    queryid|   dijeljeni blokovi pročitani|  dijeljeni blokovi napisani
+----+------------------+-----------+-----------+---------------------+---------------------
|   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
--------------------------------------------------------------------------------------------

Histogram raspodjele zahtjeva prema maksimalnom vremenu izvršavanja

Upiti

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

|-----------------------------------------------------------------------------------------------
| HISTOGRAM MAKSIMALNOG VREMENA
| UKUPNO POZIVA : 33851920
| MIN VRIJEME  : 00:00:01.063
| MAX VRIJEME  : 00:02:01.869
---------------------------------------------------------------------------------
|                      minimalno trajanje|                      maksimalno trajanje|     pozivi
+----------------------------------+----------------------------------+----------
| 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

TOP10 Snimki prema broju upita u sekundi

Upiti

--pg_qps.sql
--Izračunaj Upiti Po Sekundi 
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 'GREŠKA - Nije pronađena pg_stat_history za 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  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

|-----------------------------------------------------------------------------------------------
| TOP10 Snimci raspoređeni prema broju upita u sekundi
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snimak| snimakID|      pozivi|                      ukupno vrijeme DB|        QPS|                          I/O vrijeme| I/O vrijeme %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Satna povijest izvršenja s upitima u sekundi i vremenom I/O

Upit

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

|-----------------------------------------------------------------------------------------------
| POVIJEST IZVRŠENJA PO SATU S QueryPerSeconds i I/O Vremenom
-----------------------------------------------------------------------------------------------------------------------------------------------
| POVIJEST UPITA PO SEKUNDI
|    #|          snimka| IDsnimke|      pozivi|                      ukupno dbvrijeme|        QPS|                          I/O vrijeme| I/O vrijeme %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Tekst svih SQL-upita

Upit

ODABERITE 
  queryid , 
  query 
IZ 
  pg_stat_history
GDJE 
  queryid NIJE NULL I 
  database_id = DATABASE_ID  I
  snapshot_timestamp IZMEĐU BEGIN_TIMEPOINT I END_TIMEPOINT
GRUPIŠI queryid , query

Zaključak

Kao što se može vidjeti, prilično jednostavnim sredstvima, može se dobiti dosta korisnih informacija o opterećenju i stanju baze.

Napomena:Ako u upitima fiksiramo queryid, dobijamo povijest za pojedinačni upit (s ciljem uštede prostora, izvještaji po pojedinačnom upitu su izostavljeni).

Dakle, statistički podaci o performansama upita — postoje i prikupljaju se.
Prva faza „prikupljanje statističkih podataka“ — završena.

Može se preći na drugu fazu - „postavljanje metrika performansi“.


No, to je već sasvim druga priča.

Nastavak slijedi…

Izvor: habr.com