PostgreSQL päringute jõudluse jälgimine. Osa 1 - raportite koostamine

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

Või lugu sellest, miks andmebaasi administraator peab oma programmeerimise minevikku mäletama.

Eessõna

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

Garantiide loobumise klausel: planeeritavas artiklite tsüklis ei ole tabelite ja skriptide kasutamiseks detailselt ja täpselt kirjeldatud. Materjale ei saa kohe kasutada 'NII NAGU ON'.
Esiteks, suure materjalihulga tõttu,
teiseks, reaalse kliendi tootmisbaasi vähese kohandatuse tõttu.
Seega sisaldavad artiklid ainult ideid ja kirjeldusi kõige üldises vormis.
Võib-olla tulevikus jõuab süsteem tasemele, kus see avaldatakse GitHubis, aga võib-olla ka mitte. Aeg näitab.

Loo algus — "Kas sa mäletad, kuidas kõik algas».
Mis tulemusena sai, kõige üldisemalt — "Süntees kui üks meetod PostgreSQL töökindluse parandamiseks»

Miks ma seda kõike teen?

Esiteks, et mitte unustada, meenutades pensionipõlves toredaid aegu.
Teiseks, et süsteemida kirjutatut. Sest ma ise hakkan mõnikord segadusse minema ja unustama üksikuid osi.

Ja kõige tähtsam — ehk on kellelegi kasu ja aitab vältida ratta leiutamist ja reha kogumist. Teisisõnu, parandada oma karma (mitte Habr-aadi). Sest kõige väärtuslikum asi selles maailmas on ideed. Peamine on leida idee. Idee ellu viimine on juba puhtalt tehniline küsimus.

Nii et alustame, vaikselt...

Probleemi seadmine.

On olemas:

Andmebaas PostgreSQL (10.5), segatüüpi koormusega (OLTP+DSS), keskmise väikese koormusega, asub AWS-is.
Andmebaasi jälgimine puudub, infrastruktuuri jälgimine toimub AWS-i vaikimisi seadmete kaudu minimaalses konfiguratsioonis.

Nõutav:

Jälgida andmebaasi jõudlust ja seisundit, leida ja omada põhiteavet objektiivne, et optimeerida andmebaasis raskeid päringuid.

Lühiülevaade või lahenduste analüüs

Alustuseks proovime analüüsida lahenduste variante võrreldes kasude ja probleemidega insenerile, haldamise kasu ja kadusid las teevad need, kellel on vastavad ametikohad.

Variant 1 — "Töötamine nõudmisel"

Jätame kõik nii nagu on. Kui klient ei ole rahul andmebaasi või rakenduse töökindluse ja jõudlusega, teavitab ta DBA insenere e-posti teel või loob probleemipileti.
Insener, saades teate, uurib probleemi, pakub lahendust või lükkab probleemi edasi, lootes, et kõik laheneb iseenesest ning varsti unustatakse kõik.
Igal keedul on oma hind ja allakäinta.Igal keedul on oma hind ja allakäinta:
1. Midagi üleliigset tegema ei pea.
2. Alati on võimalus lohutada ja lahti libiseda.
3. Palju aega, mida saab kulutada isikliku meelelahutuse heaks.
Allakäinta ja keedu kõrvalmõjud:
1. Ühel või teisel hetkel hakkab klient mõtlema eksistentsi ja universumi õigluse olemusele ning küsib endalt jälle, milleks ta neile raha maksab? Tulemuseks on alati sama — küsimus on ainult selles, millal klient igavleb ja loobub. Siis jääb söögikoht tühjaks. See on kurb.
2. Inseneri areng on null.
3. Töö ja koormuse planeerimise raskused.

Variant 2 - "Tantsime rummidega, müüme ja paneme jalga".

Punkt 1- Miks meil on monitooringusüsteem, kõik, mida me saame, tulevad päringutega. Küllap käivitame mitmesuguseid päringuid andmesõnastikele ja dünaamilistele esitlusele, aktiveerime igasuguseid loendureid, koondame kõik tabelitesse, analüüsime perioodiliselt nimekirju ja tabeleid. Tulemuseks on ilusad või mitte nii ilusad graafikud, tabelid, aruanded. Peamine — et neid oleks rohkem, rohkem.
Punkt 2- Loome aktiivsust - käivitame selle kõikse analüüsimiseks.
Punkt 3- Koostame mingisuguse dokumendi, nimetame selle lihtsalt - "kuidas korraldada andmebaasi".
Punkt 4- Klient, nähes kõiki neid graafikute ja numbrite ilu, on lastesarnases naiivsuses kindel - nüüd hakkab meil kõik varsti tööle. Ta laseb hõlpsalt ja valutult lahti oma rahalistest vahenditest. Juhtkond on samuti kindel - meie insenerid töötavad tõhusalt. Koormus on maksimaalne.
Punkt 5- Korda Punkti 1 regulaarselt.
Igal keedul on oma hind ja allakäinta.Igal keedul on oma hind ja allakäinta:
1. Juhtide ja inseneride elu on lihtne, ettearvatav ja täis tegevust. Kõik sumiseb, kõik on hõivatud.
2. Kliendi elu pole samuti halb - ta on alati kindel, et peab vaid veidi ootama ja kõik laheneb. Kui ei lahe, no mis siis - see elu on ebaaus, järgmises elus — õnnestub.
Allakäinta ja keedu kõrvalmõjud:
1. Varem või hiljem leiab leidub kiiremini tegutsev teenusepakkuja, kes pakub sama teenust veidi odavamalt. Kui tulemus on sama, siis miks maksta rohkem? See viib omakorda toitmissüsteemi kadumiseni.
2. See on igav. Nagu igas mõttetuks tegevuses.
3. Nagu eelnevas variandis — areng puudub. Kuid inseneri jaoks on miinus see, et erinevalt esimesest variandist tuleb siin pidevalt genereerida andmebaasi. Ja see võtab aega. Aega, mida võiks enda kasuks kasutada. Sest kui ise enda eest ei hoolitse, ei hooli keegi sinu pärast.

Variant 3 - Ei pea ratast leiutama, vaid tuleb see osta ja sõita.

Teiste ettevõtete insenerid ei söö pitsa ja joo õlut ilma põhjuseta (ah, ülevad ajad Peterburis 90ndatel). Kasutame jälgimisse süsteeme, mis on loodud, testitud ja töötavad ning toovad tegelikult kasu ( vähemalt nende loojatele).
Igal keedul on oma hind ja allakäinta.Igal keedul on oma hind ja allakäinta:
1. Ei pea aega kulutama selle mõtletemise peale, mis on juba välja mõeldud. Vota ja kasuta.
2. Jälgimissüsteemid ei ole lollide loodud ja need on kindlasti kasulikud.
3. Tootvad jälgimissüsteemid annavad tavaliselt kasulikku filtreeritud teavet.
Allakäinta ja keedu kõrvalmõjud:
1. Insener ei ole antud juhul insener, vaid lihtsalt kellegi toote kasutaja. Või kasutaja.
2. Tellijat peab veenma vajaduses osta midagi, milles ta tegelikult ei tahaks ja ei peakski aru saama, ning aastabüüt on kinnitatud ja ei muutu. Siis peab eraldi ressurssi eraldama, mis tuleb kohandada konkreetse süsteemi järgi. St. Esiteks tuleb maksta, maksta ja veel kord maksta. Ja tellija on kitsi. See on elu norm.

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

Antud juhul ja tekkinud olukorras on võimalik teha veidi teistmoodi — aga teeme oma jälgimissüsteemi.

No ei ole süsteem, kindlasti mitte täies mõttes, see on liiga suurejooneline ja ennast täisõhustatud, aga kuidagi leevendada ülesannet ja koguda rohkem teavet jõudlusprobleemide 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 on see huvitavam kui pidevad "shrink datafile, alter tablespace, jne."
2. Need to acquire new skills and develop further. This will eventually yield well-deserved rewards and treats.
Miinused:
1. You will have to work. Work a lot.
2. You will have to regularly explain the meaning and prospects of all activities.
3. You will have to sacrifice something, as the only resource available to an engineer—time—is limited by the Universe.
4. The most terrifying and unpleasant part — as a result, it may turn into something like 'Neither a mouse, nor a frog, but an unknown creature.'

He who risks nothing, drinks no champagne.
So — the most interesting begins now.

The general idea — schematically

(The illustration is taken from the article «Süntees kui üks meetod PostgreSQL töökindluse parandamiseks»)

Explanation:

• In the target database, the standard PostgreSQL extension—'pg_stat_statements'—is installed.
• In the monitoring database, we create a set of service tables to store the history of 'pg_stat_statements' in the initial phase and for setting up metrics and monitoring later on.
• On the monitoring host, we create a set of bash scripts, including those for generating incidents in the ticketing system.

Service tables

To start, here's a schematic simplified ERD of what we ended up with:

Brief description of the tablesendpoint — host, connection point to the instance
database — database parameters
pg_stat_history — historical table for storing snapshots of the 'pg_stat_statements' view of the target database
metric_glossary — performance metrics glossary
metric_config — configuration of individual metrics
metric — specific metric for the query being monitored
metric_alert_history — history of performance alerts
log_query — service table for storing parsed entries from the PostgreSQL log file loaded from AWS
baseline — parameters of the time period used as a baseline
checkpoint — configuration of database state check metrics
checkpoint_alert_history — history of state check metrics alerts
pg_stat_db_queries — service table of active queries
activity_log — service activity log table
trap_oid — service configuration table for trap

Stage 1 — collecting statistical performance information and generating reports

The table serves to store statistical information pg_stat_history
Structure of the table 'pg_stat_history'

                                          Tabel "public.pg_stat_history"
       Veerg        |            Tüüp              |                          Modifikaatorid
---------------------+-----------------------------+-------------------------------------------
 id                  | integer                     | ei tohi olla tühi, vaikimisi järgmine väärtus ('pg_stat_history_id_seq'::regclass)
 snapshot_timestamp  | timestamp ilma ajatsoonita |
 database_id         | integer                     |
 dbid                | oid                         |
 userid              | oid                         |
 queryid             | bigint                      |
 query               | text                        |
 calls               | bigint                      |
 total_time          | topelt täpsus              |
 min_time            | topelt täpsus              |
 max_time            | topelt täpsus              |
 mean_time           | topelt täpsus              |
 stddev_time         | topelt täpsus              |
 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       | topelt täpsus              |
 blk_write_time      | topelt täpsus              |
 baseline_id         | integer                     |
Indexid:
    "pg_stat_history_pkey" PRIMARY KEY, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Välisvõtme piirangud:
    "database_id_fk" VÄLISVÕTI (database_id) VIITAB andmebaasile (id) KUSTUTAMISE KÄTTE

Nagu näha, on tabel lihtsalt kumulatiivne teave vaate kohta pg_stat_statements sihtandmebaasis.

Selle tabeli kasutamine on väga lihtne

pg_stat_history esindab kumulatiivset statistikat päringute täitmise kohta iga tunni jaoks. Iga tunni alguses, pärast tabeli täitmist, statistika pg_stat_statements nullitakse kasutades pg_stat_statements_reset().
Märkus: statistika kogutakse päringute jaoks, mille täitmise kestus on üle 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 'UUE SHAPSHOT LOODATAVAKS';
		
		--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 % ja dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Loome pg_stat_statements jaoks 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 'Loome pg_stat_statements jaoks päringute jaoks, mille min_time on suurem kui 1000ms';
	
        FOR pg_stat_snapshot IN
          --Kõik päringud, mille max_time on 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;

Seega, pärast teatud aja möödumist tabelis pg_stat_history on meil komplekt tabeli sisu pilte pg_stat_statements sihtandmebaasist.

Tegelikult raportimine

Lihtsaid päringuid kasutades on võimalik saada üsna kasulikke ja huvitavaid raporteid.

Kogutud andmed määratud ajavahemiku kohta

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 kokku aja järgi

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 TÄITMISE KOGUAEG
|   #|    queryid|      calls|    calls %|                total_time (ms) |  dbtime %
+----+-----------+-----------+-----------+--------------------------------+----------
|   1|  821760255|          2|     .00001|00:03:23.141(    203141.681 ms.)|      5.42
|   2| 4152624390|          2|     .00001|00:03:13.929(    193929.215 ms.)|      5.17
|   3| 1484454471|          4|     .00001|00:02:09.129(    129129.057 ms.)|      3.44
|   4|  655729273|          1|     .00000|00:02:01.869(    121869.981 ms.)|      3.25
|   5| 2460318461|          1|     .00000|00:01:33.113(     93113.835 ms.)|      2.48
|   6| 2194493487|          4|     .00001|00:00:17.377(     17377.868 ms.)|       .46
|   7| 1053044345|          1|     .00000|00:00:06.156(      6156.352 ms.)|       .16
|   8| 3644780286|          1|     .00000|00:00:01.063(      1063.830 ms.)|       .03

TOP10 SQL kokku I/O aja järgi

Päring

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 KOGU TÄIELIK I/O AEG
|   #|    queryid|      kõned|    kõnede %|                   I/O aeg (ms)|db I/O aege %
+----+-----------+-----------+-----------+--------------------------------+-------------
|   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

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 MAXIMAALNE TÄITMISAEG
|   #|          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 puhver lugemise/kirjutamise järgi

Päring

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

--------------------------------------------------------------------------------------------
| TOP10 SQL BY SHARED BUFFER READ/WRITE
|   #|          snapshot| snapshotID|    queryid|   shared blocks read|  shared blocks write
+----+------------------+-----------+-----------+---------------------+---------------------
|   1|  04.04.2019 17:00|       4153|  821760255|               797308|                    0
|   2|  04.04.2019 16:00|       4146|  821760255|               797308|                    0
|   3|  05.04.2019 01:03|       4169|  655729273|               797158|                    0
|   4|  04.04.2019 16:00|       4144| 4152624390|               756514|                    0
|   5|  04.04.2019 17:00|       4151| 4152624390|               756514|                    0
|   6|  04.04.2019 17:00|       4150| 2460318461|               734117|                    0
|   7|  04.04.2019 17:00|       4155| 3644780286|                52973|                    0
|   8|  05.04.2019 01:03|       4168| 1053044345|                52818|                    0
|   9|  04.04.2019 15:00|       4141| 2194493487|                52813|                    0
|  10|  04.04.2019 16:00|       4147| 2194493487|                52813|                    0
--------------------------------------------------------------------------------------------

Päringute jaotuse histogram maksimaalse täitmise aja järgi

Päringud

SELECT  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;

SELECT 
  SUM(calls) AS calls
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id =DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND 
  ( max_time >= hist_current_min AND  max_time < hist_current_max ) ;

|-----------------------------------------------------------------------------------------------
| MAX_TIME HISTOGRAM
| TOTAL CALLS : 33851920
| MIN TIME  : 00:00:01.063
| MAX TIME  : 00:02:01.869
---------------------------------------------------------------------------------
|                      min duration|                      max duration|     calls
+----------------------------------+----------------------------------+----------
| 00:00:01.063(      1063.830 ms.) | 00:00:13.144(     13144.445 ms.) | 9
| 00:00:13.144(     13144.445 ms.) | 00:00:25.225(     25225.060 ms.) | 0
| 00:00:25.225(     25225.060 ms.) | 00:00:37.305(     37305.675 ms.) | 0
| 00:00:37.305(     37305.675 ms.) | 00:00:49.386(     49386.290 ms.) | 0
| 00:00:49.386(     49386.290 ms.) | 00:01:01.466(     61466.906 ms.) | 0
| 00:01:01.466(     61466.906 ms.) | 00:01:13.547(     73547.521 ms.) | 0
| 00:01:13.547(     73547.521 ms.) | 00:01:25.628(     85628.136 ms.) | 0
| 00:01:25.628(     85628.136 ms.) | 00:01:37.708(     97708.751 ms.) | 4
| 00:01:37.708(     97708.751 ms.) | 00:01:49.789(    109789.366 ms.) | 2
| 00:01:49.789(    109789.366 ms.) | 00:02:01.869(    121869.981 ms.) | 0

TOP10 Läbipaistvused päringu sekundis

Päringud

--pg_qps.sql
--Küsimuste arvu sekundis arvutamine 
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 - pg_stat_history ei leitud id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id  0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

|-----------------------------------------------------------------------------------------------
| TOP10 Snapshotid päringu sekundite järgi
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snapshot| snapshotID|      kõned|                      tot. db aeg|        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 päringu sekundite ja I/O aja järgi

Päring

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

|-----------------------------------------------------------------------------------------------
| TUNNITÄITSE KÄIVITAMISE AJALUGU QUERY PER SECOND JA I/O AEG
-----------------------------------------------------------------------------------------------------------------------------------------------
| KÜSIMUSI PER SEKUND AJA AJALUGU
|    #|          hetkepilt| hetkepildi ID|      kõned|                      kogu DB aeg|        KPS|                          I/O aeg| I/O aeg %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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õigi SQL-selectide tekst

Päring

VALI 
  küsitlusid , 
  küsitlus 
FROM 
  pg_stat_history
WHERE 
  küsitlusid ON KINNITATUD JA 
  andmebaasi_id = DATABASE_ID  JA
  hetkeseis BETWEEN BEGIN_TIMEPOINT JA END_TIMEPOINT
GROUP BY küsitlusid , küsitlus

Kokkuvõte

Kuidas näha, on üsna lihtsate vahenditega võimalik saada piisavalt palju kasulikku teavet andmebaasi koormuse ja seisundi kohta.

Märkus:Kui päringutes fikseerida küsitlusid, saame ajaloo eraldi päringu kohta (ruumi säästmiseks on eraldi päringute aruanne välja jäetud).

Seega on olemas ja kogutakse päringute jõudluse statistikat.
Esimene etapp "statistika kogumine" on lõpetatud.

Võime minna teisele etapile - „jõudluse mõõdikute seadistamine“.


Aga see on juba hoopis teine lugu.

Jätkub…

Allikas: habr.com