PostgreSQL-kyselyiden suorituskyvyn seuranta. Osa 1 - Raportointi

Insinööri - käännetty latinasta - inspiroitunut.
Insinööri voi tehdä mitä tahansa. (c) R. Diesel.
Epigrafit.

Tai tarina siitä, miksi tietokannan ylläpitäjän on muistettava ohjelmointimenneisyytensä.

Esipuhe

Kaikki nimet on muutettu. Ottelut ovat satunnaisia. Materiaali on vain kirjoittajan henkilökohtainen mielipide.

Takuiden vastuuvapauslauseke: suunnitellussa artikkelisarjassa ei ole yksityiskohtaista ja tarkkaa kuvausta käytetyistä taulukoista ja skripteistä. Materiaalia ei voida käyttää välittömästi "SELLAISENAAN".
Ensinnäkin suuren materiaalimäärän vuoksi
toiseksi todellisen asiakkaan tuotantopohjan terävyyden vuoksi.
Siksi artikkeleissa annetaan vain ideoita ja kuvauksia yleisimmässä muodossa.
Ehkä tulevaisuudessa järjestelmä kasvaa GitHubissa julkaisemisen tasolle, tai ehkä ei. Aika näyttää.

Tarinan alku-Muistatko kuinka kaikki alkoi'.
Mitä sen seurauksena tapahtui, yleisimmin sanottuna - "Synteesi yhtenä menetelmistä parantaa PostgreSQL:n suorituskykyä»

Miksi tarvitsen tätä kaikkea?

No, ensinnäkin, jotta et unohda itseäsi, muistaen loistavia päiviä eläkkeellä.
Toiseksi systematisoida kirjoitettu. Jo itselleni alan välillä hämmentyä ja unohtaa erilliset osat.

No, ja mikä tärkeintä - yhtäkkiä se voi olla hyödyksi jollekin ja auttaa olemaan keksimättä pyörää uudelleen ja olemaan keräämättä haravaa. Toisin sanoen, paranna karmaasi (ei Khabrovsky). Sillä arvokkain asia tässä maailmassa on ideat. Pääasia on löytää idea. Ja idean muuntaminen todeksi on jo puhtaasti tekninen kysymys.

Aloitetaan siis hitaasti...

Ongelman muotoilu.

Saatavilla:

PostgreSQL (10.5), sekakuormitus (OLTP+DSS), keskikokoinen tai kevyt kuormitus, isännöi AWS-pilvessä.
Tietokannan valvontaa ei ole, infrastruktuurin valvonta esitetään vakiona AWS-työkaluina minimaalisessa kokoonpanossa.

Tarvitaan:

Tarkkaile tietokannan suorituskykyä ja tilaa, etsi ja hanki alkutiedot raskaan tietokantakyselyn optimoimiseksi.

Lyhyt esittely tai ratkaisujen analyysi

Aluksi yritetään analysoida ongelman ratkaisuvaihtoehtoja insinöörin hyötyjen ja ongelmien vertailevan analyysin näkökulmasta, ja annetaan henkilöstöluetteloon kuuluvien käsitellä hyödyt ja tappiot. johtamisesta.

Vaihtoehto 1 - "Työskentely tilauksesta"

Jätämme kaiken sellaisenaan. Jos asiakas ei ole tyytyväinen johonkin tietokannan tai sovelluksen kuntoon, toimivuuteen, hän ilmoittaa DBA-insinööreille sähköpostitse tai luomalla tapahtuman lippulaatikkoon.
Insinööri, saatuaan ilmoituksen, ymmärtää ongelman, tarjoaa ratkaisun tai hyllyttää ongelman toivoen, että kaikki ratkeaa itsestään, ja joka tapauksessa kaikki unohtuu pian.
Piparkakkuja ja munkkeja, mustelmia ja kuoppiaPiparkakut ja munkit:
1. Ei mitään ylimääräistä tekemistä
2. Aina on mahdollisuus päästä ulos ja likaantua.
3. Paljon aikaa, jonka voit viettää yksin.
Mustelmat ja kolhut:
1. Ennemmin tai myöhemmin asiakas pohtii olemisen olemusta ja yleismaailmallista oikeudenmukaisuutta tässä maailmassa ja kysyy jälleen kerran itselleen kysymyksen - miksi maksan heille rahani? Seuraus on aina sama - kysymys on vain siitä, milloin asiakas kyllästyy ja heiluttaa hyvästit. Ja syöttölaite on tyhjä. Se on surullista.
2. Insinöörin kehitys on nolla.
3. Vaikeudet töiden ja lastauksen aikataulutuksessa

Vaihtoehto 2 - "Tanssi tamburiinien kanssa, laita kengät jalkaan"

Kohta 1-Mihin tarvitsemme seurantajärjestelmän, vastaanotamme kaikki pyynnöt. Käynnistämme joukon kaikenlaisia ​​kyselyitä tietosanakirjaan ja dynaamisiin näkymiin, käynnistämme kaikenlaiset laskurit, tuomme kaiken taulukoihin, analysoimme säännöllisesti luetteloita ja taulukoita. Tämän seurauksena meillä on kauniita tai ei kovin kaavioita, taulukoita, raportteja. Pääasia - se olisi enemmän, enemmän.
Kohta 2-Luo toiminta - suorita analyysi kaikesta tästä.
Kohta 3- Valmistelemme tiettyä asiakirjaa, kutsumme tätä asiakirjaa yksinkertaisesti - "miten varustamme tietokannan."
Kohta 4- Asiakas, nähdessään kaiken tämän kaavioiden ja lukujen loiston, on lapsellisen naiivissa itseluottamuksessa - nyt kaikki toimii meille, pian. Ja helposti ja kivuttomasti jakaa taloudellisia resurssejaan. Johto on myös varma, että insinöörimme työskentelevät kovasti. Max lataus.
Kohta 5- Toista vaihe 1 säännöllisesti.
Piparkakkuja ja munkkeja, mustelmia ja kuoppiaPiparkakut ja munkit:
1. Esimiesten ja insinöörien elämä on yksinkertaista, ennustettavaa ja täynnä toimintaa. Kaikki kumisee, kaikilla on kiire.
2. Asiakkaan elämä ei myöskään ole huono - hän on aina varma, että sinun on oltava kärsivällinen ja kaikki järjestyy. Se ei parane, no, no - tämä maailma on epäreilu, seuraavassa elämässä - olet onnekas.
Mustelmat ja kolhut:
1. Ennemmin tai myöhemmin löytyy älykkäämpi vastaavan palvelun tarjoaja, joka tekee saman asian, mutta hieman halvemmalla. Ja jos tulos on sama, miksi maksaa enemmän. Mikä taas johtaa syöttölaitteen katoamiseen.
2. Se on tylsää. Kuinka tylsää mikä tahansa pieni merkityksellinen toiminta.
3. Kuten edellisessä versiossa - ei kehitystä. Mutta insinöörille miinus on se, että toisin kuin ensimmäisessä vaihtoehdossa, sinun on luotava jatkuvasti IDB. Ja se vie aikaa. Sen voi käyttää rakkaasi hyväksi. Sillä et voi huolehtia itsestäsi, kaikki välittävät sinusta.

Vaihtoehto 3 – Polkupyörää ei tarvitse keksiä, se pitää ostaa ja sillä ajaa.

Muiden yritysten insinöörit syövät tietoisesti pizzaa oluen kera (oi, Pietarin loistoaikaa 90-luvulla). Hyödynnetään valvontajärjestelmiä, jotka on tehty, virheenkorjattu ja toimivia, ja yleisesti ottaen niistä on hyötyä (no ainakin tekijöilleen).
Piparkakkuja ja munkkeja, mustelmia ja kuoppiaPiparkakut ja munkit:
1. Ei tarvitse tuhlata aikaa jo keksityn keksimiseen. Ota ja käytä.
2. Valvontajärjestelmät eivät ole tyhmien kirjoittamia, ja ne ovat tietysti hyödyllisiä.
3. Toimivat valvontajärjestelmät tarjoavat yleensä hyödyllistä suodatettua tietoa.
Mustelmat ja kolhut:
1. Insinööri ei tässä tapauksessa ole insinööri, vaan vain jonkun muun tuotteen käyttäjä tai käyttäjä.
2. Asiakkaan on oltava vakuuttunut tarpeesta ostaa jotain, jota hän ei yleensä halua ymmärtää, eikä hänen pitäisi ymmärtää, ja yleisesti ottaen vuoden budjetti on hyväksytty, eikä se muutu. Sitten sinun on varattava erillinen resurssi, määritettävä se tietylle järjestelmälle. Nuo. Ensin pitää maksaa, maksaa ja vielä kerran maksaa. Ja asiakas on niukka. Tämä on tämän elämän normi.

Mitä tehdä, Chernyshevsky? Kysymyksesi on erittäin aiheellinen. (Kanssa)

Tässä nimenomaisessa tapauksessa ja nykyisessä tilanteessa voit tehdä hieman toisin - Tehdään oma valvontajärjestelmämme.

No, ei tietenkään järjestelmä, sanan täydessä merkityksessä, tämä on liian äänekäs ja röyhkeä, mutta ainakin jotenkin helpota itseäsi ja kerää lisää tietoa suorituskykyhäiriöiden ratkaisemiseksi. Jotta et joutuisi tilanteeseen - "mene sinne, en tiedä mistä, löydä se, en tiedä mitä."

Mitkä ovat tämän vaihtoehdon edut ja haitat:

Plussat:
1. Se on mielenkiintoista. No, ainakin mielenkiintoisempi kuin jatkuva "kutista datatiedosto, muuta taulukkotilaa jne."
2. Nämä ovat uusia taitoja ja uutta kehitystä. Joka tulevaisuudessa, ennemmin tai myöhemmin, antaa hyvin ansaittuja piparkakkuja ja munkkeja.
Miinukset:
1. Täytyy tehdä töitä. Työskennellä paljon.
2. Sinun tulee säännöllisesti selittää kaiken toiminnan merkitys ja näkökulmat.
3. Jotain on uhrattava, koska ainoa insinöörin käytettävissä oleva resurssi - aika - on universumin rajoittama.
4. Pahin ja epämiellyttävin - seurauksena voi tulla roskaa, kuten "Ei hiiri, ei sammakko, vaan tuntematon pieni eläin".

Joka ei riskeeraa jotain, ei juo samppanjaa.
Hauskuus alkaa siis.

Yleinen idea - kaavamainen

(Kuva otettu artikkelista «Synteesi yhtenä menetelmistä parantaa PostgreSQL:n suorituskykyä»)

Selitys:

• Kohdetietokanta asennetaan vakiona PostgreSQL-laajennuksella "pg_stat_statements".
• Valvontatietokantaan luomme joukon palvelutaulukoita, jotka tallentavat pg_stat_statements-historian alkuvaiheessa ja konfiguroimme mittareita ja seurantaa tulevaisuudessa.
• Valvontapalvelimella luomme joukon bash-skriptejä, mukaan lukien ne, joilla luodaan tapauksia lippujärjestelmässä.

Huoltopöydät

Aluksi kaavamaisesti yksinkertaistettu ERD, mitä lopulta tapahtui:

Taulukoiden lyhyt kuvauspäätepiste - isäntä, yhteyspiste esiintymään
tietokanta - tietokantavaihtoehdot
pg_stat_history - historiallinen taulukko kohdetietokannan pg_stat_statements-näkymän väliaikaisten tilannekuvien tallentamiseen
metrinen_sanasto - Suorituskykymittareiden sanakirja
metric_config - yksittäisten mittareiden konfigurointi
metrinen - tarkkailtavan pyynnön tarkka mittari
metric_alert_history - suorituskykyvaroitusten historia
log_query - palvelutaulukko jäsennettyjen tietueiden tallentamiseen AWS:stä ladatusta PostgreSQL-lokitiedostosta
lähtötilanteessa - perustana käytetyn ajanjakson parametrit
tarkastusasema - mittareiden konfigurointi tietokannan tilan tarkistamista varten
checkpoint_alert_history - tietokannan tilan tarkistusmittareiden varoitushistoria
pg_stat_db_queries — aktiivisten pyyntöjen palvelutaulukko
aktiviteettiloki — toimintalokin palvelutaulukko
trap_oid - ansan konfigurointipalvelutaulukko

Vaihe 1 - kerää tehokkuustilastoja ja hanki raportteja

Taulukkoa käytetään tilastotietojen tallentamiseen. pg_stat_history
pg_stat_history taulukon rakenne

                                          Taulukko "public.pg_stat_history" Sarake | tyyppi | Muokkaimet--------------------------------------------- --+----- -------------------------------- id | kokonaisluku | not null oletus nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | aikaleima ilman aikavyöhykettä | tietokannan_tunnus | kokonaisluku | dbid | oid | käyttäjätunnus | oid | queryid | bigint | kysely | teksti | puhelut | bigint | yhteensä_aika | kaksinkertainen tarkkuus | min_aika | kaksinkertainen tarkkuus | max_time | kaksinkertainen tarkkuus | keskimääräinen_aika | kaksinkertainen tarkkuus | stddev_time | kaksinkertainen tarkkuus | riviä | bigint | share_blks_hit | bigint | share_blks_read | bigint | share_blks_dirtied | bigint | share_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 | kaksinkertainen tarkkuus | blk_write_time | kaksinkertainen tarkkuus | baseline_id | kokonaisluku | Hakemistot: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (tietokannan_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) F:ID_avain KEY tab. id) VIITTEET tietokanta(id ) POISTA KASKADI

Kuten näet, taulukko on vain kumulatiivista näkymätietoa pg_stat_statements kohdetietokannassa.

Tämän taulukon käyttö on hyvin yksinkertaista.

pg_stat_history edustaa kyselyn suorittamisen kertyneitä tilastoja jokaiselta tunnilta. Jokaisen tunnin alussa taulukon täytön jälkeen tilastot pg_stat_statements nollaa kanssa pg_stat_statements_reset().
Huom: tilastoja kerätään pyynnöistä, joiden kesto on yli 1 sekunti.
Täytetään pg_stat_history-taulukko

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

Tämän seurauksena tietyn ajan kuluttua taulukossa pg_stat_history meillä on sarja tilannekuvia taulukon sisällöstä pg_stat_statements kohdetietokanta.

Itseasiassa raportoiminen

Yksinkertaisten kyselyiden avulla voit saada varsin hyödyllisiä ja mielenkiintoisia raportteja.

Aggregoidut tiedot tietyltä ajanjaksolta

Tiedustelu

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

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

I/O-aika

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

TOP10 SQL:n kokonaisajan mukaan

Tiedustelu

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

--------------------------------------------------- ------------------------------------- | 10 TOP1 SQL:ää YHTEENSÄ SUORITUSAJALLE | #| queryid| puhelut| puhelut %| kokonaisaika (ms) | dbtime % +----+-----------+------------+-------------------- --------------------+----------- | 821760255| 2| 00001| .00|03:23.141:203141.681( 5.42 ms.)| 2 | 4152624390| 2| 00001| .00|03:13.929:193929.215( 5.17 ms.)| 3 | 1484454471| 4| 00001| .00|02:09.129:129129.057( 3.44 ms.)| 4 | 655729273| 1| 00000| .00|02:01.869:121869.981( 3.25 ms.)| 5 | 2460318461| 1| 00000| .00|01:33.113:93113.835( 2.48 ms.)| 6 | 2194493487| 4| 00001| .00|00:17.377:17377.868( 46 ms.)| .7 | 1053044345| 1| 00000| .00|00:06.156:6156.352( 16 ms.)| .8 | 3644780286| 1| 00000| .00|00:01.063:1063.830( 03 ms.)| .XNUMX

TOP10 SQL:n kokonaisI/O-ajan mukaan

Tiedustelu

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

--------------------------------------------------- --------------------------------------- | 10 TOP1 SQL:ää I/O-AJAN YHTEENSÄ | #| queryid| puhelut| puhelut %| I/O-aika (ms)|db I/O-aika % +----+-----------+-----------+------- -----+---------------------------------+------------ -- | 4152624390| 2| 00001| .00|08:31.616:511616.592( 31.06 ms.)| 2. kesäkuuta | 821760255| 2| 00001| .00|08:27.099:507099.036( 30.78 ms.)| 3 | 655729273| 1| 00000| .00|05:02.209:302209.137( 18.35 ms.)| 4 | 2460318461| 1| 00000| .00|04:05.981:245981.117( 14.93 ms.)| 5 | 1484454471| 4| 00001| .00|00:39.144:39144.221( 2.38 ms.)| 6 | 2194493487| 4| 00001| .00|00:18.182:18182.816( 1.10 ms.)| 7 | 1053044345| 1| 00000| .00|00:16.611:16611.722( 1.01 ms.)| 8 | 3644780286| 1| 00000| .00|00:00.436:436.205( 03 ms.)| .XNUMX

TOP10 SQL:n enimmäissuoritusajan mukaan

Tiedustelu

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

--------------------------------------------------- ------------------------------------- | 10 TOP1 SQL:ää MAX SUORITUSAIKALLA | #| tilannekuva| snapshotID| queryid| max_time (ms) +----+-------------------+------------+---------- --+----------------------------------------- | 05.04.2019| 01/03/4169 655729273:00| 02| 01.869| 121869.981:2:04.04.2019( 17 ms.) | 00| 4153 klo 821760255:00| 01| 41.570| 101570.841:3:04.04.2019( 16 ms.) | 00| 4146 klo 821760255:00| 01| 41.570| 101570.841:4:04.04.2019( 16 ms.) | 00| 4144 klo 4152624390:00| 01| 36.964| 96964.607:5:04.04.2019( 17 ms.) | 00| 4151 klo 4152624390:00| 01| 36.964| 96964.607:6:05.04.2019( 10 ms.) | 00| 4188 1484454471:00 | 01| 33.452| 93452.150:7:04.04.2019( 17 ms.) | 00| 4150 klo 2460318461:00| 01| 33.113| 93113.835:8:04.04.2019( 15 ms.) | 00| 4140 klo 1484454471:00| 00| 11.892| 11892.302:9:04.04.2019( 16 ms.) | 00| 4145 klo 1484454471:00| 00| 11.892| 11892.302:10:04.04.2019( 17 ms.) | 00| 4152 klo 1484454471:00| 00| 11.892| 11892.302:XNUMX:XNUMX( XNUMX ms.)

TOP10 SQL JAETUN puskurin luku/kirjoitus

Tiedustelu

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

--------------------------------------------------- ------------------------------------- | 10 TOP1 SQL:ää JAETUN PUSKURIN MUKAAN LUKEMINEN/KIRJOITUS | #| tilannekuva| snapshotID| queryid| jaetut lohkot lue| jaetut lohkot kirjoittaa +----+-------------------+------------+----------- -+---------------------+----------------------- | 04.04.2019| 17 klo 00:4153| 821760255| 797308| 0| 2 | 04.04.2019| 16 klo 00:4146| 821760255| 797308| 0| 3 | 05.04.2019| 01/03/4169 655729273:797158| 0| 4| 04.04.2019| 16 | 00| 4144 klo 4152624390:756514| 0| 5| 04.04.2019| 17 | 00| 4151 klo 4152624390:756514| 0| 6| 04.04.2019| 17 | 00| 4150 klo 2460318461:734117| 0| 7| 04.04.2019| 17 | 00| 4155 klo 3644780286:52973| 0| 8| 05.04.2019| 01 | 03| 4168/1053044345/52818 0:9| 04.04.2019| 15| 00| 4141 | 2194493487| 52813 klo 0:10| 04.04.2019| 16| 00| 4147 | 2194493487| 52813 klo 0:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX -------------------------------------------------- --------------------------------------------------

Kyselyn jakauman histogrammi suurimman suoritusajan mukaan

pyynnöt

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 | PUHELUT YHTEENSÄ : 33851920 | MINAIKA: 00:00:01.063 | MAKSIMIAIKA: 00:02:01.869 ---------------------------------- --------- ----------------------------- | min kesto| max kesto| puhelut +-----------------------------------+-------------- ---------------------+----------- | 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 tilannekuvaa kyselyn mukaan sekunnissa

pyynnöt

--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 tilannekuvaa QueryPerSeconds-numeroiden mukaan -------------------------------------- ------ -------------------------------------------- ------ -------------------------------------------- | #| tilannekuva| snapshotID| puhelut| yhteensä dbtime| QPS | I/O-aika | I/O-aika % +-----+-------------------+------------+-------- ----+-----------------------------------+----------- -+------------------------------------------------ | 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 klo 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 klo 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 klo 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 klo 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 klo 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 9| 05.04.2019/01/03 4167:4387191| 00| 06| 51.380:411380.293:609.332( 00 ms.)| 05| 18.847:318847.407:77.507( 10 ms.)| 04.04.2019 | 18| 01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX

Tuntikohtainen suoritushistoria QueryPerSecondsilla ja I/O-ajalla

Tiedustelu

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

Kaikkien SQL-valittujen tekstit

Tiedustelu

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

Koko

Kuten näet, melko yksinkertaisin keinoin voit saada paljon hyödyllistä tietoa työmäärästä ja tietokannan tilasta.

merkintä:Jos korjaat kyselytunnisteen kyselyissä, saamme historian erilliselle pyynnölle (tilan säästämiseksi erillisen pyynnön raportit jätetään pois).

Joten tilastotietoja kyselyn tehokkuudesta on saatavilla ja kerätty.
Ensimmäinen vaihe "tilastotietojen kerääminen" on saatu päätökseen.

Voit siirtyä toiseen vaiheeseen - "suorituskykymittareiden määrittäminen".


Mutta se on toinen tarina.

Jatkuu ...

Lähde: will.com