ProHoster > Blog > Ma'muriyat > PostgreSQL uchun ASH analogini yaratishga urinish

PostgreSQL uchun ASH analogini yaratishga urinish

Muammoni shakllantirish

PostgreSQL so'rovlarini optimallashtirish uchun faoliyat tarixini, xususan, kutishlar, qulflar va jadval statistikasini tahlil qilish qobiliyati juda talab qilinadi.

Mavjud imkoniyatlar

Tarixiy ish yukini tahlil qilish vositasi yoki "Postgres uchun AWR": juda qiziqarli yechim, lekin pg_stat_activity va pg_locks tarixi yo'q.

pgsentinel kengaytmasi :
«Barcha to'plangan ma'lumotlar faqat operativ xotirada saqlanadi va sarflangan xotira miqdori oxirgi saqlangan yozuvlar soni bilan tartibga solinadi.

Queryid maydoni qo'shildi - pg_stat_statements kengaytmasidan bir xil so'rov id (oldindan o'rnatish talab qilinadi).«

Bu, albatta, ko'p yordam beradi, lekin eng qiyin narsa - birinchi nuqta.Barcha to'plangan ma'lumotlar faqat RAMda saqlanadi ", ya'ni. maqsadli bazaga ta'siri bor. Bundan tashqari, qulflash tarixi va jadval statistikasi mavjud emas. Bular. yechim odatda to'liq emas: "O'rnatish uchun hali tayyor paket yo'q. Manbalarni yuklab olish va kutubxonani o'zingiz yig'ish tavsiya etiladi. Avval serveringiz uchun "devel" paketini o'rnatishingiz va PATH o'zgaruvchisida pg_config yo'lini o'rnatishingiz kerak.".

Umuman olganda, juda ko'p shovqin bor va jiddiy ishlab chiqarish ma'lumotlar bazalari bo'lsa, server bilan hech narsa qilish mumkin bo'lmasligi mumkin. Biz yana o'zimizga xos narsani o'ylab topishimiz kerak.

Ogohlantirish.

Juda katta hajm va test muddati tugallanmaganligi sababli, maqola asosan tezislar va oraliq natijalar to'plami sifatida axborot xarakteriga ega.
Batafsilroq material keyinroq, qismlarga bo'linadi

Yechim uchun talablar loyihasi

Saqlash imkonini beruvchi vositani ishlab chiqish kerak:

pg_stat_activity ko'rish tarixi
pg_locks ko'rinishidan foydalangan holda sessiya blokirovkasi tarixi

Yechim talabi- maqsadli ma'lumotlar bazasiga ta'sirni minimallashtirish.

Umumiy fikr– maʼlumotlarni yigʻish agenti maqsadli maʼlumotlar bazasida emas, balki monitoring maʼlumotlar bazasida tizimli xizmat sifatida ishga tushiriladi. Ha, ba'zi ma'lumotlar yo'qolishi mumkin, ammo bu hisobot uchun juda muhim emas, lekin xotira va disk maydoni nuqtai nazaridan maqsadli ma'lumotlar bazasiga ta'sir ko'rsatmaydi. Va ulanish hovuzidan foydalanilganda, foydalanuvchi jarayonlariga ta'sir minimal bo'ladi.

Amalga oshirish bosqichlari

1.Xizmat jadvallari

Amaldagi asosiy jadvallarni tahlil qilishni murakkablashtirmaslik uchun jadvallarni saqlash uchun alohida sxema qo'llaniladi.

DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;

Muhim: Sxema maqsadli ma'lumotlar bazasida emas, balki monitoring ma'lumotlar bazasida yaratilgan.

pg_stat_activity ko'rish tarixi

Jadval pg_stat_activity ko'rinishining joriy suratlarini saqlash uchun ishlatiladi

activity_hist.history_pg_stat_activity :

--ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY
DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity;
CREATE TABLE activity_hist.history_pg_stat_activity
(
  timepoint timestamp without time zone ,
  datid             oid  , 
  datname           name ,
  pid               integer,
  usesysid          oid    ,
  usename           name   ,
  application_name  text   ,
  client_addr       inet   ,
  client_hostname   text   ,
  client_port       integer,
  backend_start     timestamp with time zone ,
  xact_start        timestamp with time zone ,
  query_start       timestamp with time zone ,
  state_change      timestamp with time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,  
  queryid           bigint
);

Qo'shishni tezlashtirish uchun - indekslar yoki cheklovlar yo'q.

Tarixning o'zini saqlash uchun bo'lingan jadval ishlatiladi:

activity_hist.archive_pg_stat_activity :

DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity;
CREATE TABLE activity_hist.archive_pg_stat_activity
(
  timepoint timestamp without time zone ,
  datid             oid  , 
  datname           name ,
  pid               integer,
  usesysid          oid    ,
  usename           name   ,
  application_name  text   ,
  client_addr       inet   ,
  client_hostname   text   ,
  client_port       integer,
  backend_start     timestamp with time zone ,
  xact_start        timestamp with time zone ,
  query_start       timestamp with time zone ,
  state_change      timestamp with time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,
  queryid           bigint
)
PARTITION BY RANGE (timepoint);

Bu holda kiritish tezligi uchun hech qanday talablar mavjud emasligi sababli, hisobotlarni yaratishni tezlashtirish uchun ba'zi indekslar yaratilgan.

Seansni bloklash tarixi

Seans blokirovkalarining joriy suratlarini saqlash uchun jadvaldan foydalaniladi:

activity_hist.history_locking :

--ACTIVITY_HIST.HISTORY_LOCKING
DROP TABLE IF EXISTS activity_hist.history_locking;
CREATE TABLE activity_hist.history_locking
(
	timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean
);

Bundan tashqari, kiritishni tezlashtirish uchun indekslar yoki cheklovlar yo'q.

Tarixning o'zini saqlash uchun bo'lingan jadval ishlatiladi:

activity_hist.archive_locking:

DROP TABLE IF EXISTS activity_hist.archive_locking;
CREATE TABLE activity_hist.archive_locking
(
	timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean	
)
PARTITION BY RANGE (timepoint);

Bu holda kiritish tezligi uchun hech qanday talablar mavjud emasligi sababli, hisobotlarni yaratishni tezlashtirish uchun ba'zi indekslar yaratilgan.

2.Hozirgi tarixni to'ldirish

To'g'ridan-to'g'ri ko'rish suratlarini yig'ish uchun plpgsql funktsiyasini ishga tushiradigan bash skripti ishlatiladi.

get_current_activity.sh

#!/bin/bash
#########################################################
#get_current_activity.sh

ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S)
host=$1
s_name=$2
s_pass=$3

psql  -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE

line_count=`cat $ERROR_FILE | wc -l`
if [[ $line_count != '0' ]];
then
    rm -f /home/demon/*.err >/dev/null 2>/dev/null
	cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null  
fi
rm $ERROR_FILE >/dev/null 2>/dev/null
exit 0

plpgsql Dblink funksiyasi maqsadli ma'lumotlar bazasidagi ko'rinishlarga kiradi va monitoring ma'lumotlar bazasidagi xizmat jadvallariga qatorlar qo'shadi.

get_current_activity.sql

CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$
DECLARE 
  database_rec record;
  dblink_str text ;
BEGIN   

	EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'||
	                                         ' user='||current_s_name||' password='||current_s_pass|| ' '')';



--------------------------------------------------------------------
--GET pg_stat_activity stats
	INSERT INTO activity_hist.history_pg_stat_activity
	(
		SELECT * FROM dblink('LINK1',
			'SELECT 
			now() , 
			datid             , 
			datname           ,
			pid               ,
			usesysid              ,
			usename              ,
			application_name     ,
			client_addr          ,
			client_hostname      ,
			client_port       ,
			backend_start         ,
			xact_start            ,
			query_start           ,
			state_change          ,
			wait_event_type    ,                     
			wait_event         ,                   
			state              ,                  
			backend_xid         ,                 
			backend_xmin        ,                
			query              ,               
			backend_type   			
		FROM pg_stat_activity
		') 
		AS t (
		    timepoint 		  timestamp without time zone ,			
			datid             oid  , 
			datname           name ,
			pid               integer,
			usesysid          oid    ,
			usename           name   ,
			application_name  text   ,
			client_addr       inet   ,
			client_hostname   text   ,
			client_port       integer,
			backend_start     timestamp with time zone ,
			xact_start        timestamp with time zone ,
			query_start       timestamp with time zone ,
			state_change      timestamp with time zone ,
			wait_event_type   text ,                     
			wait_event        text ,                   
			state             text ,                  
			backend_xid       xid  ,                 
			backend_xmin      xid  ,                
			query             text ,               
			backend_type      text 			
		)
	);

---------------------------------------	
--ACTIVITY_HIST.HISTORY_LOCKING	
	INSERT INTO activity_hist.history_locking
	(
		SELECT * FROM dblink('LINK1',
			'SELECT 
			now() , 
			lock.locktype,
			lock.relation,
			lock.mode,
			lock.transactionid as tid,
			lock.virtualtransaction as vtid,
			lock.pid,
			pg_blocking_pids(lock.pid), 
			lock.granted
			FROM 	pg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database
			WHERE NOT lock.pid = pg_backend_pid()	
		') 
		AS t (
			timepoint timestamp without time zone ,
			locktype text ,
			relation oid , 
			mode text ,
			tid xid ,
			vtid text ,
			pid integer ,
			blocking_pids integer[] ,
			granted boolean
		)
	);
	PERFORM dblink_disconnect('LINK1');
	
	RETURN TRUE ;
END
$$ LANGUAGE plpgsql;

Ko'rish suratlarini yig'ish uchun systemd xizmati va ikkita skript ishlatiladi:

pg_current_activity.service

# /etc/systemd/system/pg_current_activity.service
[Unit]
Description=Collect history of pg_stat_activity , pg_locks 
Wants=pg_current_activity.timer

[Service]
Type=forking
StartLimitIntervalSec=0
ExecStart=/home/postgres/pgutils/demon/get_current_activity.sh 10.124.70.40 postgres postgres

[Install]
WantedBy=multi-user.target

pg_current_activity.timer

# /etc/systemd/system/pg_current_activity.timer
[Unit]
Description=Run pg_current_activity.sh every 1 second
Requires=pg_current_activity.service

[Timer]
Unit=pg_current_activity.service
OnCalendar=*:*:0/1
AccuracySec=1

[Install]
WantedBy=timers.target

Skriptlarga huquqlarni tayinlash:
# chmod 755 pg_current_activity.taymer
# chmod 755 pg_current_activity.service

Xizmatni boshlaylik:
# systemctl daemon-reload
# systemctl pg_current_activity.service ni ishga tushiring

Shunday qilib, ko'rishlar tarixi ikkinchi soniyali suratlar shaklida to'planadi. Albatta, agar hamma narsa avvalgidek qolsa, jadvallar juda tez o'sib boradi va ko'proq yoki kamroq samarali ishlash imkonsiz bo'ladi.

Ma'lumotlarni arxivlashni tashkil qilish kerak.

3. Tarixni arxivlash

Arxivlash uchun bo'lingan jadvallar arxivi* qo'llaniladi.

Har soatda yangi bo'limlar yaratiladi, eski ma'lumotlar tarix* jadvallaridan o'chiriladi, shuning uchun tarix* jadvallarining hajmi unchalik o'zgarmaydi va vaqt o'tishi bilan kiritish tezligi pasaymaydi.

Yangi bo'limlarni yaratish plpgsql function_hist.archive_current_activity tomonidan amalga oshiriladi. Ishning algoritmi juda oddiy (archive_pg_stat_activity jadvali uchun bo'lim misolidan foydalangan holda).

Yangi bo'lim yarating va to'ldiring

EXECUTE format(
'CREATE TABLE ' || partition_name || 
' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' , 
to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'||
to_char(date_trunc('month', partition_min_range ),'MM')||'-'||
to_char(date_trunc('day', partition_min_range ),'DD')||' '||
to_char(date_trunc('hour', partition_min_range ),'HH24')||':00', 
to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'||
to_char(date_trunc('month', partition_max_range ),'MM')||'-'||
to_char(date_trunc('day', partition_max_range ),'DD')||' '||
to_char(date_trunc('hour', partition_max_range ),'HH24')||':00'
);

INSERT INTO activity_hist.archive_pg_stat_activity
(
	SELECT 	* 
	FROM 	activity_hist.history_pg_stat_activity
	WHERE 	timepoint BETWEEN partition_min_range AND partition_max_range 		
);

Indekslarni yaratish

EXECUTE format	(
'CREATE INDEX '||index_name||
' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )' 
);

EXECUTE format	('CREATE INDEX '||index_name||
' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )' 
);

History_pg_stat_activity jadvalidan eski ma'lumotlarni olib tashlash

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Albatta, vaqti-vaqti bilan eski bo'limlar keraksiz deb o'chiriladi.

Asosiy hisobotlar

Aslida, bularning barchasi nima uchun qilinmoqda? Oracle AWR-ni juda noaniq eslatuvchi hisobotlarni olish uchun.

Shuni qo'shimcha qilish kerakki, hisobotlarni olish uchun pg_stat_activity va pg_stat_statements ko'rinishlari o'rtasida aloqa o'rnatish kerak. Jadvallar "history_pg_stat_activity", "archive_pg_stat_activity" jadvallariga "queryid" ustunini qo'shish orqali bog'langan. Ustun qiymatini qo'shish usuli ushbu maqola doirasidan tashqarida va bu erda tasvirlangan - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

SAVOLLAR UCHUN JAMI CPU VAQTI

So'rov:

WITH hist AS
(
SELECT 
	aa.query ,aa.queryid ,			
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND  pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND backend_type = 'client backend' AND datname != 'postgres' AND	( aa.wait_event_type IS NULL  ) ANDaa.state = 'active'
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 	backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL  )AND ha.state = 'active'
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

Misol:

-------------------------------------------------------------------
| TOTAL CPU TIME FOR QUERIES : 07:47:36
+----+----------------------------------------+--------------------
|   #|                                 queryid|            duration
+----+----------------------------------------+--------------------
|   1|                      389015618226997618|            04:28:58
|   2|                                        |            01:07:29
|   3|                     1237430309438971376|            00:59:38
|   4|                     4710212362688288619|            00:50:48
|   5|                       28942442626229688|            00:15:50
|   6|                     9150846928388977274|            00:04:46
|   7|                    -6572922443698419129|            00:00:06
|   8|                                        |            00:00:01
+----+----------------------------------------+--------------------

SAVOLLAR UCHUN JAMI KUTISH VAQTI

So'rov:

WITH hist AS
(
SELECT 
	aa.query ,aa.queryid ,			
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	( aa.wait_event_type IS NOT NULL  ) 
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
	backend_type = 'client backend' AND datname != 'postgres' AND				
	( ha.wait_event_type IS NOT NULL  )
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

Misol:

-------------------------------------------------------------------
| TOTAL WAITINGS TIME FOR QUERIES : 21:55:04
+----+----------------------------------------+--------------------
|   #|                                 queryid|            duration
+----+----------------------------------------+--------------------
|   1|                      389015618226997618|            16:19:05
|   2|                                        |            03:47:04
|   3|                     8085340880788646241|            00:40:20
|   4|                     4710212362688288619|            00:13:35
|   5|                     9150846928388977274|            00:12:25
|   6|                       28942442626229688|            00:11:32
|   7|                     1237430309438971376|            00:09:45
|   8|                     2649515222348904837|            00:09:37
|   9|                                        |            00:03:45
|  10|                     3167065002719415275|            00:02:20
|  11|                     5731212217001535134|            00:02:13
|  12|                     8304755792398128062|            00:01:31
|  13|                     2649515222348904837|            00:00:59
|  14|                     2649515222348904837|            00:00:22
|  15|                                        |            00:00:12
|  16|                     3422818749220588372|            00:00:08
|  17|                    -5730801771815999400|            00:00:03
|  18|                    -1473395109729441239|            00:00:02
|  19|                     2404820632950544954|            00:00:02
|  20|                    -6572922443698419129|            00:00:02
|  21|                     2369289265278398647|            00:00:01
|  22|                      180077086776069052|            00:00:01
+----+----------------------------------------+--------------------

SAVOLLAR KUTILADI

So'rovlar:

WITH hist AS
(
SELECT 
	aa.wait_event_type , aa.wait_event 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	aa.wait_event IS NOT NULL 
GROUP BY aa.wait_event_type , aa.wait_event
UNION 
SELECT 
	ha.wait_event_type , ha.wait_event 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	ha.wait_event IS NOT NULL 
GROUP BY ha.wait_event_type , ha.wait_event		
)
SELECT 	wait_event_type , wait_event 
FROM hist
GROUP BY wait_event_type , wait_event
ORDER BY 1 ASC,2 ASC

----------------------------------------------------------------------

WITH hist AS
(
SELECT 
	aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid ,			
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	( aa.wait_event_type = waitings_stat_rec.wait_event_type AND aa.wait_event = waitings_stat_rec.wait_event )
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND				
	( ha.wait_event_type = waitings_stat_rec.wait_event_type AND ha.wait_event = waitings_stat_rec.wait_event )
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

Misol:

------------------------------------------------
| WAITINGS FOR QUERIES
+-----------------------------------------------
|                      wait_event_type = Client|
|                       wait_event = ClientRead|
|                        Total time  = 00:46:56|
------------------------------------------------
|    #|             queryid|            duration
+-----+--------------------+--------------------
|    1| 8085340880788646241|            00:40:20
|    2|                    |            00:03:45
|    3| 5731212217001535134|            00:01:53
|    4|                    |            00:00:12
|    5| 9150846928388977274|            00:00:09
|    6| 3422818749220588372|            00:00:08
|    7| 1237430309438971376|            00:00:06
|    8|   28942442626229688|            00:00:05
|    9| 4710212362688288619|            00:00:05
|   10|-5730801771815999400|            00:00:03
|   11| 8304755792398128062|            00:00:02
|   12|-6572922443698419129|            00:00:02
|   13|-1473395109729441239|            00:00:02
|   14| 2404820632950544954|            00:00:02
|   15|  180077086776069052|            00:00:01
|   16| 2369289265278398647|            00:00:01

+-----------------------------------------------
|                          wait_event_type = IO|
|                      wait_event = BufFileRead|
|                        Total time  = 00:00:38|
------------------------------------------------
|    #|             queryid|            duration
+-----+--------------------+--------------------
|    1|   28942442626229688|            00:00:38

+-----------------------------------------------

QULFLANGAN JARAYONLAR TARIXI

Talab:

SELECT 
MIN(date_trunc('second',timepoint)) AS started , 
	count(*) * interval '1 second' as duration ,
	pid , blocking_pids , relation , mode , locktype 	 
FROM 
	activity_hist.archive_locking al 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY pid , blocking_pids , relation , mode , locktype			
UNION
SELECT 
	MIN(date_trunc('second',timepoint)) AS started , 
	count(*) * interval '1 second' as duration ,
	pid , blocking_pids , relation , mode , locktype
FROM 
	activity_hist.history_locking 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY pid , blocking_pids , relation , mode , locktype			
ORDER BY 1

Misol:

------------------------------------------------- ------------------------------------------------- --------------------------------- | QULFLANGAN JARAYONLAR TARIXI +-----+----------+-----+-------- --+------------ --------+--------------------+----- --------------- +------------------ | #| pid| boshlandi| davomiyligi| blocking_pids| munosabat| rejimi| qulf turi +----------+----------+-----+---------- +--------- -----------+-----------+------- -------------+----- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| munosabat | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| munosabat | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| munosabat | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| munosabat | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| munosabat | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| munosabat | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| munosabat | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| munosabat | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| munosabat | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| munosabat | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| munosabat | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| munosabat | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| munosabat | | | | | 28376| |

JARAYONLAR TARIXINI BLOKLASH

So'rovlar:

SELECT 
blocking_pids 
FROM 
	activity_hist.archive_locking al 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY blocking_pids 		
UNION
SELECT 
	blocking_pids 
FROM 
	activity_hist.history_locking 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY blocking_pids 		
ORDER BY 1

---------------------------------------------------------------

SELECT 
	pid , usename , application_name , datname ,
	MIN(date_trunc('second',timepoint)) as started , 
	count(*) * interval '1 second' as duration ,		 
	state , 
	query
				FROM  	activity_hist.archive_pg_stat_activity
				WHERE 	pid= current_pid AND 
						timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') 						 
				GROUP BY pid , usename , application_name , 
						datname , 
						state_change, 
						state , 
						query 
				UNION
				SELECT 
					pid , usename , application_name , datname ,
					MIN(date_trunc('second',timepoint)) as started , 
					count(*) * interval '1 second' as duration ,		 
					state , 
					query
				FROM  	activity_hist.history_pg_stat_activity_for_reports
				WHERE 	pid= current_pid AND 
						timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') 						 
				GROUP BY pid , usename , application_name , 
						datname , 
						state_change, 
						state , 
						query 
				ORDER BY 5 , 1

Misol:

------------------------------------------------- ------------------------------------------------- ------------------------------------------------- ------------------ JARAYONLAR TARIXINI BLOKLASH +----+----------+------- ---+-------------------+----------+-------------- ----------------------------------+--------------------- - -------+--------------------------------------- | #| pid| usename| ilova_nomi| datname| boshlandi| davomiyligi| davlat| so'rov +----------+----------+----------+-----+ --------- -+--------------------+------------------ --+------ ----------------------------------+---------------- ------- ----------------- | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| bo'sh| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| tranzaksiyada bo'sh| boshlanishi; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| tranzaksiyada bo'sh| blok jadvali wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| bo'sh| topshirmoq; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| tranzaksiyada bo'sh| boshlanishi; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| tranzaksiyada bo'sh| blok jadvali wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| bo'sh| topshirmoq; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| faol| test_del() ni tanlang;

Rivojlanish.

Ko'rsatilgan asosiy so'rovlar va natijada olingan hisobotlar ishlash hodisalarini tahlil qilishda hayotni ancha osonlashtiradi.
Asosiy so'rovlar asosida siz Oracle'ning AWR-ga noaniq o'xshash hisobot olishingiz mumkin.
Xulosa hisobot namunasi

+------------------------------------------------ ---------------------------------- | FAOLIYAT VA KUTILGANLAR BO'YICHA JAMOA HISOBOT.

Davomi bor. Keyingi navbatda bloklash tarixini yaratish (pg_stat_locks), jadvallarni to'ldirish jarayonining batafsil tavsifi.

Manba: www.habr.com

a Izoh qo'shish