Ukuqapha ukusebenza kwemibuzo ye-PostgreSQL. Ingxenye 1 - ukubika

Unjiniyela - elihunyushwe lisuka kuLatin - liphefumulelwe.
Unjiniyela angenza noma yini. (c) R. Udizili.
Ama-Epigraphs.

Noma indaba yokuthi kungani umlawuli wesizindalwazi edinga ukukhumbula uhlelo lwakhe oludlule.

Isibikezelo

Wonke amagama ashintshiwe. Okufanayo akuhleliwe. Indaba ingumbono womuntu siqu wombhali.

Umshwana wokuzihlangula wamawaranti: ochungechungeni oluhleliwe lwezihloko ngeke kube nencazelo enemininingwane nenembile yamathebula nemibhalo esetshenzisiwe. Izinto azikwazi ukusetshenziswa ngokushesha "NJENGOBA ZINJALO".
Okokuqala, ngenxa yobuningi bezinto ezibonakalayo,
okwesibili, ngenxa yobukhali ngesisekelo sokukhiqiza sekhasimende langempela.
Ngakho-ke, imibono nezincazelo kuphela ngendlela ejwayelekile kakhulu zizonikezwa ezihlokweni.
Mhlawumbe esikhathini esizayo uhlelo luzokhula lufinyelele ezingeni lokuthumela ku-GitHub, noma mhlawumbe cha. Isikhathi sizovela.

Ukuqala kwendaba-Uyakhumbula ukuthi konke kwaqala kanjani".
Kwenzekani ngenxa yalokho, ngokwemibandela evamile - "I-synthesis njengenye yezindlela zokuthuthukisa ukusebenza kwe-PostgreSQL»

Kungani ngidinga konke lokhu?

Yebo, okokuqala, ukuze ungazikhohlwa, ukhumbule izinsuku ezikhazimulayo zokuthatha umhlalaphansi.
Okwesibili, ukuhlela okubhaliwe. Ngokwami ​​kakade, ngezinye izikhathi ngiqala ukudideka futhi ngikhohlwe izingxenye ezihlukene.

Hhayi-ke, futhi okubaluleke kakhulu - ngokuzumayo kungaba usizo kumuntu futhi kusize ukuthi angaphinde asungule isondo futhi angaqoqi ireki. Ngamanye amazwi, ngcono karma yakho (hhayi Khabrovsky). Ngokuba into ebaluleke kakhulu kulo mhlaba imibono. Into esemqoka ukuthola umbono. Futhi ukuhumusha umqondo ube ngokoqobo sekuvele kuyindaba yobuchwepheshe.

Ngakho ake siqale kancane...

Ukwakhiwa kwenkinga.

Iyatholakala:

I-PostgreSQL(10.5), umthwalo oxubile (OLTP+DSS), ukulayisha okuphakathi ukuya kokukhanya, usingathwe efwini le-AWS.
Akukho ukuqapha kwesizindalwazi, ukuqapha kwengqalasizinda kuvezwa njengamathuluzi e-AWS ajwayelekile ngokucushwa okuncane.

Kuyadingeka:

Gada ukusebenza kanye nesimo sesizindalwazi, thola futhi ube nolwazi lokuqala ukuze uthuthukise imibuzo yesizindalwazi esindayo.

Isingeniso esifushane noma ukuhlaziya izixazululo

Okokuqala, ake sizame ukuhlaziya izinketho zokuxazulula inkinga ngokombono wokuhlaziya okuqhathaniswayo kwezinzuzo nezinkinga zonjiniyela, futhi labo okufanele babe ohlwini lwabasebenzi babhekane nezinzuzo nokulahlekelwa. labaphathi.

Inketho 1 - "Ukusebenza ngokufunwa"

Sishiya yonke into njengoba injalo. Uma ikhasimende linganelisekile ngokuthile kwezempilo, ukusebenza kwesizindalwazi noma isicelo, lizokwazisa onjiniyela be-DBA nge-imeyili noma ngokudala isigameko ebhokisini lamathikithi.
Unjiniyela, lapho ethole isaziso, uzoqonda inkinga, anikeze isixazululo, noma abeke eshalofini inkinga, enethemba lokuthi yonke into izozixazulula ngokwayo, futhi noma kunjalo, konke kuzolibaleka maduze.
Isinkwa sejinja namadonathi, imihuzuko namaqhuqhuvaIsinkwa se-ginger nama-donuts:
1. Akukho okunye ongakwenza
2. Njalo likhona ithuba lokuphuma futhi ungcole.
3. Isikhathi esiningi ongasichitha uwedwa.
Imihuzuko namaqhubu:
1. Ngokushesha noma kamuva, ikhasimende lizocabanga ngengqikithi yobukhona nobulungiswa bomhlaba wonke kulo mhlaba futhi liphinde lizibuze umbuzo - kungani ngibakhokhela imali yami? Umphumela uhlezi ufana - umbuzo kuphela lapho ikhasimende libhoreka futhi livalelisa. Futhi i-feeder ayinalutho. Kuyadabukisa.
2. Ukuthuthukiswa kukanjiniyela kunguziro.
3. Ubunzima bokuhlela umsebenzi nokulayisha

Inketho 2 - "Dansa ngezigubhu, gqoka futhi ugqoke izicathulo"

Isigaba 1-Kungani sidinga uhlelo lokuqapha, sizothola zonke izicelo. Sethula inqwaba yazo zonke izinhlobo zemibuzo kusichazamazwi sedatha kanye nokubukwa okuguquguqukayo, sivula zonke izinhlobo zokubala, siletha yonke into kumathebula, sihlaziye izinhlu namathebula ngezikhathi ezithile, njengokungathi. Ngenxa yalokho, sinamagrafu amahle noma cha kakhulu, amatafula, imibiko. Into esemqoka - lokho kungaba ngaphezulu, ngaphezulu.
Isigaba 2-Khiqiza umsebenzi-qhuba ukuhlaziya kwakho konke lokhu.
Isigaba 3-Silungiselela idokhumenti ethile, siyibiza le dokhumenti, ngokumane - "siyihlomisa kanjani i-database."
Isigaba 4- Ikhasimende, libona bonke lobu buhle bamagrafu nezibalo, linokuzethemba kobuntwana - manje konke kuzosisebenzela maduzane. Futhi, kalula futhi ngaphandle kobuhlungu bahlukane nezinsiza zabo zezezimali. Abaphathi futhi banesiqiniseko sokuthi onjiniyela bethu basebenza kanzima. Ubukhulu bokulayisha.
Isigaba 5- Phinda isinyathelo soku-1 njalo.
Isinkwa sejinja namadonathi, imihuzuko namaqhuqhuvaIsinkwa se-ginger nama-donuts:
1. Impilo yabaphathi nonjiniyela ilula, iyabikezelwa futhi igcwele umsebenzi. Konke kuyaduma, wonke umuntu umatasatasa.
2. Impilo yekhasimende nayo ayimbi - lihlale linesiqiniseko sokuthi udinga ukubekezela kancane futhi konke kuzolunga. Ukungabi ngcono, kahle, kahle - lo mhlaba awunabo ubulungisa, empilweni elandelayo - unenhlanhla.
Imihuzuko namaqhubu:
1. Ngokushesha noma kamuva, kuzoba nomhlinzeki ohlakaniphile wesevisi efanayo ozokwenza into efanayo, kodwa eshibhe kancane. Futhi uma umphumela ufana, kungani ukhokha ngaphezulu. Okuphinde kuholele ekunyamaleni kwe-feeder.
2. Kuyabhora. Yeka ukuthi uyisicefe noma yimuphi umsebenzi omncane onenjongo.
3. Njengakunguqulo yangaphambilini - akukho ukuthuthukiswa. Kodwa kunjiniyela, ukukhipha ukuthi, ngokungafani nenketho yokuqala, lapha udinga ukukhiqiza njalo i-IDB. Futhi lokho kuthatha isikhathi. Okungasetshenziswa ukuze kuzuze othandekayo wakho. Ngoba awukwazi ukuzinakekela, wonke umuntu uyakukhathalela.

Inketho 3-Asikho isidingo sokusungula ibhayisikili, udinga ukulithenga futhi uligibele.

Onjiniyela abavela kwezinye izinkampani badla i-pizza ngobhiya bazi (oh, izikhathi ezikhazimulayo zase-St. Petersburg ngeminyaka yama-90s). Masisebenzise amasistimu okuqapha enziwe, alungiswa futhi asebenzayo, futhi ngokuvamile aletha izinzuzo (kahle, okungenani kubadali bazo).
Isinkwa sejinja namadonathi, imihuzuko namaqhuqhuvaIsinkwa se-ginger nama-donuts:
1. Asikho isidingo sokuchitha isikhathi ngokusungula lokho osekusunguliwe. Thatha futhi usebenzise.
2. Izinhlelo zokuqapha azibhalwanga iziwula, futhi vele ziwusizo.
3. Amasistimu okuqapha asebenzayo ngokuvamile ahlinzeka ngolwazi oluwusizo oluhlungiwe.
Imihuzuko namaqhubu:
1. Unjiniyela kuleli cala akayena unjiniyela, kodwa umane nje ungumsebenzisi womkhiqizo womunye umuntu, noma umsebenzisi.
2. Ikhasimende kufanele liqiniseke ngesidingo sokuthenga into ngokuvamile engafuni ukuyiqonda, futhi akufanele, futhi ngokujwayelekile isabelomali sonyaka sivunyiwe futhi ngeke sishintshe. Ngemuva kwalokho udinga ukwaba insiza ehlukile, uyilungiselele uhlelo oluthile. Labo. Okokuqala udinga ukukhokha, ukhokhe futhi ukhokhe futhi. Futhi ikhasimende liyancisha. Lokhu kuyinto evamile yale mpilo.

Yini okufanele uyenze, Chernyshevsky? Umbuzo wakho ubaluleke kakhulu. (Nge)

Kulesi simo kanye nesimo samanje, ungenza ngokuhlukile - masenze eyethu uhlelo lokuqapha.

Hhayi-ke, akulona uhlelo, yiqiniso, ngomqondo ogcwele wegama, lokhu kunomsindo kakhulu futhi kuyagabadela, kodwa okungenani ngandlela-thile kwenza kube lula kuwe futhi uqoqe ulwazi olwengeziwe ukuze uxazulule izigameko zokusebenza. Ukuze ungazitholi usesimweni - "hamba lapho, angazi ukuthi kuphi, thola lokho, angazi ukuthi yini."

Yiziphi izinzuzo nezingozi zale nketho:

Izinzuzo:
1. Kuyathakazelisa. Hhayi-ke, okungenani okuthakazelisa kakhulu kunokuqhubekayo "ukunciphisa ifayela ledatha, shintsha indawo yetafula, njll."
2. Lawa amakhono amasha kanye nentuthuko entsha. Yikuphi esikhathini esizayo okuzonikeza ngokushesha noma kamuva isinkwa se-ginger nama-donuts.
Umthengi:
1. Kufanele usebenze. Sebenza kakhulu.
2. Kuzodingeka uchaze njalo incazelo kanye nemibono yawo wonke umsebenzi.
3. Kuzodingeka inikelwe okuthile, ngoba insiza kuphela etholakala kunjiniyela - isikhathi - inqunyelwe yi-Universe.
4. Okubi kakhulu futhi okungajabulisi kakhulu - ngenxa yalokho, udoti onjengokuthi "Akuyona igundane, hhayi i-frog, kodwa isilwane esincane esingaziwa" singase siphume.

Ubani ongazifaki engozini okuthile akaphuzi i-champagne.
Ngakho, ubumnandi buqala.

Umbono ojwayelekile - isikimu

(Umfanekiso othathwe esihlokweni «I-synthesis njengenye yezindlela zokuthuthukisa ukusebenza kwe-PostgreSQL»)

Incazelo:

• Isizindalwazi esiqondiwe sifakwe nesandiso se-PostgreSQL esijwayelekile “pg_stat_statements”.
• Kusizindalwazi sokuqapha, sakha isethi yamathebula esevisi ukuze sigcine umlando we-pg_stat_statements esigabeni sokuqala futhi silungiselele amamethrikhi nokuqapha esikhathini esizayo.
• Kusokhaya oqaphayo, sakha isethi yezikripthi ze-bash, okuhlanganisa nalezo zokukhiqiza izehlakalo ohlelweni lwamathikithi.

Amatafula esevisi

Okokuqala, i-ERD eyenziwe lula ngohlelo, yini eyenzekile ekugcineni:

Incazelo emfushane yamathebulaukuphetha - umphathi, indawo yokuxhuma kusibonelo
database - Izinketho ze-database
pg_stat_history - Ithebula lomlando lokugcina izifinyezo zesikhashana zokubukwa kwe-pg_stat_statements kwesizindalwazi esiqondiwe
metric_glossary - Isichazamazwi samamethrikhi okusebenza
metric_config - ukucushwa kwamamethrikhi angawodwana
metric - imethrikhi ethile yesicelo esigadwayo
metric_alert_history - umlando wezixwayiso zokusebenza
log_query - Ithebula lensizakalo yokugcina amarekhodi ahlukanisiwe kusuka kufayela lokungena le-PostgreSQL elandwe kwa-AWS
isisekelo - amapharamitha wesikhathi asetshenziswa njengesisekelo
indawo yokuhlola - ukumiswa kwamamethrikhi okuhlola isimo sesizindalwazi
umlando_wesexwayiso_wendawo yokuhlola - umlando oyisixwayiso wamamethrikhi wokuhlola isimo sesizindalwazi
pg_stat_db_queries — Ithebula lesevisi lezicelo ezisebenzayo
umsebenzi_log - Ithebula lesevisi yefayela lokungena
trap_oid - Ithebula lesevisi yokucushwa kwesicupho

Isigaba 1 - qoqa izibalo zokusebenza futhi uthole imibiko

Ithebula lisetshenziselwa ukugcina ulwazi lwezibalo. pg_stat_history
pg_stat_history table structure

                                          Ithebula elithi "public.pg_stat_history" Ikholomu | uhlobo | Izilungisi--------------------------------------------+---- ------------------------------- id | inamba | akukona okuzenzakalelayo okuzenzakalelayo okulandelayo('pg_stat_history_id_seq'::regclass) isifinyezo_sesikhathi | isitembu sesikhathi esingenazo izoni yesikhathi | database_id | inamba | dbi | awu | umsebenzisi | awu | umbuzo | mkhulu | umbuzo | umbhalo | ucingo | mkhulu | sonke_isikhathi | ukunemba kabili | iminithi | ukunemba kabili | isikhathi_esikhulu | ukunemba kabili | isikhathi_sesikhathi | ukunemba kabili | stddev_time | ukunemba kabili | imigqa | mkhulu | kwabelwana_blks_hit | mkhulu | kwabelwana_blks_funda | mkhulu | kwabelwana_blks_kungcolile | mkhulu | kwabelwana_blks_kubhaliwe | mkhulu | local_blks_hit | mkhulu | local_blks_read | mkhulu | local_blks_dirtied | mkhulu | local_blks_written | mkhulu | temp_blks_funda | mkhulu | temp_blks_written | mkhulu | blk_read_time | ukunemba kabili | blk_write_time | ukunemba kabili | i-id_yesisekelo | inamba | Izikhombo: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) "Izingqinamba zedatha yokhiye wangaphandle" REIGbase_Izingqinamba zedatha ye-REIGREGREIGRE NCES database(id ) EKUSUSA I-CASCADE

Njengoba ubona, ithebula liyidatha yokubuka eqongelelekayo pg_stat_statements kusizindalwazi esiqondiwe.

Ukusetshenziswa kwaleli thebula kulula kakhulu.

pg_stat_history izomela izibalo eziqoqiwe zokwenziwa kombuzo zehora ngalinye. Ekuqaleni kwehora ngalinye, ngemva kokugcwalisa itafula, izibalo pg_stat_statements setha kabusha nge pg_stat_statements_reset().
Qaphela: Izibalo ziqoqwa ngezicelo ezinobude besikhathi esingaphezu kwesekhondi elingu-1.
Igcwalisa ithebula le-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;

Ngenxa yalokho, ngemva kwesikhathi esithile etafuleni pg_stat_history sizoba nesethi yezifinyezo zokuqukethwe kwethebula pg_stat_statements isizindalwazi esiqondisiwe.

Ukubika ngempela

Ngokusebenzisa imibuzo elula, ungathola imibiko ewusizo nethakazelisayo.

Idatha ehlanganisiwe yesikhathi esinikeziwe

Uphenyo

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

ku_char(ikhefu '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

I/O Isikhathi

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

TOP10 SQL by total_time

Uphenyo

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

------------------------------------------------- ------------------------------------ | I-TOP10 SQL NGESIKHATHI SESONKE SOKWENZA | #| umbuzo| izingcingo| izingcingo %| ingqikithi_yesikhathi (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

I-TOP10 SQL ngenani lesikhathi se-I/O

Uphenyo

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 NGEYONKE ISIKHATHI SAMA-I/O | #| umbuzo| izingcingo| izingcingo %| Isikhathi se-I/O (ms)|db I/O isikhathi % +----+-------------------------------- -------------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| Juni 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

I-TOP10 SQL ngesikhathi esikhulu sokwenza

Uphenyo

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

------------------------------------------------- ------------------------------------ | I-TOP10 SQL NGESIKHATHI SESIKHULU SOKUSEBENZA | #| isifinyezo| i-snapshotID| umbuzo| isikhathi_ esikhulu (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 by SHARED buffer funda/ubhale

Uphenyo

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

------------------------------------------------- ------------------------------------ | I-TOP10 SQL NGE-BUFFER EYABELWE FUNDA/BHALA | #| isifinyezo| isithombeID| umbuzo| amabhlogo okwabelwana ngawo afundiwe| amabhlogo okwabelwana ngawo bhala +----+------------------------------------------- -+-------------------------------------------- 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 ----------------------------------------------- ------------------------------------------------

I-Histogram yokusabalalisa kombuzo ngesikhathi esiphezulu sokwenza

Izicelo

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 | IZIMALI ZONKE : 33851920 | ISIKHATHI ESININGI : 00:00:01.063 | ISIKHATHI ESININGI : 00:02:01.869 --------------------------------- --------- ---------------------------- | ubude besikhathi| ubude besikhathi | izingcingo +------------------------------------------------- ---------------------+----------- | 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 Izifinyezo ngombuzo ngomzuzwana ngamunye

Izicelo

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

|------------------------------------------------ ---------------------------------------- | I-TOP10 Snapshots i-odwe ngezinombolo ze-QueryPerSeconds --------------------------------------------------------------------------- ------------------------------------------- ------ ------------------------------------------- | #| isifinyezo| isithombeID| izingcingo| isamba sesikhathi se-db| QPS | Isikhathi se-I/O | I/O isikhathi % +-----+-------------------------------------- ----+---------------------------------+---------- -+----------------------------------+------------ 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

Umlando Wokwenziwa Wangehora nge-QueryPerSeconds kanye Nesikhathi Se-I/O

Uphenyo

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

Umbhalo wawo wonke ama-SQL akhethiwe

Uphenyo

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

Umphumela

Njengoba ubona, ngezindlela ezilula, ungathola ulwazi oluningi oluwusizo mayelana nomsebenzi kanye nesimo sesizindalwazi.

Qaphela:Uma ulungisa umbuzo emibuzweni, lapho-ke sizothola umlando wesicelo esihlukile (ukuze konge isikhala, imibiko yesicelo esehlukile ayifakiwe).

Ngakho, idatha yezibalo ekusebenzeni kombuzo iyatholakala futhi iqoqwe.
Isigaba sokuqala "sokuqoqwa kwedatha yezibalo" siqediwe.

Ungaqhubekela esigabeni sesibili - "ukulungisa amamethrikhi okusebenza".


Kodwa leyo indaba ehluke ngokuphelele.

Kuzoqhubeka ...

Source: www.habr.com