Pemantauan kinerja kueri PostgreSQL. Bagian 1 - pelaporan

Insinyur - diterjemahkan dari bahasa Latin - terinspirasi.
Seorang insinyur bisa melakukan apa saja. (c) R. Diesel.
Prasasti.

Atau cerita tentang mengapa administrator basis data perlu mengingat masa lalu pemrogramannya.

kata pengantar

Semua nama telah diubah. Pertandingan bersifat acak. Materinya semata-mata pendapat pribadi penulis.

Penafian garansi: dalam rangkaian artikel yang direncanakan tidak akan ada deskripsi rinci dan akurat tentang tabel dan skrip yang digunakan. Bahan tidak dapat langsung digunakan "SEBAGAIMANA ADANYA".
Pertama, karena banyaknya bahan,
kedua, karena ketajaman basis produksi pelanggan nyata.
Oleh karena itu, hanya ide dan deskripsi dalam bentuk paling umum yang akan diberikan dalam artikel.
Mungkin kedepannya sistem akan berkembang ke level postingan di GitHub, atau mungkin juga tidak. Waktu akan menunjukkan.

Awal cerita-Apakah Anda ingat bagaimana semuanya dimulai'.
Apa yang terjadi sebagai akibatnya, dalam istilah yang paling umum - "Sintesis sebagai salah satu metode untuk meningkatkan kinerja PostgreSQL»

Mengapa saya membutuhkan semua ini?

Nah, pertama-tama, agar tidak melupakan diri sendiri mengingat hari-hari indah di masa pensiun.
Kedua, untuk mensistematisasikan apa yang ditulis. Untuk saya sendiri, terkadang saya mulai bingung dan melupakan bagian-bagian yang terpisah.

Nah, dan yang paling penting - tiba-tiba itu bisa berguna bagi seseorang dan membantu untuk tidak menemukan kembali roda dan tidak mengumpulkan penggaruk. Dengan kata lain, tingkatkan karma Anda (bukan Khabrovsky). Karena hal yang paling berharga di dunia ini adalah ide. Yang utama adalah menemukan ide. Dan menerjemahkan ide menjadi kenyataan sudah merupakan masalah teknis murni.

Jadi mari kita mulai perlahan-lahan...

Perumusan masalah.

Tersedia:

PostgreSQL(10.5), beban campuran (OLTP+DSS), beban sedang hingga ringan, dihosting di AWS cloud.
Tidak ada pemantauan basis data, pemantauan infrastruktur disajikan sebagai alat AWS standar dalam konfigurasi minimal.

Diperlukan:

Pantau kinerja dan status database, temukan dan miliki informasi awal untuk mengoptimalkan kueri database yang berat.

Pengantar singkat atau analisis solusi

Untuk memulainya, mari kita coba menganalisis opsi untuk menyelesaikan masalah dari sudut pandang analisis komparatif tentang manfaat dan masalah bagi insinyur, dan biarkan mereka yang seharusnya ada dalam daftar staf menangani keuntungan dan kerugian. manajemen.

Opsi 1 - "Bekerja sesuai permintaan"

Kami meninggalkan semuanya apa adanya. Jika pelanggan tidak puas dengan kesehatan, kinerja database atau aplikasi, dia akan memberi tahu insinyur DBA melalui email atau dengan membuat insiden di kotak tiket.
Seorang insinyur, setelah menerima pemberitahuan, akan memahami masalahnya, menawarkan solusi, atau mengesampingkan masalah, berharap semuanya akan teratasi dengan sendirinya, dan bagaimanapun, semuanya akan segera dilupakan.
Roti jahe dan donat, memar dan benjolanRoti jahe dan donat:
1. Tidak ada tambahan yang harus dilakukan
2. Selalu ada kesempatan untuk keluar dan menjadi kotor.
3. Banyak waktu yang bisa Anda habiskan sendiri.
Memar dan benjolan:
1. Cepat atau lambat, pelanggan akan memikirkan tentang esensi keberadaan dan keadilan universal di dunia ini dan sekali lagi bertanya pada dirinya sendiri - mengapa saya membayar uang saya kepada mereka? Konsekuensinya selalu sama - satu-satunya pertanyaan adalah kapan pelanggan bosan dan melambaikan tangan. Dan pengumpan kosong. Sedih.
2. Perkembangan seorang insinyur adalah nol.
3. Kesulitan dalam penjadwalan pekerjaan dan loading

Opsi 2 - “Menari dengan rebana, pakai dan pakai sepatu”

Paragraf 1-Mengapa kami membutuhkan sistem pemantauan, kami akan menerima semua permintaan. Kami meluncurkan banyak jenis kueri ke kamus data dan tampilan dinamis, mengaktifkan semua jenis penghitung, memasukkan semuanya ke dalam tabel, menganalisis daftar dan tabel secara berkala, seolah-olah. Hasilnya, kami memiliki grafik, tabel, laporan yang bagus atau tidak. Hal utama - itu akan lebih, lebih.
Paragraf 2-Hasilkan aktivitas-jalankan analisis semua ini.
Paragraf 3-Kami sedang menyiapkan dokumen tertentu, kami menyebutnya dokumen ini, sederhananya - "bagaimana kami melengkapi database."
Paragraf 4- Pelanggan, melihat semua kemegahan grafik dan gambar ini, berada dalam kepercayaan naif yang kekanak-kanakan - sekarang semuanya akan bekerja untuk kita, segera. Dan, dengan mudah dan tanpa rasa sakit berpisah dengan sumber keuangan mereka. Manajemen juga yakin bahwa teknisi kami bekerja keras. Memuat maks.
Paragraf 5- Ulangi langkah 1 secara teratur.
Roti jahe dan donat, memar dan benjolanRoti jahe dan donat:
1. Kehidupan manajer dan insinyur itu sederhana, dapat diprediksi, dan penuh dengan aktivitas. Semuanya berdengung, semua orang sibuk.
2. Kehidupan pelanggan juga lumayan - dia selalu yakin bahwa Anda perlu sedikit bersabar dan semuanya akan berhasil. Tidak menjadi lebih baik, baiklah - dunia ini tidak adil, di kehidupan selanjutnya - beruntung.
Memar dan benjolan:
1. Cepat atau lambat, akan ada penyedia layanan serupa yang lebih pintar yang akan melakukan hal yang sama, tetapi sedikit lebih murah. Dan jika hasilnya sama, mengapa harus membayar lebih. Yang lagi-lagi akan menyebabkan hilangnya feeder.
2. Membosankan. Betapa membosankannya aktivitas kecil yang berarti.
3. Seperti pada versi sebelumnya - tidak ada pengembangan. Tetapi bagi seorang insinyur, kekurangannya adalah, tidak seperti opsi pertama, di sini Anda harus terus-menerus membuat IDB. Dan itu membutuhkan waktu. Yang bisa dihabiskan untuk kepentingan orang yang Anda cintai. Karena Anda tidak bisa menjaga diri sendiri, semua orang peduli dengan Anda.

Opsi 3-Tidak perlu menemukan sepeda, Anda perlu membelinya dan mengendarainya.

Insinyur dari perusahaan lain dengan sengaja makan pizza dengan bir (oh, masa kejayaan St. Petersburg di tahun 90-an). Mari gunakan sistem pemantauan yang dibuat, di-debug, dan berfungsi, dan secara umum, sistem tersebut membawa manfaat (yah, setidaknya bagi pembuatnya).
Roti jahe dan donat, memar dan benjolanRoti jahe dan donat:
1. Tidak perlu membuang waktu untuk menemukan apa yang sudah ditemukan. Ambil dan gunakan.
2. Sistem pemantauan tidak ditulis oleh orang bodoh, dan tentu saja berguna.
3. Sistem pemantauan kerja biasanya memberikan informasi yang disaring yang bermanfaat.
Memar dan benjolan:
1. Insinyur dalam hal ini bukanlah seorang insinyur, melainkan hanya pengguna produk orang lain atau pengguna.
2. Pelanggan harus diyakinkan akan perlunya membeli sesuatu yang umumnya tidak ingin dia pahami, dan seharusnya tidak, dan secara umum anggaran untuk tahun tersebut telah disetujui dan tidak akan berubah. Maka Anda perlu mengalokasikan sumber daya terpisah, mengkonfigurasinya untuk sistem tertentu. Itu. Pertama, Anda perlu membayar, membayar, dan membayar lagi. Dan pelanggan itu pelit. Ini adalah norma kehidupan ini.

Apa yang harus dilakukan, Chernyshevsky? Pertanyaan Anda sangat relevan. (Dengan)

Dalam kasus khusus ini dan situasi saat ini, Anda dapat melakukan sedikit berbeda - mari kita buat sistem pemantauan kita sendiri.

Ya, bukan sistem, tentu saja, dalam arti sebenarnya, ini terlalu keras dan lancang, tapi setidaknya buatlah diri Anda lebih mudah dan kumpulkan lebih banyak informasi untuk menyelesaikan insiden kinerja. Agar tidak menemukan diri Anda dalam situasi - "pergi ke sana, saya tidak tahu di mana, temukan itu, saya tidak tahu apa."

Apa pro dan kontra dari opsi ini:

Pro:
1. Ini menarik. Yah, setidaknya lebih menarik daripada konstanta "shrink datafile, alter tablespace, dll."
2. Ini adalah keterampilan baru dan perkembangan baru. Yang di masa depan cepat atau lambat akan memberikan roti jahe dan donat yang memang layak.
Cons:
1. Harus bekerja. Banyak bekerja.
2. Anda harus secara teratur menjelaskan arti dan perspektif dari semua aktivitas.
3. Sesuatu harus dikorbankan, karena satu-satunya sumber daya yang tersedia bagi insinyur - waktu - dibatasi oleh Semesta.
4. Yang terburuk dan paling tidak menyenangkan - akibatnya, sampah seperti "Bukan tikus, bukan katak, tapi binatang kecil yang tidak dikenal" mungkin muncul.

Siapa yang tidak mengambil risiko sesuatu tidak minum sampanye.
Jadi, kesenangan dimulai.

Ide umum - skematis

(Ilustrasi diambil dari artikel «Sintesis sebagai salah satu metode untuk meningkatkan kinerja PostgreSQL")

Penjelasan:

• Basis data target diinstal dengan ekstensi PostgreSQL standar "pg_stat_statements".
• Dalam database pemantauan, kami membuat satu set tabel layanan untuk menyimpan riwayat pg_stat_statements pada tahap awal dan untuk mengonfigurasi metrik dan pemantauan di masa mendatang
• Di host pemantauan, kami membuat satu set skrip bash, termasuk skrip untuk menghasilkan insiden di sistem tiket.

Tabel layanan

Pertama-tama, ERD yang disederhanakan secara skematis, apa yang terjadi pada akhirnya:

Deskripsi singkat tentang tabelendpoint - host, titik koneksi ke instance
Database - opsi basis data
pg_stat_history.pg_stat_history - tabel historis untuk menyimpan snapshot sementara dari tampilan pg_stat_statements dari database target
metrik_glosarium - Kamus metrik kinerja
metrik_config - konfigurasi metrik individu
metrik - metrik khusus untuk permintaan yang sedang dipantau
metric_alert_history - sejarah peringatan kinerja
log_query - tabel layanan untuk menyimpan catatan yang diurai dari file log PostgreSQL yang diunduh dari AWS
paduk - parameter periode waktu yang digunakan sebagai dasar
pos pemeriksaan - konfigurasi metrik untuk memeriksa status database
checkpoint_alert_history - riwayat peringatan metrik pemeriksaan status basis data
pg_stat_db_queries — tabel layanan permintaan aktif
log aktivitas — tabel layanan log aktivitas
jebakan_oid - tabel layanan konfigurasi perangkap

Tahap 1 - kumpulkan statistik kinerja dan dapatkan laporan

Tabel digunakan untuk menyimpan informasi statistik. pg_stat_history.pg_stat_history
struktur tabel pg_stat_history

                                          Tabel Kolom "public.pg_stat_history" | ketik | Pengubah-----------------------+-----------------------------+---- -------------------------------- id | bilangan bulat | bukan null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | stempel waktu tanpa zona waktu | database_id | bilangan bulat | dbid | oid | userid | oid | queryid | besar | kueri | teks | panggilan | besar | total_waktu | presisi ganda | min_time | presisi ganda | max_time | presisi ganda | mean_time | presisi ganda | stddev_time | presisi ganda | baris | besar | shared_blks_hit | besar | shared_blks_read | besar | shared_blks_dirtyed | besar | shared_blks_writen | besar | local_blks_hit | besar | local_blks_read | besar | local_blks_dikotori | besar | local_blks_writen | besar | temp_blks_read | besar | temp_blks_tertulis | besar | blk_read_time | presisi ganda | blk_write_time | presisi ganda | baseline_id | bilangan bulat | Indeks: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Kendala kunci asing: "database_id_fk" FOREIGN KEY (database_id) REFERENSI database(id ) PADA HAPUS CASCADE

Seperti yang Anda lihat, tabel hanyalah data tampilan kumulatif pg_stat_statement dalam basis data sasaran.

Penggunaan tabel ini sangat sederhana.

pg_stat_history.pg_stat_history akan mewakili akumulasi statistik eksekusi kueri untuk setiap jam. Di awal setiap jam, setelah mengisi tabel, statistik pg_stat_statement ulang dengan pg_stat_statements_reset().
Catatan: statistik dikumpulkan untuk permintaan dengan durasi lebih dari 1 detik.
Mengisi 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;

Akibatnya, setelah jangka waktu tertentu di meja pg_stat_history.pg_stat_history kita akan memiliki satu set snapshot dari isi tabel pg_stat_statement basis data sasaran.

Sebenarnya melaporkan

Menggunakan kueri sederhana, Anda bisa mendapatkan laporan yang cukup berguna dan menarik.

Data gabungan untuk jangka waktu tertentu

Minta

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 ;

Waktu DB

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

Waktu I/O

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

Minta

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

TOP10 SQL dengan total waktu I/O

Minta

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 BERDASARKAN WAKTU I/O | #| queryid| panggilan| panggilan %| Waktu I/O (ms)|db Waktu I/O % +----+-----------+------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 mdtk)| 31.06 Juni | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 mdtk)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 mdtk)| 18.35 | 4| 2460318461| 1| .00000|00:04:05.981( 245981.117 mdtk)| 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 mdtk)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 mdtk)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 mdtk)| .03

TOP10 SQL dengan waktu maksimal eksekusi

Minta

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

TOP10 SQL dengan SHARED buffer baca/tulis

Minta

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 OLEH SHARED BUFFER BACA/TULIS | #| foto| snapshotID| queryid| blok bersama membaca | blok bersama tulis +----+------------------+-----------+---------- -+-----------------------+--------------------------- | 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 distribusi kueri berdasarkan waktu eksekusi maksimum

Pertanyaan

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

|------------------------------------------------ ---------------------------------------- | HISTOGRAM MAX_TIME | JUMLAH PANGGILAN : 33851920 | WAKTU MIN : 00:00:01.063 | WAKTU MAKSIMAL : 00:02:01.869 ---------------------------------- -------- ---------------------------- | durasi min| durasi maksimal| panggilan +----------------------------------+------------- ---------------------+---------- | 00:00:01.063( 1063.830 mdtk) | 00:00:13.144( 13144.445 mdtk) | 9 | 00:00:13.144( 13144.445 mdtk) | 00:00:25.225( 25225.060 mdtk) | 0 | 00:00:25.225( 25225.060 mdtk) | 00:00:37.305( 37305.675 mdtk) | 0 | 00:00:37.305( 37305.675 mdtk) | 00:00:49.386( 49386.290 mdtk) | 0 | 00:00:49.386( 49386.290 mdtk) | 00:01:01.466( 61466.906 mdtk) | 0 | 00:01:01.466( 61466.906 mdtk) | 00:01:13.547( 73547.521 mdtk) | 0 | 00:01:13.547( 73547.521 mdtk) | 00:01:25.628( 85628.136 mdtk) | 0 | 00:01:25.628( 85628.136 mdtk) | 00:01:37.708( 97708.751 mdtk) | 4 | 00:01:37.708( 97708.751 mdtk) | 00:01:49.789( 109789.366 mdtk) | 2 | 00:01:49.789( 109789.366 mdtk) | 00:02:01.869( 121869.981 mdtk) | 0

Jepretan TOP10 menurut Kueri per Detik

Pertanyaan

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

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

Riwayat Eksekusi Setiap Jam dengan QueryPerSeconds dan Waktu I/O

Minta

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

Teks dari semua pilihan SQL

Minta

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

Total

Seperti yang Anda lihat, dengan cara yang cukup sederhana, Anda bisa mendapatkan banyak informasi berguna tentang beban kerja dan status database.

Catatan:Jika Anda memperbaiki queryid dalam kueri, maka kami akan mendapatkan riwayat untuk permintaan terpisah (untuk menghemat ruang, laporan untuk permintaan terpisah dihilangkan).

Jadi, data statistik tentang kinerja kueri tersedia dan dikumpulkan.
Tahap pertama "pengumpulan data statistik" selesai.

Anda dapat melanjutkan ke tahap kedua - "mengonfigurasi metrik kinerja".


Tapi itu cerita lain.

Untuk dilanjutkan ...

Sumber: www.habr.com