ProHoster > Blog > fitantanan-draharaha > Andrana hamorona analogue ny ASH ho an'ny PostgreSQL

Andrana hamorona analogue ny ASH ho an'ny PostgreSQL

Fanambarana olana

Mba hanamafisana ny fangatahana PostgreSQL dia ilaina ny fahaizana mamakafaka ny tantaran'ny hetsika, indrindra ny fiandrasana, ny hidin-trano ary ny antontan'isa latabatra.

fahafahana azo

Fitaovana famakafakana enta-mavesatra ara-tantara na "AWR ho an'ny Postgres": vahaolana tena mahaliana, saingy tsy misy tantaran'ny pg_stat_activity sy pg_locks.

pgsentinel extension :
Β«Ny fampahalalana voaangona rehetra dia voatahiry ao amin'ny RAM ihany, ary ny habetsaky ny fitadidiana lany dia fehezin'ny isan'ny rakitra voatahiry farany.

Nampiana ny saha queryid - ilay queryid mitovy amin'ny fanitarana pg_stat_statements (takina mialoha ny fametrahana).Β«

Mazava ho azy fa hanampy betsaka izany, fa ny tena manahirana dia ny teboka voalohany. ”Ny fampahalalana voaangona rehetra dia voatahiry ao anaty RAM ihany ”, i.e. misy fiantraikany amin'ny fototra kendrena. Ankoatr'izay, tsy misy ny tantaran'ny hidin-trano sy ny antontan'isa latabatra. Ireo. ny vahaolana amin'ny ankapobeny dia tsy feno: "Tsy mbola misy fonosana efa vita ho an'ny fametrahana. Soso-kevitra ny misintona ny loharano ary manangona ny tranomboky ny tenanao. Mila mametraka ny fonosana "devel" ho an'ny mpizara anao aloha ianao ary mametraka ny lalana mankany amin'ny pg_config ao amin'ny PATH variable.".

Amin'ny ankapobeny, be dia be ny tabataba, ary raha ny angon-drakitra famokarana matotra dia mety tsy ho azo atao ny manao na inona na inona amin'ny mpizara. Mila mamorona zavatra ho antsika manokana indray isika.

Warning.

Noho ny haben'ny habeny sy noho ny vanim-potoana fitsapana tsy feno, ny lahatsoratra dia natao indrindra ho an'ny tanjona fampahafantarana, fa ho toy ny andian-dahatsoratra sy valiny manelanelana.
Ny fitaovana amin'ny antsipiriany bebe kokoa dia homanina any aoriana, amin'ny ampahany

Drafitra takiana amin'ny vahaolana

Ilaina ny manamboatra fitaovana ahafahanao mitahiry:

pg_stat_activity mijery tantara
Tantaran'ny fanakatonana fivoriana mampiasa ny fijery pg_locks

Fitakiana vahaolana– manamaivana ny fiantraikany amin'ny angon-drakitra kendrena.

Hevitra ankapobeny– Tsy ao amin'ny angon-drakitra kendrena no atomboka ny mpandraharaha mpanangom-baovao, fa ao amin'ny angon-drakitra fanaraha-maso ho serivisy systemd. Eny, mety hisy ny fahaverezan'ny angon-drakitra sasany, saingy tsy dia zava-dehibe amin'ny tatitra izany, saingy tsy misy fiantraikany amin'ny angon-drakitra kendrena amin'ny resaka fitadidiana sy habaka kapila. Ary amin'ny fampiasana dobo fampifandraisana dia kely ny fiantraikany amin'ny fizotran'ny mpampiasa.

Dingana fampiharana

1. latabatra serivisy

Ny schema mitokana dia ampiasaina hitahirizana latabatra, mba tsy hanasarotra ny famakafakana ireo tabilao lehibe ampiasaina.

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

Zava-dehibe: Ny schema dia tsy noforonina ao amin'ny angon-drakitra kendrena, fa ao amin'ny angon-drakitra fanaraha-maso.

pg_stat_activity mijery tantara

Ny latabatra dia ampiasaina hitahiry ny sary amin'izao fotoana izao amin'ny fijery 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
);

Mba hanafainganana ny fampidirana - tsy misy fanondroana na fameperana.

Mba hitehirizana ny tantara mihitsy, dia ampiasaina ny latabatra fisarahana:

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

Koa satria amin'ity tranga ity dia tsy misy fepetra takiana amin'ny hafainganam-pandehan'ny fampidirana, misy indexes noforonina mba hanafaingana ny famoronana tatitra.

Tantara fanakanana fivoriana

Ny latabatra dia ampiasaina hitehirizana ireo sary amin'izao fotoana izao momba ny hidin-trano:

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

Ary koa, mba hanafainganana ny fampidirana dia tsy misy index na fameperana.

Mba hitehirizana ny tantara mihitsy, dia ampiasaina ny latabatra fisarahana:

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

Koa satria amin'ity tranga ity dia tsy misy fepetra takiana amin'ny hafainganam-pandehan'ny fampidirana, misy indexes noforonina mba hanafaingana ny famoronana tatitra.

2. Famenoana ny tantara ankehitriny

Mba hanangonana mivantana snapshots dia ampiasaina ny script bash izay mampandeha ny asa plpgsql.

get_current_activity.sh

#!/bin/bash
#########################################################
#get_current_activity.sh

ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S)
host=$1
s_name=$2
s_pass=$3

psql  -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE

line_count=`cat $ERROR_FILE | wc -l`
if [[ $line_count != '0' ]];
then
    rm -f /home/demon/*.err >/dev/null 2>/dev/null
	cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null  
fi
rm $ERROR_FILE >/dev/null 2>/dev/null
exit 0

plpgsql Ny fiasa dblink dia miditra amin'ny fijery ao amin'ny angon-drakitra kendrena ary mampiditra andalana ao anaty tabilao serivisy ao amin'ny angon-drakitra fanaraha-maso.

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;

Mba hanangonana snapshots dia ampiasaina ny serivisy systemd sy script roa:

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

Omeo zo amin'ny script:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Andao hanomboka ny serivisy:
# systemctl daemon-reload
# systemctl manomboka pg_current_activity.service

Noho izany, ny tantaran'ny fomba fijery dia angonina amin'ny endrika sary faharoa isaky ny segondra. Mazava ho azy, raha avela ho toy izao ny zava-drehetra, dia hitombo haingana be ny latabatra ary ho lasa tsy azo atao ny asa mamokatra bebe kokoa.

Ilaina ny mandamina ny fitahirizana angon-drakitra.

3. Fitahirizana ny tantara

Ho an'ny fitahirizana dia ampiasaina ny arsiva* latabatra voazarazara.

Fizarana vaovao no noforonina isan'ora, raha esorina amin'ny tabilao tantara* ny angona taloha, ka tsy miova firy ny haben'ny tabilao tantara* ary tsy miharatsy ny hafainganam-pandehan'ny fampidirana rehefa mandeha ny fotoana.

Ny famoronana fizarana vaovao dia ataon'ny plpgsql function activity_hist.archive_current_activity. Ny algorithm amin'ny asa dia tena tsotra (mampiasa ny ohatry ny fizarana ho an'ny latabatra archive_pg_stat_activity).

Mamorona sy mameno fizarana vaovao

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

Mamorona index

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

Esory ny angona taloha amin'ny tabilao history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Mazava ho azy, tsindraindray, ny fizarana taloha dia voafafa ho tsy ilaina.

Tatitra fototra

Raha ny marina, nahoana izany rehetra izany no atao? Mba hahazoana tatitra tsy dia mazava loatra mampahatsiahy ny Oracle's AWR.

Zava-dehibe ny manampy fa mba hahazoana tatitra dia mila manangana fifandraisana eo amin'ny pg_stat_activity sy pg_stat_statements ianao. Ny tabilao dia ampifandraisina amin'ny fampidirana tsanganana 'queryid' amin'ny tabilao 'history_pg_stat_activity', 'archive_pg_stat_activity'. Ny fomba fampidirana sandan'ny tsanganana dia mihoatra ny sahan'ity lahatsoratra ity ary voalaza eto βˆ’ pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TOTAL FOTOANA CPU HO AN'NY FANONTANIANA

fangatahana :

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

ohatra:

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

FOTOANA FIANDRASANA TOTAL AMIN'NY FANONTANIANA

fangatahana :

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

Ohatra:

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

MIANDRY FANONTANIANA

Fangatahana:

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

ohatra:

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

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

TOROHEVITRA MIHIDY

fangatahana:

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

ohatra:

--------------------------------------------------- --------------------------------------------------- --------------------------------- | TANTARAN'NY FOTOANA MIHIDY +-----+----------+-----+----------+----------- -----------------------+-------+--------------------- +------------------- | #| pid| nanomboka| faharetana| blocking_pids| fifandraisana| fomba| locktype +----------+----------+-----+--------- +--------- -----------+-----------+------- -------------+----- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| fifandraisana | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| fifandraisana | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| fifandraisana | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| fifandraisana | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| fifandraisana | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| fifandraisana | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| fifandraisana | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| fifandraisana | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| fifandraisana | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| fifandraisana | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| fifandraisana | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| fifandraisana | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| fifandraisana | | | | | 28376| |

FITSANGANANA NY TANTARA

Fangatahana:

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

ohatra:

--------------------------------------------------- --------------------------------------------------- --------------------------------------------------- ----------------------------------------------------------------------------- ---+--------------------+------------+---------------- ----------------------------------------- ----------------------+--------------------------------------------------- | #| pid| usename| anarana_fangatahana| datname| nanomboka| faharetana| fanjakana| fanontaniana +----------+----------+----------+-----+ --------- ---------------------------------------- --------------------------+---------------- ------- ----------------- | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| tsy miasa | | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| tsy miasa amin'ny transaction| manomboka; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| tsy miasa amin'ny transaction| hidy latabatra wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| tsy miasa | manolo-tena; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| tsy miasa amin'ny transaction| manomboka; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| tsy miasa amin'ny transaction| hidy latabatra wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| tsy miasa | manolo-tena; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| mavitrika| mifidiana test_del();

Fivoarana.

Ny fanontaniana fototra aseho sy ny tatitra aterak'izany dia efa manamora ny fiainana rehefa mamakafaka ny zava-nitranga.
Miorina amin'ny fanontaniana fototra, afaka mahazo tatitra mitovy amin'ny AWR an'ny Oracle ianao.
Ohatra amin'ny tatitra famintinana

+------------------------------------------------ ----------------------------------- | TATAO ATAO HO AN'NY ASA ATAO SY NY FIANDRASANA.

Mbola hitohy. Manaraka izany dia ny famoronana tantara hidin-trano (pg_stat_locks), famaritana amin'ny antsipiriany kokoa ny fizotran'ny famenoana latabatra.

Source: www.habr.com

Add a comment