ProHoster > Blog > Administrasyon an > Yon tantativ pou kreye yon analogue ASH pou PostgreSQL

Yon tantativ pou kreye yon analogue ASH pou PostgreSQL

Deklarasyon sou pwoblèm nan

Pou optimize demann PostgreSQL, kapasite nan analize istwa aktivite, an patikilye, datant, kadna, ak estatistik tab, se trè obligatwa.

Opòtinite ki disponib

Zouti istorik analiz chaj travay oswa "AWR pou Postgres": yon solisyon trè enteresan, men pa gen okenn istwa nan pg_stat_activity ak pg_locks.

ekstansyon pgsentinel :
«Tout enfòmasyon akimile yo estoke sèlman nan RAM, epi kantite memwa ki konsome yo reglemante pa kantite dènye dosye ki estoke yo.

Yo ajoute jaden an queryid - menm queryid ki soti nan ekstansyon pg_stat_statements (pre-enstalasyon obligatwa).«

Natirèlman, sa a ta ede anpil, men bagay ki pi difisil la se premye pwen an.Tout enfòmasyon akimile yo estoke sèlman nan RAM ", i.e. gen yon enpak sou baz sib la. Anplis de sa, pa gen okenn istwa fèmen ak estatistik tab. Moun sa yo. solisyon an jeneralman enkonplè: "Pa gen okenn pake ki pare pou enstalasyon ankò. Li sijere pou telechaje sous yo epi rasanble bibliyotèk la tèt ou. Ou premye bezwen enstale pake "devel" pou sèvè ou a epi mete chemen an nan pg_config nan varyab PATH la.".

An jeneral, gen anpil meli melo, ak nan ka a nan baz done pwodiksyon grav, li ka pa posib fè anyen ak sèvè a. Nou bezwen vin ak yon bagay pwòp pa nou ankò.

Avètisman

Akòz volim nan olye gwo ak akòz peryòd tès la enkonplè, atik la se sitou nan yon nati enfòmasyon, olye kòm yon seri tèz ak rezilta entèmedyè.
Materyèl plis detay yo pral prepare pita, an pati

Bouyon kondisyon pou solisyon an

Li nesesè yo devlope yon zouti ki pèmèt ou estoke:

pg_stat_activity gade istwa
Istwa fèmen sesyon yo lè l sèvi avèk gade pg_locks la

Solisyon egzijans– minimize enpak sou baz done sib la.

Lide jeneral– se ajan koleksyon done a lanse pa nan baz done a sib, men nan baz done a siveyans kòm yon sèvis systemd. Wi, gen kèk pèt done posib, men sa a se pa kritik pou rapò, men pa gen okenn enpak sou baz done a sib an tèm de memwa ak espas ki gen kapasite. Ak nan ka a nan lè l sèvi avèk yon pisin koneksyon, enpak la sou pwosesis itilizatè a se minim.

Etap aplikasyon

1.Service tab

Yo itilize yon chema separe pou estoke tab yo, pou yo pa konplike analiz prensipal tab yo itilize yo.

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

Enpòtan: Chema a pa kreye nan baz done sib la, men nan baz done siveyans la.

pg_stat_activity gade istwa

Yo itilize yon tablo pou estoke snapshots aktyèl la nan gade 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
);

Pou pi vit ensèsyon - pa gen endis oswa restriksyon.

Pou estoke istwa a tèt li, yo itilize yon tab ki divize:

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

Depi nan ka sa a pa gen okenn kondisyon pou vitès ensèsyon, kèk endèks yo te kreye pi vit kreyasyon an nan rapò.

Sesyon bloke istwa

Yo itilize yon tab pou estoke snapshots aktyèl yo nan kadna sesyon yo:

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

Epitou, akselere ensèsyon, pa gen okenn endèks oswa restriksyon.

Pou estoke istwa a tèt li, yo itilize yon tab ki divize:

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

Depi nan ka sa a pa gen okenn kondisyon pou vitès ensèsyon, kèk endèks yo te kreye pi vit kreyasyon an nan rapò.

2.Ranpli istwa aktyèl la

Pou kolekte dirèkteman snapshots gade, yo itilize yon script bash ki kouri fonksyon plpgsql la.

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 Fonksyon dblink la jwenn opinyon nan baz done sib la epi mete ranje nan tab sèvis yo nan baz done siveyans la.

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;

Pou kolekte snapshots, yo itilize sèvis systemd ak de scripts:

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

Bay dwa pou scripts:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Ann kòmanse sèvis la:
# systemctl saten-reload
# systemctl kòmanse pg_current_activity.service

Kidonk, istwa opinyon yo kolekte nan fòm snapshots dezyèm pa dezyèm. Natirèlman, si tout bagay yo rete jan yo ye, tab yo pral trè vit ogmante nan gwosè ak plis oswa mwens pwodiktif travay ap vin enposib.

Li nesesè pou òganize done achiv.

3. Achiv istwa

Pou achiv, yo itilize achiv tab ki divize*.

Nouvo patisyon yo kreye chak èdtan, alòske ansyen done yo retire nan tablo istwa* yo, kidonk gwosè tab istwa* yo pa chanje anpil epi vitès ensèsyon an pa degrade sou tan.

Kreyasyon nouvo seksyon fèt pa fonksyon plpgsql activity_hist.archive_current_activity. Algorithm nan travay se trè senp (itilize egzanp lan nan seksyon an pou tab la archive_pg_stat_activity).

Kreye epi ranpli yon nouvo seksyon

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

Kreye endèks

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

Retire ansyen done nan tablo history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Natirèlman, de tan zan tan, seksyon fin vye granmoun yo efase kòm nesesè.

Rapò debaz yo

Aktyèlman, poukisa tout bagay sa yo ap fèt? Pou jwenn rapò trè vagman okoumansman de AWR Oracle a.

Li enpòtan pou ajoute ke pou resevwa rapò, ou bezwen konstwi yon koneksyon ant pg_stat_activity ak pg_stat_statements opinyon yo. Tablo yo lye lè yo ajoute yon kolòn 'queryid' nan tab 'history_pg_stat_activity', 'archive_pg_stat_activity' yo. Metòd pou ajoute yon valè kolòn depase sijè ki abòde atik sa a epi li dekri isit la - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TOTAL CPU TIME POU QUERIES

Demann:

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

Egzanp:

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

TOTAL TAN DATAN POU KÈSYON

Demann:

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

Egzanp:

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

TANTAN POU QUESTION

Demann:

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

Egzanp:

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

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

PWOSESIS BLEYE ISTWA

Demann:

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

Egzanp:

------------------------------------------------- ------------------------------------------------- --------------------------------- | ISTWA PWOSESIS BLE +-----+----------+-----+-------- --+------------ --------+--------------------+----- --------------- +------------------- | #| pid| te kòmanse| dire| blocking_pids| relasyon| mòd| locktype +----------+----------+-----+---------- +--------- -----------+-----------+------- -------------+----- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| relasyon | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| relasyon | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| relasyon | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| relasyon | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| relasyon | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| relasyon | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| relasyon | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| relasyon | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| relasyon | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| relasyon | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| relasyon | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| relasyon | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| relasyon | | | | | 28376| |

BLOKE PWOSESIS ISTWA

Demann:

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

Egzanp:

------------------------------------------------- ------------------------------------------------- ------------------------------------------------- ----------------------- ISTWA PWOSESIS BLOKE +----+----------+------- ---+--------------------+----------+--------------- ------+--------------------+---------------------- -------+-------------------------------------- | #| pid| non itilizasyon| non_aplikasyon| non dat| te kòmanse| dire| eta| demann +----------+----------+----------+-----+ --------- -+--------------------+----------------- --+------ -------------------------+---------------- ------- ----------------- | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| san fè anyen konsa| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| san fè anyen konsa nan tranzaksyon| kòmanse; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| san fè anyen konsa nan tranzaksyon| fèmen tab wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| san fè anyen konsa| komèt; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| san fè anyen konsa nan tranzaksyon| kòmanse; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| san fè anyen konsa nan tranzaksyon| fèmen tab wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| san fè anyen konsa| komèt; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| aktif| chwazi test_del();

Devlopman.

Rekèt debaz yo montre ak rapò ki kapab lakòz yo deja fè lavi pi fasil lè w ap analize ensidan pèfòmans yo.
Dapre demann debaz, ou ka jwenn yon rapò ki vagman sanble ak AWR Oracle a.
Egzanp rapò rezime

+------------------------------------------------ ----------------------------------- | RAPÒ KONSOLIDE POU AKTIVITE AK DATAN.

A kontinye. Next nan liy se kreyasyon an nan yon istwa fèmen (pg_stat_locks), yon deskripsyon pi detaye sou pwosesis la nan ranpli tab yo.

Sous: www.habr.com

Add nouvo kòmantè