ProHoster > Блог > Nchịkwa > Mgbalị ịmepụta analog ASH maka PostgreSQL

Mgbalị ịmepụta analog ASH maka PostgreSQL

Ulationkpụzi nsogbu a

Iji bulie ajụjụ PostgreSQL, ikike inyocha akụkọ ihe mere eme, ọkachasị, ichere, mkpọchi, na ọnụ ọgụgụ tebụl, chọrọ nke ukwuu.

Ohere dị

Ngwá ọrụ nyocha ibu ọrụ akụkọ ihe mere eme ma ọ bụ "AWR maka Postgres": ngwọta na-adọrọ mmasị, mana enweghị akụkọ ihe mere eme nke pg_stat_activity na pg_locks.

pgsentinel ndọtị :
«A na-echekwa ozi niile ekpokọbara naanị na RAM, yana ọnụọgụ ndekọ echekwara na-ahazi oke ebe nchekwa riri.

A na-agbakwunye mpaghara ajụjụ - otu ajụjụ ahụ sitere na pg_stat_statements ndọtị (nwụnye nke mbụ achọrọ).«

Nke a, n'ezie, ga-enyere aka nke ukwuu, mana ihe kacha enye nsogbu bụ isi ihe mbụ. "A na-echekwa ozi niile anakọtara naanị na RAM ”, i.e. enwere mmetụta na ntọala ebumnuche. Na mgbakwunye, enweghị akụkọ mkpọchi na ọnụ ọgụgụ tebụl. Ndị ahụ. ngwọta na-ekwukarị ezughị ezu: "Enweghị ngwungwu emebere maka nwụnye ma. A na-atụ aro ka ibudata isi mmalite ma chịkọta ọba akwụkwọ n'onwe gị. Ị ga-ebu ụzọ tinye ngwugwu "mmepe" maka ihe nkesa gị wee tọọ ụzọ pg_config na mgbanwe PATH.".

N'ozuzu, e nwere ọtụtụ ihe mgbagwoju anya, na n'ihe banyere ọdụ data mmepụta ihe dị oké njọ, ọ nwere ike ọ gaghị ekwe omume ịme ihe ọ bụla na ihe nkesa. Anyị kwesịrị iwepụta ihe nke anyị ọzọ.

Dọ aka na ntị.

N'ihi nnukwu olu na n'ihi oge ule na-ezughị ezu, isiokwu a bụ maka ebumnuche ozi, kama dị ka nchịkọta akụkọ na nsonaazụ etiti.
A ga-edozi ihe zuru ezu karị ma emechaa, na akụkụ

Draft chọrọ maka ngwọta

Ọ dị mkpa ịmepụta ngwá ọrụ na-enye gị ohere ịchekwa:

Akụkọ nlele pg_stat_activity
Akụkọ mkpọchi oge site na iji nlele pg_locks

Ngwọta chọrọ–belata mmetụta na nchekwa data ebumnuche.

Echiche izugbe- A na-ewepụta onye na-anakọta data ọ bụghị na nchekwa data ebumnuche, mana na nchekwa data nlekota dị ka ọrụ sistemu. Ee, ụfọdụ ọnwụ data ga-ekwe omume, ma nke a adịghị mkpa maka ịkọ akụkọ, ma ọ dịghị mmetụta ọ bụla na nchekwa data ezubere iche maka ebe nchekwa na ohere diski. Na n'ihe banyere iji ọdọ mmiri njikọ, mmetụta na usoro ndị ọrụ dị ntakịrị.

Usoro mmejuputa

Tebụl ọrụ 1

A na-eji atụmatụ dị iche iche na-echekwa tebụl, ka ọ ghara ime ka nyocha nke tebụl ndị isi eji eme ihe.

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

Ihe dị mkpa: Emepụtaghị atụmatụ a na nchekwa data ebumnuche, kama na nchekwa data nlekota.

Akụkọ nlele pg_stat_activity

A na-eji tebụl chekwaa foto pg_stat_activity ugbu a

ọrụ_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
);

Iji mee ka ntinye ngwa ngwa - enweghị ndeksi ma ọ bụ mgbochi.

Iji chekwaa akụkọ ihe mere eme n'onwe ya, a na-eji tebụl kewara ekewa:

ọrụ_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);

Ebe ọ bụ na n'ọnọdụ a, ọ dịghị ihe achọrọ maka ọsọ ntinye, ụfọdụ index ka emepụtara iji mee ka ịmepụta akụkọ dị ngwa.

Akụkọ igbochi nnọkọ

A na-eji tebụl echekwa foto mkpọchi oge ugbu a:

ọrụ_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
);

Ọzọkwa, iji mee ka ntinye dị ngwa ngwa, enweghị index ma ọ bụ mgbochi.

Iji chekwaa akụkọ ihe mere eme n'onwe ya, a na-eji tebụl kewara ekewa:

ọrụ_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);

Ebe ọ bụ na n'ọnọdụ a, ọ dịghị ihe achọrọ maka ọsọ ntinye, ụfọdụ index ka emepụtara iji mee ka ịmepụta akụkọ dị ngwa.

2. Na-ejuputa akụkọ ihe mere eme ugbu a

Iji nakọta ihe nlele ozugbo, a na-eji edemede bash na-arụ ọrụ plpgsql.

nweta_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 Ọrụ dblink na-enweta nlele na nchekwa data ebumnuche wee tinye ahịrị n'ime tebụl ọrụ na nchekwa data nlekota.

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

Iji nakọta foto nlele, a na-eji ọrụ sistemu na script abụọ:

pg_current_activity.ọrụ

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

Kenye ikike na script:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.ọrụ

Ka anyị malite ọrụ:
# systemctl daemon-reload
# systemctl malite pg_current_activity.service

Ya mere, a na-anakọta akụkọ ihe mere eme nke echiche n'ụdị nke abụọ na nke abụọ. N'ezie, ọ bụrụ na ihe niile na-ahapụ dị ka ọ dị, tebụl ga-adị ngwa ngwa na-abawanye na size na ihe ma ọ bụ obere na-arụpụta ọrụ ga-agaghị ekwe omume.

Ọ dị mkpa ịhazi nchekwa data.

3. Archive akụkọ ihe mere eme

Maka ebe nchekwa, a na-eji ebe nchekwa tebụl nke kewara ekewa.

A na-emepụta akụkụ ọhụrụ kwa elekere kwa elekere, ebe a na-ewepụ data ochie na tebụl akụkọ ihe mere eme *, ya mere, nha nke tebụl akụkọ ihe mere eme * adịghị agbanwe nke ukwuu na ntinye ntinye adịghị eweda oge.

Emepụtara ngalaba ọhụrụ site na ọrụ plpgsql ọrụ_hist.archive_current_activity. Algọridim nke ọrụ dị nnọọ mfe (iji ihe atụ nke ngalaba maka Archive_pg_stat_activity table).

Mepụta ma dejupụta ngalaba ọhụrụ

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

Ịmepụta ndeksi

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

Na-ewepụ data ochie na tebụl History_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

N'ezie, site n'oge ruo n'oge, a na-ehichapụ ngalaba ochie dị ka ndị na-adịghị mkpa.

Akụkọ ndị bụ isi

N'ezie, gịnị kpatara e ji eme ihe a nile? Iji nweta akụkọ na-edochaghị anya na-echetara Oracle's AWR.

Ọ dị mkpa ịgbakwunye na iji nweta akụkọ, ịkwesịrị ịmepụta njikọ n'etiti pg_stat_activity na echiche pg_stat_statements. Ejikọtara tebụl ndị a site na ịgbakwunye kọlụm 'queryid' na tebụl 'history_pg_stat_activity', 'archive_pg_stat_activity'. Usoro nke ịgbakwunye uru kọlụm karịrị oke isiokwu a ma kọwaa ya ebe a - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

Mkpokọta oge CPU maka ajụjụ

Arịrịọ:

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

Ihe nlele:

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

Mkpokọta oge echere maka ajụjụ

Arịrịọ:

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

Ihe atụ:

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

NA-eche ajụjụ

Arịrịọ:

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

Ihe nlele:

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

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

Akụkọ usoro akpọchiri akpọchi

Arịrịọ:

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

Ihe nlele:

------------------------------------------------- ------------------------------------------------- --------------------------------- | AKWỤKWỌ ỤWA ekpochigidere +----------------------------- -------------------------- +------------------ | #| pid | malitere| ogologo oge| igbochi_pids| njikọ| ọnọdụ| ụdị mkpọchi +------------------------------------------ ------------------------------------ ------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| njikọ | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| njikọ | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| njikọ | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| njikọ | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| njikọ | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| njikọ | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| njikọ | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| njikọ | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| njikọ | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| njikọ | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| njikọ | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| njikọ | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| njikọ | | | | | 28376| |

Usoro Mgbochi Akụkọ

Arịrịọ:

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

Ihe nlele:

------------------------------------------------- ------------------------------------------------- ------------------------------------------------- ------------- Akụkọ Mgbochi usoro +----+------------------ ---+------------------- ----------------------------------------- -------------------------------------------------------- | #| pid | aha njirimara| ngwa_aha| aha nna| malitere| ogologo oge| steeti| ajụjụ +-------------------------------- --------------------------- ------------------------------------------------------------------ ----------------- | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| na-abaghị uru| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| na-abaghị uru na azụmahịa| malite; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| na-abaghị uru na azụmahịa| okpokoro mkpọchi wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| na-abaghị uru| eme; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| na-abaghị uru na azụmahịa| malite; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| na-abaghị uru na azụmahịa| okpokoro mkpọchi wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| na-abaghị uru| eme; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| arụ ọrụ| họrọ test_del ();

Mmepe.

Ajụjụ ndị bụ isi egosiri na akụkọ ndị a na-enweta na-eme ka ndụ dịkwuo mfe mgbe a na-enyocha mmemme arụmọrụ.
Dabere na ajụjụ ndị bụ isi, ị nwere ike nweta akụkọ na-eyighị Oracle's AWR.
Ihe atụ nchịkọta akụkọ

------------------------------------------------ ---------------------------------- | AKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWỤKWASỊAKWỤKWỤKWỤKWỤKWỤKWỤKWASỊRỊRỊRỊ

Ka ga-aga n'ihu. Na-esote n'ahịrị bụ imepụta akụkọ mkpọchi (pg_stat_locks), nkọwa zuru ezu nke usoro nhazi tebụl.

isi: www.habr.com

Tinye a comment