Praćenje performansi PostgreSQL upita. 1. dio - izvješćivanje

Inženjer – u prijevodu s latinskog – nadahnut.
Inženjer može sve. (c) R. Diesel.
Epigrafi.

Ili priča o tome zašto se administrator baze podataka mora prisjećati svoje programerske prošlosti.

predgovor

Sva imena su promijenjena. Podudaranja su nasumična. Materijal je isključivo osobno mišljenje autora.

Odricanje od jamstva: u planiranoj seriji članaka neće biti detaljnog i točnog opisa korištenih tablica i skripti. Materijali se ne mogu odmah koristiti "KAKVI JESU".
Prvo, zbog velike količine materijala,
drugo, zbog oštrine s proizvodnom bazom stvarnog kupca.
Stoga će u člancima biti dane samo ideje i opisi u najopćenitijem obliku.
Možda u budućnosti sustav naraste do razine objave na GitHubu, a možda i ne. Vrijeme će pokazati.

Početak priče-Sjećate li se kako je sve počelo".
Što se dogodilo kao rezultat, u najopćenitijim crtama - "Sinteza kao jedna od metoda poboljšanja performansi PostgreSQL-a»

Zašto mi sve ovo treba?

Pa, prvo, kako ne biste zaboravili sebe, prisjećajući se slavnih dana u mirovini.
Drugo, sistematizirati napisano. I sama se ponekad počnem zbunjivati ​​i zaboravljam odvojene dijelove.

Pa, i što je najvažnije - iznenada nekome može dobro doći i pomoći da ne izmišljaju kotač i ne skupljaju grablje. Drugim riječima, poboljšajte svoju karmu (ne Khabrovsky). Jer najvrednije na ovom svijetu su ideje. Glavna stvar je pronaći ideju. A prevođenje ideje u stvarnost već je čisto tehničko pitanje.

Pa krenimo polako...

Formulacija problema.

Dostupno:

PostgreSQL(10.5), miješano opterećenje (OLTP+DSS), srednje do malo opterećenje, smješteno u AWS oblaku.
Nema nadzora baze podataka, nadzor infrastrukture predstavljen je kao standardni AWS alat u minimalnoj konfiguraciji.

Potrebno je:

Pratite performanse i status baze podataka, pronađite i imajte početne informacije za optimizaciju teških upita baze podataka.

Kratak uvod ili analiza rješenja

Za početak, pokušajmo analizirati mogućnosti rješenja problema sa stajališta komparativne analize koristi i nevolja za inženjera, a koristima i gubicima neka se bave oni koji bi trebali biti na popisu osoblja. upravljanja.

Opcija 1 - "Rad na zahtjev"

Ostavljamo sve kako jest. Ako korisnik nije zadovoljan nečim u ispravnosti, performansama baze podataka ili aplikacije, obavijestit će DBA inženjere e-mailom ili kreiranjem incidenta u box box-u.
Inženjer će, primivši obavijest, razumjeti problem, ponuditi rješenje ili problem odložiti u nadu da će se sve riješiti samo od sebe, a ionako će se sve brzo zaboraviti.
Medenjaci i krafne, modrice i kvrgeMedenjaci i krafne:
1. Ništa dodatno
2. Uvijek postoji mogućnost izaći i isprljati se.
3. Puno vremena koje možete provesti sami.
Modrice i izbočine:
1. Prije ili kasnije, kupac će razmisliti o biti i univerzalnoj pravdi na ovom svijetu i još jednom si postaviti pitanje - zašto im plaćam svoj novac? Posljedica je uvijek ista - samo je pitanje kada kupcu postane dosadno i mahne mu rukom na pozdrav. A hranilica prazna. Tužno je.
2. Razvoj inženjera je nula.
3. Poteškoće u rasporedu rada i utovara

Opcija 2 - "Ples s tamburama, obuj i obuj cipele"

Stavak 1-Zašto nam treba sustav nadzora, mi ćemo primiti sve zahtjeve. Pokrećemo hrpu svakakvih upita prema rječniku podataka i dinamičkim prikazima, uključujemo sve vrste brojača, dovodimo sve u tablice, povremeno analiziramo popise i tablice, takoreći. Kao rezultat toga, imamo lijepe ili ne baš grafikone, tablice, izvješća. Glavna stvar - to bi bilo više, više.
Stavak 2-Generirajte aktivnost-pokrenite analizu svega ovoga.
Stavak 3-Mi pripremamo određeni dokument, zovemo taj dokument, jednostavno - "kako ćemo opremiti bazu podataka".
Stavak 4- Kupac, gledajući svu tu veličanstvenost grafikona i slika, u djetinjastom je naivnom povjerenju - sada će sve raditi za nas, uskoro. I, lako i bezbolno rastati se od svojih financijskih sredstava. Uprava je također sigurna da naši inženjeri marljivo rade. Maksimalno opterećenje.
Stavak 5- Redovito ponavljajte korak 1.
Medenjaci i krafne, modrice i kvrgeMedenjaci i krafne:
1. Život menadžera i inženjera je jednostavan, predvidljiv i ispunjen aktivnostima. Sve vrvi, svi su zaposleni.
2. Život kupca također nije loš - on je uvijek siguran da se trebate malo strpiti i sve će uspjeti. Ne ide na bolje, dobro, dobro - ovaj svijet je nepravedan, u sljedećem životu - imat ćeš sreće.
Modrice i izbočine:
1. Prije ili kasnije naći će se pametniji pružatelj slične usluge koji će napraviti istu stvar, ali malo jeftinije. A ako je rezultat isti, zašto platiti više. Što će opet dovesti do nestanka hranilice.
2. Dosadno je. Kako dosadna svaka iole smislena aktivnost.
3. Kao u prethodnoj verziji - nema razvoja. Ali za inženjera, minus je što, za razliku od prve opcije, ovdje morate stalno generirati IDB. A za to treba vremena. Koji se može potrošiti u korist voljene osobe. Jer ne možete se brinuti sami o sebi, svi brinu o vama.

Opcija 3-Ne morate izmišljati bicikl, morate ga kupiti i voziti ga.

Inženjeri iz drugih tvrtki svjesno jedu pizzu s pivom (oh, slavna vremena St. Petersburga 90-ih). Koristimo sustave za nadzor koji su napravljeni, otklonjeni i rade, a općenito govoreći, donose dobrobiti (dobro, barem svojim kreatorima).
Medenjaci i krafne, modrice i kvrgeMedenjaci i krafne:
1. Nema potrebe gubiti vrijeme na izmišljanje onoga što je već izmišljeno. Uzmi i koristi.
2. Sustave nadzora ne pišu budale, i naravno da su korisni.
3. Radni nadzorni sustavi obično daju korisne filtrirane informacije.
Modrice i izbočine:
1. Inženjer u ovom slučaju nije inženjer, već samo korisnik tuđeg proizvoda.Ili korisnik.
2. Kupac mora biti uvjeren u potrebu kupnje nečega što općenito ne želi razumjeti, a ne bi trebao, i općenito je proračun za godinu odobren i neće se mijenjati. Zatim morate dodijeliti zasebni resurs, konfigurirati ga za određeni sustav. Oni. Prvo morate platiti, platiti i još jednom platiti. A kupac je škrt. To je norma ovog života.

Što učiniti, Černiševski? Vaše pitanje je vrlo relevantno. (S)

U ovom konkretnom slučaju i trenutnoj situaciji, možete postupiti malo drugačije - napravimo vlastiti sustav nadzora.

Dobro, nije sustav, naravno, u punom smislu riječi, ovo je preglasno i drsko, ali barem si nekako olakšajte i prikupite više informacija za rješavanje incidenata u izvedbi. Kako se ne biste našli u situaciji - "idi tamo, ne znam gdje, nađi ono, ne znam što."

Koje su prednosti i mane ove opcije:

Pros:
1. Zanimljivo je. Pa, barem zanimljiviji od konstante "smanji podatkovnu datoteku, promijeni tablični prostor, itd."
2. To su nove vještine i novi razvoj. Koji će u budućnosti, prije ili kasnije, dati zaslužene medenjake i krafne.
Cons:
1. Moramo raditi. Radi puno.
2. Morat ćete redovito objašnjavati značenje i perspektive svih aktivnosti.
3. Nešto će se morati žrtvovati, jer je jedini resurs koji je inženjeru na raspolaganju - vrijeme - ograničen Svemirom.
4. Najgore i najneugodnije - kao rezultat, može ispasti smeće poput "Ne miš, ne žaba, već nepoznata mala životinja".

Tko nešto ne riskira ne pije šampanjac.
Dakle, zabava počinje.

Opća ideja - shematski

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

Objašnjenje:

• Ciljna baza podataka instalirana je sa standardnim PostgreSQL nastavkom “pg_stat_statements”.
• U bazi podataka za praćenje stvaramo skup servisnih tablica za pohranu povijesti pg_stat_statements u početnoj fazi i za konfiguraciju metrike i praćenja u budućnosti
• Na hostu za praćenje stvaramo skup bash skripti, uključujući one za generiranje incidenata u sustavu ulaznica.

Servisni stolovi

Za početak, shematski pojednostavljeni ERD, što se dogodilo na kraju:

Kratak opis tablicakrajnja - host, točka povezivanja s instancom
baza podataka - opcije baze podataka
pg_stat_povijest - povijesna tablica za pohranjivanje privremenih snimaka pogleda pg_stat_statements ciljane baze podataka
metrički_glosar - Rječnik metrike učinka
metric_config - konfiguracija pojedinačnih metrika
metrički - specifična metrika za zahtjev koji se prati
metrička_povijest_upozorenja - povijest upozorenja o performansama
log_upit - servisna tablica za pohranu parsiranih zapisa iz PostgreSQL log datoteke preuzete s AWS-a
osnovica - parametri vremenskog razdoblja koji se koriste kao baza
kontrolna točka - konfiguracija metrike za provjeru statusa baze podataka
povijest_upozorenja_kontrolne_točke - povijest upozorenja o metrici provjere statusa baze podataka
pg_stat_db_upiti — servisna tablica aktivnih zahtjeva
dnevnik aktivnosti — servisna tablica dnevnika aktivnosti
zamka_oid - tablica usluge konfiguracije zamki

Faza 1 - prikupljanje statistike izvedbe i dobivanje izvješća

Tablica se koristi za pohranjivanje statističkih podataka. pg_stat_povijest
struktura tablice pg_stat_history

                                          Tablica "public.pg_stat_history" Stupac | vrsta | Modifikatori--------------------+-------------------- --+---- -------------------------------- id | cijeli broj | nije null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | vremenska oznaka bez vremenske zone | baza_id | cijeli broj | dbid | oid | ID korisnika | oid | ID upita | bigint | upit | tekst | poziva | bigint | ukupno_vrijeme | dvostruka preciznost | min_vrijeme | dvostruka preciznost | maksimalno_vrijeme | dvostruka preciznost | srednje_vrijeme | dvostruka preciznost | stddev_vrijeme | dvostruka preciznost | redovi | bigint | shared_blks_hit | bigint | shared_blks_read | bigint | shared_blks_dirtied | bigint | shared_blks_written | bigint | lokalni_blks_hit | bigint | lokalni_blks_čitanje | bigint | lokalni_blks_prljavi | bigint | lokalni_blks_napisano | bigint | temp_blks_read | bigint | temp_blks_napisano | bigint | blk_read_time | dvostruka preciznost | blk_vrijeme_pisanja | 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) Ograničenja stranog ključa: "database_id_fk" FOREIGN KEY (database_ id) REFERENCE baza podataka(id ) ON DELETE CASCADE

Kao što vidite, tablica je samo kumulativni prikaz podataka pg_stat_izjave u ciljnoj bazi podataka.

Korištenje ove tablice je vrlo jednostavno.

pg_stat_povijest će predstavljati akumuliranu statistiku izvršenja upita za svaki sat. Na početku svakog sata, nakon popunjavanja tablice, statistika pg_stat_izjave resetirati s pg_stat_statements_reset().
Napomena: statistika se prikuplja za zahtjeve koji traju duže 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 '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;

Kao rezultat toga, nakon određenog vremenskog razdoblja u tablici pg_stat_povijest imat ćemo skup snimaka sadržaja tablice pg_stat_izjave ciljna baza podataka.

Zapravo izvještavanje

Koristeći jednostavne upite, možete dobiti vrlo korisna i zanimljiva izvješća.

Skupni podaci za određeno vremensko razdoblje

Zahtjev

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 ;

Vrijeme D.B

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

U/I vrijeme

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

TOP10 SQL prema total_time

Zahtjev

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 PREMA UKUPNOM VREMENU IZVRŠAVANJA | #| ID upita| poziva| poziva %| ukupno_vrijeme (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 prema ukupnom I/O vremenu

Zahtjev

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 PREMA UKUPNOM I/O VREMENU | #| ID upita| poziva| poziva %| I/O vrijeme (ms)|db I/O vrijeme % +----+----------+-----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06. lipnja | 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 prema maksimalnom vremenu izvršenja

Zahtjev

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 PREMA MAKSIMALNOM VREMENU IZVRŠENJA | #| snimka| ID snimke| ID upita| maksimalno_vrijeme (ms) +----+-----------------+----------+--------- --+------------------------------------------ | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2 (04.04.2019 ms.) | 17| 00 4153:821760255| 00| 01| 41.570:101570.841:3( 04.04.2019 ms.) | 16| 00 4146:821760255| 00| 01| 41.570:101570.841:4( 04.04.2019 ms.) | 16| 00 4144:4152624390| 00| 01| 36.964:96964.607:5( 04.04.2019 ms.) | 17| 00 4151:4152624390| 00| 01| 36.964:96964.607:6( 05.04.2019 ms.) | 10| 00 4188:1484454471 | 00| 01| 33.452:93452.150:7( 04.04.2019 ms.) | 17| 00 4150:2460318461| 00| 01| 33.113:93113.835:8( 04.04.2019 ms.) | 15| 00 4140:1484454471| 00| 00| 11.892:11892.302:9 (04.04.2019 ms.) | 16| 00 4145:1484454471| 00| 00| 11.892:11892.302:10 (04.04.2019 ms.) | 17| 00 4152:1484454471| 00| 00| 11.892:11892.302:XNUMX (XNUMX ms.)

TOP10 SQL by SHARED čitanje/pisanje međuspremnika

Zahtjev

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 PO DIJELJENOM MEĐUSPREMNIKU ČITANJE/PISANJE | #| snimka| ID snimke| ID upita| dijeljeni blokovi čitanje| dijeljeni blokovi pisati +----+-----------------+----------+---------- -+--------------------+-------------------- | 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 ------------------------------------------------- -------------------------------------------------

Histogram distribucije upita prema maksimalnom vremenu izvršenja

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

|------------------------------------------------ ---------------------------------------------- | MAX_TIME HISTOGRAM | UKUPNO POZIVA : 33851920 | MIN VRIJEME : 00:00:01.063 | MAKSIMALNO VRIJEME: 00:02:01.869 ---------------------------------- -------- ---------------------------- | min. trajanje| maksimalno trajanje| poziva +----------------------------------+------------- ---------------------+---------- | 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 snimaka po upitu po sekundi

Upiti

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

|------------------------------------------------ ---------------------------------------------- | TOP10 Snimki poredanih prema broju QueryPerSeconds -------------------------------------- ------ -------------------------------------------- ------ ---------------------------------------------- | #| snimka| ID snimke| poziva| ukupno dbtime| 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:609.332( 00 ms.)| 05| 18.847:318847.407:77.507( 10 ms.)| 04.04.2019 | 18| 01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX

Povijest izvršenja po satu s QueryPerSeconds i I/O vremenom

Zahtjev

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

Tekst svih SQL odabira

Zahtjev

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

Ukupan

Kao što vidite, prilično jednostavnim sredstvima možete dobiti mnogo korisnih informacija o radnom opterećenju i stanju baze podataka.

Bilješka:Ako popravite queryid u upitima, tada ćemo dobiti povijest za poseban zahtjev (radi uštede prostora, izvješća za zasebni zahtjev su izostavljena).

Dakle, statistički podaci o izvedbi upita dostupni su i prikupljeni.
Prva faza "prikupljanje statističkih podataka" je završena.

Možete prijeći na drugu fazu - "konfiguriranje metrike performansi".


Ali to je druga priča.

Da bi se nastavio ...

Izvor: www.habr.com