ProHoster > Blog > Bestjoer > Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje

Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje

Yngenieur - oerset út it Latyn - ynspirearre.
In yngenieur kin alles dwaan. (c) R. Diesel.
Epigraphs.
Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje
Of in ferhaal oer wêrom't in databasebehearder syn programmearring ferline moat ûnthâlde.

Foarwurd

Alle nammen binne feroare. Wedstriden binne willekeurich. It materiaal is allinich de persoanlike miening fan 'e auteur.

Disclaimer fan garânsjes: yn 'e plande searje fan artikels sil d'r gjin detaillearre en krekte beskriuwing wêze fan' e brûkte tabellen en skripts. Materialen kinne net fuortendaliks brûkt wurde "AS IS".
Earst, fanwege de grutte hoemannichte materiaal,
twadde, fanwege de skerpte mei de produksje basis fan in echte klant.
Dêrom sille allinich ideeën en beskriuwingen yn 'e meast algemiene foarm wurde jûn yn' e artikels.
Miskien yn 'e takomst sil it systeem groeie nei it nivo fan pleatsing op GitHub, of miskien net. De tiid sil sjen litte.

Begjin fan it ferhaal-Unthâlde jo hoe't it allegear begûn".
Wat barde as gefolch, yn 'e meast algemiene termen - "Synteze as ien fan 'e metoaden om PostgreSQL-prestaasjes te ferbetterjen»

Wêrom haw ik dit alles nedich?

No, earst, om josels net te ferjitten, oantinken oan de glorieuze dagen yn pensjoen.
Yn it twadde plak, it systematisearjen fan wat skreaun is. Foar mysels al begjin ik soms yn 'e war te reitsjen en aparte dielen te ferjitten.

No, en it wichtichste - ynienen kin it foar ien fan pas komme en helpe om it tsjil net opnij út te finen en gjin rake te sammeljen. Mei oare wurden, ferbetterje dyn karma (net Khabrovsky). Foar it meast weardefolle ding yn dizze wrâld is ideeën. It wichtichste is om in idee te finen. En om it idee yn werklikheid te meitsjen is al in suver technysk probleem.

Dus litte wy stadich begjinne ...

Formulearring fan it probleem.

Beskikber:

PostgreSQL(10.5), mingde lading (OLTP + DSS), medium oant lichte lading, hosted yn 'e AWS-wolk.
D'r is gjin databankmonitoring, ynfrastruktuermonitoring wurdt presintearre as standert AWS-ark yn in minimale konfiguraasje.

It is fereaske:

Kontrolearje de prestaasjes en status fan 'e databank, fyn en hawwe earste ynformaasje om swiere databankfragen te optimalisearjen.

Koarte ynlieding of analyze fan oplossings

Om te begjinnen, lit ús besykje te analysearjen de opsjes foar it oplossen fan it probleem út it eachpunt fan in ferlykjende analyze fan de foardielen en problemen foar de yngenieur, en lit dejingen dy't moatte wêze op 'e personiel list omgean mei de foardielen en ferliezen fan behear.

Opsje 1 - "Op fraach wurkje"

Wy litte alles sa't it is. As de klant net tefreden is mei wat yn 'e sûnens, prestaasjes fan' e databank of applikaasje, sil hy de DBA-yngenieurs ynformearje fia e-post of troch in ynsidint te meitsjen yn 'e kaartsje.
In yngenieur, dy't in notifikaasje krige, sil it probleem begripe, in oplossing oanbiede, of it probleem opslaan, yn 'e hoop dat alles himsels oplost, en yn elk gefal sil alles gau fergetten wurde.
Gingerbread en donuts, kneuzingen en bultenGingerbread en donuts:
1. Neat ekstra te dwaan
2. Der is altyd de kâns om út te kommen en smoarch te wurden.
3. In protte tiid dat jo kinne besteegje op jo eigen.
Kneuzingen en bulten:
1. Ier of letter, de klant sil tinke oer de essinsje fan wêzen en universele gerjochtichheid yn dizze wrâld en nochris freegje himsels de fraach - wêrom bin ik betelje harren myn jild? De konsekwinsje is altyd itselde - de iennichste fraach is wannear't de klant ferfeelt en ôfskie swaaide. En de feeder is leech. It is tryst.
2. De ûntwikkeling fan in yngenieur is nul.
3. Swierrichheden by it plannen fan wurk en laden

Opsje 2 - "Dûnsje mei tamboerinen, skuon oan en oan"

Paragraaf 1-Wêrom hawwe wy in tafersjochsysteem nedich, wy sille alle oanfragen ûntfange. Wy lansearje in boskje allerhanne fragen nei it gegevenswurdboek en dynamyske werjeften, skeakelje allerhanne tellers oan, bringe alles yn tabellen, analysearje periodyk as it wie listen en tabellen. As gefolch hawwe wy prachtige of net heul grafiken, tabellen, rapporten. It wichtichste ding - dat soe mear, mear.
Paragraaf 2- Aktiviteit generearje - de analyze fan dit alles útfiere.
Paragraaf 3-Wy tariede in bepaald dokumint, wy neame dit dokumint, gewoan - "hoe kinne wy ​​equip de databank."
Paragraaf 4- De klant, dy't al dizze pracht fan grafiken en sifers sjocht, is yn in bernich naïve fertrouwen - no sil alles foar ús wurkje, gau. En, maklik en pynlik diel mei har finansjele middels. Management is der ek wis fan dat ús yngenieurs hurd wurkje. Max laden.
Paragraaf 5- Werhelje stap 1 regelmjittich.
Gingerbread en donuts, kneuzingen en bultenGingerbread en donuts:
1. It libben fan managers en yngenieurs is ienfâldich, foarsisber en fol mei aktiviteit. Alles gûlt, elkenien is drok.
2. It libben fan 'e klant is ek net min - hy is altyd wis dat jo in bytsje geduld moatte wêze en alles sil goed komme. Net better wurde, no, goed - dizze wrâld is ûnrjochtfeardich, yn it folgjende libben - gelok.
Kneuzingen en bulten:
1. Ier of letter sil d'r in slimmer oanbieder wêze fan in ferlykbere tsjinst dy't itselde ding dwaan sil, mar in bytsje goedkeaper. En as it resultaat is itselde, wêrom betelje mear. Wat wer sil liede ta it ferdwinen fan de feeder.
2. It is saai. Hoe saai elke lytse betsjuttingsfolle aktiviteit.
3. Lykas yn 'e foarige ferzje - gjin ûntwikkeling. Mar foar in yngenieur is it minus dat, yn tsjinstelling ta de earste opsje, hjir konstant in IDB moatte generearje. En dat kostet tiid. Wat kin wurde bestege foar it foardiel fan jo leafste. Want do kinst net soargje foar dysels, elkenien soarget foar dy.

Opsje 3-Gjin needsaak om in fyts út te finen, jo moatte it keapje en ride.

Yngenieurs fan oare bedriuwen ite bewust pizza mei bier (oh, de glorieuze tiden fan Sint Petersburch yn 'e jierren '90). Litte wy tafersjochsystemen brûke dy't binne makke, debuggen en wurkje, en yn 't algemien bringe se foardielen (goed, teminsten foar har makkers).
Gingerbread en donuts, kneuzingen en bultenGingerbread en donuts:
1. Gjin needsaak om tiid te fergrieme mei it útfine wat al útfûn is. Nim en brûke.
2. Monitoring systemen wurde net skreaun troch dwazen, en fansels binne se nuttich.
3. Working monitoring systemen jouwe meastentiids brûkbere filtere ynformaasje.
Kneuzingen en bulten:
1. De yngenieur yn dit gefal is gjin yngenieur, mar gewoan in brûker fan in oar syn produkt, of in brûker.
2. De klant moat oertsjûge wurde fan 'e needsaak om wat te keapjen dat hy oer it algemien net wol begripe, en hy moat net, en yn' t algemien is it budzjet foar it jier goedkard en sil net feroarje. Dan moatte jo in aparte boarne tawize, it konfigurearje foar in spesifyk systeem. Dy. Earst moatte jo betelje, betelje en wer betelje. En de klant is smel. Dit is de noarm fan dit libben.

Wat te dwaan, Chernyshevsky? Jo fraach is heul pertinint. (Mei)

Yn dit bysûndere gefal en de hjoeddeistige situaasje kinne jo in bytsje oars dwaan - lit ús ús eigen tafersjochsysteem meitsje.
Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje
No, net in systeem, fansels, yn 'e folsleine betsjutting fan it wurd, dit is te lûd en fermoedlik, mar op syn minst ien of oare manier meitsje it makliker foar josels en sammelje mear ynformaasje te lossen prestaasjes ynsidinten. Om josels net yn in situaasje te finen - "gean dêrhinne, ik wit net wêr, fyn dat, ik wit net wat."

Wat binne de foar- en neidielen fan dizze opsje:

Pros:
1. It is nijsgjirrich. No, op syn minst nijsgjirriger dan de konstante "datafile krimp, tabelromte feroarje, ensfh."
2. Dit binne nije feardichheden en nije ûntwikkeling. Dy't yn 'e takomst, ier of let, wol fertsjinne peperkoeken en donuts sil jaan.
Cons:
1. Moatte wurkje. Wurkje in protte.
2. Jo moatte regelmjittich de betsjutting en perspektiven fan alle aktiviteiten útlizze.
3. Der sil wat opoffere wurde moatte, om't de ienige boarne beskikber foar de yngenieur - tiid - wurdt beheind troch it Universum.
4. De slimste en meast onaangenaam - as gefolch kin jiskefet as "Net in mûs, gjin kikkert, mar in ûnbekend lyts bist" útdraaie.

Wa't net wat riskeart, drinkt gjin sjampanje.
Dus, de wille begjint.

Algemiene idee - skematyske

Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje
(Yllustraasje nommen út artikel «Synteze as ien fan 'e metoaden om PostgreSQL-prestaasjes te ferbetterjen")

Taljochting:

  • De doeldatabase is ynstalleare mei de standert PostgreSQL-útwreiding "pg_stat_statements".
  • Yn 'e monitoaringsdatabase meitsje wy in set tsjinsttabellen om de skiednis fan pg_stat_statements op te slaan yn' e earste faze en om metriken en monitoaring yn 'e takomst te konfigurearjen
  • Op de tafersjochhost meitsje wy in set bash-skripts, ynklusyf dy foar it generearjen fan ynsidinten yn it kaartsjesysteem.

Service tabellen

Om te begjinnen, in skematysk ferienfâldige ERD, wat barde op it ein:
Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje
Koarte beskriuwing fan de tabelleneinpunt - host, ferbiningspunt nei it eksimplaar
database - database opsjes
pg_stat_histoarje - histoaryske tabel foar it bewarjen fan tydlike snapshots fan 'e werjefte fan pg_stat_statements fan' e doeldatabase
metric_glossary - Wurdboek fan prestaasjesmetriken
metric_config - konfiguraasje fan yndividuele metriken
metryske - in spesifike metrik foar it fersyk dat wurdt kontrolearre
metric_alert_history - skiednis fan prestaasjes warskôgings
log_query - tsjinsttabel foar it bewarjen fan parsearde records fan it PostgreSQL-logbestân ynladen fan AWS
basislinen - parameters fan 'e tiidperioade brûkt as basis
kontrôlepunt - konfiguraasje fan metriken foar it kontrolearjen fan de status fan 'e databank
checkpoint_alert_history - warskôgingsskiednis fan metriken foar kontrôle fan databasestatus
pg_stat_db_queries - tsjinsttabel fan aktive oanfragen
aktiviteiten Lochboek - aktiviteit log tsjinst tafel
trap_oid - trap konfiguraasje tsjinst tafel

Fase 1 - sammelje prestaasjesstatistiken en krije rapporten

In tabel wurdt brûkt om statistyske ynformaasje op te slaan. pg_stat_histoarje
pg_stat_history tabelstruktuer

                                          Tabel "public.pg_stat_history" Kolom | type | Modifiers ----------------------+-----------------------------------------id | hiele getal | not null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | tiidstempel sûnder tiidsône | database_id | hiele getal | dbid | oid | brûkersid | oid | queryid | grut | fraach | tekst | ropt | grut | totale_tiid | dûbele presyzje | min_tiid | dûbele presyzje | max_tiid | dûbele presyzje | mean_tiid | dûbele presyzje | stddev_tiid | dûbele presyzje | rigen | grut | shared_blks_hit | grut | shared_blks_read | grut | shared_blks_dirtied | grut | shared_blks_written | grut | local_blks_hit | grut | local_blks_read | grut | local_blks_dirtied | grut | local_blks_written | grut | temp_blks_read | grut | temp_blks_written | grut | blk_read_time | dûbele presyzje | blk_skriuwtiid | dûbele presyzje | baseline_id | hiele getal | Yndeksen: "pg_stat_history_pkey" Primêre kaai, Btree (id) "Database_idx" BTREE (DATABASE_ID) "Queryid_idx" BTREE (QUERYID) "SNAPSHOT_TIMESTAMP_IDX" BTREE (SNAPSHOTRE (SNAPSH _timestamp) "Keybase Constraints"_keyData Constrainte-:key _id) References Database (ID) On Wiskje Cascade

Sa't jo sjen kinne, de tabel is gewoan in kumulative werjefte gegevens pg_stat_statements yn 'e doeldatabase.

It brûken fan dizze tafel is hiel ienfâldich.

pg_stat_histoarje sil de opboude statistiken fan query-útfiering foar elke oere fertsjintwurdigje. Oan it begjin fan elk oere, nei it ynfoljen fan 'e tabel, statistiken pg_stat_statements weromsette mei pg_stat_statements_reset().
Tink derom: statistiken wurde sammele foar oanfragen mei in doer fan mear as 1 sekonde.
Befolking fan de tabel 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 'NEW SHAPSHOT IS CREATING';
		
		--Connect to the target DB	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_time double precision , 
	         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
             local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
             temp_blks_read	 bigint ,temp_blks_written bigint ,
             blk_read_time double precision , blk_write_time double precision	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
	
        FOR pg_stat_snapshot IN
          --All queries with max_time greater than 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

	                  )
	        AS t
	         ( dbid oid , userid oid , queryid bigint ,query text , calls bigint , 
  	           total_time double precision ,min_time double precision	 ,max_time double precision	 , mean_time double precision	 ,  stddev_time double precision	 , 
	           rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
               local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
               temp_blks_read	 bigint ,temp_blks_written bigint ,
               blk_read_time double precision , blk_write_time double precision	  
	         )
	    LOOP
		  INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	      VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );
		  
        END LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
    
  END LOOP;

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

As gefolch, nei in bepaalde perioade fan tiid yn 'e tabel pg_stat_histoarje wy sille in set snapshots hawwe fan 'e ynhâld fan' e tabel pg_stat_statements doeldatabase.

Eigentlik rapportearje

Mei help fan ienfâldige fragen kinne jo frij nuttich en nijsgjirrige rapporten krije.

Aggregearre gegevens foar in bepaalde perioade fan tiid

Fersyk

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

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

I/O Tiid

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

TOP10 SQL troch totaal_tiid

Fersyk

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(total_time)  AS total_time  	
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT 
GROUP BY queryid 
ORDER BY 3 DESC 
LIMIT 10
-------------------------------------------------------------------------------------- | TOP10 SQL BY TOTAL EXECUTION TIME | #| queryid| ropt| neamt totale_tiid (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 troch totale I / O tiid

Fersyk

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10
---------------------------------------------------------------------------------------------------- | TOP10 SQL BY TOTAL I/O TIME | #| queryid| ropt| neamt I/O-tiid (ms)|db I/O-tiid % +-----+-------------------------------------------------------------| 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06. june | 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 troch maksimale tiid fan útfiering

Fersyk

SELECT 
  id AS snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC 
LIMIT 10

---------------------------------------------------------------------------------------------- | TOP10 SQL BY MAX EXECUTION TIME | #| snapshot| snapshotID| queryid| max_time (ms) +----+-------------------+---------------------------------------------------- 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 ms.) | 17| 00/4153/821760255 00:01 | 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841 | 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019 | 17| 00| 4151:4152624390:00( 01 ms.) | 36.964| 96964.607/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:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00 | 11.892| 11892.302| 9:04.04.2019:16( 00 ms.) | 4145| 1484454471/00/00 11.892:11892.302 | 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX | XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)

TOP10 SQL troch SHARED buffer lêzen / skriuwe

Fersyk

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 dield BUFFER Lêze / skriuwe | #| snapshot| snapshotID| queryid| dielde blokken lêzen| dielde blokken skriuwe +----+------------------------------------------------------------------------------------------------+ 1| 04.04.2019/17/00 4153:821760255 | 797308| 0| 2| 04.04.2019 | 16| 00/4146/821760255 797308:0 | 3| 05.04.2019| 01| 03 | 4169| 655729273/797158/0 4:04.04.2019| 16| 00| 4144| 4152624390 | 756514| 0/5/04.04.2019 17:00 | 4151| 4152624390| 756514| 0 | 6| 04.04.2019/17/00 4150:2460318461 | 734117| 0| 7| 04.04.2019 | 17| 00/4155/3644780286 52973:0 | 8| 05.04.2019| 01| 03 | 4168| 1053044345/52818/0 9:04.04.2019 | 15| 00| 4141| 2194493487 | 52813| 0/10/04.04.2019 16:00| 4147| 2194493487| 52813| 0 | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX | XNUMX| XNUMX| XNUMX| XNUMX ------------------------------------------------------------------------------------

Histogram fan query distribúsje troch maksimale útfiering tiid

Fersiken

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 TIID : 00:00:01.063 | MAX TIME : 00:02:01.869 ------------------------------------------------------------------------------------------ | min doer| max doer| ropt +-------------------------------------+----------------------------------------+---------- | 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 Snapshots troch Query per sekonde

Fersiken

--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 Snapshots oardere troch QueryPerSeconds nûmers ------------------------------------------------------------------------------------------------------------------------------------------------------------ | #| snapshot| snapshotID| ropt| totale dbtime| QPS | I/O tiid | I/O-tiid % +-----+--------------------+--------+--------+--------+--------------------------------+-----+--------------------------------------------------+ 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 ms.)| 00| 01.470:1470.110:376( 2 ms.)| .04.04.2019 | 17| 00/4149/3529197 00:11 | 48.830| 708830.618| 980.332:00:12( 47.834 ms.)| 767834.052| 108.324:3:04.04.2019( 16 ms.)| 00 | 4143| 3525360/00/10 13.492:613492.351 | 979.267| 00| 08:41.396:521396.555( 84.988 ms.)| 4| 04.04.2019:21:03( 4163 ms.)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 ms.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755/776.979/00 00:01.441| 1441.386| 732| 6:04.04.2019:14( 00 ms.)| 4137| 2397326:00:04( 43.033 ms.)| .283033.854 | 665.924| 00/00/00.024 24.505:009 | 7| 04.04.2019| 15:00:4139( 2394416 ms.)| 00| 04:51.435:291435.010( 665.116 ms.)| .00 | 00| 12.025/12025.895/4.126 8:04.04.2019 | 13| 00| 4135:2373043:00( 04 ms.)| 26.791| 266791.988:659.179:00( 00 ms.)| 00.064 | 64.261| 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:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX

Oere útfieringsskiednis mei QueryPerSeconds en I/O Tiid

Fersyk

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

Tekst fan alle SQL selektearret

Fersyk

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

It resultaat

Sa't jo sjen kinne, troch frij ienfâldige middels, kinne jo in protte nuttige ynformaasje krije oer de wurkdruk en de steat fan 'e databank.

Noat:As jo ​​​​de queryid yn 'e queries reparearje, dan krije wy de skiednis foar in apart fersyk (om romte te besparjen wurde rapporten foar in apart fersyk weilitten).

Dat, statistyske gegevens oer query-prestaasjes binne beskikber en sammele.
De earste etappe "sammeling fan statistyske gegevens" is foltôge.

Jo kinne trochgean nei de twadde etappe - "konfigurearje prestaasjesmetriken".
Prestaasjemonitoring fan PostgreSQL-fragen. Diel 1 - rapportaazje

Mar dat is in oar ferhaal.

Oanhâlde wurde ...

Boarne: www.habr.com

Add a comment