ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > ืึทืŸ ืคึผืจื•ื•ื•ืŸ ืฆื• ืฉืึทืคึฟืŸ ืึทืŸ ืึทื ืึทืœืึธื’ ืคื•ืŸ ASH ืคึฟืึทืจ PostgreSQL

ืึทืŸ ืคึผืจื•ื•ื•ืŸ ืฆื• ืฉืึทืคึฟืŸ ืึทืŸ ืึทื ืึทืœืึธื’ ืคื•ืŸ ASH ืคึฟืึทืจ PostgreSQL

ื•ื™ืกื–ืึธื’ื•ื ื’ ืคื•ืŸ ื“ื™ ืคึผืจืึธื‘ืœืขื

ืฆื• ืึทืคึผื˜ืึทืžื™ื™ื– PostgreSQL ืคึฟืจืื’ืŸ, ื“ื™ ืคื™ื™ื™ืงื™ื™ื˜ ืฆื• ืคื•ื ืึทื ื“ืขืจืงืœื™ื™ึทื‘ืŸ ื˜ืขื˜ื™ืงื™ื™ื˜ ื’ืขืฉื™ื›ื˜ืข, ืกืคึผืขืฆื™ืขืœ ื•ื•ื™ื™ืฅ, ืœืึทืงืก ืื•ืŸ ื˜ื™ืฉ ืกื˜ืึทื˜ื™ืกื˜ื™ืง, ืื™ื– ื–ื™ื™ืขืจ ืคืืจืœืื ื’ื˜.

ื‘ื ื™ืžืฆื ืึทืคึผืขืจื˜ื•ื ืึทื˜ื™ื–

ื”ื™ืกื˜ืืจื™ืฉืข ื•ื•ืขืจืงืœืึธื•ื“ ืึทื ืึทืœื™ืกื™ืก ื’ืขืฆื™ื™ึทื’ ืึธื“ืขืจ "AWR ืคึฟืึทืจ Postgres": ืึท ื–ื™ื™ืขืจ ื˜ืฉื™ืงืึทื•ื•ืข ืœื™ื™ื–ื•ื ื’, ืึธื‘ืขืจ ืขืก ืื™ื– ืงื™ื™ืŸ ื’ืขืฉื™ื›ื˜ืข ืคื•ืŸ โ€‹โ€‹pg_stat_activity ืื•ืŸ pg_locks.

pgsentinel ื’ืขืฉืคึผืจื™ื™ื˜ :
ยซืึทืœืข ืึทืงื™ื•ืžื™ืึทืœื™ื™ื˜ื™ื“ ืื™ื ืคึฟืึธืจืžืึทืฆื™ืข ืื™ื– ืกื˜ืึธืจื“ ื‘ืœื•ื™ื– ืื™ืŸ ื‘ืึทืจืึทืŸ, ืื•ืŸ ื“ื™ ืงืึทื ืกื•ืžื“ ืกื•ืžืข ืคื•ืŸ โ€‹โ€‹ื–ื›ึผืจื•ืŸ ืื™ื– ืจืขื’ื™ืึทืœื™ื™ื˜ืึทื“ ื“ื•ืจืš ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ืœืขืฆื˜ืข ืกื˜ืึธืจื“ ืจืขืงืึธืจื“ืก.

ื“ื™ queryid ืคืขืœื“ ืื™ื– ืฆื•ื’ืขื’ืขื‘ืŸ - ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ queryid ืคึฟื•ืŸ ื“ื™ pg_stat_statements ืคืึทืจืœืขื ื’ืขืจื•ื ื’ (ืคืึทืจ-ื™ื ืกื˜ืึทืœื™ืจื•ื ื’ ืคืืจืœืื ื’ื˜).ยซ

ื“ืึธืก, ืคึฟืึทืจืฉื˜ื™ื™ื˜ ื–ื™ืš, ื•ื•ืึธืœื˜ ืึท ืกืš ื”ืขืœืคึฟืŸ, ืึธื‘ืขืจ ื“ืึธืก ืžืขืจืกื˜ืข ืื™ื– ื“ืขืจ ืขืจืฉื˜ืขืจ ื ืงื•ื“ื”.ื›ืœ ืึทืงื™ื•ืžื™ืึทืœื™ื™ื˜ื™ื“ ืื™ื ืคึฟืึธืจืžืึทืฆื™ืข ืื™ื– ืกื˜ืึธืจื“ ื‘ืœื•ื™ื– ืื™ืŸ ื‘ืึทืจืึทืŸ โ€, ื“.ื”. ืขืก ืื™ื– ืึท ืคึผืจืึทืœ ืื•ื™ืฃ ื“ื™ ืฆื™ืœ ื‘ืึทื–ืข. ืื™ืŸ ืึทื“ื™ืฉืึทืŸ, ืขืก ืื™ื– ืงื™ื™ืŸ ืฉืœืึธืก ื’ืขืฉื™ื›ื˜ืข ืื•ืŸ ื˜ื™ืฉ ืกื˜ืึทื˜ื™ืกื˜ื™ืง. ื™ืขื ืข. ื“ืขืจ ืœื™ื™ื–ื•ื ื’ ืื™ื– ื‘ื›ืœืœ ื“ืขืจืขื ื“ื™ืงื˜: "ืขืก ืื™ื– ื ืึธืš ืงื™ื™ืŸ ืคืึทืจื˜ื™ืง ืคึผืขืงืœ ืคึฟืึทืจ ื™ื ืกื˜ืึทืœื™ืจื•ื ื’. ืขืก ืื™ื– ืกืึทื’ื“ื–ืฉืขืกื˜ื™ื“ ืฆื• ืึธืคึผืœืึธื“ื™ืจืŸ ื“ื™ ืžืงื•ืจื™ื ืื•ืŸ ืึทืกืขืžื‘ืึทืœ ื“ื™ ื‘ื™ื‘ืœื™ืึธื˜ืขืง ื–ื™ืš. ืื™ืจ ืขืจืฉื˜ืขืจ ื“ืึทืจืคึฟืŸ ืฆื• ื™ื ืกื˜ืึทืœื™ืจืŸ ื“ื™ "ื“ืขื•ื•ืขืœ" ืคึผืขืงืœ ืคึฟืึทืจ ื“ื™ื™ืŸ ืกืขืจื•ื•ืขืจ ืื•ืŸ ืฉื˜ืขืœืŸ ื“ืขื ื“ืจืš ืฆื• pg_config ืื™ืŸ ื“ื™ PATH ื‘ื™ื™ึทื˜ืขื•ื•ื“ื™ืง.".

ืื™ืŸ ืึทืœื’ืขืžื™ื™ืŸ, ืขืก ืื™ื– ืึท ืคึผืœืึทืฅ ืคื•ืŸ ื˜ืึทืจืขืจืึทื, ืื•ืŸ ืื™ืŸ ื“ื™ ืคืึทืœ ืคื•ืŸ ืขืจื ืกื˜ ืคึผืจืึธื“ื•ืงืฆื™ืข ื“ืึทื˜ืึทื‘ื™ื™ืกื™ื–, ืขืก ืงืขืŸ ื ื™ืฉื˜ ื–ื™ื™ืŸ ืžืขื’ืœืขืš ืฆื• ื˜ืึธืŸ ืขืคึผืขืก ืžื™ื˜ ื“ื™ ืกืขืจื•ื•ืขืจ. ืžื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ืงื•ืžืขืŸ ืึทืจื•ื™ืฃ ืžื™ื˜ ืขืคึผืขืก ืคื•ืŸ ืื•ื ื“ื–ืขืจ ืื™ื™ื’ืŸ.

Warning

ืจืขื›ื˜ ืฆื• ื“ืขืจ ื’ืึทื ืฅ ื’ืจื•ื™ืก ื‘ืึทื ื“ ืื•ืŸ ืจืขื›ื˜ ืฆื• ื“ืขืจ ื“ืขืจืขื ื“ื™ืงื˜ ื˜ืขืกื˜ื™ื ื’ ืฆื™ื™ึทื˜, ื“ืขืจ ืึทืจื˜ื™ืงืœ ืื™ื– ื“ืขืจ ื”ื•ื™ืคึผื˜ ืคื•ืŸ ืึท ื™ื ืคืึธืจืžืึทื˜ื™ื•ื• ื ืึทื˜ื•ืจ, ืืœื ื•ื•ื™ ืึท ืกื›ื•ื ืคื•ืŸ ื˜ืขื–ื™ืก ืื•ืŸ ื™ื ื˜ืขืจืžื™ื“ื™ื™ื˜ ืจืขื–ื•ืœื˜ืึทื˜ืŸ.
ืžืขืจ ื“ื™ื˜ื™ื™ืœื“ ืžืึทื˜ืขืจื™ืึทืœ ื•ื•ืขื˜ ื–ื™ื™ืŸ ืฆื•ื’ืขื’ืจื™ื™ื˜ ืฉืคึผืขื˜ืขืจ, ืื™ืŸ ื˜ื™ื™ืœืŸ

ืคึผืœืึทืŸ ืจืขืงื•ื•ื™ืจืขืžืขื ืฅ ืคึฟืึทืจ ื“ื™ ืœื™ื™ื–ื•ื ื’

ืขืก ืื™ื– ื ื™ื™ื˜ื™ืง ืฆื• ืึทื ื˜ื•ื•ื™ืงืœืขืŸ ืึท ื’ืขืฆื™ื™ึทื’ ื•ื•ืึธืก ืึทืœืึทื•ื– ืื™ืจ ืฆื• ืงืจืึธื:

pg_stat_activity ื–ืขืŸ ื’ืขืฉื™ื›ื˜ืข
ืกืขืกื™ืข ืฉืœืึธืก ื’ืขืฉื™ื›ื˜ืข ื ื™ืฆืŸ ื“ื™ pg_locks ืžื™ื™ื ื•ื ื’

ืœื™ื™ื–ื•ื ื’ ืคืึธื“ืขืจื•ื ื’- ืžื™ื ืึทืžื™ื™ื– ื“ื™ ืคึผืจืึทืœ ืื•ื™ืฃ ื“ื™ ืฆื™ืœ ื“ืึทื˜ืึทื‘ื™ื™ืก.

ืึทืœื’ืขืžื™ื™ื ืข ื’ืขื“ืึทื ืง- ื“ื™ ื“ืึทื˜ืŸ ื–ืึทืžืœื•ื ื’ ืึทื’ืขื ื˜ ืื™ื– ืœืึธื ื˜ืฉื˜ ื ื™ืฉื˜ ืื™ืŸ ื“ื™ ืฆื™ืœ ื“ืึทื˜ืึทื‘ื™ื™ืก, ืึธื‘ืขืจ ืื™ืŸ ื“ื™ ืžืึธื ื™ื˜ืึธืจื™ื ื’ ื“ืึทื˜ืึทื‘ื™ื™ืก ื•ื•ื™ ืึท ืกื™ืกื˜ืขื ื“ื™ื ืกื˜. ื™ืึธ, ืขื˜ืœืขื›ืข ื“ืึทื˜ืŸ ืึธื ื•ื•ืขืจ ืื™ื– ืžืขื’ืœืขืš, ืึธื‘ืขืจ ื“ืึธืก ืื™ื– ื ื™ืฉื˜ ืงืจื™ื˜ื™ืฉ ืคึฟืึทืจ ืจื™ืคึผืึธืจื˜ื™ื ื’, ืึธื‘ืขืจ ืขืก ืื™ื– ืงื™ื™ืŸ ืคึผืจืึทืœ ืื•ื™ืฃ ื“ื™ ืฆื™ืœ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื™ืŸ ื˜ืขืจืžื™ื ืขืŸ ืคื•ืŸ ื–ื›ึผืจื•ืŸ ืื•ืŸ ื“ื™ืกืง ืคึผืœืึทืฅ. ืื•ืŸ ืื™ืŸ ื“ืขื ืคืึทืœ ืคื•ืŸ ื ื™ืฆืŸ ืึท ืงืฉืจ ื‘ืขืงืŸ, ื“ื™ ืคึผืจืึทืœ ืื•ื™ืฃ ื‘ืึทื ื™ืฆืขืจ ืคึผืจืึทืกืขืกืึทื– ืื™ื– ืžื™ื ื™ืžืึทืœ.

ื™ืžืคึผืœืึทืžืขื ื˜ื™ื™ืฉืึทืŸ ืกื˜ืึทื’ืขืก

1.ืกืขืจื•ื•ื™ืก ื˜ื™ืฉืŸ

ื ื‘ืึทื–ื•ื ื“ืขืจ ืกื˜ืฉืขืžืึท ืื™ื– ื’ืขื ื™ืฆื˜ ืฆื• ืงืจืึธื ื˜ื™ืฉืŸ, ืึทื–ื•ื™ ื ื™ืฉื˜ ืฆื• ืงืึธืžืคึผืœื™ืฆื™ืจืŸ ื“ื™ ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ื”ื•ื™ืคึผื˜ ื˜ื™ืฉืŸ ื’ืขื ื™ืฆื˜.

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

ื•ื•ื™ื›ื˜ื™ืง: ื“ื™ ืกื˜ืฉืขืžืึท ืื™ื– ื ื™ืฉื˜ ื‘ืืฉืืคืŸ ืื™ืŸ ื“ื™ ืฆื™ืœ ื“ืึทื˜ืึทื‘ื™ื™ืก, ืึธื‘ืขืจ ืื™ืŸ ื“ื™ ืžืึธื ื™ื˜ืึธืจื™ื ื’ ื“ืึทื˜ืึทื‘ื™ื™ืก.

pg_stat_activity ื–ืขืŸ ื’ืขืฉื™ื›ื˜ืข

ื ื˜ื™ืฉ ืื™ื– ื’ืขื ื™ืฆื˜ ืฆื• ืงืจืึธื ืงืจืึทื ื˜ ืกื ืึทืคึผืฉืึทืฅ ืคื•ืŸ ื“ื™ 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
);

ืฆื• ืคืึทืจื’ื™ื›ืขืจืŸ ื“ื™ ื™ื ืกืขืจืฉืึทืŸ - ืงื™ื™ืŸ ื™ื ื“ืขืงืกื™ื– ืึธื“ืขืจ ืจื™ืกื˜ืจื™ืงืฉืึทื ื–.

ืฆื• ืงืจืึธื ื“ื™ ื’ืขืฉื™ื›ื˜ืข ื–ื™ืš, ืึท ืคึผืึทืจื˜ื™ืฉืึทื ื“ ื˜ื™ืฉ ืื™ื– ื’ืขื ื™ืฆื˜:

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

ื–ื™ื ื˜ ืื™ืŸ ื“ืขื ืคืึทืœ ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ืจืขืงื•ื•ื™ืจืขืžืขื ืฅ ืคึฟืึทืจ ื™ื ืกืขืจืฉืึทืŸ ื’ื™ื›ืงื™ื™ึทื˜, ืขื˜ืœืขื›ืข ื™ื ื“ืขืงืกื™ื– ื–ืขื ืขืŸ ื‘ืืฉืืคืŸ ืฆื• ืคืึทืจื’ื™ื›ืขืจืŸ ื“ื™ ืฉืึทืคื•ื ื’ ืคื•ืŸ ืจื™ืคึผืึธืจืฅ.

ืกืขืกื™ืข ื‘ืœืึทืงื™ื ื’ ื’ืขืฉื™ื›ื˜ืข

ื ื˜ื™ืฉ ืื™ื– ื’ืขื ื™ืฆื˜ ืฆื• ืงืจืึธื ืงืจืึทื ื˜ ืกื ืึทืคึผืฉืึทืฅ ืคื•ืŸ ืกืขืกื™ืข ืœืึทืงืก:

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

ืื•ื™ืš, ืฆื• ืคืึทืจื’ื™ื›ืขืจืŸ ื“ื™ ื™ื ืกืขืจืฉืึทืŸ, ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ื™ื ื“ืขืงืกื™ื– ืึธื“ืขืจ ืจื™ืกื˜ืจื™ืงืฉืึทื ื–.

ืฆื• ืงืจืึธื ื“ื™ ื’ืขืฉื™ื›ื˜ืข ื–ื™ืš, ืึท ืคึผืึทืจื˜ื™ืฉืึทื ื“ ื˜ื™ืฉ ืื™ื– ื’ืขื ื™ืฆื˜:

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

ื–ื™ื ื˜ ืื™ืŸ ื“ืขื ืคืึทืœ ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ืจืขืงื•ื•ื™ืจืขืžืขื ืฅ ืคึฟืึทืจ ื™ื ืกืขืจืฉืึทืŸ ื’ื™ื›ืงื™ื™ึทื˜, ืขื˜ืœืขื›ืข ื™ื ื“ืขืงืกื™ื– ื–ืขื ืขืŸ ื‘ืืฉืืคืŸ ืฆื• ืคืึทืจื’ื™ื›ืขืจืŸ ื“ื™ ืฉืึทืคื•ื ื’ ืคื•ืŸ ืจื™ืคึผืึธืจืฅ.

2.ืคื™ืœื™ื ื’ ื“ื™ ืงืจืึทื ื˜ ื’ืขืฉื™ื›ื˜ืข

ืฆื• ื’ืœื™ื™ืš ืงืœื™ื™ึทื‘ืŸ ืžื™ื™ื ื•ื ื’ ืกื ืึทืคึผืฉืึทืฅ, ืึท ื‘ืึทืฉ ืฉืจื™ืคื˜ ืื™ื– ื’ืขื ื™ืฆื˜ ื•ื•ืึธืก ืœื•ื™ืคื˜ ื“ื™ 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 ื“ื™ ื“ื‘ืœื™ื ืง ืคึฟื•ื ืงืฆื™ืข ืึทืงืกืขืก ืงื•ืงืŸ ืื™ืŸ ื“ื™ ืฆื™ืœ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื•ืŸ ื™ื ืกืขืจืฅ ืจืึธื•ื– ืื™ืŸ ื“ื™ื ืกื˜ ื˜ื™ืฉืŸ ืื™ืŸ ื“ื™ ืžืึธื ื™ื˜ืึธืจื™ื ื’ ื“ืึทื˜ืึทื‘ื™ื™ืก.

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;

ืฆื• ื–ืึทืžืœืขืŸ ืžื™ื™ื ื•ื ื’ ืกื ืึทืคึผืฉืึทืฅ, ื“ื™ ืกื™ืกื˜ืขื ื“ื™ื ืกื˜ ืื•ืŸ ืฆื•ื•ื™ื™ ืกืงืจื™ืคึผืก ื–ืขื ืขืŸ ื’ืขื ื™ืฆื˜:

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

ื‘ืึทืฉื˜ื™ืžืขืŸ ืจืขื›ื˜ ืฆื• ืกืงืจื™ืคึผืก:
# ื˜ืฉืžืึธื“ 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

ืœืืžื™ืจ ืื ื”ื™ื™ื‘ืŸ ื“ื™ ืกืขืจื•ื•ื™ืก:
# ืกื™ืกื˜ืขืžืงื˜ืœ ื“ื™ื™ืžืึทืŸ-ืจื™ืœืึธื•ื“
# systemctl ืึธื ื”ื™ื™ื‘ pg_current_activity.service

ืื–ื•ื™, ื“ื™ ื’ืขืฉื™ื›ื˜ืข ืคื•ืŸ โ€‹โ€‹ืงื•ืงืŸ ืื™ื– ื’ืขื–ืืžืœื˜ ืื™ืŸ ื“ื™ ืคืึธืจืขื ืคื•ืŸ ืจื’ืข-ื“ื•ืจืš-ืกืขืงื•ื ื“ืข ืกื ืึทืคึผืฉืึทืฅ. ืคื•ืŸ ืงื•ืจืก, ืื•ื™ื‘ ืึทืœืฅ ืื™ื– ืœื™ื ืงืก ื•ื•ื™ ืื™ื–, ื“ื™ ื˜ื™ืฉืŸ ื•ื•ืขื˜ ื–ื™ื™ืขืจ ื’ืขืฉื•ื•ื™ื ื“ ืคืึทืจื’ืจืขืกืขืจืŸ ืื™ืŸ ื’ืจื™ื™ืก ืื•ืŸ ืžืขืจ ืึธื“ืขืจ ื•ื•ื™ื™ื ื™ืงืขืจ ืคึผืจืึธื“ื•ืงื˜ื™ื•ื• ืึทืจื‘ืขื˜ ื•ื•ืขื˜ ื•ื•ืขืจืŸ ืื•ืžืžืขื’ืœืขืš.

ืขืก ืื™ื– ื ื™ื™ื˜ื™ืง ืฆื• ืึธืจื’ืึทื ื™ื–ื™ืจืŸ ื“ืึทื˜ืŸ ืึทืจื˜ืฉื™ื•ื•ื™ื ื’.

3. ืึทืจื˜ืฉื™ื•ื•ื™ื ื’ ื’ืขืฉื™ื›ื˜ืข

ืคึฟืึทืจ ืึทืจื˜ืฉื™ื•ื•ื™ื ื’, ืคึผืึทืจื˜ื™ืฉืึทื ื“ ื˜ื™ืฉืŸ ืึทืจืงื™ื™ื•ื• * ื–ืขื ืขืŸ ื’ืขื ื™ืฆื˜.

ื ื™ื• ืคึผืึทืจื˜ื™ืฉืึทื ื– ื–ืขื ืขืŸ ื‘ืืฉืืคืŸ ื™ืขื“ืขืจ ืฉืขื”, ื‘ืฉืขืช ืึทืœื˜ ื“ืึทื˜ืŸ ื–ืขื ืขืŸ ืึทื•ื•ืขืงื’ืขื ื•ืžืขืŸ ืคื•ืŸ ื“ื™ ื’ืขืฉื™ื›ื˜ืข * ื˜ื™ืฉืŸ, ืึทื–ื•ื™ ื“ื™ ื’ืจื™ื™ืก ืคื•ืŸ ื“ื™ ื’ืขืฉื™ื›ื˜ืข * ื˜ื™ืฉืŸ ื˜ื•ื˜ ื ื™ืฉื˜ ื˜ื•ื™ืฉืŸ ืคื™ืœ ืื•ืŸ ื“ื™ ื™ื ืกืขืจืฉืึทืŸ ื’ื™ื›ืงื™ื™ึทื˜ ืื™ื– ื ื™ืฉื˜ ื“ื™ื’ืจื™ื™ื“ ืื™ื‘ืขืจ ืฆื™ื™ึทื˜.

ื“ื™ ืฉืึทืคื•ื ื’ ืคื•ืŸ ื ื™ื™ึทืข ืกืขืงืฉืึทื ื– ืื™ื– ื“ื•ืจื›ื’ืขืงืึธื›ื˜ ื“ื•ืจืš ื“ื™ plpgsql ืคื•ื ืงืฆื™ืข activity_hist.archive_current_activity. ื“ืขืจ ืึทืœื’ืขืจื™ื“ืึทื ืคื•ืŸ ืึทืจื‘ืขื˜ ืื™ื– ื–ื™ื™ืขืจ ืคึผืฉื•ื˜ (ื ื™ืฆืŸ ื“ืขื ื‘ื™ื™ึทืฉืคึผื™ืœ ืคื•ืŸ ื“ื™ ืึธืคึผื˜ื™ื™ืœื•ื ื’ ืคึฟืึทืจ ื“ื™ archive_pg_stat_activity ื˜ื™ืฉ).

ืฉืึทืคึฟืŸ ืื•ืŸ ืคึผืœืึธืžื‘ื™ืจืŸ ืึท ื ื™ื™ึทืข ืึธืคึผื˜ื™ื™ืœื•ื ื’

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

ืฉืืคืŸ ื™ื ื“ืขืงืกื™ื–

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

ืจื™ืžื•ื•ื•ื™ื ื’ ืึทืœื˜ ื“ืึทื˜ืŸ ืคื•ืŸ ื“ื™ History_pg_stat_activity ื˜ื™ืฉ

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

ืคึฟืึทืจืฉื˜ื™ื™ื˜ ื–ื™ืš, ืึทื– ืคึฟื•ืŸ ืฆืฒึทื˜ ืฆื• ืฆืฒึทื˜ ื•ื•ืขืจืŸ ืึทืœื˜ืข ืกืขืงืฆื™ืขืก ืื•ื™ืกื’ืขืžืขืงื˜ ื•ื•ื™ ื ื™ื˜ ื ื™ื™ื˜ื™ืง.

ื™ืงืขืจื“ื™ืง ืจื™ืคึผืึธืจืฅ

ืึทืงื˜ื•ืึทืœืœื™, ื•ื•ืึธืก ืื™ื– ืึทืœืข ื“ืขื ื’ืขื˜ืืŸ? ืฆื• ื‘ืึทืงื•ืžืขืŸ ืจื™ืคึผืึธืจืฅ ื–ื™ื™ืขืจ ื•ื•ื™ื™ื’ืœื™ ืจืขืžืึทื ื™ืกืึทื ื˜ ืคื•ืŸ ืึธืจืึทืงืœืข ืก AWR.

ืขืก ืื™ื– ื•ื•ื™ื›ื˜ื™ืง ืฆื• ืœื™ื™ื’ืŸ ืึทื– ืื™ืŸ ืกื“ืจ ืฆื• ื‘ืึทืงื•ืžืขืŸ ืจื™ืคึผืึธืจืฅ, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื‘ื•ื™ืขืŸ ืึท ืงืฉืจ ืฆื•ื•ื™ืฉืŸ ื“ื™ ืงื•ืงืŸ ืคื•ืŸ pg_stat_activity ืื•ืŸ pg_stat_statements. ื“ื™ ื˜ื™ืฉืŸ ื–ืขื ืขืŸ ืœื™ื ื’ืงื˜ ื“ื•ืจืš ืึทื“ื™ื ื’ ืึท 'ืงื•ื•ืขืจื™ื™ื“' ื–ื™ื™ึทืœ ืฆื• ื“ื™ 'history_pg_stat_activity', 'archive_pg_stat_activity' ื˜ื™ืฉืŸ. ื“ืขืจ ืื•ืคึฟืŸ ืคื•ืŸ ืึทื“ื™ื ื’ ืึท ื–ื™ื™ึทืœ ื•ื•ืขืจื˜ ืื™ื– ื•ื•ื™ื™ึทื˜ืขืจ ืคื•ืŸ ื“ืขื ืคืึทืจื ืขื ืคื•ืŸ ื“ืขื ืึทืจื˜ื™ืงืœ ืื•ืŸ ืื™ื– ื“ื™ืกืงืจื™ื™ื‘ื“ ื“ืึธ - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

ื’ืึทื ืฅ ืงืคึผื• ืฆื™ื™ื˜ ืคึฟืึทืจ ืคึฟืจืื’ืŸ

ืคืืจืœืื ื’ :

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

ื‘ื™ื™ึทืฉืคึผื™ืœ:

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

ื’ืึทื ืฅ ื•ื•ืืจื˜ืŸ ืฆื™ื™ื˜ ืคึฟืึทืจ ืคึฟืจืื’ืŸ

ืคืืจืœืื ื’ :

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

ื ื‘ื™ื™ืฉืคื™ืœ:

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

ื•ื•ืืจื˜ืŸ ืคึฟืึทืจ ืคึฟืจืื’ืŸ

ื‘ืขื˜ืŸ:

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

ื‘ื™ื™ึทืฉืคึผื™ืœ:

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

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

ืคืืจืฉืคืืจื˜ ืคึผืจืึทืกืขืกืึทื– ื’ืขืฉื™ื›ื˜ืข

ื‘ืขื˜ืŸ:

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

ื‘ื™ื™ึทืฉืคึผื™ืœ:

------------------------------------------ ------------------------------------------ ---------------------------------- | ืคืืจืฉืคืืจื˜ ืคึผืจืึทืกืขืกืึทื– ื’ืขืฉื™ื›ื˜ืข +--------------------------------------- --------------------------------------------- +------------------------ | #| ืคึผื™ื“| ืกื˜ืึทืจื˜ืขื“| ื’ืขื“ื•ื™ืขืจ| blocking_pids| ื‘ืึทืฆื™ื•ื ื’| ืžืึธื“ืข| ืœืึธืงืงื˜ื™ืคึผืข +------------------------------------ -------------------------------------------+ -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| ื‘ืึทืฆื™ื•ื ื’ | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| ื‘ืึทืฆื™ื•ื ื’ | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| ื‘ืึทืฆื™ื•ื ื’ | | | | | 28376| |

ื‘ืœืึทืงื™ื ื’ ืคึผืจืึทืกืขืกืึทื– ื’ืขืฉื™ื›ื˜ืข

ื‘ืขื˜ืŸ:

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

ื‘ื™ื™ึทืฉืคึผื™ืœ:

------------------------------------------ ------------------------------------------ ------------------------------------------ ----------------------- ื‘ืœืึทืงื™ื ื’ ืคึผืจืึทืกืขืกืึทื– ื’ืขืฉื™ื›ื˜ืข +----+------------ ---+--------------------+-------------- ------+--------------------+------- -------+----------------------------------------------- | #| ืคึผื™ื“| ื ืืžืขืŸ| ืึทืคึผืœืึทืงื™ื™ืฉืึทืŸ_ื ืึธืžืขืŸ| ื“ืึทื˜ื ืึทืžืข| ืกื˜ืึทืจื˜ืขื“| ื’ืขื“ื•ื™ืขืจ| ืฉื˜ืึทื˜| ืึธื ืคึฟืจืขื’ +------------------------------------------ +----------------------------------- --------------------------+-------------------------------- ------------------ | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| ืœื™ื™ื“ื™ืง| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| ืœื™ื™ื“ื™ืง ืื™ืŸ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ| ืึธื ื”ื™ื™ื‘ืŸ; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| ืœื™ื™ื“ื™ืง ืื™ืŸ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ| ืฉืœืึธืก ื˜ื™ืฉ ื•ื•ืึทืคืขืจ_ื“ืึทื˜ืึท; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| ืœื™ื™ื“ื™ืง| commit; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| ืœื™ื™ื“ื™ืง ืื™ืŸ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ| ืึธื ื”ื™ื™ื‘ืŸ; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| ืœื™ื™ื“ื™ืง ืื™ืŸ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ| ืฉืœืึธืก ื˜ื™ืฉ ื•ื•ืึทืคืขืจ_ื“ืึทื˜ืึท; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| ืœื™ื™ื“ื™ืง| commit; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| ืึทืงื˜ื™ื•ื•| ืื•ื™ืกืงืœื™ื™ึทื‘ืŸ test_del ();

ืึทื ื˜ื•ื•ื™ืงืœื•ื ื’.

ื“ื™ ื™ืงืขืจื“ื™ืง ืงื•ื•ื™ืจื™ื– ื’ืขื•ื•ื™ื–ืŸ ืื•ืŸ ื“ื™ ืจื™ื–ืึทืœื˜ื™ื ื’ ืจื™ืคึผืึธืจืฅ ืฉื•ื™ืŸ ืžืึทื›ืŸ ืœืขื‘ืŸ ืคื™ืœ ื’ืจื™ื ื’ืขืจ ื•ื•ืขืŸ ืึทื ืึทืœื™ื™ื–ื™ื ื’ ืคืึธืจืฉื˜ืขืœื•ื ื’ ื™ื ืกืึทื“ืึทื ืฅ.
ื‘ืึทื–ื™ืจื˜ ืื•ื™ืฃ ื™ืงืขืจื“ื™ืง ืคึฟืจืื’ืŸ, ืื™ืจ ืงืขื ืขืŸ ื‘ืึทืงื•ืžืขืŸ ืึท ื‘ืึทืจื™ื›ื˜ ื•ื•ืึธืก ืจื™ื–ืขืžื‘ืึทืœื– ื“ื™ AWR ืคื•ืŸ Oracle.
ืงื™ืฆืขืจ ื‘ืึทืจื™ื›ื˜ ื‘ื™ื™ึทืฉืคึผื™ืœ

+------------------------------------------------ ---------------------------------- | ืงืึธื ืกืึธืœื™ื“ืึทื˜ืขื“ ื‘ืึทืจื™ื›ื˜ ืคึฟืึทืจ ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜ ืื•ืŸ ื•ื•ืืจื˜ืŸ.

ืฆื• ื–ื™ื™ืŸ ื’ืขืฆื•ื™ื’ืŸ. ื•ื•ื™ื™ึทื˜ืขืจ ืื™ืŸ ืฉื•ืจื” ืื™ื– ื“ื™ ืฉืึทืคื•ื ื’ ืคื•ืŸ ืึท ืฉืœืึธืก ื’ืขืฉื™ื›ื˜ืข (pg_stat_locks), ืึท ืžืขืจ ื“ื™ื˜ื™ื™ืœื“ ื‘ืึทืฉืจื™ื™ึทื‘ื•ื ื’ ืคื•ืŸ ื“ืขื ืคึผืจืึธืฆืขืก ืคื•ืŸ ืคื™ืœื•ื ื’ ื˜ื™ืฉืŸ.

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’