ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Kev sim los tsim ASH analogue rau PostgreSQL

Kev sim los tsim ASH analogue rau PostgreSQL

Nqe lus ntawm qhov teeb meem

Txhawm rau txhim kho PostgreSQL cov lus nug, muaj peev xwm txheeb xyuas keeb kwm kev ua ub no, tshwj xeeb, tos, xauv, thiab cov ntaub ntawv txheeb cais, yog qhov xav tau heev.

Muaj cib fim

Keeb Kwm Kev Tshawb Fawb Kev Ua Haujlwm lossis "AWR rau Postgres": kev daws teeb meem zoo heev, tab sis tsis muaj keeb kwm ntawm pg_stat_activity thiab pg_locks.

pgsentinel txuas ntxiv :
Β«Tag nrho cov ntaub ntawv khaws tseg tsuas yog khaws cia hauv RAM, thiab kev siv lub cim xeeb yog tswj hwm los ntawm tus lej ntawm cov ntaub ntawv khaws tseg kawg.

Cov queryid teb yog ntxiv - tib queryid los ntawm pg_stat_statements extension (yuav tsum tau ua ntej-installation).Β«

Qhov no, ntawm chav kawm, yuav pab tau ntau, tab sis qhov teeb meem tshaj plaws yog thawj lub ntsiab lus. "Tag nrho cov ntaub ntawv khaws tseg tsuas yog khaws cia hauv RAM ”, i.e. muaj kev cuam tshuam rau lub hom phiaj puag. Tsis tas li ntawd, tsis muaj qhov xauv keeb kwm thiab cov ntaub ntawv txheeb cais. Cov. qhov kev daws teeb meem feem ntau hais tsis tiav: "Tseem tsis tau npaj txhij-ua pob rau installation tsis tau. Nws raug nquahu kom rub tawm cov peev txheej thiab sib sau cov tsev qiv ntawv koj tus kheej. Koj thawj zaug yuav tsum tau nruab lub pob "devel" rau koj lub server thiab teeb tsa txoj hauv kev rau pg_config hauv PATH hloov pauv.".

Nyob rau hauv dav dav, muaj ntau fuss, thiab nyob rau hauv cov ntaub ntawv ntawm loj ntau lawm databases, tej zaum yuav tsis muaj peev xwm ua dab tsi nrog lub server. Peb yuav tsum tuaj nrog ib yam dab tsi ntawm peb tus kheej dua.

Ceebtoom

Vim yog lub ntim loj heev thiab vim lub sijhawm xeem tsis tiav, tsab xov xwm tsuas yog siv rau cov ntaub ntawv xov xwm, tsis yog cov txheej txheem ntawm cov ntsiab lus thiab cov txiaj ntsig nruab nrab.
Cov ntaub ntawv ntxaws ntxiv yuav raug npaj tom qab, hauv ntu

Daim ntawv xav tau rau kev daws teeb meem

Nws yog ib qho tsim nyog los tsim ib lub cuab yeej uas tso cai rau koj khaws cia:

pg_stat_activity saib keeb kwm
Session lock keeb kwm siv pg_locks saib

Kev daws teeb meem- txo qhov cuam tshuam rau lub hom phiaj database.

General tswv yim- tus neeg sawv cev sau cov ntaub ntawv tau pib tsis nyob rau hauv lub hom phiaj database, tab sis nyob rau hauv kev soj ntsuam database raws li ib tug systemd kev pab cuam. Yog lawm, qee cov ntaub ntawv poob tuaj yeem ua tau, tab sis qhov no tsis yog qhov tseem ceeb rau kev tshaj tawm, tab sis tsis muaj kev cuam tshuam rau lub hom phiaj database ntawm lub cim xeeb thiab qhov chaw disk. Thiab nyob rau hauv rooj plaub ntawm kev siv lub pas dej sib txuas, qhov cuam tshuam rau cov neeg siv cov txheej txheem yog tsawg heev.

Cov theem pib

1. Cov rooj pabcuam

Ib tug cais schema yog siv los khaws cov ntxhuav, thiaj li tsis cuam tshuam rau kev tsom xam ntawm lub ntsiab rooj siv.

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

Tseem ceeb: Cov schema tsis yog tsim nyob rau hauv lub hom phiaj database, tab sis nyob rau hauv lub saib xyuas database.

pg_stat_activity saib keeb kwm

Ib lub rooj yog siv los khaws cov snapshots tam sim no ntawm pg_stat_activity saib

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

Txhawm rau kom nrawm nrawm - tsis muaj kev ntsuas lossis kev txwv.

Txhawm rau khaws keeb kwm nws tus kheej, lub rooj sib faib yog siv:

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

Txij li thaum nyob rau hauv cov ntaub ntawv no tsis muaj qhov yuav tsum tau rau kev ceev ceev, qee qhov indexes tau tsim los ua kom ceev cov kev tsim cov ntaub ntawv.

Session thaiv keeb kwm

Ib lub rooj yog siv los khaws cov snapshots tam sim no ntawm kev kaw qhov rooj:

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

Tsis tas li, txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau txhawm rau ua kom nrawm, tsis muaj qhov ntsuas lossis kev txwv.

Txhawm rau khaws keeb kwm nws tus kheej, lub rooj sib faib yog siv:

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

Txij li thaum nyob rau hauv cov ntaub ntawv no tsis muaj qhov yuav tsum tau rau kev ceev ceev, qee qhov indexes tau tsim los ua kom ceev cov kev tsim cov ntaub ntawv.

2. Sau cov keeb kwm tam sim no

Txhawm rau sau ncaj qha saib snapshots, ib tsab ntawv bash yog siv uas ua haujlwm 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

lpgsql ua Lub dblink muaj nuj nqi nkag mus saib hauv lub hom phiaj database thiab ntxig kab rau hauv cov rooj pabcuam hauv kev saib xyuas database.

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;

Txhawm rau sau cov duab snapshots, cov kev pabcuam systemd thiab ob tsab ntawv raug siv:

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

Muab cov cai rau scripts:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Cia peb pib qhov kev pabcuam:
# systemctl daemon-reload
# systemctl pib pg_current_activity.service

Yog li, keeb kwm ntawm kev pom yog sau nyob rau hauv daim ntawv ntawm ob-los-thib ob snapshots. Tau kawg, yog tias txhua yam raug tso tseg, cov ntxhuav yuav loj tuaj sai sai thiab ntau dua lossis tsawg dua kev ua haujlwm yuav ua tsis tau.

Nws yog tsim nyog los npaj cov ntaub ntawv archiving.

3. Cov ntaub ntawv keeb kwm

Rau archiving, partitioned tables archive * yog siv.

Cov partitions tshiab raug tsim txhua teev, thaum cov ntaub ntawv qub raug tshem tawm ntawm keeb kwm * cov ntxhuav, yog li qhov loj ntawm keeb kwm * cov ntxhuav tsis hloov ntau thiab qhov ntxig nrawm tsis degrade raws sijhawm.

Kev tsim cov ntu tshiab yog ua los ntawm plpgsql muaj nuj nqi activity_hist.archive_current_activity. Lub algorithm ntawm kev ua haujlwm yog qhov yooj yim heev (siv cov piv txwv ntawm ntu rau archive_pg_stat_activity table).

Tsim thiab sau ib ntu tshiab

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

Tsim cov indexes

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

Tshem tawm cov ntaub ntawv qub los ntawm cov lus keeb kwm_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Ntawm chav kawm, los ntawm lub sij hawm mus rau lub sij hawm, qub seem yog deleted raws li tsis tsim nyog.

Cov ntawv ceeb toom

Qhov tseeb, vim li cas txhua qhov no tau ua tiav? Txhawm rau kom tau txais cov ntawv ceeb toom vaguely nco txog Oracle's AWR.

Nws yog ib qho tseem ceeb ntxiv tias txhawm rau tau txais cov ntawv ceeb toom, koj yuav tsum tsim kom muaj kev sib txuas ntawm pg_stat_activity thiab pg_stat_statements views. Cov ntxhuav tau txuas los ntawm kev ntxiv 'queryid' kem rau 'history_pg_stat_activity', 'archive_pg_stat_activity' cov ntxhuav. Tus txheej txheem ntawm kev ntxiv ib kab nqi yog tshaj qhov ntawm cov kab lus no thiab tau piav qhia ntawm no βˆ’ pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TAG NRHO CPU LUB SIJ HAWM RAU QUERIES

Thov:

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

Piv Txwv:

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

TAG NRHO LUB SIJ HAWM RAU QHOV TSEEB

Thov:

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

Piv txwv:

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

YUAV TSUM TAU QHIA

Thov:

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

Piv Txwv:

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

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

LOCKED PROCESSES keeb kwm

Thov:

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

Piv Txwv:

------------------------------------------------------ ------------------------------------------------------ --------------------------------------------- | LOCKED PROCESSES HISTORY --------+----------------+--------+--------+-------- ------------+--------+--------------------- +-------------------- | #| pid | pib| ntev | thaiv_pids| kev sib raug zoo| hom | locktype --------+----------------+-----+---------- -------- -------------------------+-------------------------+----- ----------------- | 1| 26224 | 2019-09-02 19:32:16 | 00:01:45 | {26211}| 16541 | AccessShareLock| kev sib raug zoo | 2| 26390 | 2019-09-02 19:34:03 | 00:00:53 | {26211}| 16541 | AccessShareLock| kev sib raug zoo | 3| 26391 | 2019-09-02 19:34:03 | 00:00:53 | {26211}| 16541 | AccessShareLock| kev sib raug zoo | 4| 26531 | 2019-09-02 19:35:27 | 00:00:12 | {26211}| 16541 | AccessShareLock| kev sib raug zoo | 5 | 27284 2019; ib 09-02-19 44:02:00 | 00:19:27276 | {16541}| 6 | AccessShareLock| kev sib raug zoo | 27283| 2019 09; ib 02-19-44 02:00:00 | 19:27276:16541 | {7}| 27286 | AccessShareLock| kev sib raug zoo | 2019 | 09 02 | 19-44-02 00:00:19 | 27276:16541:8 | {27423}| 2019 | AccessShareLock| kev sib raug zoo | 09 | 02 | 19-45-24 00:00:12 | 27394:16541:9 | {27648}| 2019 | AccessShareLock| kev sib raug zoo | 09 | 02 :ib 19-48-06 00:00:20 | 27647:16541:10 | {27650}| 2019 | AccessShareLock| kev sib raug zoo | 09 | 02 | 19-48-06 00:00:20 | 27647:16541:11 | {27735}| 2019 | AccessShareLock| kev sib raug zoo | 09 | 02 :ib 19-49-08 00:00:06 | 27650:16541:12 | {28380}| 2019 | AccessExclusiveLock | kev sib raug zoo | 09 | 02 | 19-56-03 00:01:56 | 28379:16541:13 | {28379}| 2019 | AccessShareLock| kev sib raug zoo | 09 | 02; ib 19-56-03 00:00:01 | 28377:16541:28376 | XNUMX :ib XNUMX | AccessExclusiveLock | kev sib raug zoo | | | | | XNUMX :ib |

TXOJ CAI NTAWM HAIV NEEG

Thov:

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

Piv Txwv:

------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ----------------------- THOV KEV PAB CUAM NTUJ +----+----------------+-------- -------------------------------------------------------------------- ----------+--------------------+-------------------- --------+------------------------------------------------ | #| pid | siv lub npe | app_name| npe | pib| ntev | xeev| lus nug ----------------+----------------+----------+-----+ --------- ----------------------------+-------------------- -------------------------------------------------------------------- ------------------ | 1| 26211 | tus | psql| twb 1| 2019-09-02 19:31:54 | 00:00:04 | idle| | 2| 26211 | tus | psql| twb 1| 2019-09-02 19:31:58 | 00:00:06 | tsis ua haujlwm hauv kev lag luam | pib; | 3| 26211 | tus | psql| twb 1| 2019-09-02 19:32:16 | 00:01:45 | tsis ua haujlwm hauv kev lag luam | xauv lub rooj wafer_data; | 4| 26211 | tus | psql| twb 1| 2019-09-02 19:35:54 | 00:01:23 | idle| cog lus; | 5 | 26211 | tus | psql| twb 1| 2019-09-02 19:38:46 | 00:00:02 | tsis ua haujlwm hauv kev lag luam | pib; | 6| 26211 | tus | psql| twb 1| 2019-09-02 19:38:54 | 00:00:08 | tsis ua haujlwm hauv kev lag luam | xauv lub rooj wafer_data; | 7 | 26211 | tus | psql| twb 1| 2019-09-02 19:39:08 | 00:42:42 | idle| cog lus; | 8 | 26211 | tus | psql| twb 1| 2019-09-03 07:12:07 | 00:00:52 | nquag | xaiv test_del();

Kev loj hlob.

Cov lus nug tseem ceeb tau qhia thiab cov ntawv tshaj tawm tau ua rau lub neej yooj yim dua thaum txheeb xyuas qhov xwm txheej ua haujlwm.
Raws li cov lus nug yooj yim, koj tuaj yeem tau txais daim ntawv tshaj tawm uas tsis zoo ib yam li Oracle's AWR.
Summary report example

+---------------------------------------------------- -------------------------------------------------- | CONSOLIDATED REPORT FOR ACTIVITY THIAB WAITINGS.

Yuav tsum tau txuas ntxiv. Tom ntej no hauv kab yog kev tsim cov keeb kwm kaw (pg_stat_locks), cov lus piav qhia ntxaws ntxiv ntawm cov txheej txheem sau cov ntxhuav.

Tau qhov twg los: www.hab.com

Ntxiv ib saib