Kuyang'anira magwiridwe antchito a mafunso a PostgreSQL. Gawo 1 - kupereka malipoti

Engineer - kumasuliridwa kuchokera ku Chilatini - ouziridwa.
Mainjiniya amatha kuchita chilichonse. (c) R. Dizilo.
Epigraphs.

Kapena nkhani yonena za chifukwa chake woyang'anira nkhokwe ayenera kukumbukira mapulogalamu ake akale.

Maulosi

Mayina onse asinthidwa. Machesi amangochitika mwachisawawa. Nkhaniyi ndi maganizo a mwiniwake wa wolembayo.

Chodzikanira cha zitsimikizo: muzolemba zomwe zakonzedweratu sipadzakhala kufotokozera mwatsatanetsatane komanso molondola za matebulo ndi zolemba zomwe zimagwiritsidwa ntchito. Zida sizingagwiritsidwe ntchito nthawi yomweyo "AS IS".
Choyamba, chifukwa cha kuchuluka kwa zinthu.
kachiwiri, chifukwa cha sharpness ndi kupanga maziko a kasitomala weniweni.
Chifukwa chake, malingaliro ndi mafotokozedwe amtundu wamba adzaperekedwa m'nkhani.
Mwina m'tsogolomu dongosololi lidzakula mpaka kufika pamlingo wotumizira pa GitHub, kapena ayi. Nthawi idzawoneka.

Chiyambi cha nkhani -Mukukumbukira momwe zonse zidayambira".
Zomwe zidachitika chifukwa chake, m'mawu ambiri - "Kaphatikizidwe ngati njira imodzi yopititsira patsogolo magwiridwe antchito a PostgreSQL»

Chifukwa chiyani ndikufunika zonsezi?

Chabwino, choyamba, kuti musaiwale nokha, kukumbukira masiku aulemerero akupuma pantchito.
Kachiwiri, kukonza zomwe zidalembedwa. Kwa ine ndekha, nthawi zina ndimayamba kusokonezeka ndikuyiwala magawo osiyanasiyana.

Chabwino, ndipo chofunikira kwambiri - mwadzidzidzi zitha kukhala zothandiza kwa wina ndikuthandizira kuti asayambitsenso gudumu komanso kuti asatolere. M'mawu ena, kusintha karma wanu (osati Khabrovsky). Kwa chinthu chamtengo wapatali kwambiri padziko lapansi ndi malingaliro. Chinthu chachikulu ndikupeza lingaliro. Ndipo kumasulira lingalirolo kukhala zenizeni kale ndi nkhani mwaukadaulo.

Ndiye tiyeni tiyambe pang'onopang'ono ...

Kupanga vuto.

Zilipo:

PostgreSQL (10.5), katundu wosakanikirana (OLTP + DSS), wapakati mpaka wopepuka, wosungidwa mumtambo wa AWS.
Palibe kuwunika kwa database, kuyang'anira zomangamanga kumawonetsedwa ngati zida za AWS zokhazikika pamasinthidwe ochepa.

Amafunika:

Yang'anirani momwe nkhokweyo ikugwirira ntchito, pezani ndikukhala ndi chidziwitso choyambirira kuti mukwaniritse mafunso olemera a database.

Chiyambi chachidule kapena kusanthula mayankho

Poyamba, tiyeni tiyese kusanthula njira zothetsera vutoli potengera kusanthula kofananiza za phindu ndi zovuta za injiniya, ndikulola omwe akuyenera kukhala pamndandanda wa ogwira ntchito kuti athane ndi zopindulitsa ndi zotayika. za kasamalidwe.

Njira 1 - "Kugwira ntchito pakufunika"

Timasiya zonse momwe zilili. Ngati kasitomala sakukhutitsidwa ndi china chake paumoyo, magwiridwe antchito a nkhokwe kapena kugwiritsa ntchito, azidziwitsa mainjiniya a DBA kudzera pa imelo kapena kupanga chochitika mubokosi la tikiti.
Katswiri, atalandira zidziwitso, amvetsetsa vutolo, apereka yankho, kapena kuwongolera vutolo, akuyembekeza kuti chilichonse chizithetsa, ndipo chilichonse chidzaiwalika posachedwa.
Mkate wa gingerbread ndi donuts, mikwingwirima ndi totupaGingerbread ndi donuts:
1. Palibe chowonjezera kuchita
2. Nthawi zonse pali mwayi wotuluka ndikudetsedwa.
3. Nthawi yochuluka yomwe mungathe kuthera nokha.
Mikwingwirima ndi makutu:
1. Posakhalitsa, kasitomala adzaganiza za chiyambi cha kukhala ndi chilungamo cha dziko lonse lapansi ndikudzifunsanso funsoli - chifukwa chiyani ndikulipira ndalama zanga? Zotsatira zake zimakhala zofanana - funso lokhalo ndi pamene kasitomala amatopa ndikutsazikana. Ndipo chodyera chilibe kanthu. Ndizomvetsa chisoni.
2. Kukula kwa injiniya ndi ziro.
3. Zovuta pakukonza ntchito ndi kutsitsa

Njira 2 - "Vina ndi maseche, valani ndi kuvala nsapato"

Ndime 1-N'chifukwa chiyani timafunikira njira yowunikira, tidzalandira zopempha zonse. Timatsegula mulu wa mafunso amitundu yonse ku dikishonale ya data ndi mawonedwe amphamvu, kuyatsa zowerengera zamitundu yonse, kubweretsa chilichonse m'matebulo, kusanthula mindandanda ndi matebulo nthawi ndi nthawi, titero kunena kwake. Zotsatira zake, tili ndi ma graph okongola kapena ayi kwambiri, matebulo, malipoti. Chinthu chachikulu - izo zikanakhala zambiri, zambiri.
Ndime 2-Pezani zochita-yendetsani kusanthula zonsezi.
Ndime 3-Tikukonzekera chikalata china, timachitcha chikalatachi, mophweka - "momwe timakonzekeretsa bwanji database."
Ndime 4- Makasitomala, akuwona kukongola konseku kwa ma graph ndi ziwerengero, ali ndi chidaliro chaubwana - tsopano zonse zitigwirira ntchito posachedwa. Ndipo, mosavuta komanso mopanda ululu amagawana ndi chuma chawo. Management ndikutsimikizanso kuti mainjiniya athu akugwira ntchito molimbika. Kutsegula kwakukulu.
Ndime 5- Bwerezani gawo loyamba pafupipafupi.
Mkate wa gingerbread ndi donuts, mikwingwirima ndi totupaGingerbread ndi donuts:
1. Moyo wa mamanejala ndi mainjiniya ndi wosavuta, wodziwikiratu komanso wodzazidwa ndi zochitika. Chilichonse chikuyenda, aliyense ali wotanganidwa.
2. Moyo wa kasitomala nawonso si woipa - nthawi zonse amakhala wotsimikiza kuti muyenera kuleza mtima pang'ono ndipo zonse zidzayenda bwino. Sizikuyenda bwino, chabwino - dziko lino siliri chilungamo, m'moyo wotsatira - mudzakhala ndi mwayi.
Mikwingwirima ndi makutu:
1. Posakhalitsa, padzakhala wopereka wanzeru wa ntchito yofananira yomwe idzachita zomwezo, koma zotsika mtengo. Ndipo ngati zotsatira zake ndi zofanana, bwanji kulipira zambiri. Zomwe zidzatsogoleranso kutha kwa wodyetsa.
2. Ndizotopetsa. Zosasangalatsa bwanji zochita zilizonse zatanthauzo.
3. Monga momwe zinalili kale - palibe chitukuko. Koma kwa injiniya, chotsitsa ndikuti, mosiyana ndi njira yoyamba, apa muyenera kupanga IDB nthawi zonse. Ndipo zimenezo zimatenga nthawi. Zomwe zingagwiritsidwe ntchito phindu la wokondedwa wanu. Pakuti simungathe kudzisamalira nokha, aliyense amasamala za inu.

Njira 3-Palibe chifukwa chopangira njinga, muyenera kugula ndikuikwera.

Akatswiri ochokera kumakampani ena amadya pizza ndi mowa mwadala (o, nthawi zaulemerero za St. Petersburg m'ma 90). Tiyeni tigwiritse ntchito njira zowunikira zomwe zimapangidwa, zosinthidwa ndikugwira ntchito, ndipo nthawi zambiri, zimabweretsa phindu (chabwino, kwa omwe adazipanga).
Mkate wa gingerbread ndi donuts, mikwingwirima ndi totupaGingerbread ndi donuts:
1. Palibe chifukwa chotaya nthawi kupanga zomwe zidapangidwa kale. Tengani ndikugwiritsa ntchito.
2. Njira zowunikira sizilembedwa ndi opusa, ndipo ndizothandiza.
3. Njira zowunikira ntchito nthawi zambiri zimapereka chidziwitso chothandiza.
Mikwingwirima ndi makutu:
1. Injiniya pankhaniyi si injiniya, koma wogwiritsa ntchito zinthu za munthu wina kapena wogwiritsa ntchito.
2. Wogula ayenera kukhala wotsimikiza za kufunikira kogula chinthu chomwe kawirikawiri safuna kumvetsa, ndipo sayenera, ndipo kawirikawiri bajeti ya chaka yavomerezedwa ndipo sichidzasintha. Kenako muyenera kugawa gwero lapadera, sinthani dongosolo linalake. Iwo. Choyamba muyenera kulipira, kulipira ndi kulipira kachiwiri. Ndipo kasitomala ndi wotopa. Ichi ndi chikhalidwe cha moyo uno.

Zoyenera kuchita, Chernyshevsky? Funso lanu ndilofunika kwambiri. (Ndi)

Pankhani iyi komanso momwe zilili pano, mutha kuchita mosiyana - tiyeni tipange zathu zowunikira.

Chabwino, osati dongosolo, ndithudi, m'lingaliro lonse la mawu, izi ndizokweza kwambiri komanso zodzikuza, koma mwinamwake zipangitsa kuti zikhale zosavuta kwa inu nokha ndikusonkhanitsa zambiri kuti muthe kuthetsa zochitika. Kuti musadzipeze nokha muzochitika - "pita kumeneko, sindikudziwa kuti, pezani, sindikudziwa chiyani."

Kodi zabwino ndi zoyipa za njirayi ndi yotani:

Zotsatira:
1. Ndizosangalatsa. Chabwino, chosangalatsa kwambiri kuposa nthawi zonse "shrink datafile, alter tablespace, etc."
2. Awa ndi maluso atsopano ndi chitukuko chatsopano. Zomwe m'tsogolomu posachedwa zidzapereka gingerbread yoyenera ndi donuts.
Wotsatsa:
1. Muyenera kugwira ntchito. Gwirani ntchito kwambiri.
2. Mudzayenera kufotokoza mokhazikika tanthauzo ndi kawonedwe ka zochitika zonse.
3. Chinachake chidzayenera kuperekedwa nsembe, chifukwa gwero lokhalo lopezeka kwa injiniya - nthawi - ndilochepa ndi Chilengedwe.
4. Zoipitsitsa ndi zosakondweretsa - Zotsatira zake, zinyalala monga "Osati mbewa, osati chule, koma nyama yaying'ono yosadziwika" ikhoza kutuluka.

Amene saika pachiwopsezo chinachake samamwa champagne.
Choncho, zosangalatsa zimayamba.

Lingaliro lazonse - schematic

(Chithunzi chotengedwa m'nkhani «Kaphatikizidwe ngati njira imodzi yopititsira patsogolo magwiridwe antchito a PostgreSQL")

Kufotokozera:

• Dongosolo lolowera lomwe lakhazikitsidwa limayikidwa ndi kukula kwa PostgreSQL "pg_stat_statements".
• M'nkhokwe yowunikira, timapanga mndandanda wamatebulo osungira kuti pg_stat_statements mbiri iyambike ndikukonza ma metric ndi kuwunikira mtsogolo.
• Pa gulu loyang'anira, timapanga zolemba za bash, kuphatikizapo zomwe zimapanga zochitika mu dongosolo la matikiti.

Ma tebulo a utumiki

Poyamba, ERD yophweka, zomwe zidachitika pamapeto pake:

Kufotokozera mwachidule matebulomapeto - Host, malo olumikizirana ndi chitsanzo
Nawonso achichepere - Zosankha za database
pg_stat_history - tebulo la mbiri yakale losungiramo zithunzi zosakhalitsa za pg_stat_statements mawonedwe a database yomwe mukufuna
metric_glossary - Dikishonale ya ma metrics ochita bwino
metric_config - kasinthidwe ka metrics payekha
ma metric - metric yeniyeni ya pempho lomwe likuyang'aniridwa
metric_alert_history - mbiri ya machenjezo a magwiridwe antchito
log_query - tebulo lautumiki kuti musunge zojambulidwa kuchokera pa fayilo ya log ya PostgreSQL yotsitsidwa kuchokera ku AWS
maziko - magawo a nthawi yomwe amagwiritsidwa ntchito ngati maziko
malo ofufuzira - kasinthidwe ka metrics kuti muwone momwe database ilili
checkpoint_alert_history - mbiri yochenjeza ya ma metric omwe ali mu database
pg_stat_db_queries - tebulo lautumiki la zopempha zogwira ntchito
ntchito_log - tebulo la ntchito zantchito
trap_oid - tebulo kasinthidwe utumiki msampha

Gawo 1 - sonkhanitsani ziwerengero zantchito ndikupeza malipoti

Gome limagwiritsidwa ntchito kusunga ziwerengero. pg_stat_history
pg_stat_history table structure

                                          Mndandanda wa "public.pg_stat_history" | mtundu | Zosintha------------------------------------------------- ------------------------------- id | chiwerengero | osati null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | timestamp popanda zone nthawi | database_id | chiwerengero | ddi | ayi | ntchito | ayi | funso | chachikulu | funso | mawu | mafoni | chachikulu | nthawi_yonse | kulondola kawiri | mphindi_nthawi | kulondola kawiri | nthawi_nthawi | kulondola kawiri | nthawi_nthawi | kulondola kawiri | stddev_time | kulondola kawiri | mizere | chachikulu | adagawana_blks_hit | chachikulu | share_blks_read | chachikulu | share_blks_zodetsedwa | chachikulu | adagawana_blks_written | chachikulu | local_blks_hit | chachikulu | local_blks_read | chachikulu | local_blks_dirtied | chachikulu | local_blks_written | chachikulu | temp_blks_werengani | chachikulu | temp_blks_written | chachikulu | blk_read_time | kulondola kawiri | blk_write_time | kulondola kawiri | maziko_id | chiwerengero | Ma index: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Foreign-key data constraints (REIGbase) REIGbase data constraints NCES database (id ) PAFUTA CASCADE

Monga mukuonera, tebulo ndi deta yowonjezereka pg_stat_statements mu database ya chandamale.

Kugwiritsa ntchito tebuloli ndikosavuta.

pg_stat_history idzayimira ziwerengero zomwe zasonkhanitsidwa za kufunsidwa kwa ola lililonse. Kumayambiriro kwa ola lililonse, mutadzaza tebulo, ziwerengero pg_stat_statements bwererani ndi pg_stat_statements_reset().
Taonani: ziwerengero zimasonkhanitsidwa pazopempha ndi nthawi yopitilira sekondi imodzi.
Kudzaza pg_stat_history tebulo

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

Zotsatira zake, patapita nthawi inayake patebulo pg_stat_history tidzakhala ndi zithunzi za zomwe zili mu tebulo pg_stat_statements target database.

Kunena zoona

Pogwiritsa ntchito mafunso osavuta, mutha kupeza malipoti othandiza komanso osangalatsa.

Zophatikiza za nthawi yoperekedwa

Funsani

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

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

I/O Nthawi

ku_char(nthawi '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

Funsani

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 NDI NTHAWI YONSE YOTHANDIZA | #| funso| mafoni | mafoni %| nthawi_nthawi (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 ndi nthawi yonse ya I/O

Funsani

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 NDI YONSE YA I/O NTHAWI | #| funso| mafoni | mafoni %| Nthawi ya I/O (ms)|db I/O nthawi % +----+-------------------------------- -------------------------------------+----------- -- | 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

TOP10 SQL pofika nthawi yayitali yophedwa

Funsani

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 YOPHUNZITSA NTHAWI YOPHUNZITSIRA MAX | #| chithunzi | chithunziID| funso| 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 yolembedwa ndi SHARED buffer werengani/lemba

Funsani

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 YOLEMBEDWA NDI BUFFER WERENGANI/LEMBA | #| chithunzi | chithunziID| funso| midadada adagawana kuwerenga| midadada yogawana nawo lembani +----+------------------------------------------- -+-------------------------------------------------------------------------------------- 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 yogawa mafunso ndi nthawi yochuluka yochitira

Zopempha

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 HITOGRAM | KUYENERA KUYAMBIRA : 33851920 | MIN TIME : 00:00:01.063 | MAX NTHAWI : 00:02:01.869 --------------------------------- -------- ---------------------------- | nthawi ya mphindi| nthawi yayitali| kuyimba foni +------------------------------------------------ --------------------------------- | 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 ndi Query pa Sekondi iliyonse

Zopempha

--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 oyitanidwa ndi manambala a QueryPerSeconds ------------------------------------- ------ ------------------------------------------- ------ ------------------------------------------ | #| chithunzi | chithunziID| mafoni | nthawi yonse | QPS | I/O nthawi | I/O nthawi% +-----+--------------------------------------- -------------------------------------------------- -+------------------------------------------------------------------------------------------------- 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

Mbiri Yakuphedwa Kwaola ndi QueryPerSeconds ndi I/O Time

Funsani

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

Zolemba za SQL zonse zimasankhidwa

Funsani

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

Zotsatira

Monga mukuwonera, mwa njira zosavuta, mutha kupeza zambiri zothandiza pazantchito komanso momwe nkhokweyo ilili.

Zindikirani:Ngati mukonza queryid m'mafunso, ndiye kuti tidzalandira mbiri ya pempho lapadera (kuti tisunge malo, malipoti a pempho lapadera amachotsedwa).

Chifukwa chake, ziwerengero zamafunso zimapezeka ndikusonkhanitsidwa.
Gawo loyamba la "kusonkhanitsa deta" latha.

Mutha kupita ku gawo lachiwiri - "kusintha ma metrics ogwirira ntchito".


Koma imeneyo ndi nkhani yosiyana kotheratu.

Zipitilizidwa…

Source: www.habr.com