ProHoster > بلاګ > اداره > د PostgreSQL لپاره د ASH انلاګ جوړولو هڅه

د PostgreSQL لپاره د ASH انلاګ جوړولو هڅه

د ستونزې تشکیل

د PostgreSQL پوښتنو غوره کولو لپاره ، د فعالیت تاریخ تحلیل کولو وړتیا ، په ځانګړي توګه انتظار ، تالاشۍ او د میز احصایې خورا اړین دي.

شته فرصتونه

د تاریخي کاري بار تحلیلي وسیله یا "د پوسټګریس لپاره AWR": یو ډیر په زړه پوری حل، مګر د pg_stat_activity او pg_locks هیڅ تاریخ شتون نلري.

pgsentinel توسیع :
«ټول راټول شوي معلومات یوازې په RAM کې زیرمه شوي، او د حافظې مصرف شوي مقدار د وروستي ذخیره شوي ریکارډونو شمیر لخوا تنظیم کیږي.

د queryid ساحه اضافه شوې - د pg_stat_statements توسیع څخه ورته queryid (د مخکې نصبولو اړتیا ده).«

البته، دا به ډیره مرسته وکړي، مګر ترټولو ستونزمن شی لومړی ټکی دی."ټول راټول شوي معلومات یوازې په RAM کې زیرمه شوي "، i.e. د هدف په اساس باندې اغیزه شتون لري. سربیره پردې ، د تالاشۍ تاریخ او د میز احصایې شتون نلري. هغوی. د حل لاره په عموم ډول نیمګړتیا ده: "تر اوسه د نصب لپاره هیڅ چمتو شوی بسته نشته. دا وړاندیز کیږي چې سرچینې ډاونلوډ کړئ او پخپله کتابتون راټول کړئ. تاسو لومړی اړتیا لرئ د خپل سرور لپاره "ترقی" کڅوړه نصب کړئ او د PATH متغیر کې pg_config ته لاره وټاکئ.".

په عموم کې، ډیری ګډوډي شتون لري، او د جدي تولید ډیټابیسونو په صورت کې، دا ممکن د سرور سره هیڅ شی نه وي. موږ باید یو ځل بیا د خپل ځان سره راشو.

خبرداری

د خورا لوی حجم له امله او د ازموینې نیمګړې دورې له امله ، مقاله په عمده ډول د معلوماتي طبیعت څخه ده ، نه د مقالو او منځنیو پایلو د یوې مجموعې په توګه.
نور تفصیلي مواد به وروسته په برخو کې چمتو شي

د حل لپاره د مسودې اړتیاوې

دا اړینه ده چې داسې وسیله رامینځته کړئ چې تاسو ته اجازه درکوي ذخیره کړئ:

pg_stat_activity د لید تاریخ
د pg_locks لید په کارولو سره د سیشن لاک تاریخ

د حل اړتیا- په هدف ډیټابیس باندې اغیز کم کړئ.

عمومي نظر- د معلوماتو راټولولو اجنټ په نښه شوي ډیټابیس کې نه پیل کیږي ، مګر د نظارت ډیټابیس کې د سیسټم شوي خدمت په توګه. هو، د ځینو معلوماتو ضایع ممکنه ده، مګر دا د راپور ورکولو لپاره مهم نه دی، مګر د حافظې او ډیسک ځای په اړه د هدف ډیټابیس باندې هیڅ اغیزه نلري. او د اتصال حوض کارولو په حالت کې ، د کارونکي پروسو اغیزه لږترلږه ده.

د تطبیق پړاوونه

1.د خدمت میزونه

یو جلا سکیما د میزونو ذخیره کولو لپاره کارول کیږي، ترڅو د کارول شوي اصلي میزونو تحلیل پیچلي نه کړي.

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

مهم: سکیما د هدف ډیټابیس کې نه رامینځته کیږي ، مګر د نظارت ډیټابیس کې.

pg_stat_activity د لید تاریخ

یو جدول د pg_stat_activity لید اوسني سنیپ شاټونو ذخیره کولو لپاره کارول کیږي

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

د ننوتلو ګړندی کولو لپاره - هیڅ شاخص یا محدودیتونه نشته.

د تاریخ پخپله ذخیره کولو لپاره، یو ویشل شوی جدول کارول کیږي:

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

څرنګه چې پدې حالت کې د ننوتلو سرعت لپاره هیڅ اړتیا نشته، ځینې شاخصونه رامینځته شوي ترڅو د راپورونو رامینځته کولو ګړندی کړي.

د سیشن بلاک کولو تاریخ

یو جدول د سیشن لاکونو اوسني عکسونو ذخیره کولو لپاره کارول کیږي:

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

همچنان ، د ننوتلو ګړندي کولو لپاره ، هیڅ شاخص یا محدودیتونه شتون نلري.

د تاریخ پخپله ذخیره کولو لپاره، یو ویشل شوی جدول کارول کیږي:

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

څرنګه چې پدې حالت کې د ننوتلو سرعت لپاره هیڅ اړتیا نشته، ځینې شاخصونه رامینځته شوي ترڅو د راپورونو رامینځته کولو ګړندی کړي.

2. د اوسني تاریخ ډکول

د مستقیم لید سنیپ شاټونو راټولولو لپاره ، د باش سکریپټ کارول کیږي چې د plpgsql فعالیت پرمخ وړي.

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 فعالیت په هدف ډیټابیس کې لیدونو ته لاسرسی لري او د څارنې ډیټابیس کې د خدماتو جدولونو کې قطارونه داخلوي.

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;

د لید سنیپ شاټونو راټولولو لپاره، سیسټمډ خدمت او دوه سکریپټونه کارول کیږي:

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

سکریپټونو ته حقونه ورکړئ:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

راځئ چې خدمت پیل کړو:
# systemctl daemon-reload
# systemctl پیل pg_current_activity.service

په دې توګه، د نظرونو تاریخ د دویمې ثانیې انځورونو په بڼه راټول شوی. البته، که هرڅه لکه څنګه چې پاتې وي، میزونه به په چټکۍ سره په اندازې کې زیاتوالی ومومي او ډیر یا لږ ګټور کار به ناممکن شي.

دا اړینه ده چې د معلوماتو آرشیف تنظیم کړئ.

3. د تاریخ آرشیف کول

د آرشیف کولو لپاره، ویشل شوي میزونه آرشیف * کارول کیږي.

نوي برخې هر ساعت رامینځته کیږي ، پداسې حال کې چې زاړه معلومات د تاریخ * جدولونو څخه لرې کیږي ، نو د تاریخ * جدولونو اندازه ډیر نه بدلیږي او د ننوتلو سرعت د وخت په تیریدو سره نه خرابیږي.

د نویو برخو جوړول د plpgsql فعالیت فعالیت_hist.archive_current_activity لخوا ترسره کیږي. د کار الګوریتم خورا ساده دی (د archive_pg_stat_activity جدول لپاره د برخې مثال په کارولو سره).

یوه نوې برخه جوړه او ډکه کړئ

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

د شاخصونو جوړول

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

د تاریخ_pg_stat_activity جدول څخه زاړه معلومات لرې کول

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

البته، وخت په وخت، زاړه برخې د غیر ضروري په توګه حذف کیږي.

بنسټیز راپورونه

په حقیقت کې، دا ټول ولې ترسره کیږي؟ د راپورونو ترلاسه کولو لپاره په خورا مبهم ډول د اوریکل AWR یادونه کوي.

دا مهمه ده چې اضافه کړئ چې د راپورونو ترلاسه کولو لپاره، تاسو اړتیا لرئ چې د pg_stat_activity او pg_stat_statements لیدونو ترمنځ اړیکه جوړه کړئ. جدولونه په 'history_pg_stat_activity'، 'archive_pg_stat_activity' جدولونو کې د 'Queryid' کالم په اضافه کولو سره تړل شوي دي. د کالم ارزښت اضافه کولو طریقه د دې مقالې له دائرې څخه بهر ده او دلته تشریح شوې - pg_stat_statements + pg_stat_activity + loq_query = pg_ash؟ .

د پوښتنو لپاره د CPU ټول وخت

غوښتنه:

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

بېلګه:

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

د پوښتنو لپاره د انتظار ټول وخت

غوښتنه:

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

یو مثال:

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

د پوښتنو لپاره انتظار

غوښتنې:

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

بېلګه:

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

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

د تړلو پروسو تاریخ

غوښتنه:

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

بېلګه:

------------------------------------------------------------------ ------------------------------------------------------------------ ----------------------------------| تړل شوي پروسیجر تاریخ +------+------------+------+------------+------------ -------------------------------------------- +----------------------------------------- +-------------------------------- | #| pid| پیل شو | موده| blocking_pids| اړیکه | حالت| لاک ډول +---------+---------+------+--------- +--------- -----------+------------+------------------------------- -------------- | ۱| 1| 26224-2019-09 02:19:32| 16:00:01| {45}| 26211| AccessShareLock| اړیکه | 16541| 2| 26390-2019-09 02:19:34| 03:00:00| {53}| 26211| AccessShareLock| اړیکه | 16541| 3| 26391-2019-09 02:19:34| 03:00:00| {53}| 26211| AccessShareLock| اړیکه | ۴| 16541| 4-26531-2019 09:02:19| 35:27:00 | {00}| 12| AccessShareLock| اړیکه | 26211| 16541| 5-27284-2019 09:02:19| 44:02:00 | {00}| 19| AccessShareLock| اړیکه | ۶| 27276| 16541-6-27283 2019:09:02| 19:44:02 | {00}| 00| AccessShareLock| اړیکه | 19| 27276| 16541-7-27286 2019:09:02| 19:44:02 | {00}| 00| AccessShareLock| اړیکه | ۸| 19| 27276-16541-8 27423:2019:09| 02:19:45 | {24}| 00| AccessShareLock| اړیکه | ۹| 00| 12-27394-16541 9:27648:2019| 09:02:19| {48}| 06| AccessShareLock| اړیکه | ۱۰| 00| 00-20-27647 16541:10:27650| 2019:09:02| {19}| 48| AccessShareLock| اړیکه | 06| 00| 00-20-27647 16541:11:27735| 2019:09:02 | {19}| 49| Access ExclusiveLock| اړیکه | 08| 00| 00-06-27650 16541:12:28380| 2019:09:02| {19}| 56| AccessShareLock| اړیکه | 03| 00| 01-56-28379 16541:13:28379| 2019:09:02| 19| 56| Access ExclusiveLock| اړیکه | | | | | 03| |

د بلاک کولو پروسې تاریخ

غوښتنې:

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

بېلګه:

---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- ------------------------ د بلاک کولو پروسې تاریخ +------+------------------ ------------------------- +---------------------------- ------------------------------------------------------------------------- -------------------------------------------------------------- | #| pid| کارن نوم| غوښتنلیک_نوم| datname| پیل شو | موده| ایالت| پوښتنه +---------+---------+------------+------+--------- ---------------------------------------------------------------------- ------------------------------------------------------------------------------------- ------------------| ۱| 1| tuser| psql| tdb26211| 1-2019-09 02:19:31| 54:00:00| بې کاره | | 04| 2| tuser| psql| tdb26211| 1-2019-09 02:19:31| 58:00:00 | په راکړه ورکړه کې بې کاره | پيل كيدل؛ شروع كيدل: او چنېدل، راوتل؛ | 06| 3| tuser| psql| tdb26211| 1-2019-09 02:19:32| 16:00:01| په راکړه ورکړه کې بې کاره | د میز بندول wafer_data; | ۴| 45| tuser| psql| tdb4| 26211-1-2019 09:02:19| 35:54:00 | بې کاره | ژمنه | 01| 23| tuser| psql| tdb5| 26211-1-2019 09:02:19| 38:46:00 | په راکړه ورکړه کې بې کاره | پيل كيدل؛ شروع كيدل: او چنېدل، راوتل؛ | ۶| 00| tuser| psql| tdb02| 6-26211-1 2019:09:02| 19:38:54 | په راکړه ورکړه کې بې کاره | د میز بندول wafer_data; | 00| 00| tuser| psql| tdb08| 7-26211-1 2019:09:02| 19:39:08| بې کاره | ژمنه | ۸| 00| tuser| psql| tdb42| 42-8-26211 1:2019:09| 03:07:12 | فعال| test_del();

پراختیا.

ښودل شوي لومړني پوښتنې او پایلې راپورونه لا دمخه ژوند خورا اسانه کوي کله چې د فعالیت پیښې تحلیل کړئ.
د لومړنیو پوښتنو پراساس، تاسو کولی شئ یو راپور ترلاسه کړئ چې په مبهم ډول د اوریکل AWR سره ورته وي.
د لنډیز راپور مثال

+---------------------------------------------------------------- ----------------------------------- | د فعالیت او انتظار لپاره توافق شوی راپور.

نور بیا. بل په لیکه کې د لاک تاریخ رامینځته کول دي (pg_stat_locks) ، د جدولونو ډکولو پروسې ډیر تفصیلي توضیحات.

سرچینه: www.habr.com

Add a comment