Ngawaskeun kinerja query PostgreSQL. Bagian 1 - ngalaporkeun

Insinyur - ditarjamahkeun tina basa Latin - diideuan.
Insinyur tiasa ngalakukeun naon waé. (c) R. Diesel.
Epigraphs.

Atanapi carita ngeunaan kunaon pangurus pangkalan data kedah émut katukang programna.

foreword

Kabéh ngaran geus robah. The coincidences acak. bahan ngagambarkeun solely pendapat pribadi pangarang.

Bantahan tina jaminan: Runtuyan artikel anu direncanakeun moal ngandung pedaran anu lengkep sareng akurat ngeunaan tabel sareng skrip anu dianggo. Bahanna teu tiasa dianggo langsung "AS IS".
Anu mimiti, kusabab volume bahan anu ageung,
Bréh, alatan hubungan deukeut jeung basa produksi hiji customer nyata.
Ku alatan éta, artikel bakal ngandung ukur gagasan jeung déskripsi dina formulir paling umum.
Meureun di mangsa nu bakal datang sistem bakal tumuwuh nepi ka tingkat keur dipasang dina GitHub, atawa meureun henteu. Waktos bakal nunjukkeun.

Awal carita - "Naha anjeun émut kumaha éta sadayana dimimitian".
Naon anu lumangsung salaku hasilna, dina istilah paling umum - "Sintésis salaku salah sahiji metode pikeun ningkatkeun kinerja PostgreSQL»

Naha kuring peryogi sadayana ieu?

Muhun, kahiji, sangkan teu poho, inget poé glorious dina pangsiun.
Bréh, pikeun sistematis naon anu ditulis. Kusabab kadang kuring mimiti bingung sarta poho bagian nu tangtu.

Nya, sareng anu paling penting nyaéta éta tiasa dianggo pikeun batur sareng ngabantosan aranjeunna ngahindarkeun deui kabayang sareng henteu ngumpulkeun rake. Dina basa sejen, ningkatkeun karma anjeun (sanes Khabrov urang). Kusabab hal anu paling berharga di dunya ieu nyaéta ide. Hal utama pikeun manggihan ide. Tapi ngarobah hiji gagasan kana kanyataanana mangrupakeun patarosan teknis murni.

Ku kituna, hayu urang mimitian, saeutik demi saeutik ...

Rumusan masalah.

Aya:

PostgreSQL (10.5) database, tipe beban campuran (OLTP + DSS), beban sedeng-lampu, ayana dina awan AWS.
Henteu aya ngawaskeun pangkalan data; ngawaskeun infrastruktur disayogikeun dina bentuk alat AWS standar dina konfigurasi minimal.

Dikeukeun pisan:

Ngawas kinerja sarta status tina database, panggihan tur gaduh informasi awal pikeun optimizing queries database beurat.

bubuka ringkes atawa analisis pilihan solusi

Pikeun mimitian ku, hayu urang coba analisa pilihan pikeun ngarengsekeun masalah tina sudut pandang analisa komparatif ngeunaan kauntungan sareng kalemahan insinyur, sareng ngantepkeun jalma anu ngagaduhan hakna dumasar kana jadwal kepegawaian nguruskeun kauntungan sareng karugian manajemén.

Pilihan 1 - "Gawe on demand"

Urang ninggalkeun sagalana sakumaha anu kasebut. Mun nasabah teu wareg jeung hal dina fungsionalitas nu, kinerja database atawa aplikasi, anjeunna bakal ngabéjaan insinyur DBA ku e-mail atanapi ku nyieun hiji kajadian dina baki tikét.
Insinyur, anu nampi béwara, bakal ngartos masalahna, nawiskeun solusi atanapi nempatkeun masalah dina pembakar tukang, ngarep-arep yén sadayana bakal ngabéréskeun sorangan, sareng kumaha waé, sadayana bakal hilap.
Gingerbread jeung donat, bruises jeung nabrakGingerbread sareng donat:
1. Aya teu kudu ngalakukeun nanaon tambahan.
2. Aya salawasna kasempetan pikeun nyieun excuses na screw up.
3. A loba waktu nu bisa méakkeun dina kawijaksanaan anjeun sorangan.
Borok sareng bisul:
1. Sooner atanapi engké, nasabah bakal mikir ngeunaan hakekat ayana sarta kaadilan universal di dunya ieu sarta sakali deui nanya ka dirina pertanyaan - naha kuring mayar aranjeunna duit kuring? Balukarna sok sami - hiji-hijina patarosan nyaéta nalika palanggan bakal bosen sareng pamit. Jeung feeder bakal kosong. Sedih.
2. ngembangkeun Insinyur - enol.
3. Kasusah dina ngarencanakeun karya sareng ngamuat

Pilihan 2- "Menari sareng rebana, ngukus sareng ngagem sapatu"

Paragraf 1-Naha urang kudu sistem ngawaskeun, urang bakal nampa sagalana kalawan requests. Urang ngajalankeun kebat tina sagala sorts queries ka kamus data jeung pintonan dinamis, ngahurungkeun sagala sorts counters, nempatkeun sagalana kana tabel, sarta périodik nganalisis daptar na tabel. Hasilna, urang gaduh grafik, tabel, laporan anu saé atanapi henteu saé. Hal utama anu gaduh leuwih, leuwih.
Paragraf 2-Kami ngahasilkeun kagiatan sareng ngaluncurkeun analisa sadayana ieu.
Paragraf 3-Kami nyiapkeun dokumen anu tangtu, kami nyauran dokumen ieu ngan saukur - "kumaha urang kedah nyetél pangkalan data."
Paragraf 4-Palanggan, ningali sadaya splendor ieu grafik na angka, aya dina bubudakeun, kapercayaan naif - ayeuna sagalana bakal dianggo pikeun urang, geura-giru. Na, anjeunna gampang jeung painlessly bagian kalawan sumberdaya finansial na. Manajemén ogé yakin yén insinyur urang damel saé. Loading di maksimum.
Paragraf 5- Ulang Lengkah 1 rutin.
Gingerbread jeung donat, bruises jeung nabrakGingerbread sareng donat:
1. Kahirupan manajer sarta insinyur basajan, bisa diprediksi sarta ngeusi aktivitas. Sadayana ngageter, sadayana sibuk.
2. Kahirupan palanggan ogé henteu goréng - anjeunna salawasna yakin yén anjeunna ngan ukur kedah sabar sakedik sareng sadayana bakal jalan kaluar. Henteu janten langkung saé, saé, dunya henteu adil, dina kahirupan salajengna anjeun bakal untung.
Borok sareng bisul:
1. Sooner atanapi engké, bakal aya panyadia gancang tina layanan sarupa anu bakal ngalakukeun hal anu sarua, tapi saeutik langkung mirah. Sareng upami hasilna sami, naha mayar langkung. Nu deui bakal ngakibatkeun leungit feeder.
2. Ieu boring. Kumaha boring sagala aktivitas euweuh hartina.
3. Sapertos dina versi sateuacana, teu aya pamekaran. Tapi pikeun insinyur, downside nyaeta, kawas pilihan kahiji, anjeun kudu terus-terusan ngahasilkeun IBD. Sareng ieu peryogi waktos. Anu anjeun tiasa nyéépkeun pikeun kapentingan anu dipikacinta. Kusabab anjeun teu bisa ngurus diri, teu saurang ogé mere damn ngeunaan anjeun.

Pilihan 3 - Anjeun teu kedah nyiptakeun sapédah, anjeun ngan ukur kedah mésér sareng naék.

Teu keur nanaon nu insinyur ti pausahaan séjén dahar pizza jeung bir (oh, poé kamulyaan St. Petersburg dina 90s). Hayu urang nganggo sistem ngawaskeun anu didamel, di-debug sareng jalan, sareng umumna diomongkeun kauntungan (saé, sahenteuna pikeun panyipta).
Gingerbread jeung donat, bruises jeung nabrakGingerbread sareng donat:
1. Teu perlu runtah waktu datang nepi ka hal anu geus invented. Candak eta sarta ngagunakeun éta.
2. Sistem pangimeutan teu ditulis ku fools sarta aranjeunna, tangtosna, mangpaat.
3. Sistem ngawaskeun gawé biasana nyadiakeun informasi disaring mangpaat.
Borok sareng bisul:
1. Insinyur dina hal ieu sanés insinyur, tapi ngan ukur pangguna produk batur, atanapi pangguna.
2. nasabah kudu yakin kana kudu meuli hal anu, umumna disebutkeun, manéhna teu hayang ngarti, jeung teu kudu, sarta sacara umum anggaran pikeun sataun geus disatujuan tur moal robah. Teras anjeun kedah nyayogikeun sumber anu misah sareng ngonpigurasikeunana pikeun sistem anu khusus. Jelema. kahiji anjeun kudu mayar, mayar jeung mayar deui. Jeung nasabah téh pelit. Ieu norma kahirupan ieu.

Naon anu kudu dipigawé - Chernyshevsky? Patarosan anjeun relevan pisan. (jeung)

Dina kasus khusus ieu sareng kaayaan ayeuna, anjeun tiasa ngalakukeunana rada béda - hayu urang ngadamel sistem monitoring sorangan.

Nya, sanés sistem, tangtosna, dina harti pinuh ku kecap, éta nyaring teuing sareng presumptuous, tapi sahenteuna kumaha waé ngajantenkeun tugas anjeun langkung gampang sareng ngumpulkeun langkung seueur inpormasi pikeun ngabéréskeun kajadian kinerja. Pikeun henteu mendakan diri dina kaayaan - "ka ditu, kuring henteu terang dimana, milari hiji hal, kuring henteu terang naon."

Naon pro jeung kontra ngeunaan pilihan ieu:

pro:
1. Ieu metot. Nya, sahenteuna éta langkung pikaresepeun tibatan "ngaleutikan file data, ngarobih tablespace, jsb."
2. Ieu kaahlian anyar jeung ngembangkeun anyar. Nu, sooner atanapi engké, bakal mere Anjeun well-deserved Gingerbread jeung donat.
kontra:
1. Anjeun kudu digawé. Kerja keras.
2. Anjeun kudu rutin ngajelaskeun harti jeung prospek sadaya kagiatan.
3. Aya anu kedah dikorbankeun, sabab hiji-hijina sumber daya anu sayogi pikeun insinyur - waktos - diwatesan ku Alam Semesta.
4. Hal awon jeung paling pikaresepeun - hasilna tiasa janten omong kosong sapertos "Henteu beurit, sanés bangkong, tapi sato anu teu dipikanyaho."

Jalma anu henteu nyandak résiko henteu nginum sampanye.
Ku kituna - fun dimimitian.

gagasan umum - schematically

(Ilustrasi dicokot tina artikel «Sintésis salaku salah sahiji metode pikeun ningkatkeun kinerja PostgreSQL")

Katerangan:

• Ekstensi PostgreSQL standar "pg_stat_statements" dipasang dina pangkalan data target.
• Dina pangkalan data ngawaskeun, kami nyiptakeun sakumpulan tabel jasa pikeun nyimpen sajarah pg_stat_statements dina tahap awal sareng pikeun ngonpigurasikeun métrik sareng ngawaskeun ka hareup.
• Dina host ngawaskeun, urang nyiptakeun sakumpulan skrip bash, kalebet pikeun ngahasilkeun kajadian dina sistem tikét.

méja jasa

Kahiji, hiji schematic disederhanakeun ERD, naon anu lumangsung dina tungtungna:

pedaran ringkes tabeltitik - host, titik sambungan kana conto
database - parameter database
pg_stat_history - tabel sajarah pikeun nyimpen snapshots samentara tina pg_stat_statements view tina database target
metric_glossary - kamus métrik kinerja
metric_config - konfigurasi métrik individu
métrik - métrik husus pikeun pamundut nu keur diawaskeun
metric_alert_history - sajarah warnings kinerja
log_query - tabel jasa pikeun nyimpen rékaman parsed tina file log PostgreSQL diundeur ti AWS
dasar - parameter tina période waktu dipaké salaku dasar
papariksaan - Konfigurasi métrik pikeun mariksa status pangkalan data
checkpoint_alert_history - sajarah peringatan ngeunaan métrik dipariksa kaséhatan database
pg_stat_db_queries - tabel jasa tina requests aktip
log_aktivitas - tabel jasa log aktivitas
trap_oid - méja jasa konfigurasi bubu

Tahap 1 - ngumpulkeun inpormasi statistik ngeunaan kinerja sareng nampi laporan

Méja dipaké pikeun nyimpen informasi statistik pg_stat_history
pg_stat_history struktur tabel

                                          Méja "public.pg_stat_history" Kolom | Tipe | Modifiers ---------+------------------------- - ------------------------------------------ id | integer | teu null standar nextval ( 'pg_stat_history_id_seq':: regclass) snapshot_timestamp | timestamp tanpa zone waktos | id_database | integer | dbid | aduh | id pamaké | aduh | queryid | badag | patarosan | téks | nelepon | badag | total_time | precision ganda | min_time | precision ganda | max_time | precision ganda | mean_time | precision ganda | stddev_time | precision ganda | jajaran | badag | shared_blks_hit | badag | shared_blks_read | badag | shared_blks_dirtied | badag | shared_blks_written | badag | local_blks_hit | badag | local_blks_read | badag | local_blks_dirtied | badag | local_blks_written | badag | temp_blks_read | badag | temp_blks_written | badag | blk_read_time | precision ganda | blk_write_time | precision ganda | baseline_id | integer | Indéks: "pg_stat_history_pkey" KUNCI UTAMA, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Foreign-key constraints: (Yfdatabase_IDX_database) ) ON ngahapus CASCADE

Sakumaha anjeun tiasa tingali, tabél ngan data view kumulatif pg_stat_statements dina database target.

Ngagunakeun tabél ieu basajan pisan

pg_stat_history bakal ngagambarkeun akumulasi statistik palaksanaan query pikeun tiap jam. Dina awal unggal jam, sanggeus ngeusian tabel, statistik pg_stat_statements reset kalawan pg_stat_statements_reset().
Catetan: Statistik dikumpulkeun pikeun patarosan kalayan durasi palaksanaan langkung ti 1 detik.
Populating tabel pg_stat_history

--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
  endpoint_rec record ;
  database_rec record ;
  pg_stat_snapshot record ;
  current_snapshot_timestamp timestamp without time zone;
BEGIN
  current_snapshot_timestamp = date_trunc('minute',now());  
  
  FOR endpoint_rec IN SELECT * FROM endpoint 
  LOOP
    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
	  LOOP
	    
		RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
		
		--Connect to the target DB	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_time double precision , 
	         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
             local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
             temp_blks_read	 bigint ,temp_blks_written bigint ,
             blk_read_time double precision , blk_write_time double precision	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
	
        FOR pg_stat_snapshot IN
          --All queries with max_time greater than 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

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

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

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

Hasilna, sanggeus sababaraha periode waktu dina tabél pg_stat_history urang bakal boga susunan snapshots tina eusi méja pg_stat_statements database sasaran.

Sabenerna ngalaporkeun

Ngagunakeun queries basajan, anjeun bisa meunangkeun laporan rada mangpaat tur metot.

Data agrégat pikeun kurun waktu nu tangtu

Kahoyong

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 ;

Waktos DB

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

I/O Waktos

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

TOP10 SQL ku total_time

Kahoyong

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 ku total waktos palaksanaan | #| queryid| nelepon| nelepon %| total_time (ms) | dbtime% +----------+-----------+-----------+-----------+ ------ --------------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 mnt)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 mnt)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 mnt)| 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 mdet)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 mdet)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 mdet)| .03

TOP10 SQL ku total I / waktos O

Kahoyong

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 ku total I / O TIME | #| queryid| nelepon| nelepon %| Waktos I/O (ms)|db waktos I/O % +----+-----------+-----------+------ -------------------------+--------------------------------+----- ------ -- | 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 mnt)| 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 mdet)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 mdet)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 mdet)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 mdet)| .03

TOP10 SQL ku max waktos palaksanaan

Kahoyong

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 ku MAX waktos palaksanaan | #| jepretan| snapshotID| queryid| max_time (ms) +-------------------------------------- ------------------------------------------ | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2 ( 04.04.2019 mdet) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16 ( 00 mdet) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144 ( 4152624390 mdet) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00 (01 mdet) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452 (93452.150 mdet) | 7| 04.04.2019/17/00 4150:2460318461| 00| 01| 33.113:93113.835:8( 04.04.2019 mdet) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16 (00 mdet) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 mdet) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX mdet) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX(XNUMX md)

TOP10 SQL ku panyangga dibagikeun dibaca / nulis

Kahoyong

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 ku dibagikeun panyangga maca / nulis | #| jepretan| snapshotID| queryid| blok dibagikeun dibaca| tulis blok dibagi +----+-------------------+---------- --------------------------------------- | 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 sebaran requests ku waktos palaksanaan maksimum

Paménta

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 | JUMLAH TELEPON: 33851920 | MIN WAKTU: 00:00:01.063 | WAKTU MAKSIMAL: 00:02:01.869 ------------------------------------------ ---------------------------------------------------- | lilana mnt| lilana max| nelepon +---------------------------------+-------------------------- ---------------------+---------- | 00:00:01.063( 1063.830 mdet) | 00:00:13.144 ( 13144.445 mdet) | 9 | 00:00:13.144 ( 13144.445 mdet) | 00:00:25.225( 25225.060 mdet) | 0 | 00:00:25.225( 25225.060 mdet) | 00:00:37.305( 37305.675 mdet) | 0 | 00:00:37.305( 37305.675 mdet) | 00:00:49.386( 49386.290 mdet) | 0 | 00:00:49.386( 49386.290 mdet) | 00:01:01.466( 61466.906 mdet) | 0 | 00:01:01.466( 61466.906 mdet) | 00:01:13.547( 73547.521 mdet) | 0 | 00:01:13.547( 73547.521 mdet) | 00:01:25.628 ( 85628.136 mdet) | 0 | 00:01:25.628 ( 85628.136 mdet) | 00:01:37.708 ( 97708.751 mdet) | 4 | 00:01:37.708 ( 97708.751 mdet) | 00:01:49.789 ( 109789.366 mdet) | 2 | 00:01:49.789 ( 109789.366 mdet) | 00:02:01.869 ( 121869.981 mdet) | 0

TOP10 Snapshots ku Query per Second

Paménta

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

Sajarah Palaksanaan Sajam sareng QueryPerSeconds sareng I / O Time

Kahoyong

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

Téks sadaya SQL-milih

Kahoyong

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

hasil

Sakumaha anjeun tiasa tingali, ngagunakeun cara anu cukup saderhana, anjeun tiasa nampi seueur inpormasi anu mangpaat ngeunaan beban kerja sareng kaayaan pangkalan data.

Catetan:Lamun urang ngarekam queryid dina query, urang bakal meunang sajarah pikeun query misah (dina raraga ngahemat spasi, laporan pikeun query misah disingkahkeun).

Janten, data statistik ngeunaan kinerja query sayogi sareng dikumpulkeun.
Tahap kahiji "ngumpulan data statistik" réngsé.

Anjeun tiasa ngaléngkah ka tahap kadua - "nyetél métrik kinerja".


Tapi éta carita anu lengkep.

Ngalajengkeun…

sumber: www.habr.com