ProHoster > Blog > Rêveberî > Hewldanek ji bo afirandina analogek ASH ji bo PostgreSQL

Hewldanek ji bo afirandina analogek ASH ji bo PostgreSQL

Formulkirina pirsgirêkê

Ji bo xweşbînkirina pirsên PostgreSQL, şiyana analîzkirina dîroka çalakiyê, bi taybetî, li bendê, qefilandin û statîstîkên tabloyê, pir hewce ye.

Derfetên berdest

Amûra Analîza Karê Dîrokî an "AWR ji bo Postgres": çareseriyek pir balkêş, lê dîroka pg_stat_activity û pg_locks tune.

dirêjkirina pgsentinel :
«Hemî agahdariya berhevkirî tenê di RAM-ê de têne hilanîn, û mîqdara bîranînê ya ku tê vexwarin ji hêla hejmara tomarên paşîn ên hilandî ve têne rêve kirin.

Qada queryid tê zêdekirin - heman pirsiyar ji pêveka pg_stat_statements (pêş-sazkirinê pêwîst e).«

Ev, bê guman, dê gelek alîkar be, lê ya herî bi êş xala yekem e."Hemî agahdariya berhevkirî tenê di RAM-ê de têne hilanîn ", ango. bandorek li ser bingeha armancê heye. Digel vê yekê, dîroka kilît û statîstîkên tabloyê tune. Ewan. çareserî bi gelemperî ne temam e: "Ji bo sazkirinê hêj pakêtek amade tune. Tê pêşniyar kirin ku çavkaniyan dakêşin û pirtûkxaneyê bi xwe kom bikin. Pêşî hûn hewce ne ku pakêta "pêşvebirinê" ji bo servera xwe saz bikin û riya pg_config di guhêrbar PATH de saz bikin.".

Bi gelemperî, pir tevlihevî heye, û di doza databasên hilberîna ciddî de, dibe ku nekare tiştek bi serverê re bike. Pêdivî ye ku em dîsa tiştek ji xwe re peyda bikin.

Hişyariyê

Ji ber qebareya pir mezin û ji ber serdema ceribandina netemam, gotar bi piranî ji bo mebestên agahdarî ye, lê wekî komek tez û encamên navîn.
Materyalên berfirehtir dê paşê, di beşan de bêne amadekirin

Pêşnûmeya pêdiviyên ji bo çareseriyê

Pêdivî ye ku amûrek pêşve bibe ku destûrê dide te ku hilanînê:

pg_stat_activity dîroka dîtinê
Dîroka girtina danişînê bi karanîna dîtina pg_locks

Pêdiviya çareseriyê-bandora li ser databasa armancê kêm bikin.

Fikra giştî- Nûnera berhevkirina daneyan ne di databasa armancê de, lê di databasa çavdêriyê de wekî karûbarek pergalê tê destpêkirin. Erê, hin windabûna daneyê mimkun e, lê ev ji bo raporkirinê ne krîtîk e, lê di warê bîr û cîhê dîskê de ti bandorek li ser databasa armanc tune. Û di mijara karanîna hewza pêwendiyê de, bandor li ser pêvajoyên bikarhêner hindik e.

Qonaxên pêkanînê

1.Tabloyên xizmetê

Ji bo hilanîna tabloyan nexşeyek cihêreng tê bikar anîn, da ku analîza tabloyên sereke yên ku hatine bikar anîn tevlihev nebe.

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

Girîng: Schema ne di databasa armancê de, lê di databasa çavdêriyê de hatî çêkirin.

pg_stat_activity dîroka dîtinê

Tabloyek tê bikar anîn da ku dîmenên heyî yên dîtina pg_stat_activity hilîne

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

Ji bo lêzêdekirina têketinê - bê index an sînorkirin.

Ji bo tomarkirina dîrokê bixwe, tabloyek dabeşkirî tê bikar anîn:

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

Ji ber ku di vê rewşê de ji bo leza têketinê hewcedarî tune, hin index hatine afirandin ku lezkirina çêkirina raporan.

Dîroka astengkirina rûniştinê

Tabloyek ji bo hilanîna dîmenên heyî yên qefleyên danişînê tê bikar anîn:

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

Di heman demê de, ji bo bilezkirina têketinê, ti index an sînorkirin tune.

Ji bo tomarkirina dîrokê bixwe, tabloyek dabeşkirî tê bikar anîn:

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

Ji ber ku di vê rewşê de ji bo leza têketinê hewcedarî tune, hin index hatine afirandin ku lezkirina çêkirina raporan.

2.Dagirtina dîroka heyî

Ji bo rasterast berhevkirina dîmenên dîmenê, skrîptek bash tê bikar anîn ku fonksiyona plpgsql dimeşîne.

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 Fonksiyona dblink di databasa armancê de digihîje dîtinan û rêzan di tabloyên karûbarê databasa çavdêriyê de dixe.

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;

Ji bo berhevkirina dîmenên dîmenan, karûbarê systemd û du nivîsar têne bikar anîn:

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

Mafên nivîsandinê bidin:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Ka em xizmetê dest pê bikin:
# systemctl daemon-reload
# systemctl pg_current_activity.service dest pê bike

Ji ber vê yekê, dîroka dîtinan di şiklê dîmenên saniye-duyan de têne berhev kirin. Bê guman, heke her tişt wekî ku tê hiştin, tablo dê pir zû mezin bibin û xebata kêm-zêde hilber dê ne gengaz bibe.

Pêdivî ye ku arşîvkirina daneyan organîze bike.

3. Dîroka arşîvkirin

Ji bo arşîvkirin, arşîva tabloyên dabeşkirî * têne bikar anîn.

Parçeyên nû her demjimêrek têne çêkirin, dema ku daneyên kevn ji tabloyên dîrokê* têne derxistin, ji ber vê yekê mezinahiya tabloyên dîrokê * pir nayê guheztin û leza têketinê bi demê re kêm nabe.

Afirandina beşên nû bi fonksiyona plpgsql activity_hist.archive_current_activity pêk tê. Algorîtmaya xebatê pir hêsan e (bikaranîna mînaka beşê ji bo tabloya archive_pg_stat_activity).

Beşek nû ava bikin û dagirin

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

Çêkirina indexan

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

Rakirina daneyên kevn ji tabloya history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Bê guman, dem bi dem, beşên kevn wekî nepêwist têne jêbirin.

Raporên bingehîn

Bi rastî, çima ev hemû têne kirin? Ji bo bidestxistina raporên pir nezelal ku AWR-ya Oracle tîne bîra xwe.

Girîng e ku lê zêde bikin ku ji bo wergirtina raporan, hûn hewce ne ku têkiliyek di navbera dîtinên pg_stat_activity û pg_stat_statements de ava bikin. Tablo bi lê zêdekirina stûnek 'queryid' li tabloyên 'history_pg_stat_activity', 'archive_pg_stat_activity' têne girêdan. Rêbaza lêzêdekirina nirxek stûnê li derveyî çarçoweya vê gotarê ye û li vir tête diyar kirin - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TOTAL DEMA CPU JI BO PIRSAN

Daxwaz:

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

Nimûne:

-------------------------------------------------------------------
| 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 DEMA LIBERÊ JI BO PIRSAN

Daxwaz:

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

Mînak:

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

LI BENDA PIRSAN DIKE

Daxwazan:

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

Nimûne:

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

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

PÊVAJOYÊN GIRTÎ DÎROKA

Tika:

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

Nimûne:

------------------------------------------------ ------------------------------------------------ --------------------------------- | DÎROKA PÊVAJOYÊN GIRTÎ +-----+----------+-----+-------------------------- --------+----------------------------------------- +------------------- | #| pid| dest pê kir| duration| blocking_pids| têkilî| mode| locktype +----------+----------+-----+------------------------ -----------+-------------------------------------- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| têkilî | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| têkilî | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| têkilî | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| têkilî | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| têkilî | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| têkilî | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| têkilî | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| têkilî | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| têkilî | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| têkilî | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| têkilî | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| têkilî | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| têkilî | | | | | 28376| |

DÎROKA PÊVAJOYÊN ASTEKIRIN

Daxwazan:

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

Nimûne:

------------------------------------------------ ------------------------------------------------ ------------------------------------------------ ------------------------ DÎROKA PÊVAJOYÊN ASTENGKIRIN +----+------------------- ---+---------------------------------------------- ---------------------------------------------- -------+--------------------------------------- | #| pid| navê bikarhêner| serlêdan_nav| datname| dest pê kir| duration| dewlet| pirs +----------+----------+---------+-----+ --------- -+-------------------------------------- --+------- ----------------------------------------- ------- ----------------- | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| bêkar| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| bêkar di danûstandinê de| destpêkirin; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| bêkar di danûstandinê de| lock table wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| bêkar| bikaranîn; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| bêkar di danûstandinê de| destpêkirin; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| bêkar di danûstandinê de| lock table wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| bêkar| bikaranîn; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| çalak| test_del hilbijêre();

Pêşveçûnî.

Pirsên bingehîn ên ku têne xuyang kirin û raporên encam jixwe dema ku bûyerên performansê analîz dikin jiyanê pir hêsantir dikin.
Li ser bingeha pirsên bingehîn, hûn dikarin raporek ku bi zelalî dişibihe AWR-a Oracle-ê bistînin.
Mînaka rapora kurt

+----------------------------------------------- ----------------------------------- | JI BO ÇALAKIYÊ Û BÊRANÎNAN RAPORA HEVKARÎ.

Ez bêtir ji te hez dikim. Di rêzê de çêkirina dîroka kilîtkirinê ye (pg_stat_locks), ravekirinek berfirehtir a pêvajoya dagirtina tabloyan.

Source: www.habr.com

Add a comment