🥇Övervakning av fråga prestanda i PostgreSQL. Del 1 — rapportering

Ingenjör – översatt från latin betyder inspirerad.
Ingenjör kan göra allt. (c) R.Diesel.
Epigraf.

Eller historien om varför en databasadministratör bör minnas sin programmeringsbakgrund.

Förord

Alla namn har ändrats. Eventuella likheter är slumpmässiga. Materialet representerar enbart författarens personliga åsikt.

Ansvarsfriskrivning: i det planerade artikelcykeln kommer det inte att finnas någon detaljerad eller exakt beskrivning av de använda tabellerna och skripten. Materialet kan inte användas direkt ”AS IS”.
För det första på grund av den stora mängden material,
för det andra på grund av anpassningen till produktionsdatabasen för den verkliga klienten.
Därför kommer artiklarna att ge endast idéer och beskrivningar i mycket allmänna termer.
Kanske kommer systemet i framtiden att växa till en nivå där det publiceras på GitHub, och kanske inte. Tiden får utvisa.

Början av historien- “Kommer du ihåg hur allt började».
Vad som kom ut i resultatet, i de mest allmänna termer- “Syntes som en av metoderna för att förbättra prestandan hos PostgreSQL»

Varför jag gör allt detta?

För det första för att jag själv inte ska glömma, när jag sitter på pension och minns de goda gamla dagarna.
För det andra för att systematisera det skrivna. För ibland börjar jag själv bli förvirrad och glömmer vissa delar.

Och kanske viktigast av allt – om det kan vara till nytta för någon och hjälpa till att inte återuppfinna hjulet och inte snubbla över samma problem. Med andra ord, förbättra sin karma (inte Habr-karman). För det mest värdefulla i denna värld är idéer. Huvudsaken är att hitta en idé. Att förverkliga en idé i verkligheten är redan en rent teknisk fråga.

Så, låt oss börja, sakta...

Problemställning.

Finns:

PostgreSQL-databas (10.5), blandad belastningstyp (OLTP+DSS), medel-låg belastning, placerad i molnet AWS.
Övervakning av databasen saknas, infrastrukturövervakningen presenteras genom AWS: s standardverktyg i minimal konfiguration.

Krävs:

Övervaka prestanda och tillstånd för databasen, samt hitta och ha initial information för att optimera tunga frågor till databasen.

Kort förord eller analys av lösningsalternativ

Till att börja med ska vi försöka analysera alternativen för att lösa problemet ur ett jämförande perspektiv av fördelar och nackdelar för ingenjören, medan fördelarna och förlusterna för managementet kan hanteras av de som har ansvar för det.

Alternativ 1- “Arbeta vid behov”

Vi lämnar allt som det är. Om kunden inte är nöjd med något i databasens eller applikationens funktionalitet eller prestanda kommer han att meddela DBA-teknikerna via e-post eller skapa en incident i ticketsystemet.
Ingenjören, som får meddelandet, kommer att undersöka problemet, föreslå en lösning eller skjuta upp problemet, i hopp om att det ordnar sig själv, och ändå kommer snart allt att glömmas.
Pepparkakor och bullar, blåmärken och knölarPepparkakor och bullar:
1. Det behövs inte göra något överflödigt.
2. Det finns alltid möjlighet att smita undan.
3. Massor av tid som kan användas efter eget godtycke.
Blåmärken och knölar:
1. Förr eller senare kommer kunden att börja tänka på livets mening och universums rättvisa i denna värld och återigen ställa sig frågan – varför betalar jag dem mina pengar? Konsekvensen är alltid densamma – frågan är bara när kunden kommer att tröttna och vinka adjö. Och foderstationen kommer att bli tom. Det är sorgligt.
2. Ingenjörens utveckling – noll.
3. Svårigheter med planering av arbete och belastning

Alternativ 2 – "Vi dansar med trumman, säljer och sätter på dem"

Punkt 1- Varför behöver vi ett övervakningssystem? Vi kommer att få allt via förfrågningar. Vi kör en massa olika förfrågningar mot datakatalogen och dynamiska vyer, aktiverar olika räknare, sammanställer allt i tabeller, analyserar listor och tabeller periodiskt. Resultatet är att vi får snygga eller mindre snygga grafer, tabeller, rapporter. Huvudsaken är att det är så mycket som möjligt.
Punkt 2- Vi genererar aktivitet – kör en analys av allt detta.
Punkt 3- Vi förbereder ett dokument som vi helt enkelt kallar – "hur vi ska organisera databasen".
Punkt 4- Kunden, som ser all denna prakt av grafer och siffror, befinner sig i en barnslig och naiv övertygelse – nu fungerar allt snart. Och han skiljer sig lätt och smidigt från sina ekonomiska resurser. Ledningen är också övertygad – ingenjörerna arbetar verkligen hårt. Belastningen är på max.
Punkt 5- Upprepa regelbundet Punkt 1.
Pepparkakor och bullar, blåmärken och knölarPepparkakor och bullar:
1. Livet för chefer och ingenjörer är enkelt, förutsägbart och fullt av aktivitet. Allt surrar, alla är upptagna.
2. Livet för kunden är också bra – han är alltid säker på att han bara behöver vänta lite till så kommer allt att bli bra. Det blir det inte, men vad ska man göra – världen är orättvis, i nästa liv kanske det blir tur.
Blåmärken och knölar:
1. Frågan är inte om, utan när, en mer effektiv leverantör av samma tjänst dyker upp, som erbjuder detsamma, men något billigare. Om resultatet ändå är detsamma, varför betala mer? Det leder återigen till att försörjningen försvinner.
2. Det är tråkigt. Precis som vilken meningslös aktivitet som helst.
3. Precis som i det föregående alternativet – det sker ingen utveckling. Men för ingenjören är nackdelen att, till skillnad från det första alternativet, här måste man ständigt generera databaser. Och det tar tid. Tid som kan användas mer fördelaktigt för sig själv. För om man inte tar hand om sig själv, bryr sig ingen om en.

Alternativ 3 – Man behöver inte hitta på hjulet igen, man behöver köpa det och åka.

Ingenjörer från andra företag äter inte pizzeria utan anledning, vilket de gör med öl (åh, de härliga tiderna i Sankt Petersburg på 90-talet). Låt oss använda övervakningssystem som är framtagna, testade och fungerar, och som faktiskt ger nytta (i alla fall för deras skapare).
Pepparkakor och bullar, blåmärken och knölarPepparkakor och bullar:
1. Man behöver inte slösa tid på att hitta på något som redan är uppfunnit. Ta det som finns och använd det.
2. Övervakningssystem skrivs inte av dumma människor och de är självklart användbara.
3. Funktionerande övervakningssystem ger vanligtvis användbar filtrerad information.
Blåmärken och knölar:
1. Ingenjören i detta fall är inte ingenjör, utan bara en användare av en annans produkt. Eller en användare.
2. Kunden måste övertygas om nödvändigheten av att köpa något som denne i allmänhet inte vill, och för vilket denne egentligen inte behöver förstås, och budgeten för året är bekräftad och kommer inte att ändras. Sedan måste man avsätta en särskild resurs, ställa in för det specifika systemet. Det vill säga, först måste man betala, betala och betala igen. Och kunden är snål. Det är normen i detta liv.

Vad ska vi göra – Tjernysjevski? Din fråga är mycket relevant. (c)

I detta specifika fall och den rådande situationen kan man agera lite annorlunda – låt oss skapa vårt eget övervakningssystem.

Nåväl, inte ett system i ordets fulla mening, det är för kraftfullt sagt och självsäkert, men åtminstone underlätta uppgiften och samla så mycket information som möjligt för att lösa prestandaincidenter. För att inte hamna i situationen – "gå dit, jag vet inte vart, hitta det, jag vet inte vad".

Vilka fördelar och nackdelar har detta alternativ:

Fördelar:
1. Det är intressant. Åtminstone intressantare än ständiga "shrink datafile, alter tablespace, etc."
2. Det här är nya färdigheter och ny utveckling. Vilket på sikt, förr eller senare, ger välförtjänta belöningar.
Nackdelar:
1. Det kommer att krävas arbete. Mycket arbete.
2. Det kommer att vara nödvändigt att regelbundet förklara syftet och perspektiven för alla aktiviteter.
3. Man måste offra något, eftersom den enda resursen som ingenjören har — tid — är begränsad av universum.
4. Det mest skrämmande och obehagliga — resultatet kan bli något som "Ingen mus, ingen groda, utan ett okänt djur".

Den som inte riskerar får inte heller bubbel.
Så börjar det mest intressanta.

Den övergripande idén — schematiskt

(Illustrationen är hämtad från artikeln «Syntes som en av metoderna för att förbättra prestandan hos PostgreSQL»)

Förklaring:

I den målbasen installeras tillägget PostgreSQL — “pg_stat_statements”.
I övervakningsdatabasen skapar vi en uppsättning tjänstetabletter för att lagra historik för pg_stat_statements i det inledande skedet och för att konfigurera mätvärden och övervakning framöver.
På övervakningsvärden skapar vi en uppsättning bash-skript, inklusive för att generera incidenter i biljettsystemet.

Tjänstetabletter

Först en schematisk och förenklad ERD, vad blev resultatet:

Kort beskrivning av tabellernaendpoint — värd, punkt för anslutning till instansen
database — parametrar för databasen
pg_stat_history — historisk tabell för att lagra tidsbilder av presentationen av pg_stat_statements för målbasen
metric_glossary — ordlista för prestandamätningar
metric_config — konfiguration av specifika mätningar
metric — specifik mätning för den övervakade förfrågan
metric_alert_history — historik över prestandameddelanden
log_query — tjänstetabell för lagring av analyserade poster från loggfilen i PostgreSQL som laddas från AWS
baseline — parametrar för tidsperioden som används som baslinje
checkpoint — konfiguration av mätningar för hälsokontroll av databasen
checkpoint_alert_history — historik över meddelanden från mätningar för hälsokontroll av databasen
pg_stat_db_queries — tjänstetabell för aktiva förfrågningar
activity_log — tjänstetabell för aktivitetslogg
trap_oid — tjänstetabell för konfiguration av trap

Steg 1 — vi samlar in statistisk information om prestanda och får rapporter

För lagring av statistisk information fungerar tabellen pg_stat_history
Strukturen för tabellen pg_stat_history

                                          Tabell "public.pg_stat_history"
       Kolumn        |            Typ             |                          Modifierar
---------------------+-----------------------------+-------------------------------------------
 id                  | heltal                     | inte null standard nextval('pg_stat_history_id_seq'::regclass)
 snapshot_timestamp  | tidsstämpel utan tidszon |
 database_id         | heltal                     |
 dbid                | oid                         |
 userid              | oid                         |
 queryid             | stort heltal               |
 query               | text                        |
 calls               | stort heltal               |
 total_time          | dubbel precision            |
 min_time            | dubbel precision            |
 max_time            | dubbel precision            |
 mean_time           | dubbel precision            |
 stddev_time         | dubbel precision            |
 rows                | stort heltal               |
 shared_blks_hit     | stort heltal               |
 shared_blks_read    | stort heltal               |
 shared_blks_dirtied | stort heltal               |
 shared_blks_written | stort heltal               |
 local_blks_hit      | stort heltal               |
 local_blks_read     | stort heltal               |
 local_blks_dirtied  | stort heltal               |
 local_blks_written  | stort heltal               |
 temp_blks_read      | stort heltal               |
 temp_blks_written   | stort heltal               |
 blk_read_time       | dubbel precision            |
 blk_write_time      | dubbel precision            |
 baseline_id         | heltal                     |
Index:
    "pg_stat_history_pkey" PRIMÄR NYCKEL, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Utländska nyckelbegränsningar:
    "database_id_fk" UTLÄNDSK NYCKEL (database_id) REFERENSERAR database(id) VID BORTTAGNING CASCADE

Som det framgår representerar tabellen bara kumulativa data av vyerna pg_stat_statements i den mål-databasen.

Användningen av denna tabell är mycket enkel

pg_stat_history kommer att representera ackumulerad statistik över frågaexekvering varje timme. I början av varje timme, efter att tabellen har fyllts, återställs statistiken pg_stat_statements med hjälp av pg_stat_statements_reset().
Anmärkning: statistiken samlas in för frågor med en exekveringstid över 1 sekund.
Fyllning 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 AVBILD SKAPAS';
		
		--Anslut till måldatabasen	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'värd % och databas % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Skapar avbild av pg_stat_statements för databas %',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 'Skapar avbild av pg_stat_statements för frågor med min_time över 1000ms';
	
        FOR pg_stat_snapshot IN
          --Alla frågor med max_time större än 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 ett resultat, efter en viss tidsperiod i tabellen pg_stat_history har vi en uppsättning bilder av innehållet i tabellen pg_stat_statements för den aktuella databasen.

Själva rapporteringen

Med hjälp av enkla frågor kan man få ganska användbara och intressanta rapporter.

Aggerade data för den angivna tidsperioden

Fråga

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 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 efter total tid

Fråga

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 EFTER TOTAL UTFÖRANDE TID
|   #|    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 efter total I/O tid

Fråga

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 EFTER TOTAL TID I/O
|   #|    queryid|      anrop|    anrop %|                   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 efter max exekveringstid

Fråga

VÄLJ 
  id SOM snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FRÅN 
  pg_stat_history 
DÄR 
  queryid ÄR INTE NULL OCH 
  database_id = DATABASE_ID  OCH
  snapshot_timestamp MELLAN BEGIN_TIMEPOINT OCH END_TIMEPOINT
ORDNA EFTER 4 DESC 
BEGRÄNSA 10

-----------------------------------------------------------------------------------------
| TOP10 SQL EFTER MAX EXEKVERINGSTID
|   #|          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 efter DELAD buffer läsning/skrivning

Fråga

VÄLJ 
  id SOM snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FRÅN 
  pg_stat_history
DÄR 
  queryid ÄR INTE NULL OCH 
  database_id = DATABASE_ID  OCH
  snapshot_timestamp MELLAN BEGIN_TIMEPOINT OCH END_TIMEPOINT OCH
  ( shared_blks_read > 0 ELLER shared_blks_written > 0 )
ORDNA EFTER 4 DESC  , 5 DESC 
BEGRÄNSA 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 över fördelningen av förfrågningar efter maximal kötid

Förfrågningar

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 Ögonblicksbilder per fråga och sekund

Förfrågningar

--pg_qps.sql
--Beräkna frågor 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 'FEL - Hittade inte pg_stat_history för 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 sorterade efter QueryPerSeconds-nummer
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snapshot| snapshotID|      samtal|                      totalt dbtid|        QPS|                          I/O-tid| I/O-tid %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Timvis utförandeshistorik med QueryPerSeconds och I/O-tid

Fråga

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

|-----------------------------------------------------------------------------------------------
| TIMHISTORIA FÖR TIMME MED QueryPerSeconds och I/O-tid
-----------------------------------------------------------------------------------------------------------------------------------------------
| HISTORIA FÖR FRÅGOR PER SEKUND
|    #|          ögonblicksbild| ögonblicksbildID|      samtal|                      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

Texten av alla SQL-val

Fråga

VÄLJ 
  queryid , 
  fråga 
FRÅN 
  pg_stat_history
DÄR 
  queryid ÄR INTE NULL OCH 
  database_id = DATABASE_ID OCH
  snapshot_timestamp MELLAN BEGIN_TIMEPOINT OCH END_TIMEPOINT
GRUPPERA EFTER queryid , fråga

Resultat

Som det framgår, med ganska enkla medel kan man få en hel del användbar information om belastningen och tillståndet hos databasen.

Anmärkning:Om vi registrar queryid i frågorna får vi historik per enskild fråga (för att spara utrymme har rapporterna för enskilda frågor utelämnats).

Så, statistiska data om fråga prestanda — finns och samlas in.
Det första steget "insamling av statistikdata" — är avslutat.

Vi kan gå vidare till det andra steget - "inställning av prestanda metriker".

Men det är redan en helt annan historia.