🥇PostgreSQL-i päringute jõudluse jälgimine. Osa 1 — aruandlus

Insener — ladina keelest tõlgituna — inspireeritud.
Insener suudab kõike. (c) R.Dizel.
Epigrammid.

Või lugu sellest, miks andmebaasi administraator peab meenutama oma programmeerija minevikku.

Eessõna

Kõik nimed on muudetud. Kokkusattumised on juhuslikud. Materjal esindab autori isiklikku arvamust.

Garantiiväärdenyse lahtiseletus: planeeritavas artiklite tsüklis ei ole põhjalikku ja täpset kirjeldust kasutatud tabelite ja skriptide kohta. Materjale ei saa kohe kasutada "NII NAGU ON".
Esiteks, materjali suuruse tõttu,
teiseks, kuna see on suunatud reaalse kliendi prodaktsiooni baasi.
Seetõttu sisaldavad artiklid vaid ideid ja kirjeldusi üldiselt.
Võib-olla tulevikus süsteem kasvab selleni, et see tõstatatakse GitHubi, aga võib-olla ka mitte. Aeg näitab.

Loo algus — "Kas sa mäletad, kuidas kõik algas».
Mis tulemuseks sai, kõige üldisemates joontes — "Süntes kui üks meetod PostgreSQLi jõudluse parandamiseks»

Miks mul seda kõike vaja on?

No, esmalt, et ise ei unustaks, meenutades pensionile jäädes hiidpäevi.
Teiseks, et süstematiseerida kirjutatut. Sest isegi mina, mõnikord, hakkan segadusse minema ja unustan eraldi osad.

Ja mis kõige tähtsam — äkki on see kellelegi kasulik ja aitab vältida rattaga taassünni ja naelu korjamist. Teisisõnu, parandada oma kar maha (mitte Habr'i oma). Sest kõige väärtuslikum asi selles maailmas on ideed. Peamine on leida idee. Idee teostamine reaalsuses on juba puhtalt tehniline küsimus.

Nii et, alustame, tasapisi...

Probleemi seadmine.

On olemas:

Andmebaas PostgreSQL (10.5), segatud koormusega (OLTP+DSS), keskmise-väikese koormusega, mis asub AWS pilves.
Andmebaasi jälgimine puudub, infrastruktuuri jälgimine on esindatud AWS standardsete vahenditega minimaalsetes seadistustes.

Nõutav:

Jälgida andmebaasi jõudlust ja seisundit, leida ja omada algandmeid raskete päringute optimeerimiseks andmebaasile.

Lühike eelõhtu või lahenduste variandid

Kuna, proovime kõigepealt lahenduse võimalusi lahata võrdleva analüüsi valguses, kasu ja probleemidega insenerile, samas kui kasumi ja kahjude üle muretsegu need, kellele see kuulub vastavalt ametijuhendile.

Variant 1 - «Töötame nõudmisel»

Jätame kõik nii nagu on. Kui tellija ei ole rahul andmebaasi või rakenduse töövõime või jõudlusega, teavitab ta DBA insenere e-posti teel või loob probleemist päringu tiketikaupa.
Insener, olles teate saanud, uurib probleemi, pakub lahendust või lükkab probleemi edasisse tulevikku, lootes, et see lahendatakse iseenesest ning varsti unustatakse ikkagi.
Küpsised ja pannkoogid, sinikad ja muhkudKüpsised ja pannkoogid:
1. Ei ole vaja midagi liigset teha.
2. Alati on võimalus vabandada ja kergekäeliselt asja käsitleda.
3. Üksjagu aega, mida saab kasutada omal soovil.
Sinikad ja muhkud:
1. Varsti või hiljem hakkab tellija mõtlema eluteemale ja universumliku õiguse olemusele ning esitab endale taas küsimuse — milleks ma maksan neile oma raha? Tagajärg on alati sama — küsimus on vaid selles, millal tellija kaotab huvi ja loobub. Ja toiduahela tühjaks jääb. See on kurb.
2. Inseneri areng — null.
3. Töö planeerimise ja koormuse keerukused.

Variant 2 - «Tantsime trummide ümber, müüme ja varustame»

Punkt 1-Miks me vajame seire süsteemi? Saame kõik päringud. Käivitame erinevaid päringuid andmebaasi ja dünaamiliste esitusviiside kaudu, aktiveerime mitmesuguseid loendureid, koondame kõik tabelitesse ja perioodiliselt analüüsime neid. Tulemuseks on ilusad või vähem ilusad graafikud, tabelid ja raportid. Peamine on, et neid oleks palju, palju.
Punkt2-Käivitame aktiivsuse - teeme kogu selle analüüsi.
Punkt3-Valmistame ette mingi dokumendi, kutsume seda lihtsalt - «kuidas korraldada andmebaasi».
Punkt4-Kliendi nähes seda graafikute ja numbrite ilu usub lapseoomes naiivsuses, et nüüd kõik hakkab tööle, varsti. Ja loobub lihtsalt ja valutult oma rahalistest ressurssidest. Juhtkond on samuti kindel - meie insenerid teevad head tööd. Koormus on maksimaalne.
Punkt5-Korrata regulaarselt punkti 1.
Küpsised ja pannkoogid, sinikad ja muhkudKüpsised ja pannkoogid:
1. Juhtide ja inseneride elu on lihtne, ettearvatav ja täis aktiivsust. Kõik sumiseb, kõik on hõivatud.
2. Klientide elu ei ole samuti halb — nad on alati kindlad, et tuleb vaid veidi oodata ja kõik laabub. Kui see ei toimi, noh, mis siis — maailm on ebaõiglane, järgmises elus on ehk vedamine.
Sinikad ja muhkud:
1. Varsti või hiljem leitakse kiirem teenusepakkuja, kes teeb sama asja pisut odavamalt. Ja kui tulemus on sama, miks maksta rohkem? See viib jälle toidupauside kadumiseni.
2. See on igav. Nagu igasugune vähe mõtestatud tegevus.
3. Nagu eelnevas variandis — arengut ei toimu. Kuid inseneri jaoks on miinus see, et eristavalt esimese variandi puhul tuleb siin pidevalt genereerida andmebaasi. See õtab aega. Mille võiks kulutada enda kasuks. Sest kui sa enda eest ei hoolitse, siis ei hooli keegi sinust.

Variant 3 — pole vaja leiutada jalgratast, tuleb see osta ja sõita.

Teiste firmade insenerid ei söö pitsa ja joo õlut asjata (ah, need head ajad Peterburi 90ndatel). Kasutame juba olemasolevaid monitooringusüsteeme, mis on välja töötatud, tööd ja toovad üldiselt kasu (noh, vähemalt nende loojatele).
Küpsised ja pannkoogid, sinikad ja muhkudKüpsised ja pannkoogid:
1. Ei ole vajalik kulutada aega sellele, mis on juba välja mõeldud. Lihtsalt võta ja kasuta.
2. Monitooringusüsteemide loomine ei ole lollide teema; nad on kindlasti kasulikud.
3. Tootlikud monitooringusüsteemid annavad tavaliselt kasulikku filtreeritud teavet.
Sinikad ja muhkud:
1. Insener ei ole siin insener, vaid lihtsalt kellegi teise toote kasutaja. Või kasutaja.
2. Tellijat tuleb veenda, et on vaja osta midagi, millesse ta ei soovi süveneda, ning mis tal ei peaks olema ka kohustuseks, ning aastabürot on kinnitatud ja ei muutu. Seejärel tuleb eraldi ressursid eraldada ja need konkreetse süsteemi jaoks seadistada. Ehk siis kõigepealt tuleb maksta, maksta ja veel kord maksta. Tellija on aga kitsi. See on elu norm.

Mis siis teha - Tšernõševski? Sinu küsimus on täiesti kohane. (c)

Antud konkreetses olukorras ja olemasolevate asjaolude tõttu on võimalik tegutseda veidi teisiti — aga loome omaenda monitooringusüsteemi.

Muidugi mitte süsteemi täielikus mõttes, see oleks liiga suurejooneline ja enesekehtestav, kuid vähemalt võiks endale ülesande lihtsamaks teha ning koguda rohkem teavet jõudluse probleemide lahendamiseks. Et mitte sattuda olukorda — „mine sinna, ei tea kuhu, leia see, ei tea mida.”

Millised on selle variandi plussid ja miinused:

Plussid:
1. See on huvitav. No vähemalt huvitavam kui pidevad „shrink datafile, alter tablespace jne.”
2. Need on uued oskused ja uus areng. Mis tulevikus varem või hiljem toob välja teenitud preemiad ja maiustused.
Miinused:
1. Töö tuleb teha. Töö tuleb palju teha.
2. Tuleb regulaarselt seletada, mis on kogu tegevuse mõte ja perspektiivid.
3. Millegagi tuleb ohverdada, kuna inseneri ainus saadaval olev ressurss — aeg — on Universumi piiratud.
4. Kõige hullem ja kõige ebameeldivam — võib tulemuseks olla midagi, mis on „Ei hiirepojake, ei konn, vaid tundmatu loom.”

Kes ei riski, see ei joo šampanjat.
Nii et — algab kõige huvitavam osa.

Üldine idee — skeemiliselt

(Illustratsioon on võetud artiklist «Süntes kui üks meetod PostgreSQLi jõudluse parandamiseks»)

Selgitus:

Sihtandmebaasi installitakse PostgreSQLi standaardne laiendus — „pg_stat_statements.”
Jälgimisandmebaasis loome teenuste tabelite komplekti pg_stat_statements ajaloo salvestamiseks algfaasis ning edaspidiste mõõdikute ja jälgimise seadistamiseks.
Jälgimisseadmestikus loome bash-skriptide komplekti, sealhulgas tõrgete genereerimiseks pileti süsteemis.

Teenustabelid

Alustuseks skeemiliselt lihtsustatud ERD, mis me lõpuks saime:

Tabelite lühike kirjeldusendpoint — host, ühenduspunkt instantsiga
database — andmebaasi parameetrid
pg_stat_history — ajaline tabel, mis salvestab ajutised näidud pg_stat_statements vaates sihtandmebaasist
metric_glossary — jõudluse mõõdikute sõnastik
metric_config — konkreetsete mõõdikute konfiguratsioon
metric — konkreetne mõõdik jälgitavale päringule
metric_alert_history — jõudluse hoiatusite ajalugu
log_query — teenustabel, mis salvestab PostgreSQL logifailist analüüsitud kirjeid, mis laaditakse AWSt
baseline — ajavahemiku parameetrid, mida kasutatakse baasi alusena
checkpoint — andmebaasi olekukontrolli mõõdikute konfiguratsioon
checkpoint_alert_history — andmebaasi olekukontrolli mõõdikute hoiatusite ajalugu
pg_stat_db_queries — aktiivsete päringute teenustabel
activity_log — aktiivsuse logi teenustabel
trap_oid — trap konfiguratsiooni teenustabel

Etapp 1 — kogume jõudluse statistikat ja saame aruanded

Statistika talletamiseks kasutatakse tabelit pg_stat_history
pg_stat_history tabeli struktuur

                                          Tabel "public.pg_stat_history"
       Veerg        |            Tüüp             |                          Muutujad
---------------------+-----------------------------+-------------------------------------------
 id                  | täisarv                     | mitte null vaikimisi nextval('pg_stat_history_id_seq'::regclass)
 snapshot_timestamp  | ajatemperatuur ilma ajavööndita |
 database_id         | täisarv                     |
 dbid                | oid                         |
 userid              | oid                         |
 queryid             | suur arv                   |
 query               | tekst                       |
 calls               | suur arv                   |
 total_time          | kahekordne täpsus          |
 min_time            | kahekordne täpsus          |
 max_time            | kahekordne täpsus          |
 mean_time           | kahekordne täpsus          |
 stddev_time         | kahekordne täpsus          |
 rows                | suur arv                   |
 shared_blks_hit     | suur arv                   |
 shared_blks_read    | suur arv                   |
 shared_blks_dirtied | suur arv                   |
 shared_blks_written | suur arv                   |
 local_blks_hit      | suur arv                   |
 local_blks_read     | suur arv                   |
 local_blks_dirtied  | suur arv                   |
 local_blks_written  | suur arv                   |
 temp_blks_read      | suur arv                   |
 temp_blks_written   | suur arv                   |
 blk_read_time       | kahekordne täpsus          |
 blk_write_time      | kahekordne täpsus          |
 baseline_id         | täisarv                     |
Indeksid:
    "pg_stat_history_pkey" PRIIMARNE VÕTI, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Välisvõtmiste piirangud:
    "database_id_fk" VÄLISVÕTI (database_id) VIITAB andmebaas(id) KUSTUTA CASCADE

Nagu näha, esitab tabel lihtsalt kumulatiivseid andmeid kuvamisest pg_stat_statements sihtandmebaasis.

Selle tabeli kasutamine on väga lihtne

pg_stat_history ja see esindab iga tunni jooksul kogunenud päringute täitmise statistikat. Iga tunni alguses, pärast tabeli täitmist, statistika pg_stat_statements nulldatakse abil pg_stat_statements_reset().
Märkus: statistika kogutakse päringute jaoks, mille täitmise kestus ületab 1 sekundi.
Tabeli täitmine 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 'LOODUS ALLA KASUTATAVAD';
		
		--Ühenda siht DB-ga	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % ja dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Loodud snapshoot pg_stat_statements andmebaasi %',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 'Loodud snapshoot pg_stat_statements kysimustega, mille min_time on suurem kui 1000ms';
	
        FOR pg_stat_snapshot IN
          --Kõik kysimused maks_time suurem kui 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;

Seetõttu, pärast teatud perioodi tabelis pg_stat_history on meil tabeli sisu kogum pg_stat_statements sihtandmebaasist.

Tegelikult raportimine

Lihtsate päringute abil on võimalik saada üsna kasulikke ja huvitavaid aruandeid.

Kogutud andmed määratud ajavahemiku jooksul

Päring

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 aeg

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

I/O aeg

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 koguaegade põhjal

Päring

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 KOKKU EXECUTION AEG
|   #|    queryid|      kutseid|    kutseid %|                kokku_aeg (ms) |  db_time %
+----+-----------+-----------+-----------+--------------------------------+----------
|   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 kokku I/O aeg

Päring

VALI 
  queryid , 
  SUM(kutseid) AS kutseid ,
  SUM(blk_lugemise_aeg + blk_kirjutamise_aeg)  AS io_aeg
KUST 
  pg_stat_historia
KUS 
  queryid EI OLE NULL JA 
  andmebaasi_id = ANDMEBAASI_ID  JA
  hetk_tempus BETWEEN ALGUS_AEG ja LÕPP_AEG
RÜHMITA QUERYID 
KORDA 3 DESC 
PIIRANG 10

----------------------------------------------------------------------------------------
| TOP10 SQL KOGUSE I/O AEG ASJA
|   #|    queryid|      kutsed|    kutsed %|                   I/O aeg (ms)|db I/O aeg %
+----+-----------+-----------+-----------+--------------------------------+-------------
|   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 maksimaalse täitmise aja järgi

Päring

VALI 
  id NIMETAMISEKS , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
KUSTUTA 
  pg_stat_history 
KUS 
  queryid EI OLE NULL JA 
  database_id = DATABASE_ID  JA
  snapshot_timestamp VAHEMAL BEGIN_TIMEPOINT JA END_TIMEPOINT
KORDA 4 DESC 
PIIRANG 10

-----------------------------------------------------------------------------------------
| TOP10 SQL MAXIMAALNE SOORDEMI AEG
|   #|          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 jagatud puhvri lugemine/kirjutamine

Päring

VALI 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
KUST 
  pg_stat_history
KUS 
  queryid EI OLE NULL JA 
  database_id = DATABASE_ID  JA
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT JA END_TIMEPOINT JA
  ( shared_blks_read > 0 VÕI shared_blks_written > 0 )
KORDA 4 ALLA  , 5 ALLA 
PIIRA 10

--------------------------------------------------------------------------------------------
| TOP10 SQL ÖHIKUDE JAGAMISSE PÄRAST LOETLETUD BLOKKE
|   #|          hetkepilt| hetkepildi ID|    päringu ID|   jagatud blokeeritud lugemine|  jagatud blokeeritud kirjutamine
+----+------------------+-----------+-----------+---------------------+---------------------
|   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
--------------------------------------------------------------------------------------------

Päringute jaotumise histogramm maksimaalse täitmise aja järgi

Päringud

VALI  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) AS hist_width
KUSTA  
  pg_stat_history 
KUS  
  queryid EI OLE NULL JA
  database_id = DATABASE_ID  JA
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT JA END_TIMEPOINT ;

VALI  
  SUM(calls) AS calls
KUSTA  
  pg_stat_history 
KUS  
  queryid EI OLE NULL JA
  database_id = DATABASE_ID  JA
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT JA END_TIMEPOINT JA 
  ( max_time >= hist_current_min JA  max_time < hist_current_max ) ;

|-----------------------------------------------------------------------------------------------
| MAX_TIME HISTOGRAM
| KOKKU KÕNE : 33851920
| MIN AEG  : 00:00:01.063
| MAX AEG  : 00:02:01.869
---------------------------------------------------------------------------------
|                      min kestus|                      max kestus|     kõned
+----------------------------------+----------------------------------+----------
| 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 KOHANDUSED PÄEVAKÜSIMUSI

Päringud

--pg_qps.sql
--Küsimuste arv sekundis
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 'VIGA - pole leidnud pg_stat_history id-ga = %',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 tellitud QPS numbrite järgi
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O aeg| I/O aeg %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Tunnine täitmise ajalugu QueryPerSeconds ja I/O aja järgi

Päring

VALI 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( vali pg_qps( id )) KPS ,
  blk_read_time ,
  blk_write_time
KUST 
  pg_stat_history
KUS 
  queryid ON NULL JA 
  database_id = DATABASE_ID  JA
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT JA END_TIMEPOINT
KORDA 2

|-----------------------------------------------------------------------------------------------
| TUNNID AJAOTAMISE AJALUGU QueryPerSeconds ja I/O Aega
-----------------------------------------------------------------------------------------------------------------------------------------------
| KÜSIMUS PER SEKUND AJALUGU
|    #|          hetk| hetkID|      kõned|                      koguaeg|        QPS|                          I/O aeg| I/O aja %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Kõik SQL-valikute tekst

Päring

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

Kokkuvõte

Nagu näha, on üsna lihtsate vahenditega võimalik saada palju kasulikku teavet andmebaasi koormusest ja olekust.

Märkus:Kui päringutes fikseerida queryid, saame ajaloo iga päringu kohta (ruumi säästmise eesmärgil on eraldi päringu kohta aruanded vahele jäetud).

Nii et päringu tulemuslikkuse statistika on olemas ja kogutakse.
Esimene etapp 'statistika kogumine' on lõpetatud.

Saame liikuda teise etapi, 'tulemuse mõõdikute seadistamise' juurde.

Aga see on juba hoopis teine lugu.