문제 성명

PostgreSQL 쿼리를 최적화하려면 활동 내역, 특히 대기, 잠금, 테이블 통계를 분석하는 능력이 매우 필요합니다.

이용 가능한 기회

기록 워크로드 분석 도구 또는 "AWR for Postgres": 매우 흥미로운 해결책이지만 pg_stat_activity 및 pg_locks의 기록은 없습니다.

pgsentinel 확장 :
«축적된 모든 정보는 RAM에만 저장되며, 메모리 소비량은 마지막으로 저장된 레코드 수에 따라 규제됩니다.

queryid 필드가 추가되었습니다 - pg_stat_statements 확장의 동일한 queryid입니다(사전 설치 필요).«

물론 도움이 많이 되겠지만 가장 문제가 되는 것은 첫 번째 포인트입니다.”축적된 모든 정보는 RAM에만 저장됩니다. ", 즉. 대상 기반에 영향이 있습니다. 또한 잠금 기록 및 테이블 통계가 없습니다. 저것들. 일반적으로 솔루션은 불완전합니다.아직 설치를 위해 기성품 패키지가 없습니다. 소스를 다운로드하고 라이브러리를 직접 조립하는 것이 좋습니다. 먼저 서버에 "devel" 패키지를 설치하고 PATH 변수에 pg_config 경로를 설정해야 합니다.".

일반적으로 소란이 많으며 심각한 프로덕션 데이터베이스의 경우 서버로 아무것도 할 수 없을 수도 있습니다. 우리는 다시 우리 자신의 것을 생각해 내야합니다.

경고

분량이 많고 테스트 기간이 불완전하기 때문에 이 기사는 일련의 논문 및 중간 결과보다는 주로 정보 제공 성격을 띠고 있습니다.
더 자세한 자료는 나중에 부분적으로 준비하겠습니다.

솔루션에 대한 초안 요구 사항

다음을 저장할 수 있는 도구를 개발해야 합니다.

pg_stat_activity 보기 기록
pg_locks 뷰를 사용한 세션 잠금 기록

솔루션 요구사항– 대상 데이터베이스에 대한 영향을 최소화합니다.

일반적인 생각– 데이터 수집 에이전트는 대상 데이터베이스가 아닌 모니터링 데이터베이스에서 systemd 서비스로 시작됩니다. 예, 일부 데이터 손실이 가능하지만 이는 보고에 중요한 것은 아니지만 메모리 및 디스크 공간 측면에서 대상 데이터베이스에 영향을 미치지 않습니다. 그리고 연결 풀을 사용하는 경우 사용자 프로세스에 미치는 영향은 최소화됩니다.

구현 단계

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

또한 삽입 속도를 높이기 위해 색인이나 제한 사항이 없습니다.

기록 자체를 저장하기 위해 분할된 테이블이 사용됩니다.

활동_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 함수를 실행하는 bash 스크립트가 사용됩니다.

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 dblink 함수는 대상 데이터베이스의 뷰에 액세스하고 모니터링 데이터베이스의 서비스 테이블에 행을 삽입합니다.

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;

뷰 스냅샷을 수집하기 위해 systemd 서비스와 두 개의 스크립트가 사용됩니다.

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

스크립트에 권한 할당:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

서비스를 시작해 보겠습니다.
# systemctl daemon-reload
# systemctl start pg_current_activity.service

따라서 조회 기록은 초 단위 스냅샷 형식으로 수집됩니다. 물론 모든 것을 그대로 두면 테이블의 크기가 매우 빠르게 증가하고 어느 정도 생산적인 작업이 불가능해집니다.

데이터 보관을 구성하는 것이 필요합니다.

3. 보관이력

보관에는 분할된 테이블 archive*가 사용됩니다.

매시간 새로운 파티션이 생성되고, 오래된 데이터는 기록* 테이블에서 제거되므로 시간이 지나도 기록* 테이블의 크기가 크게 변하지 않으며 삽입 속도도 저하되지 않습니다.

새 섹션 생성은 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;

물론, 때때로 오래된 섹션은 불필요하게 삭제됩니다.

기본 보고서

사실, 이 모든 일이 왜 일어나는 걸까요? Oracle의 AWR을 매우 모호하게 연상시키는 보고서를 얻기 위해.

보고서를 받으려면 pg_stat_activity 및 pg_stat_statements 보기 사이에 연결을 구축해야 한다는 점을 추가하는 것이 중요합니다. 'history_pg_stat_activity', 'archive_pg_stat_activity' 테이블에 'queryid' 열을 추가하여 테이블을 연결합니다. 열 값을 추가하는 방법은 이 문서의 범위를 벗어나며 여기에 설명되어 있습니다. pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

쿼리에 대한 총 CPU 시간

요구 :

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

예 :

------------------------------------- ------------------------------------- --------------------------------- | 잠긴 프로세스 기록 +------+----------+------+--------- --+------------ ---------+---------+------ --------------- +------ | #| PID| 시작됨| 기간| Blocking_pids| 관계| 모드| 잠금 유형 +----------+----------+-------+---------- +--------- ---------+------------+------- ------------+---- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| 액세스공유잠금| 관계 | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| 액세스공유잠금| 관계 | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| 액세스공유잠금| 관계 | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| 액세스공유잠금| 관계 | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| 액세스공유잠금| 관계 | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| 액세스공유잠금| 관계 | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| 액세스공유잠금| 관계 | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| 액세스공유잠금| 관계 | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| 액세스공유잠금| 관계 | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| 액세스공유잠금| 관계 | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| 접근독점잠금| 관계 | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| 액세스공유잠금| 관계 | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| 접근독점잠금| 관계 | | | | | 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

예 :

------------------------------------- ------------------------------------- ------------------------------------- ---------- 차단 프로세스 기록 +------+------------+------- ---+---------+------------+------------- ----+---------+-------- - -----+--------------- | #| PID| 사용자 이름| 애플리케이션_이름| 데이터 이름| 시작됨| 기간| 상태| 쿼리 +----------+----------+----------+------+ --------- -+---------+------ ---+------ -------------+--- ------- ----------------- | 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| 거래 유휴 상태| 잠금 테이블 Wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| 유휴| 저지르다; | 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| 거래 유휴 상태| 잠금 테이블 Wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| 유휴| 저지르다; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| 활성| test_del()을 선택하세요.

개발.

표시된 기본 쿼리와 결과 보고서는 이미 성능 문제를 분석할 때 작업을 훨씬 쉽게 만들어줍니다.
기본적인 쿼리를 기반으로 오라클의 AWR과 어렴풋이 유사한 보고서를 얻을 수 있습니다.
요약 보고서 예

+------------------------------------------------- ---------------------- | 활동 및 대기에 대한 통합 보고서. 

계속됩니다. 다음 줄은 테이블 채우기 프로세스에 대한 더 자세한 설명인 잠금 기록(pg_stat_locks) 생성입니다.

출처 : habr.com