主持人 > 博客 > 管理 > 嘗試為 PostgreSQL 建立 ASH 類似物

嘗試為 PostgreSQL 建立 ASH 類似物

制定問題

為了優化 PostgreSQL 查詢，非常需要分析活動歷史記錄的能力，特別是等待、鎖定和表格統計資料。

可用機會

歷史工作負載分析工具或“AWR for Postgres”：一個非常有趣的解決方案，但沒有 pg_stat_activity 和 pg_locks 的歷史記錄。

pgsentinel 擴展 :
«所有累積的資訊僅儲存在RAM中，並且消耗的記憶體量由最後儲存的記錄的數量來調節。

新增了 queryid 欄位 - 與 pg_stat_statements 擴充功能相同的 queryid（需要預先安裝）。«

這當然會有很大幫助，但最麻煩的還是第一點。”所有累積的資訊僅儲存在 RAM 中 「， IE。 對目標基地有影響。 此外，沒有鎖歷史記錄和表格統計資料。 那些。 一般來說，解決方案是不完整的：“目前還沒有現成的安裝包。 建議下載原始程式碼並自行組裝庫。 您首先需要為您的伺服器安裝「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
);

此外，為了加快插入速度，沒有索引或限制。

為了儲存歷史記錄本身，使用分區表：

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 函數的 bash 腳本。

取得當前活動.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 函數存取目標資料庫中的視圖，並將行插入監控資料庫中的服務表中。

取得當前活動.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守護進程重新加載
# systemctl 啟動 pg_current_activity.service

因此，視圖的歷史記錄以每秒快照的形式收集。 當然，如果一切保持原樣，桌子的尺寸將很快增加，或多或少的生產性工作將變得不可能。

有必要對資料進行整理歸檔。

3.歷史歸檔

對於歸檔，使用分區表 archive*。

每小時都會建立新分割區，同時從history* 表中刪除舊數據，因此history* 資料表的大小不會發生太大變化，並且插入速度不會隨著時間的推移而降低。

新節的建立由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 視圖之間建立連線。 透過將「queryid」欄位新增至「history_pg_stat_activity」、「archive_pg_stat_activity」表來連結這些表。 添加列值的方法超出了本文的範圍，如下所述 - 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| 開始| 持續時間| 阻塞PID| 關係| 模式| 鎖型+---------+----------+-----+------------ +------- -- -----------+------------+-------- -------------+- ---- -------------- | 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();

發展。

顯示的基本查詢和產生的報告已經使分析效能事件時變得更加容易。
基於基本查詢，您可以獲得與 Oracle 的 AWR 大致相似的報表。
總結報告範例

+------------------------------------------------ - ---------------------------------- | 活動和等待的綜合報告。

待續。 接下來是建立鎖定歷史記錄（pg_stat_locks），這是填充表過程的更詳細描述。

來源： www.habr.com

添加評論