🥇PostgreSQL so'rovlari bajarilishini monitoring qilish. 1-qism - hisobot

Muhandis - lotin tilidan tarjima qilingan - ilhomlantirilgan.
Muhandis hamma narsani qila oladi. (c) R. Dizel.
Epigraflar.

Yoki ma'lumotlar bazasi ma'muri nima uchun dasturlash o'tmishini eslab qolish kerakligi haqidagi hikoya.

muqaddima

Barcha ismlar o'zgartirildi. Uchrashuvlar tasodifiy. Material faqat muallifning shaxsiy fikridir.

Kafolatlardan voz kechish: rejalashtirilgan maqolalar seriyasida foydalanilgan jadvallar va skriptlarning batafsil va aniq tavsifi bo'lmaydi. Materiallarni darhol "ISH BO'LGAN" sifatida ishlatish mumkin emas.
Birinchidan, katta miqdordagi material tufayli,
ikkinchidan, haqiqiy mijozning ishlab chiqarish bazasi bilan o'tkirligi tufayli.
Shuning uchun maqolalarda faqat eng umumiy shakldagi fikrlar va tavsiflar beriladi.
Ehtimol, kelajakda tizim GitHub-ga joylashtirish darajasiga ko'tariladi yoki yo'q. Vaqt ko'rsatadi.

Hikoyaning boshlanishi -Hammasi qanday boshlanganini eslaysizmi?".
Natijada nima sodir bo'ldi, eng umumiy ma'noda - "Sintez PostgreSQL ish faoliyatini yaxshilash usullaridan biri sifatida»

Nega menga bularning barchasi kerak?

Xo'sh, birinchi navbatda, nafaqadagi shonli kunlarni eslab, o'zingizni unutmaslik uchun.
Ikkinchidan, yozilganlarni tizimlashtirish. Allaqachon o'zim uchun, ba'zida men chalkashib ketaman va alohida qismlarni unutaman.

Xo'sh, va eng muhimi - to'satdan kimdir uchun foydali bo'lishi mumkin va g'ildirakni qayta ixtiro qilmaslikka va rake yig'maslikka yordam beradi. Boshqacha qilib aytganda, karmangizni yaxshilang (Xabrovskiy emas). Chunki bu dunyodagi eng qimmatli narsa g'oyalardir. Asosiysi, fikrni topish. Va g'oyani haqiqatga aylantirish allaqachon texnik masala.

Shunday qilib, asta-sekin boshlaylik ...

Muammoni shakllantirish.

Mavjud:

PostgreSQL(10.5), aralash yuk (OLTP+DSS), oʻrtacha va engil yuk, AWS bulutida joylashtirilgan.
Ma'lumotlar bazasi monitoringi mavjud emas, infratuzilma monitoringi minimal konfiguratsiyada standart AWS vositalari sifatida taqdim etiladi.

Majburiy:

Ma'lumotlar bazasining ishlashi va holatini kuzatib boring, og'ir ma'lumotlar bazasi so'rovlarini optimallashtirish uchun dastlabki ma'lumotlarni toping va unga ega bo'ling.

Yechimlarni qisqacha tanishtirish yoki tahlil qilish

Boshlash uchun, keling, muammoni hal qilish variantlarini muhandis uchun foyda va muammolarni qiyosiy tahlil qilish nuqtai nazaridan tahlil qilishga harakat qilaylik va xodimlar ro'yxatida bo'lishi kerak bo'lganlar foyda va yo'qotishlar bilan shug'ullansin. boshqaruv.

Variant 1 - "Talab bo'yicha ishlash"

Biz hamma narsani avvalgidek qoldiramiz. Agar mijoz sog'lig'i, ma'lumotlar bazasi yoki ilovaning ishlashidagi biror narsadan qoniqmasa, u DBA muhandislarini elektron pochta orqali yoki chipta qutisida hodisa yaratish orqali xabardor qiladi.
Xabarnoma olgan muhandis hamma narsa o'z-o'zidan hal bo'lishiga umid qilib, muammoni tushunadi, yechim taklif qiladi yoki muammoni to'xtatadi va baribir hamma narsa tez orada unutiladi.
Gingerbread va donuts, ko'karishlar va zarbalarGingerbread va donuts:
1. Hech narsa qilish kerak emas
2. Har doim tashqariga chiqish va ifloslanish imkoniyati mavjud.
3. O'zingiz sarflashingiz mumkin bo'lgan ko'p vaqt.
Ko'karishlar va ko'karishlar:
1. Ertami-kechmi mijoz bu dunyoda borliq va umuminsoniy adolatning mohiyati haqida o‘ylaydi va yana bir bor o‘ziga savol beradi – nega men ularga pulimni to‘layapman? Natija har doim bir xil bo'ladi - mijoz zerikib, xayrlashganda bitta savol. Va oziqlantiruvchi bo'sh. Bu achinarli.
2. Muhandisning rivojlanishi nolga teng.
3. Ishni rejalashtirish va yuklashda qiyinchiliklar

Variant 2 - "Dambur bilan raqsga tushing, poyabzal kiying va kiying"

1-band-Bizga monitoring tizimi nima uchun kerak, barcha so‘rovlarni qabul qilamiz. Biz ma'lumotlar lug'ati va dinamik ko'rinishlarga har xil turdagi so'rovlarni ishga tushiramiz, barcha turdagi hisoblagichlarni yoqamiz, hamma narsani jadvallarga keltiramiz, vaqti-vaqti bilan ro'yxat va jadvallarni tahlil qilamiz. Natijada, bizda chiroyli yoki unchalik bo'lmagan grafiklar, jadvallar, hisobotlar mavjud. Asosiysi, bu ko'proq, ko'proq bo'ladi.
2-band-Faoliyatni yaratish - bularning barchasini tahlil qilish.
3-band-Biz ma'lum bir hujjat tayyorlayapmiz, bu hujjatni oddiygina - "ma'lumotlar bazasini qanday jihozlaymiz" deb ataymiz.
4-band- Xaridor, grafik va raqamlarning bu ajoyibligini ko'rib, bolalarcha sodda ishonchga ega - endi hamma narsa biz uchun ishlaydi, tez orada. Va, oson va og'riqsiz ularning moliyaviy resurslari bilan bo'linadi. Rahbariyat ham muhandislarimiz astoydil mehnat qilayotganiga amin. Maksimal yuklash.
5-band- 1-bosqichni muntazam ravishda takrorlang.
Gingerbread va donuts, ko'karishlar va zarbalarGingerbread va donuts:
1. Menejerlar va muhandislarning hayoti oddiy, oldindan aytib bo'ladigan va faoliyat bilan to'la. Hammasi shovqin-suron, hamma band.
2. Buyurtmachining hayoti ham yomon emas – u har doim bir oz sabr qilish kerak va hammasi joyida bo‘lishiga amin. Yaxshilashmas, yaxshi, yaxshi - bu dunyo adolatsiz, keyingi hayotda - omadli.
Ko'karishlar va ko'karishlar:
1. Ertami-kechmi, xuddi shunday ishni bajaradigan, ammo biroz arzonroq bo'lgan shunga o'xshash xizmatning aqlli provayderi paydo bo'ladi. Va agar natija bir xil bo'lsa, nima uchun ko'proq pul to'lash kerak. Bu yana oziqlantiruvchining yo'qolishiga olib keladi.
2. Bu zerikarli. Har qanday kichik mazmunli faoliyat qanchalik zerikarli.
3. Oldingi versiyada bo'lgani kabi - rivojlanish yo'q. Ammo muhandis uchun minus shundaki, birinchi variantdan farqli o'laroq, bu erda siz doimiy ravishda ITB yaratishingiz kerak. Va bu vaqt talab etadi. Qaysi bir sevganingiz manfaati uchun sarflanishi mumkin. Chunki siz o'zingizga g'amxo'rlik qila olmaysiz, hamma sizga g'amxo'rlik qiladi.

Variant 3-Velosiped ixtiro qilish shart emas, uni sotib olib minishingiz kerak.

Boshqa kompaniyalarning muhandislari bila turib pivo bilan pivo yeyishadi (oh, 90-yillardagi Sankt-Peterburgning ulug'vor paytlari). Keling, yaratilgan, disk raskadrovka qilingan va ishlaydigan monitoring tizimlaridan foydalanaylik va umuman olganda, ular foyda keltiradi (hech bo'lmaganda ularning yaratuvchilari uchun).
Gingerbread va donuts, ko'karishlar va zarbalarGingerbread va donuts:
1. Ilgari ixtiro qilingan narsani ixtiro qilish uchun vaqtni behuda sarflashning hojati yo'q. Oling va foydalaning.
2. Monitoring tizimlari ahmoqlar tomonidan yozilmagan va ular albatta foydalidir.
3. Ishchi monitoring tizimlari odatda foydali filtrlangan ma'lumotlarni taqdim etadi.
Ko'karishlar va ko'karishlar:
1. Bu holatda muhandis muhandis emas, balki boshqa birovning mahsulotidan foydalanuvchi yoki foydalanuvchi.
2. Buyurtmachi, umuman olganda, tushunishni istamaydigan narsani sotib olish zarurligiga ishonch hosil qilishi kerak va u buni qilmasligi kerak va umuman, yil uchun byudjet tasdiqlangan va o'zgarmaydi. Keyin alohida resurs ajratishingiz, uni ma'lum bir tizim uchun sozlashingiz kerak. Bular. Avval siz to'lashingiz, to'lashingiz va qayta to'lashingiz kerak. Mijoz esa ziqna. Bu hayotning normasi.

Nima qilish kerak, Chernishevskiy? Savolingiz juda o'rinli. (bilan)

Ushbu alohida holatda va hozirgi vaziyatda siz biroz boshqacha qilishingiz mumkin - keling, o'zimizning monitoring tizimini yarataylik.

Xo'sh, tizim emas, albatta, so'zning to'liq ma'nosida, bu juda baland va mag'rur, lekin hech bo'lmaganda qandaydir tarzda o'zingizni osonlashtiring va ishlash hodisalarini hal qilish uchun ko'proq ma'lumot to'plang. Vaziyatga tushib qolmaslik uchun - "u erga boring, qaerdan bilmayman, buni topaman, nima ekanligini bilmayman."

Ushbu variantning ijobiy va salbiy tomonlari qanday:

Taroziga soling:
1. Bu qiziq. Hech bo'lmaganda doimiy "ma'lumotlar faylini qisqartirish, jadval maydonini o'zgartirish va hokazo" dan ko'ra qiziqroq.
2. Bular yangi ko'nikmalar va yangi rivojlanishdir. Kelajakda ertami-kechmi munosib gingerbread va donuts beradi.
Kamchiliklari:
1. Ishlash kerak. Ko'p ishla.
2. Siz muntazam ravishda barcha faoliyatning mazmuni va istiqbollarini tushuntirishingiz kerak bo'ladi.
3. Biror narsani qurbon qilish kerak bo'ladi, chunki muhandis uchun mavjud bo'lgan yagona resurs - vaqt - Koinot tomonidan cheklangan.
4. Eng yomoni va eng yoqimsizi - Natijada, "Sichqoncha emas, qurbaqa emas, balki noma'lum kichkina hayvon" kabi axlat paydo bo'lishi mumkin.

Kim biror narsani xavf ostiga qo'ymasa, shampan ichmaydi.
Shunday qilib, o'yin-kulgi boshlanadi.

Umumiy fikr - sxematik

(Rasm maqoladan olingan «Sintez PostgreSQL ish faoliyatini yaxshilash usullaridan biri sifatida»)

Izoh:

Maqsadli ma'lumotlar bazasi standart PostgreSQL "pg_stat_statements" kengaytmasi bilan o'rnatilgan.
Monitoring ma'lumotlar bazasida biz pg_stat_statements tarixini dastlabki bosqichda saqlash va kelajakda ko'rsatkichlar va monitoringni sozlash uchun xizmat jadvallari to'plamini yaratamiz.
Monitoring hostida biz bash skriptlari to'plamini, shu jumladan chiptalar tizimidagi hodisalarni yaratish uchun yaratamiz.

Xizmat stollari

Boshlash uchun, sxematik ravishda soddalashtirilgan ERD, oxirida nima bo'ldi:

Jadvallarning qisqacha tavsifiso'nggi nuqta - xost, misolga ulanish nuqtasi
ma'lumotlar bazasi - ma'lumotlar bazasi imkoniyatlari
pg_stat_history - maqsadli ma'lumotlar bazasining pg_stat_statements ko'rinishining vaqtinchalik suratlarini saqlash uchun tarixiy jadval
metrik_lug'at - Ishlash ko'rsatkichlari lug'ati
metric_config - individual ko'rsatkichlar konfiguratsiyasi
metrik - kuzatilayotgan so'rov uchun aniq ko'rsatkich
metric_alert_history - ishlash haqida ogohlantirishlar tarixi
log_so'rovi - AWS-dan yuklab olingan PostgreSQL jurnali faylidan tahlil qilingan yozuvlarni saqlash uchun xizmat jadvali
boshlang'ich nuqtasi - baza sifatida foydalaniladigan vaqt davri parametrlari
nazorat punkti - ma'lumotlar bazasi holatini tekshirish uchun ko'rsatkichlar konfiguratsiyasi
checkpoint_alert_history - ma'lumotlar bazasi holatini tekshirish ko'rsatkichlarining ogohlantirish tarixi
pg_stat_db_queries — faol so‘rovlarning xizmat ko‘rsatish jadvali
faoliyat_logi — faoliyat jurnali xizmati jadvali
trap_oid - tuzoq konfiguratsiyasiga xizmat ko'rsatish jadvali

1-bosqich - ishlash statistikasini to'plash va hisobotlarni olish

Statistik ma'lumotlarni saqlash uchun jadvaldan foydalaniladi. pg_stat_history
pg_stat_history jadval tuzilishi

                                          Jadval "public.pg_stat_history" ustuni | turi | Modifikatorlar-------------------+--------------------- --+---- --------------------------------- id | butun | null standart emas nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | vaqt mintaqasisiz vaqt tamg'asi | database_id | butun | dbid | oid | userid | oid | queryid | bigint | so'rov | matn | qo'ng'iroqlar | bigint | umumiy_vaqt | ikki tomonlama aniqlik | min_vaqt | ikki tomonlama aniqlik | maksimal_vaqt | ikki tomonlama aniqlik | o'rtacha_vaqt | ikki tomonlama aniqlik | stddev_time | ikki tomonlama aniqlik | qatorlar | 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 | ikki tomonlama aniqlik | blk_write_time | ikki tomonlama aniqlik | baseline_id | butun | Indekslar: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "ma'lumotlar bazasi_idx" btree (ma'lumotlar bazasi_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Tashqi kalitlar cheklovi (K_tabaseid) (EYF) MA'LUMOTLAR ma'lumotlar bazasi (id ) CASKADNI O‘CHIRIShDA

Ko'rib turganingizdek, jadval faqat yig'ilgan ko'rinish ma'lumotlaridir pg_stat_statements maqsadli ma'lumotlar bazasida.

Ushbu jadvaldan foydalanish juda oddiy.

pg_stat_history har bir soat uchun so'rovlar bajarilishining to'plangan statistikasini ifodalaydi. Har soat boshida, jadvalni to'ldirgandan so'ng, statistika pg_stat_statements bilan qayta o'rnatish pg_stat_statements_reset().
Eslatma: Statistik ma'lumotlar davomiyligi 1 soniyadan ortiq bo'lgan so'rovlar uchun yig'iladi.
pg_stat_history jadvalini to'ldirish

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

Natijada, jadvalda ma'lum vaqtdan keyin pg_stat_history bizda jadval mazmunining oniy tasvirlari to'plami bo'ladi pg_stat_statements maqsadli ma'lumotlar bazasi.

Haqiqatan ham hisobot berish

Oddiy so'rovlardan foydalanib, siz juda foydali va qiziqarli hisobotlarni olishingiz mumkin.

Muayyan vaqt uchun jamlangan ma'lumotlar

So'rov

SELECT 
  database_id , 
  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,
  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,
  SUM(shared_blks_read) AS shared_blks_read ,
  SUM(shared_blks_dirtied) AS shared_blks_dirtied,
  SUM(shared_blks_written) AS shared_blks_written , 
  SUM(local_blks_hit) AS local_blks_hit , 
  SUM(local_blks_read) AS local_blks_read , 
  SUM(local_blks_dirtied) AS local_blks_dirtied , 
  SUM(local_blks_written)  AS local_blks_written,
  SUM(temp_blks_read) AS temp_blks_read, 
  SUM(temp_blks_written) temp_blks_written , 
  SUM(blk_read_time) AS blk_read_time , 
  SUM(blk_write_time) AS blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

D.B. Vaqt

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

I/U vaqti

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

Total_time bo'yicha TOP10 SQL

So'rov

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

------------------------------------------------- ----------------------------------- | UMUMIY IJRO ETILGAN TOP10 SQL | #| queryid| qo'ng'iroqlar| qo'ng'iroqlar %| umumiy_vaqt (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

Umumiy kiritish/chiqarish vaqti bo'yicha TOP10 SQL

So'rov

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

------------------------------------------------- --------------------------------------- | UMUMIY I/U VAQTI BOʻYICHA TOP10 SQL | #| queryid| qo'ng'iroqlar| qo'ng'iroqlar %| I/U vaqti (ms)|db I/U vaqti % +----+-----------+-----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 iyun | 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

Maksimal bajarilish vaqti bo'yicha TOP10 SQL

So'rov

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 MAXSIZLIK BAJAR ETISH VAQTI BOʻYICHA | #| surat | snapshotID| queryid| maksimal_vaqt (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 milodiy)

TOP10 SQL tomonidan SHARED bufer o'qish/yozish

So'rov

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

------------------------------------------------- ----------------------------------- | O'QISh/YOZISH BO'LGAN BUFER BO'YICHA TOP10 SQL | #| surat | snapshotID| queryid| umumiy bloklar o'qildi| umumiy bloklar yozish +----+------------------+-----------+---------- -+--------------------+--------------------- | 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 ------------------------------------------------- -------------------------------------------------

Maksimal bajarish vaqti bo'yicha so'rovlarni taqsimlash gistogrammasi

so'rovlar

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 | JAMI QO'NG'iroqlar: 33851920 | MIN VAQT: 00:00:01.063 | MAX VAQT: 00:02:01.869 ---------------------------------- -------- -------------------------------------- | minimal davomiylik| maksimal davomiylik| qo'ng'iroqlar +---------------------------------+---------------------- --------------------+---------- | 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

Bir soniyada soʻrovlar boʻyicha TOP10 ta surat

so'rovlar

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

|------------------------------------------------ --------------------------------------- | QueryPerSeconds raqamlari bo'yicha tartiblangan TOP10 ta surat --------------------------------------------- --------------------------------------------- ------ ------------------------------------------- | #| surat | snapshotID| qo'ng'iroqlar| jami dbtime| QPS | I/U vaqti | I/U vaqti % +-----+------------------+-----------+------- ----+---------------------------------+---------- -+---------------------------------+----------- | 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

QueryPerSeconds va kiritish/chiqarish vaqti bilan soatlik bajarilish tarixi

So'rov

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

Barcha tanlangan SQL matni

So'rov

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

Xulosa

Ko'rib turganingizdek, juda oddiy vositalar yordamida siz ish yuki va ma'lumotlar bazasi holati haqida juda ko'p foydali ma'lumotlarni olishingiz mumkin.

Eslatma:Agar siz so'rovlarda so'rov identifikatorini tuzatsangiz, biz alohida so'rov uchun tarixni olamiz (joyni tejash uchun alohida so'rov uchun hisobotlar o'tkazib yuboriladi).

Shunday qilib, so'rovlar bajarilishi bo'yicha statistik ma'lumotlar mavjud va to'planadi.
Birinchi bosqich "statistik ma'lumotlarni yig'ish" yakunlandi.

Siz ikkinchi bosqichga o'tishingiz mumkin - "ish ko'rsatkichlarini sozlash".

Ammo bu butunlay boshqacha voqea.

Davomi bor…

Manba: www.habr.com

PostgreSQL so'rovlarining ishlashi monitoringi. 1-qism - hisobot

muqaddima

Nega menga bularning barchasi kerak?

Muammoni shakllantirish.

Mavjud:

Majburiy:

Yechimlarni qisqacha tanishtirish yoki tahlil qilish

Variant 1 - "Talab bo'yicha ishlash"

Variant 2 - "Dambur bilan raqsga tushing, poyabzal kiying va kiying"

Variant 3-Velosiped ixtiro qilish shart emas, uni sotib olib minishingiz kerak.

Nima qilish kerak, Chernishevskiy? Savolingiz juda o'rinli. (bilan)

Ushbu variantning ijobiy va salbiy tomonlari qanday:

Umumiy fikr - sxematik

Xizmat stollari

1-bosqich - ishlash statistikasini to'plash va hisobotlarni olish

Ushbu jadvaldan foydalanish juda oddiy.

Haqiqatan ham hisobot berish

Muayyan vaqt uchun jamlangan ma'lumotlar

D.B. Vaqt

I/U vaqti

Total_time bo'yicha TOP10 SQL

Umumiy kiritish/chiqarish vaqti bo'yicha TOP10 SQL

Maksimal bajarilish vaqti bo'yicha TOP10 SQL

TOP10 SQL tomonidan SHARED bufer o'qish/yozish

Maksimal bajarish vaqti bo'yicha so'rovlarni taqsimlash gistogrammasi

Bir soniyada soʻrovlar boʻyicha TOP10 ta surat

QueryPerSeconds va kiritish/chiqarish vaqti bilan soatlik bajarilish tarixi

Barcha tanlangan SQL matni

Xulosa

a Izoh qo'shish Bu savolga javob bekor