🥇Overvåkning av ytelsen til forespørslene i PostgreSQL. Del 1 — rapportering

Ingeniør — oversatt fra latin — inspirert.
Ingeniør kan gjøre alt. (c) R.Dizel.
Epigrafene.

Eller historien om hvorfor databaseadministratoren bør huske sin programmeringsfortid.

Forord

Alle navn er endret. Sammenfall er tilfeldig. Materialet representerer utelukkende forfatterens personlige mening.

Ansvarsfraskrivelse: det vil ikke være detaljert og presis beskrivelse av brukte tabeller og skript i den planlagte artikkelserien. Materialene kan ikke brukes «SOM DET ER».
Først og fremst på grunn av det store volumet av materiale,
for det andre på grunn av tilpasningen til produksjonsdatabasen til den virkelige kunden.
Derfor vil artiklene bare inneholde ideer og beskrivelser i mest generelle termer.
Kanskje systemet i fremtiden vil nå nivået for publisering på GitHub, eller kanskje ikke. Tiden vil vise.

Begynnelsen på historien- «Husker du hvordan alt begynte».
Hva som kom ut av det, i de mest generelle trekkene- «Syntese som en av metodene for å forbedre ytelsen til PostgreSQL»

Hvorfor trenger jeg alt dette?

Vel, først og fremst for å ikke glemme det selv, når jeg ser tilbake på mine glade dager i pensjon.
For det andre for å systematisere det som er skrevet. For noen ganger begynner jeg selv å bli forvirret og glemme enkelte deler.

Og viktigst av alt — kanskje noen kan dra nytte av det og unngå å oppfinne hjulet på nytt og samle rake. Med andre ord, forbedre sin karma (ikke Habr-karm). For det mest verdifulle i denne verden er ideene. Det viktigste er å finne ideen. Å realisere ideen i virkelighet er allerede et rent teknisk spørsmål.

Så, la oss begynne, gradvis...

Problemstillingen.

Det er:

PostgreSQL-database (10.5), blandet type last (OLTP+DSS), middels-lav belastning, plassert i AWS-skyen.
Databaseovervåking er fraværende, overvåking av infrastrukturen presenteres i form av standardverktøy fra AWS i minimal konfigurasjon.

Kreves:

Å overvåke ytelse og tilstand på databasen, finne og ha utgangspunkt for optimalisering av tunge spørringer til databasen.

Kort innledning eller analyse av løsningsalternativer

For å begynne vil vi prøve å undersøke løsningsalternativene fra et sammenlignende analyseperspektiv av fordeler og ulemper for ingeniøren, mens nytte og tap for ledelsen får håndtere de som er pålagt å gjøre det ifølge stillingsbeskrivelsen.

Alternativ 1- «Working on demand»

Vi lar alt stå som det er. Hvis kunden ikke er fornøyd med noe i funksjonaliteten, ytelsen til databasen eller applikasjonen, vil han informere DBA-ingeniørene via e-post eller ved å opprette en hendelse i billettsystemet.
Ingeniøren, som får varslingen, vil undersøke problemet, foreslå en løsning eller skyve problemet til side, i håp om at det ordner seg av seg selv og at det snart vil bli glemt.
Pepperkaker og boller, blåmerker og hjernerystelserPepperkaker og boller:
1. Det er ikke nødvendig å gjøre noe overflødig.
2. Det er alltid en mulighet til å slippe unna og ta det med ro.
3. Masse tid som kan brukes etter eget skjønn.
Blåmerker og hjernerystelser:
1. Før eller senere vil kunden tenke over livets mening og universets rettferdighet i denne verden og igjen stille seg spørsmålet – hva betaler jeg dem pengene mine for? Konsekvensene er alltid de samme – spørsmålet er bare når kunden blir lei og vinker farvel. Og smøringen blir tom. Det er trist.
2. Ingeniørens utvikling – null.
3. Vanskeligheter med planlegging av arbeid og belastning.

Alternativ 2 - «Vi danser med tamburiner, lurer dem og får dem til å kjøpe»

Punkt 1- Hvorfor trenger vi et overvåkingssystem, vi vil få alt gjennom forespørslene. Vi starter en mengde forespørsel til datakatalogen og dynamiske fremstillinger, aktiverer forskjellige tellere, samler alt i tabeller, analyserer lister og tabeller med jevne mellomrom. Resultatet er pen eller ikke så pen grafikk, tabeller, rapporter. Det viktigste er at det blir mer, mer.
Punkt 2- Generer aktivitet – analyserer alt dette.
Punkt 3- Forbereder et dokument og kaller dette dokumentet ganske enkelt – «hvordan vi organiserer databasen».
Punkt 4- Kunden, som ser all denne prakten av grafer og tall, er i en barnslig naiv tro – nå vil alt fungere, snart. Og han gir lett og smertefritt fra seg sine penger. Ledelsen er også sikker – ingeniørene våre jobber på spreng. Belastningen er på maks.
Punkt 5- Gjenta Punkt 1 regelmessig.
Pepperkaker og boller, blåmerker og hjernerystelserPepperkaker og boller:
1. Livet til ledere og ingeniører er enkelt, forutsigbart og fullt av aktivitet. Alt surrer, alle er opptatt.
2. Livet til kunden er også greit - han er alltid sikker på at han bare må vente litt til, og alt blir bra. Det blir ikke bra, vel, hva skal jeg si – verden er urettferdig, kanskje i neste liv vil det gå bedre.
Blåmerker og hjernerystelser:
1. Snart eller senere vil det dukke opp en mer effektiv leverandør av en lignende tjeneste, som gjør det samme, men litt billigere. Og hvis resultatet er det samme, hvorfor betale mer? Dette vil igjen føre til at inntektene forsvinner.
2. Det er kjedelig. Akkurat som enhver meningsløs aktivitet er kjedelig.
3. Som i det forrige alternativet — ingen utvikling. Men for ingeniøren er ulempe at man her må generere en database kontinuerlig. Og det tar tid. Tid som kan brukes bedre på en selv, for ingen vil ta vare på deg, det er det ingen som bryr seg om.

Alternativ 3 - Du trenger ikke å finne opp hjulet på nytt, kjøp det og bruk det.

Ingeniører i andre selskaper spiser ikke pizza med øl uten grunn (ah, de gode tidene i St. Petersburg på 90-tallet). La oss bruke overvåkingssystemer som er laget, gjort klar og fungerer, og som tross alt bringer nytte (i det minste til skaperne).
Pepperkaker og boller, blåmerker og hjernerystelserPepperkaker og boller:
1. Du trenger ikke å bruke tid på å finne på noe som allerede er oppfunnet. Ta det og bruk det.
2. Overvåkingssystemer er ikke laget av idioter, og de er selvfølgelig nyttige.
3. Arbeidende overvåkingssystemer gir vanligvis nyttig filtrert informasjon.
Blåmerker og hjernerystelser:
1. Ingeniøren i dette tilfellet er ikke en ingeniør, men bare en bruker av et annet produkt. Eller en bruker.
2. Kunden må overbevises om nødvendigheten av å kjøpe noe han egentlig ikke vil sette seg inn i, og i tillegg er budsjettet for året fastsatt og vil ikke endres. Deretter må man allokere separate ressurser, konfigurere for et bestemt system. Det vil si at man først må betale, betale og betale igjen. Og kunden er hjemmesnekret. Det er normalen i dette livet.

Hva skal man gjøre - Tsjernysevskij? Ditt spørsmål er veldig relevant. (c)

I dette spesifikke tilfellet og den oppståtte situasjonen kan man gjøre det litt annerledes — la oss lage vårt eget overvåkingssystem.

Vel, ikke et system i ordets forstand, det ville være en overdrivelse og selvgodhet, men i det minste finne en måte å lette oppgaven på og samle mer informasjon for å løse ytelseshendelser. Slik at man ikke havner i situasjonen — «gå dit jeg ikke vet hvor, finn det jeg ikke vet hva».

Hva er fordelene og ulempene med dette alternativet:

Fordeler:
1. Det er interessant. I hvert fall mer interessant enn konstante «shrink datafile, alter tablespace, etc.»
2. Dette er nye ferdigheter og ny utvikling. Noe som på sikt vil gi velfortjente belønninger og fordeler.
Ulemper:
1. Du må jobbe. Jobbe mye.
2. Du må regelmessig forklare meningen og perspektivene av all aktiviteten.
3. Du må ofre noe, for den eneste tilgjengelige ressursen for ingeniøren – tid – er begrenset av universet.
4. Det mest skremmende og ubehagelige – kan resultatet bli noe som «Ikke mus, ikke frosk, men en ukjent skapning».

Den som ikke tar risiko, får ikke champagne.
Så — begynner det mest interessante.

Den overordnede ideen — skjematisk

(Illustrasjonen er hentet fra artikkelen «Syntese som en av metodene for å forbedre ytelsen til PostgreSQL»)

Forklaring:

I målbasen installeres standardutvidelsen PostgreSQL — “pg_stat_statements”.
I overvåkningsdatabasen oppretter vi et sett med tjenestetabeller for å lagre historikk av pg_stat_statements i starten og for å konfigurere målinger og overvåkning videre.
På overvåkningsverten lager vi et sett med bash-skript, inkludert for å generere hendelser i billettsystemet.

Tjenestetabeller

For å begynne, her er et skjematisk forenklet ERD, hva som ble resultatet:

Kort beskrivelse av tabelleneendpoint – vert, tilkoblingspunkt til instansen
database – databaseparametere
pg_stat_history – historisk tabell for lagring av tidsbilder av pg_stat_statements i målbasen
metric_glossary – ordbok for ytelsesmål
metric_config – konfigurasjon av individuelle målinger
metric – spesifik måling for forespørselen som overvåkes
metric_alert_history – historikk over ytelsesvarsler
log_query – tjenestetabell for lagring av analyserte poster fra loggfilen til PostgreSQL lastet opp fra AWS
baseline – parametere for tidsperioder som brukes som basis
checkpoint – konfigurasjon av målinger for verifisering av databaseforhold
checkpoint_alert_history – historikk over varsel for målinger av databaseforhold
pg_stat_db_queries – tjenestetabell for aktive forespørseler
activity_log – tjenestetabell for aktivitetslogg
trap_oid – tjenestetabell for konfigurasjon av fangst

Trinn 1 — samle statistisk informasjon om ytelse og få rapporter

Tabellen brukes til lagring av statistisk informasjon pg_stat_history
Strukturen til tabellen pg_stat_history

                                          Tabellen "public.pg_stat_history"
       Kolonne         |            Type             |                          Modifiers
---------------------+-----------------------------+-------------------------------------------
 id                  | integer                     | not null default nextval('pg_stat_history_id_seq'::regclass)
 snapshot_timestamp  | timestamp without time zone |
 database_id         | integer                     |
 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            |
 baseline_id         | integer                     |
Indekser:
    "pg_stat_history_pkey" PRIMARY KEY, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Utenlandske nøkkelbegrensninger:
    "database_id_fk" FOREIGN KEY (database_id) REFERENCES database(id) ON DELETE CASCADE

Som vi kan se, er tabellen bare kumulative data fra visningen pg_stat_statements i databasemål.

Bruken av denne tabellen er veldig enkel

pg_stat_history og vil representere oppsamlet statistikk for spørringer som er utført hver time. I begynnelsen av hver time, etter at tabellen er fylt, blir statistikken pg_stat_statements tilbakestilt ved hjelp av pg_stat_statements_reset().
Merk: statistikken samles inn for forespørselene med en kjøretid på mer enn 1 sekund.
Fylling av tabellen 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 'NY SHAPSHOT BLIR OPPRETTET';
		
		--Koble til måldatabasen	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'vert % og dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Lager snapshot av pg_stat_statements for databasen %',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 'Lager snapshot av pg_stat_statements for spørringer med min_time mer enn 1000ms';
	
        FOR pg_stat_snapshot IN
          --Alle spørringer med max_time større enn 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;

Som resultat, etter en viss tidsperiode i tabellen pg_stat_history vil vi ha et sett med bilder av innholdet i tabellen pg_stat_statements til den aktuelle databasen.

Rapportering

Ved å bruke enkle spørringer kan man få nyttige og interessante rapporter.

Aggregert data for en angitt tidsperiode

Spørring

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) AS 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 Tid

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

I/O Tid

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

Spørring

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 ETTER TOTAL UTFØRELSESTID
|   #|    queryid|      calls|    calls %|                total_time (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 etter total I/O tid

Spørring

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 ET TOTAL I/O-TID
|   #|    queryid|      kall|    kall %|                   I/O-tid (ms)|db I/O-tid %
+----+-----------+-----------+-----------+--------------------------------+-------------
|   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 etter maks tidsbruk for utførelse

Spørring

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 ET MAKS UTFØRELSSTID
|   #|          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 etter DELT buffer lesing/skriving

Spørring

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

--------------------------------------------------------------------------------------------
| TOP10 SQL BY SHARED BUFFER READ/WRITE
|   #|          snapshot| snapshotID|    queryid|   shared blocks read|  shared blocks write
+----+------------------+-----------+-----------+---------------------+---------------------
|   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 for query distribution by maximum execution time

Forespørsel

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
| TOTAL CALLS : 33851920
| MIN TIME  : 00:00:01.063
| MAX TIME  : 00:02:01.869
---------------------------------------------------------------------------------
|                      min duration|                      max duration|     calls
+----------------------------------+----------------------------------+----------
| 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 snapshots by queries per second

Forespørsel

--pg_qps.sql
--Beregn forespørsel per sekund 
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 'FEIL - Fant ikke 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  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 Snapshots rangert av QueryPerSeconds tal
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O time| I/O time %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Timeleggjing av utføringshistorikk med QueryPerSeconds og I/O tid

Spørring

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

|-----------------------------------------------------------------------------------------------
| TIMEKORT FOR UTFØRING MED QueryPerSeconds OG I/O TID
-----------------------------------------------------------------------------------------------------------------------------------------------
| HISTORIKK FOR SPØRSMÅL PER SEKUND
|    #|          snapshot| snapshotID|      anrop|                      total dbtime|        QPS|                          I/O tid| I/O tid %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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 av alle SQL-valg

Spørring

VELG 
  queryid , 
  query 
FRÅ 
  pg_stat_history
KOR 
  queryid ER IKKE NULL OG 
  database_id = DATABASE_ID  OG
  snapshot_timestamp MELLOM BEGIN_TIMEPOINT OG END_TIMEPOINT
GRUPPER ETTER queryid , query

Resultat

Som tydelig er det mulig å få en betydelig mengde nyttig informasjon om belastning og tilstand på databasen ved hjelp av ganske enkle midler.

Merknad:Hvis vi fikserer queryid i forespørslene, vil vi få historikk for den enkelte forespørselen (for å spare plass er rapportene for den enkelte forespørselen utelatt).

Så, statistiske data om forespørselers ytelse — er samlet og tilgjengelig.
Første fase «innsamling av statistiske data» — er fullført.

Vi kan gå videre til den andre fasen - «justering av ytelsesmetrikker».

Men det er en helt annen historie.