ProHoster > Blag > Riarachán > Iarracht chun analóg ASH a chruthú do PostgreSQL

Iarracht chun analóg ASH a chruthú do PostgreSQL

An fhadhb a fhoirmiú

Chun fiosrúcháin PostgreSQL a bharrfheabhsú, tá an cumas chun anailís a dhéanamh ar stair ghníomhaíochta, go háirithe, feithimh, glais, agus staitisticí tábla, ag teastáil go mór.

Deiseanna atá ar fáil

Uirlis Anailíse Ualach Oibre Stairiúil nó "AWR do Postgres": réiteach an-suimiúil, ach níl aon stair pg_stat_activity agus pg_locks.

síneadh pgsentinel :
«Stóráiltear gach faisnéis carntha i RAM amháin, agus rialaítear an méid cuimhne a chaitear ag líon na dtaifead deireanach a stóráiltear.

Cuirtear an réimse queryid leis - an queryid céanna ón síneadh pg_stat_statements (réamhshuiteáil riachtanach).«

Chabhródh sé seo go mór, ar ndóigh, ach is é an rud is trioblóidí an chéad phointe.”Stóráiltear gach faisnéis carntha i RAM amháin ”, i.e. tá tionchar ar an mbonn sprice. Ina theannta sin, níl aon stair ghlais agus staitisticí tábla ann. Iad siúd. tá an réiteach neamhiomlán i gcoitinne: “Níl aon phacáiste réidh le suiteáil fós. Moltar na foinsí a íoslódáil agus an leabharlann a chur le chéile tú féin. Ní mór duit an pacáiste “forbair” do do fhreastalaí a shuiteáil ar dtús agus an cosán a shocrú go pg_config san athróg PATH.".

Go ginearálta, tá go leor fuss ann, agus i gcás bunachair shonraí táirgthe tromchúiseacha, b'fhéidir nach bhféadfaí aon rud a dhéanamh leis an bhfreastalaí. Caithfimid teacht suas le rud éigin dár gcuid féin arís.

Rabhadh.

Mar gheall ar an líon sách mór agus mar gheall ar an tréimhse tástála neamhiomlán, tá an t-alt faisnéiseach go príomha, seachas mar thacar tráchtais agus torthaí idirmheánacha.
Déanfar ábhar níos mionsonraithe a ullmhú níos déanaí, i gcodanna

Dréacht-riachtanais don réiteach

Is gá uirlis a fhorbairt a ligeann duit a stóráil:

pg_stat_ stair amhairc gníomhaíochta
Stair glasála seisiúin ag baint úsáide as an radharc pg_locks

Riachtanas réitigh– an tionchar ar an spriocbhunachar sonraí a íoslaghdú.

Smaoineamh ginearálta– ní seoltar an gníomhaire bailithe sonraí sa spriocbhunachar sonraí, ach sa bhunachar sonraí faireacháin mar sheirbhís chórasach. Sea, is féidir roinnt sonraí a chailliúint, ach níl sé seo ríthábhachtach le haghaidh tuairiscithe, ach níl aon tionchar ar an mbunachar sonraí sprice i dtéarmaí cuimhne agus spás diosca. Agus i gcás linn nasc a úsáid, is beag an tionchar ar phróisis úsáideoirí.

Céimeanna cur chun feidhme

táblaí 1.Service

Úsáidtear scéimre ar leith chun táblaí a stóráil, ionas nach gcuirfear casta ar an anailís ar na príomhtháblaí a úsáidtear.

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

Tábhachtach: Ní chruthaítear an scéimre sa bhunachar sonraí sprice, ach sa bhunachar sonraí monatóireachta.

pg_stat_ stair amhairc gníomhaíochta

Úsáidtear tábla chun pictiúir reatha den amharc pg_stat_activity a stóráil

gníomhaíocht_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
);

Chun dlús a chur isteach - gan aon innéacsanna nó srianta.

Chun an stair féin a stóráil, úsáidtear tábla deighilte:

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

Ós rud é sa chás seo nach bhfuil aon cheanglais ann maidir le luas ionsáite, cruthaíodh roinnt innéacsanna chun cruthú tuarascálacha a bhrostú.

Stair blocála seisiúin

Úsáidtear tábla chun pictiúir reatha de ghlas seisiúin a stóráil:

gníomhaíocht_hist.history_glasáil :

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

Chomh maith leis sin, chun cur isteach a bhrostú, níl aon innéacsanna nó srianta ann.

Chun an stair féin a stóráil, úsáidtear tábla deighilte:

gníomhaíocht_hist.cartlann_glasáil:

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

Ós rud é sa chás seo nach bhfuil aon cheanglais ann maidir le luas ionsáite, cruthaíodh roinnt innéacsanna chun cruthú tuarascálacha a bhrostú.

2.An stair reatha a líonadh

Chun seatanna a bhailiú go díreach, úsáidtear script bash a ritheann an fheidhm plpgsql.

faigh_gníomhaíocht_reatha.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 Faigheann an fheidhm dblink rochtain ar radharcanna sa bhunachar sonraí sprice agus cuireann sí sraitheanna isteach i dtáblaí seirbhíse sa bhunachar sonraí monatóireachta.

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;

Chun seatanna a bhailiú, úsáidtear an tseirbhís sistéamaithe agus dhá script:

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

Sann cearta do scripteanna:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Cuirimis tús leis an tseirbhís:
# systemctl daemon-reload
# systemctl tús a chur le pg_current_activity.service

Mar sin, bailítear stair na dtuairimí i bhfoirm pictiúir soicind ar soicind. Ar ndóigh, má tá gach rud fágtha mar atá, méadóidh na táblaí go han-tapa i méid agus beidh obair tháirgiúil níos mó nó níos lú dodhéanta.

Is gá cartlannú sonraí a eagrú.

3. Stair cartlannaithe

Chun cartlannú a dhéanamh, úsáidtear cartlann táblaí deighilte*.

Cruthaítear deighiltí nua gach uair an chloig, agus baintear seansonraí ó na táblaí staire*, mar sin ní athraíonn méid na dtáblaí staire* mórán agus ní dhíghrádaíonn an luas ionsáite le himeacht ama.

Cruthaíonn an fheidhm plpgsql activity_hist.archive_current_activity chun rannóga nua a chruthú. Tá an t-algartam oibre an-simplí (ag baint úsáide as sampla na rannóige don tábla archive_pg_stat_activity).

Cruthaigh agus líon isteach alt nua

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

Innéacsanna a chruthú

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

Seanshonraí á mbaint den tábla history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Ar ndóigh, ó am go ham, scriostar sean-ailt mar ní gá.

Tuarascálacha bunúsacha

I ndáiríre, cén fáth go bhfuil sé seo go léir á dhéanamh? Chun tuairiscí a fháil atá an-doiléir i gcuimhne AWR Oracle.

Tá sé tábhachtach a chur leis go gcaithfidh tú nasc a chruthú idir na radharcanna pg_stat_activity agus pg_stat_statements chun tuairiscí a fháil. Nasctar na táblaí trí cholún ‘queryid’ a chur leis na táblaí ‘history_pg_stat_activity’, ‘archive_pg_stat_activity’. Tá an modh chun luach colúin a shuimiú lasmuigh de scóip an ailt seo agus tá cur síos air anseo - pg_stat_statements +pg_stat_activity +loq_query = pg_ash? .

AM IOMLÁN LAP DO CHEISTEANNA

Iarratas :

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

Sampla:

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

AM FEITHIMH IOMLÁN DO CHEISTEANNA

Iarratas :

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

Sampla:

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

FEITHIMH AR CHEISTEANNA

Iarratais:

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

Sampla:

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

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

STAIR PRÓISIS GHLUAISTE

Iarratas:

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

Sampla:

----------------------------------------------- ----------------------------------------------- ------------------------------- | STAIR PRÓISIS GHLUAISTE +-----+----------+-----+------- --+----------- -------+------------------+----- -------------- +------------------ | #| pid| thosaigh| ré| ag bacadh_pids| gaol| mód| locktype +----------+----------+-----+--------- +------ ----------+----------+------ -------------+----- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211} | 16541| AccessShareLock| ndáil | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211} | 16541| AccessShareLock| ndáil | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211} | 16541| AccessShareLock| ndáil | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211} | 16541| AccessShareLock| ndáil | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| ndáil | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| ndáil | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| ndáil | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| ndáil | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| ndáil | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| ndáil | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| ndáil | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| ndáil | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| ndáil | | | | | 28376| |

STAIR PRÓISIS BHLOSCAIL

Iarratais:

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

Sampla:

----------------------------------------------- ----------------------------------------------- ----------------------------------------------- ---------------------- STAIR PRÓISIS BHLOSCAIL +----+----------+------ ---+-------------------+---------+------------- ------+-------------------+--------------------- -------+------------------------------------- | #| pid| ainm úsáide| ainm_iarratas| datainm| thosaigh| ré| stáit | ceist +----------+----------+---------+-----+ -------- -+------------------+---------------- --+------ -------------------------+--------------- ------- ---------------- | 1| 26211| tuisir | psql| tdb1| 2019-09-02 19:31:54| 00:00:04| díomhaoin | | 2| 26211| tuisir | psql| tdb1| 2019-09-02 19:31:58| 00:00:06| díomhaoin in idirbheart| tús ; | 3| 26211| tuisir | psql| tdb1| 2019-09-02 19:32:16| 00:01:45| díomhaoin in idirbheart| glas tábla wafer_data; | 4| 26211| tuisir | psql| tdb1| 2019-09-02 19:35:54| 00:01:23| díomhaoin | tiomnaigh ; | 5| 26211| tuisir | psql| tdb1| 2019-09-02 19:38:46| 00:00:02| díomhaoin in idirbheart| tús ; | 6| 26211| tuisir | psql| tdb1| 2019-09-02 19:38:54| 00:00:08| díomhaoin in idirbheart| glas tábla wafer_data; | 7| 26211| tuisir | psql| tdb1| 2019-09-02 19:39:08| 00:42:42| díomhaoin | tiomnaigh ; | 8| 26211| tuisir | psql| tdb1| 2019-09-03 07:12:07| 00:00:52| gníomhach | roghnaigh test_del();

Forbairt.

Déanann na ceisteanna bunúsacha a léirítear agus na tuairiscí a eascraíonn as an saol i bhfad níos éasca cheana féin agus anailís á dhéanamh ar theagmhais feidhmíochta.
Bunaithe ar cheisteanna bunúsacha, is féidir leat tuairisc a fháil atá cosúil go doiléir le AWR Oracle.
Sampla de thuarascáil achomair

+---------------------------------------------- --------------------------------- | TUARASCÁIL COMHDHLÚITE DON GHNÍOMHAÍOCHTA AGUS FEITHIMH.

Le leanúint ar aghaidh. Is é an chéad líne eile ná cruthú stair ghlais (pg_stat_locks), cur síos níos mionsonraithe ar an bpróiseas chun táblaí a líonadh.

Foinse: will.com

Add a comment