Pulogalamu ya ProHoster > Blog > Ulamuliro > Kuyesera kupanga analogue ya ASH ya PostgreSQL

Kuyesera kupanga analogue ya ASH ya PostgreSQL

Kupanga kwa vuto

Kuti mukwaniritse mafunso a PostgreSQL, kuthekera kosanthula mbiri ya zochitika, makamaka kudikirira, kutseka, ndi ziwerengero za tebulo, ndikofunikira kwambiri.

Mwayi wopezeka

Chida Chowunikira Mbiri Yantchito kapena "AWR ya Postgres": yankho losangalatsa kwambiri, koma palibe mbiri ya pg_stat_activity ndi pg_locks.

pgsentinel yowonjezera :
Β«Zidziwitso zonse zomwe zasonkhanitsidwa zimasungidwa mu RAM yokha, ndipo kuchuluka kwa kukumbukira kumayendetsedwa ndi kuchuluka kwa zosungidwa zomaliza.

Gawo la queryid liwonjezedwa - funso lomwelo kuchokera ku pg_stat_statements extension (kukhazikitsanso kofunika).Β«

Izi, ndithudi, zingathandize kwambiri, koma chinthu chovuta kwambiri ndi mfundo yoyamba. "Zonse zomwe zasonkhanitsidwa zimasungidwa mu RAM yokha ”, ndi. pali chikoka pa maziko chandamale. Kuphatikiza apo, palibe mbiri yotseka ndi ziwerengero za tebulo. Iwo. yankho lake nthawi zambiri silikwanira: β€œPalibe phukusi lokonzekera lokonzekera panobe. Iwo amati kukopera magwero ndi kusonkhanitsa laibulale nokha. Choyamba muyenera kukhazikitsa phukusi la "devel" la seva yanu ndikukhazikitsa njira yopita ku pg_config mu PATH variable.".

Nthawi zambiri, pali mikangano yambiri, ndipo pakakhala nkhokwe zazikulu zopanga, sizingakhale zotheka kuchita chilichonse ndi seva. Tiyenera kubweranso ndi china chake chathu.

Chenjezo.

Chifukwa cha kuchuluka kwakukulu komanso chifukwa cha nthawi yosakwanira yoyesera, nkhaniyo imakhala yachidziwitso, m'malo mwake monga mfundo ndi zotsatira zapakatikati.
Zambiri zatsatanetsatane zidzakonzedwa pambuyo pake, m'magawo

Zofunikira pakukonza yankho

Ndikofunikira kupanga chida chomwe chimakulolani kusunga:

pg_stat_activity mbiri yakale
Mbiri yotseka gawo pogwiritsa ntchito pg_locks view

Chofunikira pakuyankha-kuchepetsa kukhudzika kwa database yomwe mukufuna.

Lingaliro lazonse- wothandizira kusonkhanitsa deta amayambitsidwa osati mu nkhokwe ya chandamale, koma mu database yowunikira ngati ntchito ya systemd. Inde, kutayika kwa deta kwina n'kotheka, koma izi sizofunikira kuti mupereke lipoti, koma palibe chokhudzidwa pa malo osungiramo zinthu zomwe mukufuna kukumbukira ndi malo a disk. Ndipo pankhani yogwiritsa ntchito dziwe lolumikizirana, zomwe zimakhudza njira za ogwiritsa ntchito ndizochepa.

Magawo a kukhazikitsa

1.Matebulo a utumiki

Dongosolo lapadera limagwiritsidwa ntchito posungira matebulo, kuti asasokoneze kusanthula kwamatebulo akulu omwe amagwiritsidwa ntchito.

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

Zofunika: Chiwonetserochi sichinapangidwe mu database yomwe mukufuna, koma mu database yowunikira.

pg_stat_activity mbiri yakale

Tebulo limagwiritsidwa ntchito kusunga zithunzi za pg_stat_activity view

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

Kufulumizitsa kuyika - palibe indexes kapena zoletsa.

Kuti musunge mbiri yokha, tebulo logawanika limagwiritsidwa ntchito:

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

Popeza pakadali pano palibe zofunikira pakuyika liwiro, ma index ena adapangidwa kuti afulumizitse kupanga malipoti.

Mbiri yotsekereza gawo

Tebulo limagwiritsidwa ntchito kusunga zithunzi za maloko amgawo:

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

Komanso, kuti muchepetse kuyika, palibe ma index kapena zoletsa.

Kuti musunge mbiri yokha, tebulo logawanika limagwiritsidwa ntchito:

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

Popeza pakadali pano palibe zofunikira pakuyika liwiro, ma index ena adapangidwa kuti afulumizitse kupanga malipoti.

2.Kudzaza mbiri yakale

Kuti mutenge mwachindunji zithunzi zowonera, bash script imagwiritsidwa ntchito yomwe imagwira ntchito ya 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

plgsql Ntchito ya dblink imapeza mawonedwe muzosungira zomwe mukufuna ndikuyika mizere m'matebulo a ntchito mu database yowunikira.

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;

Kuti mutenge zithunzi zowonera, ntchito ya systemd ndi zolemba ziwiri zimagwiritsidwa ntchito:

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

Perekani ufulu ku zolembedwa:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Tiyeni tiyambe ntchito:
# systemctl daemon-yongolanso
# systemctl yambani pg_current_activity.service

Choncho, mbiri ya malingaliro imasonkhanitsidwa mu mawonekedwe azithunzi zachiwiri ndi zachiwiri. Zachidziwikire, ngati zonse zitasiyidwa momwe zilili, matebulo amakula mwachangu kwambiri ndipo ntchito yochulukirapo kapena yocheperako idzakhala yosatheka.

M'pofunika kukonza deta archiving.

3. Kusunga mbiri yakale

Posungira, ma partitioned tables archive* amagwiritsidwa ntchito.

Magawo atsopano amapangidwa ola lililonse, pamene deta yakale imachotsedwa pamatebulo a mbiri yakale *, kotero kukula kwa matebulo a mbiriyakale * sikumasintha kwambiri ndipo liwiro loyika silikuwonongeka pakapita nthawi.

Kupanga magawo atsopano kumachitidwa ndi plpgsql function activity_hist.archive_current_activity. Algorithm yantchito ndiyosavuta (pogwiritsa ntchito chitsanzo cha gawo la archive_pg_stat_activity table).

Pangani ndi kudzaza gawo latsopano

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

Kupanga ma index

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

Kuchotsa zakale kuchokera patsamba la history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Inde, nthawi ndi nthawi, zigawo zakale zimachotsedwa ngati zosafunikira.

Malipoti oyambira

Kwenikweni, n’chifukwa chiyani zonsezi zikuchitika? Kuti mupeze malipoti okumbukira momveka bwino za Oracle's AWR.

Ndikofunika kuwonjezera kuti kuti mulandire malipoti, muyenera kupanga mgwirizano pakati pa pg_stat_activity ndi pg_stat_statements view. Matebulowa alumikizidwa powonjezera ndime ya 'queryid' ku 'history_pg_stat_activity', 'archive_pg_stat_activity'. Njira yowonjezerera mtengo wagawo ndi yopitilira muyeso wa nkhaniyi ndipo ikufotokozedwa apa - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

NTHAWI YONSE YA CPU YA MAFUNSO

Pempho :

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

Chitsanzo:

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

NTHAWI YOYENERA KUDIKIRA KWA MAFUNSO

Pempho :

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

Mwachitsanzo:

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

KUDIKIRANI MAFUNSO

Zopempha:

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

Chitsanzo:

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

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

ZINTHU ZOKHOKEDWA MBIRI

Pempho:

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

Chitsanzo:

------------------------------------------------- ------------------------------------------------- --------------------------------- | MBIRI YA NTCHITO ZOKHOKHWA +-----+----------------------------------------- --------+-------------------------------------------------- +------------------- | #| pid| anayamba | nthawi| blocking_pids| mgwirizano| mode| locktype +---------------------------------------- +-------- --------------------------------------------------- --------------- | 1 | 26224 | 2019-09-02 19:32:16 | 00:01:45 | {26211} | 16541 | AccessShareLock| mgwirizano | 2 | 26390 | 2019-09-02 19:34:03 | 00:00:53 | {26211} | 16541 | AccessShareLock| mgwirizano | 3 | 26391 | 2019-09-02 19:34:03 | 00:00:53 | {26211} | 16541 | AccessShareLock| mgwirizano | 4 | 26531 | 2019-09-02 19:35:27 | 00:00:12 | {26211} | 16541 | AccessShareLock| mgwirizano | 5 | 27284 | 2019-09-02 19:44:02 | 00:00:19 | {27276} | 16541 | AccessShareLock| mgwirizano | 6 | 27283 | 2019-09-02 19:44:02 | 00:00:19 | {27276} | 16541 | AccessShareLock| mgwirizano | 7 | 27286 | 2019-09-02 19:44:02 | 00:00:19 | {27276} | 16541 | AccessShareLock| mgwirizano | 8 | 27423 | 2019-09-02 19:45:24 | 00:00:12 | {27394} | 16541 | AccessShareLock| mgwirizano | 9 | 27648 | 2019-09-02 19:48:06 | 00:00:20 | {27647} | 16541 | AccessShareLock| mgwirizano | 10 | 27650 | 2019-09-02 19:48:06 | 00:00:20 | {27647} | 16541 | AccessShareLock| mgwirizano | 11 | 27735 | 2019-09-02 19:49:08 | 00:00:06 | {27650} | 16541 | AccessExclusiveLock| mgwirizano | 12 | 28380 | 2019-09-02 19:56:03 | 00:01:56 | {28379} | 16541 | AccessShareLock| mgwirizano | 13 | 28379 | 2019-09-02 19:56:03 | 00:00:01 | 28377 | 16541 | AccessExclusiveLock| mgwirizano | | | | | | | | | 28376 | | |

KULETSA NTCHITO MBIRI

Zopempha:

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

Chitsanzo:

------------------------------------------------- ------------------------------------------------- ------------------------------------------------- ----------------------- KULETSA MBIRI YA NTCHITO +-----+------------------ -------------------------------------------------- ------+----------------------------------------- - -------+--------------------------------------- | #| pid| dzina | application_dzina| dzina| anayamba | nthawi| dziko | funso +----------+--------------------+-----+ --------- ------------------------------------------------- ------------------------------------------- ------- ----------------- | 1 | 26211 | tulo| psql | tdb1| 2019-09-02 19:31:54 | 00:00:04 | chale| | | 2 | 26211 | tulo| psql | tdb1| 2019-09-02 19:31:58| 00:00:06 | osagwira ntchito | yamba; | | 3 | 26211 | tulo| psql | tdb1| 2019-09-02 19:32:16 | 00:01:45 | osagwira ntchito | loko wafer_data; | | 4 | 26211 | tulo| psql | tdb1| 2019-09-02 19:35:54 | 00:01:23 | chale| perekani; | | 5 | 26211 | tulo| psql | tdb1| 2019-09-02 19:38:46 | 00:00:02 | osagwira ntchito | yamba; | | 6 | 26211 | tulo| psql | tdb1| 2019-09-02 19:38:54 | 00:00:08 | osagwira ntchito | loko wafer_data; | | 7 | 26211 | tulo| psql | tdb1| 2019-09-02 19:39:08 | 00:42:42 | chale| perekani; | | 8 | 26211 | tulo| psql | tdb1| 2019-09-03 07:12:07 | 00:00:52 | ntchito| sankhani test_del();

Chitukuko.

Mafunso ofunikira omwe awonetsedwa ndi malipoti otsatiridwawo amapangitsa moyo kukhala wosavuta powunika zochitika zantchito.
Kutengera ndi mafunso oyambira, mutha kupeza lipoti lomwe limafanana mosadziwika bwino ndi Oracle's AWR.
Chitsanzo cha lipoti lachidule

+------------------------------------------------ ----------------------------------- | LIPOTI LOPHUNZITSIDWA LA ZOCHITA NDI KUDIKIRA.

Zipitilizidwa. Chotsatira pamzere ndikupanga mbiri ya loko (pg_stat_locks), kufotokozera mwatsatanetsatane njira yodzaza matebulo.

Source: www.habr.com

Kuwonjezera ndemanga