Prestasiemonitering van PostgreSQL-navrae. Deel 1 - verslagdoening

Ingenieur - uit Latyn vertaal - geïnspireer.
'n Ingenieur kan enigiets doen. (c) R. Diesel.
Epigrawe.

Of 'n storie oor hoekom 'n databasisadministrateur sy programmeringsverlede moet onthou.

voorwoord

Alle name is verander. Wedstryde is ewekansig. Die materiaal is uitsluitlik die persoonlike mening van die skrywer.

Vrywaring van waarborge: in die beplande reeks artikels sal daar geen gedetailleerde en akkurate beskrywing wees van die tabelle en skrifte wat gebruik word nie. Materiaal kan nie onmiddellik "SOOS IS" gebruik word nie.
Eerstens, as gevolg van die groot hoeveelheid materiaal,
tweedens, as gevolg van die skerpte met die produksiebasis van 'n regte klant.
Daarom sal slegs idees en beskrywings in die mees algemene vorm in die artikels gegee word.
Miskien sal die stelsel in die toekoms groei tot die vlak van plasing op GitHub, of dalk nie. Tyd sal wys.

Begin van die storie-Onthou jy hoe dit alles begin het".
Wat gebeur het as gevolg daarvan, in die mees algemene terme - "Sintese as een van die metodes om PostgreSQL-prestasie te verbeter»

Hoekom het ek dit alles nodig?

Wel, eerstens, om nie jouself te vergeet nie, en onthou die glorieryke dae in aftrede.
Tweedens, om te sistematiseer wat geskryf is. Vir reeds myself begin ek soms deurmekaar raak en afsonderlike dele vergeet.

Wel, en die belangrikste – skielik kan dit vir iemand handig te pas kom en help om nie die wiel weer uit te vind nie en nie om 'n hark te versamel nie. Met ander woorde, verbeter jou karma (nie Khabrovsky nie). Want die waardevolste ding in hierdie wêreld is idees. Die belangrikste ding is om 'n idee te vind. En om die idee in werklikheid te vertaal is reeds 'n suiwer tegniese kwessie.

So kom ons begin stadig...

Formulering van die probleem.

Beskikbaar:

PostgreSQL(10.5), gemengde vrag (OLTP+DSS), medium tot ligte vrag, gehuisves in die AWS-wolk.
Daar is geen databasismonitering nie, infrastruktuurmonitering word aangebied as standaard AWS-instrumente in 'n minimale opset.

vereis:

Monitor die werkverrigting en status van die databasis, vind en het aanvanklike inligting om swaar databasisnavrae te optimaliseer.

Kort inleiding of ontleding van oplossings

Om mee te begin, kom ons probeer om die opsies vir die oplossing van die probleem te ontleed vanuit die oogpunt van 'n vergelykende ontleding van die voordele en probleme vir die ingenieur, en laat diegene wat veronderstel is om op die personeellys te wees die voordele en verliese hanteer. van bestuur.

Opsie 1 - "Werk op aanvraag"

Ons los alles soos dit is. As die kliënt nie tevrede is met iets in die gesondheid, prestasie van die databasis of toepassing nie, sal hy die DBA-ingenieurs per e-pos in kennis stel of deur 'n voorval in die kaartjieboks te skep.
'n Ingenieur, wat 'n kennisgewing ontvang het, sal die probleem verstaan, 'n oplossing bied, of die probleem opstel, met die hoop dat alles vanself sal oplos, en in elk geval, alles sal gou vergeet word.
Gemmerkoek en oliebolle, kneusplekke en knoppeGemmerkoek en oliebolle:
1. Niks ekstra om te doen nie
2. Daar is altyd die geleentheid om uit te klim en vuil te word.
3. Baie tyd wat jy op jou eie kan spandeer.
Kneusplekke en stampe:
1. Vroeër of later sal die kliënt nadink oor die wese van wese en universele geregtigheid in hierdie wêreld en homself weer die vraag vra – hoekom betaal ek hulle my geld? Die gevolg is altyd dieselfde – die enigste vraag is wanneer die kliënt verveeld raak en totsiens waai. En die voerbak is leeg. Dit is hartseer.
2. Die ontwikkeling van 'n ingenieur is nul.
3. Probleme met die skedulering van werk en laai

Opsie 2 - "Dans met tamboeryne, trek aan en trek skoene aan"

Paragraaf 1-Hoekom het ons 'n moniteringstelsel nodig, ons sal alle versoeke ontvang. Ons loods 'n klomp allerhande navrae na die datawoordeboek en dinamiese aansigte, skakel allerhande tellers aan, bring alles in tabelle, ontleed as't ware gereeld lyste en tabelle. As gevolg hiervan, het ons pragtige of nie baie grafieke, tabelle, verslae. Die belangrikste ding - dit sou meer, meer wees.
Paragraaf 2Genereer aktiwiteit - voer die ontleding van dit alles uit.
Paragraaf 3-Ons is besig om 'n sekere dokument voor te berei, ons noem hierdie dokument, eenvoudig - "hoe rus ons die databasis toe."
Paragraaf 4- Die kliënt, wat al hierdie prag van grafieke en figure sien, is in 'n kinderlike naïewe vertroue - nou sal alles binnekort vir ons werk. En, maklik en pynloos deel van hul finansiële hulpbronne. Die bestuur is ook seker dat ons ingenieurs hard werk. Maksimum laai.
Paragraaf 5- Herhaal stap 1 gereeld.
Gemmerkoek en oliebolle, kneusplekke en knoppeGemmerkoek en oliebolle:
1. Die lewe van bestuurders en ingenieurs is eenvoudig, voorspelbaar en gevul met aktiwiteit. Alles gons, almal is besig.
2. Die kliënt se lewe is ook nie sleg nie – hy is altyd seker dat jy bietjie geduldig moet wees en alles sal regkom. Word nie beter nie, wel, wel - hierdie wêreld is onregverdig, in die volgende lewe - gelukkig.
Kneusplekke en stampe:
1. Vroeër of later sal daar 'n slimmer verskaffer van 'n soortgelyke diens wees wat dieselfde ding sal doen, maar 'n bietjie goedkoper. En as die resultaat dieselfde is, hoekom meer betaal. Wat weer sal lei tot die verdwyning van die voerder.
2. Dis vervelig. Hoe vervelig enige klein betekenisvolle aktiwiteit.
3. Soos in die vorige weergawe - geen ontwikkeling. Maar vir 'n ingenieur is die minus dat, anders as die eerste opsie, jy hier voortdurend 'n IDB moet genereer. En dit neem tyd. Wat bestee kan word tot voordeel van jou geliefde. Want jy kan nie vir jouself sorg nie, almal gee om vir jou.

Opsie 3-Nie nodig om 'n fiets uit te vind nie, jy moet dit koop en daarmee ry.

Ingenieurs van ander maatskappye eet willens en wetens pizza saam met bier (o, die glorieryke tye van St. Petersburg in die 90's). Kom ons gebruik moniteringstelsels wat gemaak, ontfout en werk, en oor die algemeen bring dit voordele in (wel, ten minste vir hul skeppers).
Gemmerkoek en oliebolle, kneusplekke en knoppeGemmerkoek en oliebolle:
1. Jy hoef nie tyd te mors om uit te vind wat reeds uitgevind is nie. Neem en gebruik.
2. Moniteringstelsels word nie deur dwase geskryf nie, en natuurlik is dit nuttig.
3. Werkende moniteringstelsels verskaf gewoonlik nuttige gefiltreerde inligting.
Kneusplekke en stampe:
1. Die ingenieur in hierdie geval is nie 'n ingenieur nie, maar net 'n gebruiker van iemand anders se produk. Of 'n gebruiker.
2. Die kliënt moet oortuig wees van die behoefte om iets te koop wat hy oor die algemeen nie wil verstaan ​​nie, en hy moet nie, en oor die algemeen is die begroting vir die jaar goedgekeur en sal nie verander nie. Dan moet jy 'n aparte hulpbron toewys, dit vir 'n spesifieke stelsel konfigureer. Dié. Eerstens moet jy betaal, betaal en weer betaal. En die kliënt is suinig. Dit is die norm van hierdie lewe.

Wat om te doen, Chernyshevsky? Jou vraag is baie pertinent. (Met)

In hierdie spesifieke geval en die huidige situasie kan jy 'n bietjie anders doen - kom ons maak ons ​​eie moniteringstelsel.

Wel, nie 'n stelsel nie, natuurlik in die volle sin van die woord, dit is te hard en aanmatigend, maar maak dit ten minste op een of ander manier makliker vir jouself en versamel meer inligting om prestasie-insidente op te los. Om jouself nie in 'n situasie te bevind nie - "gaan soontoe, ek weet nie waar nie, vind dit, ek weet nie wat nie."

Wat is die voor- en nadele van hierdie opsie:

Pros:
1. Dis interessant. Wel, ten minste meer interessant as die konstante "krimp datalêer, verander tafelspasie, ens."
2. Dit is nuwe vaardighede en nuwe ontwikkeling. Wat in die toekoms vroeër of later welverdiende gemmerbrood en oliebolle sal gee.
Nadele:
1. Moet werk. Werk baie.
2. Jy sal gereeld die betekenis en perspektiewe van alle aktiwiteit moet verduidelik.
3. Iets sal opgeoffer moet word, want die enigste hulpbron tot beskikking van die ingenieur – tyd – word deur die Heelal beperk.
4. Die ergste en onaangenaamste - gevolglik kan vullis soos "Nie 'n muis nie, nie 'n padda nie, maar 'n onbekende diertjie" uitdraai.

Wie nie iets waag nie, drink nie sjampanje nie.
So, die pret begin.

Algemene idee - skematies

(Illustrasie geneem uit artikel «Sintese as een van die metodes om PostgreSQL-prestasie te verbeter»)

Verduideliking:

• Die teikendatabasis is geïnstalleer met die standaard PostgreSQL-uitbreiding “pg_stat_statements”.
• In die moniteringsdatabasis skep ons 'n stel dienstabelle om die pg_stat_statements-geskiedenis in die aanvanklike stadium te stoor en om metrieke en monitering in die toekoms op te stel
• Op die moniteringsgasheer skep ons 'n stel bash-skrifte, insluitend dié om insidente in die kaartjiestelsel te genereer.

Dienstafels

Om mee te begin, 'n skematies vereenvoudigde ERD, wat op die ou end gebeur het:

Kort beskrywing van die tabelleeindpunt - gasheer, verbindingspunt na die instansie
databasis - databasis opsies
pg_stat_geskiedenis - historiese tabel vir die stoor van tydelike foto's van die pg_stat_statements-aansig van die teikendatabasis
metriese_woordelys - Woordeboek van prestasie statistieke
metric_config - konfigurasie van individuele statistieke
metrieke - 'n spesifieke maatstaf vir die versoek wat gemonitor word
metriese_waarskuwinggeskiedenis - geskiedenis van prestasie waarskuwings
log_navraag - dienstabel vir die stoor van ontleed rekords vanaf die PostgreSQL-loglêer wat vanaf AWS afgelaai is
basislyn - parameters van die tydperk wat as basis gebruik word
kontrolepunt - opstelling van statistieke om die status van die databasis na te gaan
kontrolepunt_waarskuwinggeskiedenis - waarskuwingsgeskiedenis van databasisstatuskontrole-statistieke
pg_stat_db_queries — dienstabel van aktiewe versoeke
aktiwiteit log - aktiwiteit log diens tabel
trap_oid - lokvalkonfigurasiedienstabel

Fase 1 - versamel prestasiestatistieke en kry verslae

'n Tabel word gebruik om statistiese inligting te stoor. pg_stat_geskiedenis
pg_stat_history tabelstruktuur

                                          Tabel "public.pg_stat_history" Kolom | tik | Wysigers----------------------+----------------------- --+---- ---------------------------- id | heelgetal | nie null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | tydstempel sonder tydsone | databasis_id | heelgetal | dbid | oid | gebruikers-ID | oid | navraag | groot | navraag | teks | oproepe | groot | totale_tyd | dubbele presisie | min_tyd | dubbele presisie | maksimum_tyd | dubbele presisie | gemiddelde_tyd | dubbele presisie | stddev_tyd | dubbele presisie | rye | groot | shared_blks_hit | groot | shared_blks_read | groot | shared_blks_dirtied | groot | gedeel_blks_geskryf | groot | plaaslike_blks_treffer | groot | local_blks_read | groot | local_blks_dirtied | groot | plaaslike_blks_geskryf | groot | temp_blks_read | groot | temp_blks_written | groot | blk_leestyd | dubbele presisie | blk_skryftyd | dubbele presisie | basislyn_id | heelgetal | Indekse: "pg_stat_history_pkey" PRIMÊRE SLEUTEL, btree (id) "database_idx" btree (databasis_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Buitelandse-sleutelbeperkings: "databasis_ID_KEYk" (databasis_ID_KEYk) ) OP DELETE CASCADE

Soos u kan sien, is die tabel slegs 'n kumulatiewe aansigdata pg_stat_statements in die teikendatabasis.

Die gebruik van hierdie tabel is baie eenvoudig.

pg_stat_geskiedenis sal die opgehoopte statistieke van navraaguitvoering vir elke uur verteenwoordig. Aan die begin van elke uur, na die invul van die tabel, statistieke pg_stat_statements herstel met pg_stat_statements_reset().
Let wel: statistieke word ingesamel vir versoeke met 'n duur van meer as 1 sekonde.
Vul die pg_stat_history-tabel in

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

As gevolg hiervan, na 'n sekere tydperk in die tabel pg_stat_geskiedenis ons sal 'n stel kiekies hê van die inhoud van die tabel pg_stat_statements teiken databasis.

Eintlik rapporteer

Deur eenvoudige navrae te gebruik, kan u baie nuttige en interessante verslae kry.

Geaggregeerde data vir 'n gegewe tydperk

Navraag

SELECT 
  database_id , 
  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,
  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,
  SUM(shared_blks_read) AS shared_blks_read ,
  SUM(shared_blks_dirtied) AS shared_blks_dirtied,
  SUM(shared_blks_written) AS shared_blks_written , 
  SUM(local_blks_hit) AS local_blks_hit , 
  SUM(local_blks_read) AS local_blks_read , 
  SUM(local_blks_dirtied) AS local_blks_dirtied , 
  SUM(local_blks_written)  AS local_blks_written,
  SUM(temp_blks_read) AS temp_blks_read, 
  SUM(temp_blks_written) temp_blks_written , 
  SUM(blk_read_time) AS blk_read_time , 
  SUM(blk_write_time) AS blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

D.B. Tyd

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

I/O Tyd

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

TOP10 SQL by totaal_tyd

Navraag

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 BY TOTALE UITVOERTYD | #| navraag| oproepe| oproepe totale_tyd (ms) | dbtime % +----+---------------------------- --------------------+---------------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 me.)| 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 me.)| 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 volgens totale I/O tyd

Navraag

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 BY TOTALE I/O TYD | #| navraag| oproepe| oproepe I/O tyd (ms)|db I/O tyd % +----+------------+-----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 me.)| 31.06 Junie | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 me.)| 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 me.)| 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 volgens maksimum tyd van uitvoering

Navraag

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 BY MAKS UITVOERTYD | #| momentopname| snapshotID| navraag| maksimum_tyd (ms) +----+------------------+------------+-------- --+------------------------------------------------ | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 me.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 me.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 me.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00( 01 me.) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452( 93452.150 me.) | 7| 04.04.2019/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:16( 00 me.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 me.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX me.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)

TOP10 SQL deur GEDEELDE buffer lees/skryf

Navraag

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 DEUR GEDEELDE BUFFER LEES/SKRYF | #| momentopname| snapshotID| navraag| gedeelde blokke gelees| gedeelde blokke skryf +----+-------------------+-----------+----- --+----------------------------+------------------------ | 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 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX -------------------------------------------------- --------------------------------------------------

Histogram van navraagverspreiding volgens maksimum uitvoeringstyd

Versoeke

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 | TOTAAL OPROEPE : 33851920 | MIN TYD : 00:00:01.063 | MAKS TYD: 00:02:01.869 ------------------------------------------ ---------------------------- | min duur| maksimum duur| oproepe +----------------------------------+------- ----------------------+---------- | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 me.) | 9 | 00:00:13.144( 13144.445 me.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 me.) | 0 | 00:00:37.305( 37305.675 me.) | 00:00:49.386( 49386.290 me.) | 0 | 00:00:49.386( 49386.290 me.) | 00:01:01.466( 61466.906 me.) | 0 | 00:01:01.466( 61466.906 me.) | 00:01:13.547( 73547.521 me.) | 0 | 00:01:13.547( 73547.521 me.) | 00:01:25.628( 85628.136 me.) | 0 | 00:01:25.628( 85628.136 me.) | 00:01:37.708( 97708.751 me.) | 4 | 00:01:37.708( 97708.751 me.) | 00:01:49.789( 109789.366 me.) | 2 | 00:01:49.789( 109789.366 me.) | 00:02:01.869( 121869.981 me.) | 0

TOP10-kiekies volgens navraag per sekonde

Versoeke

--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-kiekies gerangskik volgens QueryPerSeconds-nommers -------------------------------------------- ------ -------------------------------------------- ------ -------------------------------------------------- | #| momentopname| snapshotID| oproepe| totale dbtime| QPS | I/O tyd | I/O tyd % +-----+------------------------+-----------+------- ----+------------------------------------+---------- --+----------------------------------------+---------------- | 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 me.)| 00| 01.470:1470.110:376( 2 ms.)| .04.04.2019 | 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834 me.)| 767834.052| 108.324:3:04.04.2019( 16 me.)| 00 | 4143| 3525360/00/10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555( 84.988 me.)| 4| 04.04.2019:21:03( 4163 me.)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 me.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755/776.979/00 00:01.441| 1441.386| 732| 6:04.04.2019:14( 00 me.)| 4137| 2397326:00:04( 43.033 me.)| .283033.854 | 665.924| 00/00/00.024 24.505:009 | 7| 04.04.2019| 15:00:4139( 2394416 me.)| 00| 04:51.435:291435.010( 665.116 me.)| .00 | 00| 12.025/12025.895/4.126 8:04.04.2019| 13| 00| 4135:2373043:00( 04 me.)| 26.791| 266791.988:659.179:00( 00 me.)| 00.064 | 64.261| 024/9/05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 me.)| 609.332| 00:05:18.847( 318847.407 me.)| .77.507 | 10| 04.04.2019/18/01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00 me.)| 00| 01.319:1319.676:1.666( XNUMX me.)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX me.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX me.)| XNUMX

Uurlikse uitvoeringsgeskiedenis met QueryPerSeconds en I/O-tyd

Navraag

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

Teks van alle SQL-kies

Navraag

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

Totale

Soos u kan sien, kan u op redelik eenvoudige maniere baie nuttige inligting oor die werklading en die toestand van die databasis kry.

Let wel:As jy die navraag-ID in die navrae regmaak, sal ons die geskiedenis vir 'n aparte versoek kry (om spasie te bespaar, word verslae vir 'n aparte versoek weggelaat).

Dus, statistiese data oor navraagprestasie is beskikbaar en versamel.
Die eerste fase "insameling van statistiese data" is voltooi.

U kan voortgaan na die tweede fase - "konfigurasie van prestasiemaatstawwe".


Maar dit is 'n heel ander verhaal.

Vervolg…

Bron: will.com