ProHoster > Blog > Administrim > Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi

Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi

Inxhinieri – nĂ« pĂ«rkthim nga latinishtja – i frymĂ«zuar.
Inxhinieri mund të bëjë gjithçka. (c) R.Dizel.
Epigrafet.
Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi
Ose historia se pse një administrator i bazës së të dhënave duhet të kujtojë të kaluarën e tij si programues.

Parathënie

Të gjitha emrat janë ndryshuar. Përputhjet janë rastësore. Materiali është një mendim krejtësisht personal i autorit.

Shkëputja e garancive: në ciklin e planifikuar të artikujve nuk do të ketë përshkrime të hollësishme dhe të sakta të tavolinave dhe skripteve të përdorura. Materialet nuk do të mund të përdoren menjëherë "AS IS".
Së pari, për shkak të volumit të madh të materialit,
së dyti, për shkak të fokusit në bazën reale të prodhimit të klientit.
Prandaj, në artikuj do të jepen vetëm ide dhe përshkrime në mënyrë shumë të përgjithshme.
Mund të jetë që në të ardhmen sistemi do të arrijë në nivelin e publikimit në GitHub, ndoshta dhe jo. Koha do ta tregojë.

Fillimi i historisë - "A e mban mend se si filloi gjithçka».
ÇfarĂ« doli si rezultat, nĂ« pĂ«rmbledhje - "Sintetizimi si njĂ« nga metodat pĂ«r pĂ«rmirĂ«simin e performancĂ«s sĂ« PostgreSQL»

Pse më nevojitet gjithë kjo?

E para, për të mos harruar vetë, duke përmenduar ditët e lavdishme në pension.
E dyta, për të sistematizuar atë që kam shkruar. Sepse ndonjëherë filloj të humb dhe harroj pjesë të veçanta.

Dhe e rĂ«ndĂ«sishmja – ndoshta dikujt mund t'i ndihmojĂ« dhe t'i propozojĂ« njĂ« zgjidhje pĂ«r tĂ« mos shpikur biçikleta dhe pĂ«r tĂ« mos mbledhur gĂ«rshĂ«rĂ«. TĂ« tjerat, nĂ« fjalĂ«, tĂ« pĂ«rmirĂ«sojnĂ« karma e tij (jo tĂ« Habrave). Sepse, gjĂ«ja mĂ« e çmuar nĂ« kĂ«tĂ« botĂ« janĂ« idetĂ«. E rĂ«ndĂ«sishme Ă«shtĂ« tĂ« gjesh idenĂ«. NdĂ«rsa ta realizosh idenĂ« nĂ« realitet Ă«shtĂ« njĂ« çështje krejtĂ«sisht teknike.

Pra, le të fillojmë, ngadalë...

Vënia në praktikë e detyrës.

Disponohet:

Baza e të dhënave PostgreSQL (10.5), tip i ngarkesës së përzier (OLTP+DSS), mesatarisht-e vogël e ngarkesës, e vendosur në cloud AWS.
Monitorimi i bazës së të dhënave mungon, monitorimi i infrastrukturës paraqitet në formën e mjeteve standarde AWS në konfigurimin minimal.

KĂ«rkohet:

Të monitorojë performancën dhe gjendjen e bazës së të dhënave, të gjejë dhe të ketë informacion fillestar për optimizimin e kërkesave të rënda ndaj BDs.

Një shkurtim parafolës ose analizë e mundësive të zgjidhjes

Fillimisht, le të shqyrtojmë mundësitë e zgjidhjes së detyrës nga këndvështrimi i analizës krahasuese të përfitimeve dhe të këqijave për inxhinierin, ndërsa përfitimet dhe humbjet e menaxhmentit le t'u ngelen atyre që janë të caktuar sipas vendimit të punës.

Mundësia 1 - "Punoni sipas kërkesës"

Lëvizim gjithçka siç është. Nëse klienti nuk është i kënaqur me diçka në funksionimin, performancën e bazës së të dhënave ose aplikacionit, ai do t'i njoftojë inxhinierët DBA me e-mail ose duke krijuar një incident në sistemin e biletave.
Inxhinieri, pasi merr njoftimin, do të shqyrtojë problemin, do të propozojë një zgjidhje, ose do ta shtyjë problemin në kohë të pacaktuar, duke shpresuar se gjithçka do të zgjidhet vetë, dhe gjithsesi së shpejti do të harrohet.
Biskota dhe puffa, grimca dhe goditjeBiskota dhe puffa:
1. Nuk ka nevojë të bëni asgjë të panevojshme
2. Gjithmonë ka mundësi për të ikur dhe për të shmangur punën.
3. Një sasi e madhe kohe, e cila mund të shpenzohet sipas dëshirës.
Grimca dhe goditje:
1. Herët apo vonë, klienti do të mendojë për esencën e ekzistencës dhe drejtësinë universale në këtë botë dhe do të bëjë një herë tjetër pyetjen - për çfarë po paguaj paratë e mia? Pasojat gjithmonë janë të njëjta - pyetja është vetëm se kur klienti do të mërzitet dhe do të heqë dorë. Dhe banka do të zbrazet. Kjo është trishtues.
2. Zhvillimi i inxhinierit - zero.
3. Vështirësia në planifikimin e punës dhe ngarkesës

Opsioni 2 - "Dancojmë me zhurmë, hedhim dhe veshim"

Pika 1-Pse na nevojitet një sistem monitorimi, ne do të marrim gjithçka me kërkesa. Nisemi me një sërë kërkesash në fjalorin e të dhënave dhe prezantimet dinamik, aktivizojmë një sërë numëruesish, i përmbledhim të gjitha në tabela, analizojmë lista dhe tabela herë pas here. Si rezultat kemi grafikë të bukura ose jo shumë, tabela, raporte. E rëndësishme është që të kemi sa më shumë, sa më shumë.
Pika 2-Generojmë aktivitet - fillojmë analizën e të gjithë kësaj.
Pika 3-Përgatitnim një dokument, e quajmë këtë dokument, thjesht - "si ta organizojmë bazën e të dhënave."
Pika 4-Klienti, duke parë të gjithë këtë mrekulli grafikësh dhe numrash, është në një besim fëmijëror naiv - tani gjithçka do të funksionojë, së shpejti. Dhe, lehtë dhe pa dhimbje, ndahet nga burimet e tij financiare. Menaxhimi gjithashtu është i sigurt - inxhinierët tanë punojnë gjithçka. Ngarkesa është në maksimum.
Pika 5-Rregullisht përsëritni Pikën 1.
Biskota dhe puffa, grimca dhe goditjeBiskota dhe puffa:
1. Jeta e menaxherëve dhe inxhinierëve është e thjeshtë, e parashikueshme dhe e mbushur me aktivitet. Gjithçka bzzz, të gjithë janë të zënë.
2. Jeta e klientit gjithashtu nuk është e keqe - ai gjithmonë është i sigurt se duhet ta presë pak më shumë dhe gjithçka do të rregullohet. Nuk rregullohet, mirë, çfarë të bëjmë - ky është një botë e padrejtë, në jetën tjetër do të kemi fat.
Grimca dhe goditje:
1. Përshtypja është se, vonë a herët, do të dalë një ofrues më i shpejtë i shërbimeve të ngjashme, i cili do të bëjë të njëjtën gjë, por pak më lirë. E nëse rezultati është i njëjtë, pse të paguash më shumë? Kjo do të çojë përsëri në zhdukjen e burimeve.
2. ËshtĂ« e mĂ«rzitshme. Siç Ă«shtĂ« e mĂ«rzitshme çdo aktivitet pa kuptim.
3. Siç ishte nĂ« opsionin e mĂ«parshĂ«m — nuk ka zhvillim. Por pĂ«r inxhinierin, minus Ă«shtĂ« se, ndryshe nga opsioni i parĂ«, kĂ«tu duhet tĂ« gjenerosh vazhdimisht IBD. Kjo merr kohĂ«. E cila mund tĂ« shpenzohet nĂ« pĂ«rfitim tĂ« vetvetes. Sepse nĂ«se nuk kujdesesh pĂ«r veten, askush nuk ka pĂ«r tu shqetĂ«suar pĂ«r ty.

Opsioni 3 - Nuk është nevoja të shpikni biçikletën, thjesht blini një dhe ngisni.

Inxhinierët e kompanive të tjera nuk hanë pizzë rastësisht, e shoqëruar me birrë (oh, ditët e lavdishme në Shën Petersburg të viteve '90). Le të përdorim sistemet e monitorimit që janë krijuar, të gjitha janë provuar dhe funksionojnë, dhe që sjellin vërtet përfitim (të paktën për krijuesit e tyre).
Biskota dhe puffa, grimca dhe goditjeBiskota dhe puffa:
1. Nuk është e nevojshme të humbni kohë duke shpikur diçka që tashmë është shpikur. Merrni dhe përdorni.
2. Sistemet e monitorimit nuk janë shkruar nga budallenj dhe natyrisht që janë të dobishme.
3. Sistemet e monitorimit që funksionojnë zakonisht japin informacione të filtruar dhe të dobishme.
Grimca dhe goditje:
1. Në këtë rast, inxhinieri nuk është inxhinier, por thjesht një përdorues i një produkti të huaj. Ose një përdorues.
2. Duhet ta bindësh klientin për rëndësinë e blerjes së diçkaje që, në fakt, nuk dëshiron ta kuptojë, as që duhet, dhe gjithashtu buxheti për vitin është miratuar dhe nuk do të ndryshojë. Më pas, duhet të ndash burime të veçanta, të konfigurosh për sistemin specifik. Domethënë, fillimisht duhet të paguash, paguash dhe përsëri të paguash. E klienti është lakmitar. Kjo është norma e kësaj jete.

ÇfarĂ« duhet bĂ«rĂ« - ÇernyshĂ«vski? Pyetja jote Ă«shtĂ« menjĂ«herĂ« relevante. (c)

NĂ« kĂ«tĂ« rast konkret dhe nĂ« situatĂ«n e krijuar, mund tĂ« veprohet pak ndryshe — le tĂ« krijojmĂ« sistemin tonĂ« tĂ« monitorimit.
Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi
Epo, jo njĂ« sistem nĂ« kuptimin e plotĂ« tĂ« fjalĂ«s, kjo Ă«shtĂ« shumĂ« e madhe dhe arrogante, por ndonjĂ« gjĂ« pĂ«r tĂ« lehtĂ«suar punĂ«n dhe pĂ«r tĂ« grumbulluar sa mĂ« shumĂ« informacione pĂ«r zgjidhjen e incidenteve tĂ« performancĂ«s. QĂ« tĂ« mos pĂ«rfundosh nĂ« situatĂ«n — “Shko atje, nuk e di ku, gjej atĂ«, nuk e di çfarĂ«â€.

Cilat janë disa përfitime dhe disavantazhe të këtij opsioni:

Avantazhet:
1. ËshtĂ« interesante. TĂ« paktĂ«n interesante mĂ« shumĂ« se vazhdimisht «shrink datafile, alter tablespace, etj.»
2. KĂ«to janĂ« aftĂ«si tĂ« reja dhe njĂ« zhvillim i ri. Çka nĂ« perspektivĂ« do tĂ« sjellĂ« vonĂ« ose herĂ«t Ă«mbĂ«lsira dhe merita tĂ« merituara.
Disavantazhet:
1. Duhet të punosh. Të punosh shumë.
2. Duhet të shpjegosh rregullisht kuptimin dhe perspektivat e tërë aktivitetit.
3. Diçka do tĂ« duhet tĂ« sakrifikohet, pasi burimi i vetĂ«m i disponueshĂ«m pĂ«r inxhinierin — koha — Ă«shtĂ« e kufizuar nga Universi.
4. MĂ« e keqja dhe mĂ« e pakĂ«naqshme — si rezultati mund tĂ« dalĂ« diçka e tillĂ« si "As miush, as frog, por njĂ« krijesĂ« e panjohur."

Kush nuk rrezikon, nuk pini shampanjë.
Pra — fillon mĂ« e mira.

Ideja kryesore — nĂ« mĂ«nyrĂ« skematike

Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi
(Ilustrimi është marrë nga artikulli «Sintetizimi si një nga metodat për përmirësimin e performancës së PostgreSQL»)

Shpjegimi:

  • NĂ« bazĂ«n e synuar vendoset zgjerimi standard PostgreSQL — "pg_stat_statements".
  • NĂ« bazĂ«n e tĂ« dhĂ«nave tĂ« monitorimit krijojmĂ« njĂ« grup tabelash shĂ«rbimi pĂ«r ruajtjen e historisĂ« sĂ« pg_stat_statements nĂ« fazĂ«n fillestare dhe pĂ«r konfigurimin e metrikeve dhe monitorimit mĂ« vonĂ«
  • NĂ« hostin e monitorimit krijojmĂ« njĂ« grup skriptesh bash, pĂ«rfshirĂ« pĂ«r gjenerimin e incidenteve nĂ« sistemin e bileta.

Tabela shërbimi

Për fillim, një ERD e thjeshtë-skematike, çfarë rezultoi në fund:
Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi
PĂ«rshkrim i shkurtĂ«r i tabelaveendpoint — hosti, pika e lidhjes me instancĂ«n
database — parametrat e bazĂ«s sĂ« tĂ« dhĂ«nave
pg_stat_history — tabela historike pĂ«r ruajtjen e kopjeve tĂ« pĂ«rkohshme tĂ« pamjes pg_stat_statements tĂ« bazĂ«s sĂ« tĂ« dhĂ«nave tĂ« synuar
metric_glossary — fjalori i metrikeve tĂ« performancĂ«s
metric_config — konfigurimi i metrikeve tĂ« veçanta
metric — njĂ« metrikĂ« specifike pĂ«r kĂ«rkesĂ«n qĂ« monitorohet
metric_alert_history — historia e paralajmĂ«rimeve tĂ« performancĂ«s
log_query — tabela shĂ«rbimi pĂ«r ruajtjen e shĂ«nimeve tĂ« analizuar nga skedari log i PostgreSQL tĂ« ngarkuar nga AWS
baseline — parametrat e periudhĂ«s temporale tĂ« pĂ«rdorur si bazĂ«
checkpoint — konfigurimi i metrikeve pĂ«r kontrollin e gjendjes sĂ« bazĂ«s sĂ« tĂ« dhĂ«nave
checkpoint_alert_history — historia e paralajmĂ«rimeve tĂ« metrikeve pĂ«r kontrollin e gjendjes sĂ« bazĂ«s sĂ« tĂ« dhĂ«nave
pg_stat_db_queries — tabela shĂ«rbimi pĂ«r kĂ«rkesat aktive
activity_log — tabela shĂ«rbimi pĂ«r regjistrin e aktiviteteve
trap_oid — tabela shĂ«rbimi pĂ«r konfigurimin e kapjes

Hapi 1 — mbledhim informacion statistikor mbi performancĂ«n dhe marrim raportet

Për ruajtjen e informacionit statistikor shërben tabela pg_stat_history
Struktura e tabelës pg_stat_history

                                          Tabela "public.pg_stat_history"
       Kolona        |            Tip             |                          Modifikuesit
---------------------+-----------------------------+-------------------------------------------
 id                  | integer                     | jo null default nextval('pg_stat_history_id_seq'::regclass)
 snapshot_timestamp  | timestamp pa zonë kohore |
 database_id         | integer                     |
 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            |
 baseline_id         | integer                     |
Indekset:
    "pg_stat_history_pkey" KLAVË E DYTË, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Kushtet e çelësit të huaj:
    "database_id_fk" ÇELËSI I HUAJ (database_id) REFERON database(id) NË FSHIJE KASKADE

Siç shihet, tabela është thjesht të dhëna kumulative të pamjes pg_stat_statements në bazën e të dhënave të synuara.

Përdorimi i kësaj tabele është shumë i thjeshtë

pg_stat_history do të përfaqësojë statistikën e akumuluar të ekzekutimit të kërkesave për çdo orë. Në fillim të çdo ore, pasi të plotësohet tabela, statistika pg_stat_statements rinizet përmes pg_stat_statements_reset().
Vërejtje: statistika mblidhet për kërkesat me kohë ekzekutimi më të madhe se 1 sekondë.
Plotësimi i tabelës pg_stat_history

--pg_stat_history.sql
KRIJONI OSE ZËVENDËSONI FUNKSIONIN pg_stat_history() KTHE KTHIM BOOLEAN SI $$
SHPREH
  endpoint_rec regjistër ;
  database_rec regjistër ;
  pg_stat_snapshot regjistër ;
  current_snapshot_timestamp timestamp pa zonë kohe;
FILLIMI
  current_snapshot_timestamp = data_trunc('minute',now());  
  
  PËR endpoint_rec NË ZGJEDH KËTË * NGA endpoint 
  LËVIZ
    PËR database_rec NË ZGJEDH KËTË * NGA database KU endpoint_id = endpoint_rec.id 
	  LËVIZ
	    
		RUAJ NJOFTIM 'SHKUP PODHJE TË RE TË KRIJOHET';
		
		--Koncekti te DB e synuar	  
	    EXECUTE 'ZGJEDH dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RUAJ NJOFTIM 'host % dhe dbname % ',endpoint_rec.host,database_rec.name;
		RUAJ NJOFTIM 'Krijimi i një snapshot të pg_stat_statements për bazën e të dhënave %',database_rec.name;
		
		ZGJEDH 
	      *
		NË 
		  pg_stat_snapshot
	    NGA dblink('LINK1',
	      'ZGJEDH 
	       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)
	       NGA pg_stat_statements KU dbid=(ZGJEDH oid nga pg_database ku datname=current_database() ) 
		   GRUPI NGA dbid
  	      '
	               )
	      SI 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 
		  )		  
	    VLERAT
	      (
	       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 	   
	      );		   
		  
        RUAJ NJOFTIM 'Krijimi i një snapshot të pg_stat_statements për pyetjet me min_time më shumë se 1000ms';
	
        PËR pg_stat_snapshot NË
          --Të gjitha pyetjet me max_time më shumë se 1000 ms
	      ZGJEDH 
	        *
	      NGA dblink('LINK1',
	        'ZGJEDH 
	         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
	         NGA pg_stat_statements 
			 KU dbid=(ZGJEDH oid nga pg_database ku datname=current_database() DHE min_time >= 1000 ) 
  	        '

	                  )
	        SI 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	  
	         )
	    LËVIZ
		  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 
		  )		  
	      VLERAT
	      (
	       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 	   
	      );
		  
        FUND LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  FUND LOOP ;--PËR database_rec NË ZGJEDH KËTË * NGA database KU endpoint_id = endpoint_rec.id 
    
  FUND LOOP;

KTHIM TË VËRTETË;  
FUND
$$ GJUHA plpgsql;

Si në rezultat, pas një periudhe të caktuar kohe në tabelë pg_stat_history do të kemi një grup fotografish të përmbajtjes së tabelës pg_stat_statements të bazës së të dhënave qëllimore.

Raportimi në vetvete

Duke përdorur pyetje të thjeshta, mund të merrni raporte shumë të dobishme dhe interesante.

Të dhënat e agreguara për një periudhë të caktuar kohe

Pyetje

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

Koha DB

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

Koha I/O

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

TOP10 SQL sipas kohës totale

Pyetje

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

TOP10 SQL sipas kohës totale I/O

Pyetje

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 NGA KOHA TOTALE I/O
|   #|    queryid|      calls|    calls %|                   Kohë I/O (ms)|% Kohë I/O në db
+----+-----------+-----------+-----------+--------------------------------+-------------
|   1| 4152624390|          2|     .00001|00:08:31.616(    511616.592 ms.)|        31.06
|   2|  821760255|          2|     .00001|00:08:27.099(    507099.036 ms.)|        30.78
|   3|  655729273|          1|     .00000|00:05:02.209(    302209.137 ms.)|        18.35
|   4| 2460318461|          1|     .00000|00:04:05.981(    245981.117 ms.)|        14.93
|   5| 1484454471|          4|     .00001|00:00:39.144(     39144.221 ms.)|         2.38
|   6| 2194493487|          4|     .00001|00:00:18.182(     18182.816 ms.)|         1.10
|   7| 1053044345|          1|     .00000|00:00:16.611(     16611.722 ms.)|         1.01
|   8| 3644780286|          1|     .00000|00:00:00.436(       436.205 ms.)|          .03

TOP10 SQL sipas kohës maksimale të ekzekutimit

Pyetje

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 NGA KOHA MAKSIMALE E EKZEKUTIMIT
|   #|          snapshot| snapshotID|    queryid|                           max_time (ms)
+----+------------------+-----------+-----------+----------------------------------------
|   1|  05.04.2019 01:03|       4169|  655729273|        00:02:01.869(    121869.981 ms.)
|   2|  04.04.2019 17:00|       4153|  821760255|        00:01:41.570(    101570.841 ms.)
|   3|  04.04.2019 16:00|       4146|  821760255|        00:01:41.570(    101570.841 ms.)
|   4|  04.04.2019 16:00|       4144| 4152624390|        00:01:36.964(     96964.607 ms.)
|   5|  04.04.2019 17:00|       4151| 4152624390|        00:01:36.964(     96964.607 ms.)
|   6|  05.04.2019 10:00|       4188| 1484454471|        00:01:33.452(     93452.150 ms.)
|   7|  04.04.2019 17:00|       4150| 2460318461|        00:01:33.113(     93113.835 ms.)
|   8|  04.04.2019 15:00|       4140| 1484454471|        00:00:11.892(     11892.302 ms.)
|   9|  04.04.2019 16:00|       4145| 1484454471|        00:00:11.892(     11892.302 ms.)
|  10|  04.04.2019 17:00|       4152| 1484454471|        00:00:11.892(     11892.302 ms.)

TOP10 SQL sipas leximeve/shkrimeve tĂ« bufferit tĂ« PËRBASHKËT

Pyetje

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 NGA SHKËMBIMIT TË BLLoqeve TË BAZËS
|   #|          snapshot| snapshotID|    queryid|   blloqe të ndara lexuara|  blloqe të ndara shkruara
+----+------------------+-----------+-----------+---------------------+---------------------
|   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
--------------------------------------------------------------------------------------------

Histogrami i shpërndarjes së kërkesave sipas kohës maksimale të ekzekutimit

KĂ«rkesat

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

|-----------------------------------------------------------------------------------------------
| HISTOGRAMI I KOHEVE MAKSIMALE
| TOTALI I KËRKESEVE : 33851920
| KOHA MINIMALE  : 00:00:01.063
| KOHA MAKSIMALE  : 00:02:01.869
---------------------------------------------------------------------------------
|                      mini përshkrimi|                      maxi përshkrimi|     calls
+----------------------------------+----------------------------------+----------
| 00:00:01.063(      1063.830 ms.) | 00:00:13.144(     13144.445 ms.) | 9
| 00:00:13.144(     13144.445 ms.) | 00:00:25.225(     25225.060 ms.) | 0
| 00:00:25.225(     25225.060 ms.) | 00:00:37.305(     37305.675 ms.) | 0
| 00:00:37.305(     37305.675 ms.) | 00:00:49.386(     49386.290 ms.) | 0
| 00:00:49.386(     49386.290 ms.) | 00:01:01.466(     61466.906 ms.) | 0
| 00:01:01.466(     61466.906 ms.) | 00:01:13.547(     73547.521 ms.) | 0
| 00:01:13.547(     73547.521 ms.) | 00:01:25.628(     85628.136 ms.) | 0
| 00:01:25.628(     85628.136 ms.) | 00:01:37.708(     97708.751 ms.) | 4
| 00:01:37.708(     97708.751 ms.) | 00:01:49.789(    109789.366 ms.) | 2
| 00:01:49.789(    109789.366 ms.) | 00:02:01.869(    121869.981 ms.) | 0

TOP10 Snapshotet sipas Kërkesave për Sekondë

KĂ«rkesat

--pg_qps.sql
--Llogaritni Pyetjet Për Sekondë 
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 'Gabim - Nuk u gjet pg_stat_history për id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id  0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


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

|-----------------------------------------------------------------------------------------------
| TOP10 Snapshot-et e renditur nga numrat e QueryPerSeconds
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O time| I/O time %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|    2|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    3|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    4|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|    5|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|    6|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    7|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    8|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    9|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   10|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666

Historia e Ekzekutimit të Orëve me QueryPerSeconds dhe Kohën e I/O

Pyetje

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

|-----------------------------------------------------------------------------------------------
| HISTORIA E EKZEKUTIMIT NGA ORA DHE Koha I/O
-----------------------------------------------------------------------------------------------------------------------------------------------
| HISTORIA E KËRKIMEVE NË SEKONDË
|    #|          snapshot| snapshotID|      calls|                      koha totale db|        KPS|                          Koha I/O| Pjesa e kohës I/O %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    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

Përmbajtja e të gjithë SQL-selektëve

Pyetje

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

PĂ«rfundimi

Si duket, me mjete mjaft të thjeshta, mund të marrim një sasi të madhe informacioni të dobishëm mbi ngarkesën dhe gjendjen e bazës së të dhënave.

Vërejtje:Nëse në kërkesa regjistrojmë queryid, do të marrim historikun për një kërkesë të veçantë (për të kursyer hapësirë, raportet për kërkesa të veçanta janë lënë jashtë).

Pra, tĂ« dhĂ«nat statistikore mbi performancĂ«n e kĂ«rkesave—janĂ« tĂ« pranishme dhe po mblidhen.
Faza e parĂ« "mbledhja e tĂ« dhĂ«nave statistikore"—ështĂ« pĂ«rfunduar.

Mund tĂ« kalojmĂ« nĂ« fazĂ«n e dytë—"konfigurimi i metrikave tĂ« performancĂ«s".
Monitorimi i performancës së kërkesave PostgreSQL. Pjesa 1 - raportimi

Por kjo është një histori krejt tjetër.

To be continued...

Burimi: habr.com

Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Blini hosting tĂ« besueshĂ«m pĂ«r faqe interneti me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster