PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念與實現解讀 - 珍藏級
作者
digoal
日期
2019-01-25
標籤
PostgreSQL , perf insight , 等待事件 , 採樣 , 發現問題 , Oracle 兼容性
背景
通常普通的監控會包括系統資源的監控：
cpu
io
內存
網絡 等，但是僅憑資源的監控，當問題發生時，如何快速的定位到問題在哪裡？需要更高級的監控：
更高級的監控方法通常是從數據庫本身的特性觸發，但是需要對數據庫具備非常深刻的理解，才能做出好的監控和診斷系統。屬於專家型或叫做經驗型的監控和診斷系統。
[《[未完待續] PostgreSQL 一鍵診斷項 - 珍藏級》](https://github.com/digoal/blog/blob/master/201806/20180613_05.md)
《PostgreSQL 實時健康監控 大屏 - 低頻指標 - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標(服務器) - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標 - 珍藏級》
《PostgreSQL pgmetrics - 多版本、健康監控指標採集、報告》
《PostgreSQL pg_top pgcenter - 實時top類工具》
《PostgreSQL、Greenplum 日常監控 和 維護任務 - 最佳實踐》
《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL優化內容) - 珍藏級》
《PostgreSQL 鎖等待監控 珍藏級SQL - 誰堵塞了誰》
然而數據庫在不斷的演進，經驗型的診斷系統好是好，但是不通用，有沒有更加通用，有效的發現系統問題的方法？
AWS與Oracle perf insight的思路非常不錯，實際上就是等待事件的統計追蹤，作為性能診斷的方法。
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html
簡單來說就是對系統不停的打點，例如每秒一個採樣，僅記錄這一秒數據庫活躍的會話（包括等待中的會話），等待事件，QUERY，時間，用戶，數據庫。這幾個指標。
活躍度會話，不管是在耗費CPU，還是在等待（鎖，IO）或者其他，實際上都是佔用了資源的。可以算出平均的活躍會話（例如10秒的平均值，5秒的平均值）(avg active sessions)。
這個avg active sessions是一個值，這個值和數據庫實例的CPU個數進行比較，就可以衡量出系統是否存在瓶頸（當avg active sessions超過CPU個數時，說明存在瓶頸）。
當某個時間窗口存在瓶頸，瓶頸在哪裡，則可以通過這個時間窗口內的打點明細，進行統計。等待事件，QUERY，用戶，數據庫。
PostgreSQL打點的方法也很多：
1、（推薦）通過pg_stat_activity 內存中的動態視圖獲取，每秒取一次ACTIVE的內容（例如：會話ID，等待事件，QUERY，時間，用戶，數據庫）。
https://www.postgresql.org/docs/11/monitoring-stats.html#MONITORING-STATS-VIEWS
2、（不推薦）開啟審計日誌，在審計日誌中獲取，這個在高併發系統中，不太好用。並且審計日誌是在結束時打印，一個QUERY的中間執行過程並不完全是佔用CPU或其他資源的，所以審計日誌獲取的信息對於perf insight並沒有什麼效果。
perf insight的入門門檻低，可以擺平很多問題，在出現問題時快速定位到問題SQL，問題的等待事件在哪裡。結合經驗型的監控，可以構建PG非常強大的監控、診斷、優化體系。
perf insight 實現講解
舉例1
會話1
postgres=# begin;
BEGIN
postgres=# lock table abc in access exclusive mode ;
LOCK TABLE 會話2
postgres=# select * from abc; 從pg_stat_activity獲取狀態，可以看到會話2在等待，會話處於active狀態，這種消耗需要被記錄到avg active session中，用來評估資源消耗指標。
postgres=# select now(),state,datname,usename,wait_event_type,wait_event,query from pg_stat_activity where state in ('active', 'fastpath function call');
now | state | datname | usename | wait_event_type | wait_event | query
-------------------------------+--------+----------+----------+-----------------+------------+--------------------------------------------------------------------------------------------
2019-01-25 21:17:28.540264+08 | active | postgres | postgres | | | select datname,usename,query,state,wait_event_type,wait_event,now() from pg_stat_activity;
2019-01-25 21:17:28.540264+08 | active | postgres | postgres | Lock | relation | select * from abc;
(2 rows) 舉例2
使用pgbench壓測數據庫，每秒打點，後期進行可視化展示
pgbench -i -s 100 1、壓測只讀
pgbench -M prepared -n -r -P 1 -c 64 -j 64 -T 300 -S 2、查看壓測時的活躍會話狀態
postgres=#
select now()::timestamptz(0),state,
datname,usename,wait_event_type,wait_event,query
from pg_stat_activity
where state in
('active', 'fastpath function call')
and pid<>pg_backend_pid();
now | state | datname | usename | wait_event_type | wait_event | query
---------------------+--------+----------+----------+-----------------+------------+-------------------------------------------------------
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | Client | ClientRead | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-25 21:28:52 | active | postgres | postgres | | | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
(46 rows) 3、為了方便統計，可以在本地建表，用於收集pg_stat_activity的內容，在實際的生產中，可以把這個信息讀走，存到其他地方（例如專用於監控的其他數據庫）。
postgres=# create unlogged table perf_insight as
select now()::timestamptz(0) as ts,
extract(epoch from backend_start)||'.'||pid as sessid,
state,datname,usename,
wait_event_type||'_'||wait_event as waiting ,
query from
pg_stat_activity
where state in
('active', 'fastpath function call')
and pid<>pg_backend_pid();
SELECT 48 4、試著寫入當時pg_stat_activity狀態
postgres=#
insert into perf_insight
select now()::timestamptz(0),
extract(epoch from backend_start)||'.'||pid,
state,datname,
usename,wait_event_type||'_'||wait_event,
query from pg_stat_activity
where state in ('active', 'fastpath function call')
and pid<>pg_backend_pid();
INSERT 0 42 5、使用psql watch，每秒打一個點
postgres=# \watch 1 6、只讀壓測，壓測結果,130萬QPS
pgbench -M prepared -n -r -P 1 -c 64 -j 64 -T 300 -S
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 300 s
number of transactions actually processed: 390179555
latency average = 0.049 ms
latency stddev = 0.026 ms
tps = 1300555.237752 (including connections establishing)
tps = 1300584.885231 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.049 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; 7、接下來，開啟一個讀寫壓測，9.4萬TPS（yue 47萬qps）
pgbench -M prepared -n -r -P 1 -c 64 -j 64 -T 300
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 300 s
number of transactions actually processed: 28371829
latency average = 0.677 ms
latency stddev = 0.413 ms
tps = 94569.412707 (including connections establishing)
tps = 94571.934011 (excluding connections establishing)
statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.045 BEGIN;
0.108 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.069 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.091 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.139 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.068 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.153 END; 8、perf insight 可視化需要的素材
時間、狀態、會話ID、數據庫名、用戶名、等待事件、查詢
當然，我們可以再細化，例如增加會話ID字段，可以針對一個會話來進行展示和統計。
postgres=# \d perf_insight
Unlogged table "public.perf_insight"
Column | Type |
---------+--------------------------------+-
ts | timestamp(0) with time zone | 時間戳
sessid | text | 會話ID
state | text | 狀態
datname | name | 數據庫
usename | name | 用戶
waiting | text | 等待事件
query | text | SQL語句 9、查看perf insight素材內容
postgres=# select * from perf_insight limit 10;
ts | sessid | state | datname | usename | waiting | query
---------------------+------------------------+--------+----------+----------+--------------------------+----------------------------------------------------------------------
2019-01-26 09:43:28 | 1548467007.4805.32968 | active | postgres | postgres | Lock_transactionid | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
2019-01-26 09:43:28 | 1548467007.47991.32966 | active | postgres | postgres | Client_ClientRead | END;
2019-01-26 09:43:28 | 1548467007.48362.32979 | active | postgres | postgres | Lock_transactionid | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
2019-01-26 09:43:28 | 1548467007.48388.32980 | active | postgres | postgres | Lock_tuple | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
2019-01-26 09:43:28 | 1548467007.48329.32978 | active | postgres | postgres | Lock_transactionid | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
2019-01-26 09:43:28 | 1548467007.48275.32976 | active | postgres | postgres | Lock_tuple | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
2019-01-26 09:43:28 | 1548467007.48107.32970 | active | postgres | postgres | Lock_transactionid | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
2019-01-26 09:43:28 | 1548467007.48243.32975 | active | postgres | postgres | Lock_transactionid | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
2019-01-26 09:43:28 | 1548467007.48417.32981 | active | postgres | postgres | IPC_ProcArrayGroupUpdate | SELECT abalance FROM pgbench_accounts WHERE aid = $1;
2019-01-26 09:43:28 | 1548467007.48448.32982 | active | postgres | postgres | Lock_tuple | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
(10 rows) 10、查看在這段時間中，有多少種等待事件
postgres=# select distinct waiting from perf_insight ;
waiting
--------------------------
LWLock_wal_insert
LWLock_XidGenLock
Lock_extend
LWLock_ProcArrayLock
Lock_tuple
Lock_transactionid
LWLock_lock_manager
Client_ClientRead
IPC_ProcArrayGroupUpdate
LWLock_buffer_content
IPC_ClogGroupUpdate
LWLock_CLogControlLock
IO_DataFileExtend
(14 rows) perf insight 可視化,統計
採集粒度為1秒，可以對n秒的打點求平均值(分不同維度)，得到可視化圖形：
1、總avg active sessions ，用於告警。
2、其他維度，用於分析造成性能瓶頸問題的權重：
2.1、等待事件維度(NULL表示無等待，純CPU time) avg active sessions
2.2、query 維度 avg active sessions
2.3、數據庫維度 avg active sessions
2.4、用戶維度 avg active sessions
如何判斷問題：
例如，對於一個64線程的系統：
avg active sessions 在64以下時，可以認為是沒有問題的。
1 總 avg active sessions，用於告警。
5秒統計間隔。
select
coalesce(t1.ts, t2.ts) ts,
coalesce(avg_active_sessions,0) avg_active_sessions
from
(
select
to_timestamp((extract(epoch from ts))::int8/5*5) ts,
count(*)/5::float8 avg_active_sessions
from perf_insight
group by 1
) t1
full outer join
(select
generate_series(
to_timestamp((extract(epoch from min(ts)))::int8/5*5),
to_timestamp((extract(epoch from max(ts)))::int8/5*5),
interval '5 s'
) ts
from perf_insight
) t2
on (t1.ts=t2.ts);
ts | avg_active_sessions
------------------------+---------------------
2019-01-26 05:39:20+08 | 14.2
2019-01-26 05:39:25+08 | 30.4
2019-01-26 05:39:30+08 | 35.8
2019-01-26 05:39:35+08 | 41.8
2019-01-26 05:39:40+08 | 38.6
2019-01-26 05:39:45+08 | 38.2
2019-01-26 05:39:50+08 | 34.6
2019-01-26 05:39:55+08 | 35.6
2019-01-26 05:40:00+08 | 42.4
2019-01-26 05:40:05+08 | 36.8
2019-01-26 05:40:10+08 | 36.2
2019-01-26 05:40:15+08 | 39.4
2019-01-26 05:40:20+08 | 40
2019-01-26 05:40:25+08 | 35.8
2019-01-26 05:40:30+08 | 37.2
2019-01-26 05:40:35+08 | 36.4
2019-01-26 05:40:40+08 | 40.6
2019-01-26 05:40:45+08 | 39.2
2019-01-26 05:40:50+08 | 36.6
2019-01-26 05:40:55+08 | 37.4
2019-01-26 05:41:00+08 | 38
2019-01-26 05:41:05+08 | 38.6
2019-01-26 05:41:10+08 | 38.4
2019-01-26 05:41:15+08 | 40.4
2019-01-26 05:41:20+08 | 35.8
2019-01-26 05:41:25+08 | 40.6
2019-01-26 05:41:30+08 | 39.4
2019-01-26 05:41:35+08 | 37.4
2019-01-26 05:41:40+08 | 36.6
2019-01-26 05:41:45+08 | 39.6
2019-01-26 05:41:50+08 | 36.2
2019-01-26 05:41:55+08 | 37.4
2019-01-26 05:42:00+08 | 37.8
2019-01-26 05:42:05+08 | 39
2019-01-26 05:42:10+08 | 36.2
2019-01-26 05:42:15+08 | 37
2019-01-26 05:42:20+08 | 36.4
2019-01-26 05:42:25+08 | 36
2019-01-26 05:42:30+08 | 37.6
2019-01-26 05:42:35+08 | 0
2019-01-26 05:42:40+08 | 0
2019-01-26 05:42:45+08 | 0
2019-01-26 05:42:50+08 | 8.4
2019-01-26 05:42:55+08 | 40.6
2019-01-26 05:43:00+08 | 42.4
2019-01-26 05:43:05+08 | 37.4
2019-01-26 05:43:10+08 | 44.8
2019-01-26 05:43:15+08 | 36.2
2019-01-26 05:43:20+08 | 39.6
2019-01-26 05:43:25+08 | 41.4
2019-01-26 05:43:30+08 | 34.2
2019-01-26 05:43:35+08 | 41.8
2019-01-26 05:43:40+08 | 37.4
2019-01-26 05:43:45+08 | 30.2
2019-01-26 05:43:50+08 | 36.6
2019-01-26 05:43:55+08 | 36
2019-01-26 05:44:00+08 | 33.8
2019-01-26 05:44:05+08 | 37.8
2019-01-26 05:44:10+08 | 39.2
2019-01-26 05:44:15+08 | 36.6
2019-01-26 05:44:20+08 | 39.8
2019-01-26 05:44:25+08 | 35.2
2019-01-26 05:44:30+08 | 35.8
2019-01-26 05:44:35+08 | 42.8
2019-01-26 05:44:40+08 | 40.8
2019-01-26 05:44:45+08 | 39.4
2019-01-26 05:44:50+08 | 40
2019-01-26 05:44:55+08 | 40.2
2019-01-26 05:45:00+08 | 41.2
2019-01-26 05:45:05+08 | 41.6
2019-01-26 05:45:10+08 | 40.6
2019-01-26 05:45:15+08 | 33.8
2019-01-26 05:45:20+08 | 35.8
2019-01-26 05:45:25+08 | 42.2
2019-01-26 05:45:30+08 | 37.8
2019-01-26 05:45:35+08 | 37.6
2019-01-26 05:45:40+08 | 40.2
2019-01-26 05:45:45+08 | 37.4
2019-01-26 05:45:50+08 | 38.2
2019-01-26 05:45:55+08 | 39.6
2019-01-26 05:46:00+08 | 41.6
2019-01-26 05:46:05+08 | 36
2019-01-26 05:46:10+08 | 34.6
2019-01-26 05:46:15+08 | 37.8
2019-01-26 05:46:20+08 | 40.8
2019-01-26 05:46:25+08 | 42
2019-01-26 05:46:30+08 | 36.4
2019-01-26 05:46:35+08 | 44.6
2019-01-26 05:46:40+08 | 38.8
2019-01-26 05:46:45+08 | 35
2019-01-26 05:46:50+08 | 36.2
2019-01-26 05:46:55+08 | 37.2
2019-01-26 05:47:00+08 | 36
2019-01-26 05:47:05+08 | 38.2
2019-01-26 05:47:10+08 | 37.2
2019-01-26 05:47:15+08 | 42.8
2019-01-26 05:47:20+08 | 32
2019-01-26 05:47:25+08 | 41
2019-01-26 05:47:30+08 | 44
2019-01-26 05:47:35+08 | 37.4
2019-01-26 05:47:40+08 | 36.2
2019-01-26 05:47:45+08 | 39
2019-01-26 05:47:50+08 | 27.8
(103 rows) 10秒統計間隔的SQL
select
coalesce(t1.ts,t2.ts) ts,
coalesce(avg_active_sessions,0) avg_active_sessions
from
(
select
to_timestamp((extract(epoch from ts))::int8/10*10) ts,
count(*)/10::float8 avg_active_sessions
from perf_insight
group by 1
) t1
full outer join
(
select
generate_series(
to_timestamp((extract(epoch from min(ts)))::int8/10*10),
to_timestamp((extract(epoch from max(ts)))::int8/10*10),
interval '10 s'
) ts
from perf_insight
) t2
on (t1.ts=t2.ts);
ts | avg_active_sessions
------------------------+---------------------
2019-01-26 05:39:20+08 | 22.3
2019-01-26 05:39:30+08 | 38.8
2019-01-26 05:39:40+08 | 38.4
2019-01-26 05:39:50+08 | 35.1
2019-01-26 05:40:00+08 | 39.6
2019-01-26 05:40:10+08 | 37.8
2019-01-26 05:40:20+08 | 37.9
2019-01-26 05:40:30+08 | 36.8
2019-01-26 05:40:40+08 | 39.9
2019-01-26 05:40:50+08 | 37
2019-01-26 05:41:00+08 | 38.3
2019-01-26 05:41:10+08 | 39.4
2019-01-26 05:41:20+08 | 38.2
2019-01-26 05:41:30+08 | 38.4
2019-01-26 05:41:40+08 | 38.1
2019-01-26 05:41:50+08 | 36.8
2019-01-26 05:42:00+08 | 38.4
2019-01-26 05:42:10+08 | 36.6
2019-01-26 05:42:20+08 | 36.2
2019-01-26 05:42:30+08 | 18.8
2019-01-26 05:42:40+08 | 0
2019-01-26 05:42:50+08 | 24.5
2019-01-26 05:43:00+08 | 39.9
2019-01-26 05:43:10+08 | 40.5
2019-01-26 05:43:20+08 | 40.5
2019-01-26 05:43:30+08 | 38
2019-01-26 05:43:40+08 | 33.8
2019-01-26 05:43:50+08 | 36.3
2019-01-26 05:44:00+08 | 35.8
2019-01-26 05:44:10+08 | 37.9
2019-01-26 05:44:20+08 | 37.5
2019-01-26 05:44:30+08 | 39.3
2019-01-26 05:44:40+08 | 40.1
2019-01-26 05:44:50+08 | 40.1
2019-01-26 05:45:00+08 | 41.4
2019-01-26 05:45:10+08 | 37.2
2019-01-26 05:45:20+08 | 39
2019-01-26 05:45:30+08 | 37.7
2019-01-26 05:45:40+08 | 38.8
2019-01-26 05:45:50+08 | 38.9
2019-01-26 05:46:00+08 | 38.8
2019-01-26 05:46:10+08 | 36.2
2019-01-26 05:46:20+08 | 41.4
2019-01-26 05:46:30+08 | 40.5
2019-01-26 05:46:40+08 | 36.9
2019-01-26 05:46:50+08 | 36.7
2019-01-26 05:47:00+08 | 37.1
2019-01-26 05:47:10+08 | 40
2019-01-26 05:47:20+08 | 36.5
2019-01-26 05:47:30+08 | 40.7
2019-01-26 05:47:40+08 | 37.6
2019-01-26 05:47:50+08 | 13.9
(52 rows) 2 具體到一個時間段內，是什麼問題
例如2019-01-26 05:45:20+08，這個時間區間，性能問題鑽取：
1、數據庫維度的資源消耗時間佔用，判定哪個數據庫佔用的資源最多
postgres=#
select
datname,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 05:45:20+08' -- 問題時間點
group by 1
order by cnt desc;
datname | cnt
----------+-----
postgres | 39
(1 row) 2、用戶維度的資源消耗時間佔用，判定哪個用戶佔用的資源最多
postgres=#
select
usename,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 05:45:20+08' -- 問題時間點
group by 1
order by cnt desc;
usename | cnt
----------+-----
postgres | 39
(1 row) 3、等待事件維度的資源消耗時間佔用，判定問題集中在哪些等待事件上，可以針對性的優化、加資源。
postgres=#
select
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 05:45:20+08' -- 問題時間點
group by 1
order by cnt desc;
waiting | cnt
--------------------------+------
CPU_TIME | 15.3
Client_ClientRead | 10.6
IPC_ProcArrayGroupUpdate | 6.1
Lock_transactionid | 5.4
Lock_tuple | 0.5
LWLock_wal_insert | 0.3
LWLock_ProcArrayLock | 0.2
LWLock_buffer_content | 0.2
IPC_ClogGroupUpdate | 0.2
LWLock_lock_manager | 0.1
LWLock_CLogControlLock | 0.1
(11 rows) 4、SQL維度的資源消耗時間佔用，判定問題集中在哪些SQL上，可以針對性的優化。
postgres=#
select
query,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 05:45:20+08' -- 問題時間點
group by 1
order by cnt desc;
query | cnt
-------------------------------------------------------------------------------------------------------+------
END; | 11.5
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | 11.3
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | 6.8
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | 4.5
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | 2.3
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | 2.1
BEGIN; | 0.5
(7 rows) 5、單條QUERY在不同等待事件上的資源消耗時間佔用，判定問題SQL的突出等待事件，可以針對性的優化、加資源。
postgres=#
select
query,
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 05:45:20+08' -- 問題時間點
group by 1,2
order by 1,cnt desc;
query | waiting | cnt
-------------------------------------------------------------------------------------------------------+--------------------------+-----
BEGIN; | Client_ClientRead | 0.3
BEGIN; | CPU_TIME | 0.2
END; | CPU_TIME | 4.6
END; | IPC_ProcArrayGroupUpdate | 3.7
END; | Client_ClientRead | 3.1
END; | IPC_ClogGroupUpdate | 0.1
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | CPU_TIME | 1
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | Client_ClientRead | 0.6
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | IPC_ProcArrayGroupUpdate | 0.6
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | IPC_ClogGroupUpdate | 0.1
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | CPU_TIME | 1.2
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | Client_ClientRead | 0.6
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | Lock_transactionid | 0.3
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | CPU_TIME | 3.8
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | Client_ClientRead | 2.9
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | LWLock_wal_insert | 0.1
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | Lock_transactionid | 4
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | CPU_TIME | 2.5
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | Client_ClientRead | 2.1
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | IPC_ProcArrayGroupUpdate | 1.7
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | Lock_tuple | 0.5
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_buffer_content | 0.2
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_ProcArrayLock | 0.2
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_wal_insert | 0.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | CPU_TIME | 2
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | Lock_transactionid | 1.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | Client_ClientRead | 1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | IPC_ProcArrayGroupUpdate | 0.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | LWLock_CLogControlLock | 0.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | LWLock_lock_manager | 0.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | LWLock_wal_insert | 0.1
(31 rows) 6、點中單條QUERY，在不同等待事件上的資源消耗時間佔用，判定問題SQL的突出等待事件，可以針對性的優化、加資源。
通過4，發現佔用最多的是END這條SQL，那麼這條SQL的等待時間分佈如何？是什麼等待引起的？
postgres=#
select
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 05:45:20+08' -- 問題時間點
and query='END;'
group by 1
order by cnt desc;
waiting | cnt
--------------------------+-----
CPU_TIME | 4.6
IPC_ProcArrayGroupUpdate | 3.7
Client_ClientRead | 3.1
IPC_ClogGroupUpdate | 0.1
(4 rows) 3 開啟一個可以造成性能問題的壓測場景，通過perf insight直接發現問題
1、開啟640個併發，讀寫壓測，由於數據量小，併發高，直接導致了ROW LOCK衝突的問題，使用perf insight問題畢現。
pgbench -M prepared -n -r -P 1 -c 640 -j 640 -T 300 postgres=#
select
query,
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 06:38:20+08' -- 問題時間點
group by 1,2
order by 1,cnt desc;
query | waiting | cnt
-------------------------------------------------------------------------------------------------------+--------------------------+-------
BEGIN; | Lock_transactionid | 0.3
BEGIN; | Lock_tuple | 0.3
BEGIN; | LWLock_lock_manager | 0.1
END; | IPC_ProcArrayGroupUpdate | 29.5
END; | CPU_TIME | 14.1
END; | Lock_transactionid | 13
END; | Client_ClientRead | 8.4
END; | Lock_tuple | 8.1
END; | LWLock_lock_manager | 3
END; | LWLock_ProcArrayLock | 0.4
END; | LWLock_buffer_content | 0.3
END; | IPC_ClogGroupUpdate | 0.1
END; | LWLock_wal_insert | 0.1
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | IPC_ProcArrayGroupUpdate | 1.3
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | CPU_TIME | 0.4
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | Lock_transactionid | 0.3
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | Lock_tuple | 0.2
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | Client_ClientRead | 0.2
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP); | LWLock_lock_manager | 0.1
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | Lock_tuple | 0.9
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | Lock_transactionid | 0.9
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | IPC_ProcArrayGroupUpdate | 0.4
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | Client_ClientRead | 0.3
SELECT abalance FROM pgbench_accounts WHERE aid = $1; | CPU_TIME | 0.1
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | Lock_transactionid | 1.7
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | IPC_ProcArrayGroupUpdate | 1.4
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | Lock_tuple | 0.9
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | LWLock_lock_manager | 0.1
UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; | CPU_TIME | 0.1
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | Lock_transactionid | 161.5 # 突出問題在這裡
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | IPC_ProcArrayGroupUpdate | 27.2
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | Lock_tuple | 27.2
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_lock_manager | 19.6
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | CPU_TIME | 12.3
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | Client_ClientRead | 4
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_buffer_content | 3.3
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_ProcArrayLock | 0.3
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | LWLock_wal_insert | 0.1
UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; | IPC_ClogGroupUpdate | 0.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | Lock_transactionid | 178.4 # 突出問題在這裡
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | Lock_tuple | 83.7 # 突出問題在這裡
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | CPU_TIME | 5.6
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | IPC_ProcArrayGroupUpdate | 5.3
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | LWLock_lock_manager | 3.8
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | Client_ClientRead | 2
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | LWLock_ProcArrayLock | 0.1
UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2; | LWLock_buffer_content | 0.1
(47 rows) postgres=#
select
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
where
to_timestamp((extract(epoch from ts))::int8/10*10) -- 以10秒統計粒度的圖形為例
='2019-01-26 06:38:20+08' -- 問題時間點
group by 1
order by cnt desc;
waiting | cnt
--------------------------+-------
Lock_transactionid | 356.1
Lock_tuple | 121.3
IPC_ProcArrayGroupUpdate | 65.1
CPU_TIME | 32.6
LWLock_lock_manager | 26.7
Client_ClientRead | 14.9
LWLock_buffer_content | 3.7
LWLock_ProcArrayLock | 0.8
LWLock_wal_insert | 0.2
IPC_ClogGroupUpdate | 0.2
(10 rows) 其他壓測場景使用perf insight發現問題的例子
1、批量數據寫入，BLOCK extend或wal insert lock瓶頸，或pglz壓縮瓶頸。
create table test(id int, info text default repeat(md5(random()::text),1000));
vi test.sql
insert into test(id) select generate_series(1,10);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 300 postgres=#
select
to_timestamp((extract(epoch from ts))::int8/10*10) ts,
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
group by 1,2
order by 1,cnt desc;
ts | waiting | cnt
------------------------+--------------------------+------
2019-01-26 10:28:50+08 | IO_DataFileExtend | 0.1
2019-01-26 10:29:00+08 | CPU_TIME | 50
2019-01-26 10:29:00+08 | Lock_extend | 11.9 -- 擴展數據文件
2019-01-26 10:29:00+08 | Client_ClientRead | 0.3
2019-01-26 10:29:00+08 | IO_DataFileExtend | 0.2
2019-01-26 10:29:00+08 | LWLock_lock_manager | 0.1
2019-01-26 10:29:10+08 | CPU_TIME | 47.1
2019-01-26 10:29:10+08 | Lock_extend | 13.5
2019-01-26 10:29:10+08 | Client_ClientRead | 0.7
2019-01-26 10:29:10+08 | IO_DataFileExtend | 0.3
2019-01-26 10:29:10+08 | LWLock_buffer_content | 0.2
2019-01-26 10:29:10+08 | LWLock_lock_manager | 0.1
2019-01-26 10:29:20+08 | CPU_TIME | 54.5
2019-01-26 10:29:20+08 | Lock_extend | 6.7
2019-01-26 10:29:20+08 | Client_ClientRead | 0.2
2019-01-26 10:29:20+08 | IO_DataFileExtend | 0.1
2019-01-26 10:29:30+08 | CPU_TIME | 61.9 -- CPU，通過perf top來看是 pglz接口的瓶頸(pglz_compress)
2019-01-26 10:29:30+08 | Client_ClientRead | 0.2
2019-01-26 10:29:40+08 | CPU_TIME | 30.9
2019-01-26 10:29:40+08 | LWLock_wal_insert | 0.2
2019-01-26 10:29:40+08 | Client_ClientRead | 0.1
(28 rows) 所以上面這個問題，如果改成不壓縮，那麼瓶頸就會變成其他的：
alter table test alter COLUMN info set storage external;
postgres=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
id | integer | | | | plain | |
info | text | | | repeat(md5((random())::text), 1000) | external | | 瓶頸就會變成其他的：
2019-01-26 10:33:50+08 | Lock_extend | 43.2
2019-01-26 10:33:50+08 | LWLock_buffer_content | 14.8
2019-01-26 10:33:50+08 | CPU_TIME | 4.6
2019-01-26 10:33:50+08 | LWLock_lock_manager | 0.5
2019-01-26 10:33:50+08 | LWLock_wal_insert | 0.4
2019-01-26 10:33:50+08 | IO_DataFileExtend | 0.4
2019-01-26 10:33:50+08 | Client_ClientRead | 0.1
2019-01-26 10:34:00+08 | Lock_extend | 55.6
2019-01-26 10:34:00+08 | LWLock_buffer_content | 6.3
2019-01-26 10:34:00+08 | CPU_TIME | 1.2
2019-01-26 10:34:00+08 | IO_DataFileExtend | 0.8
2019-01-26 10:34:00+08 | LWLock_wal_insert | 0.1
2019-01-26 10:34:10+08 | Lock_extend | 6.3
2019-01-26 10:34:10+08 | LWLock_buffer_content | 5.8
2019-01-26 10:34:10+08 | CPU_TIME | 0.7 因此治本的方法是提供更好的壓縮接口，這也是PG 12的版本正在改進的：
[《[未完待續] PostgreSQL 開放壓縮接口 與 lz4壓縮插件》](https://github.com/digoal/blog/blob/master/201803/20180315_02.md)
[《[未完待續] PostgreSQL zstd 壓縮算法 插件》](https://github.com/digoal/blog/blob/master/201803/20180315_01.md)
2、秒殺，單條UPDATE。行鎖瓶頸。
create table t_hot (id int primary key, cnt int8);
insert into t_hot values (1,0);
vi test.sql
update t_hot set cnt=cnt+1 where id=1;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 300
postgres=#
select
to_timestamp((extract(epoch from ts))::int8/10*10) ts,
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
group by 1,2
order by 1,cnt desc;
2019-01-26 10:37:50+08 | Lock_tuple | 29.6 -- 瓶頸為行鎖衝突
2019-01-26 10:37:50+08 | LWLock_lock_manager | 11.4 -- 伴隨熱點塊
2019-01-26 10:37:50+08 | LWLock_buffer_content | 8.4
2019-01-26 10:37:50+08 | Lock_transactionid | 7.6
2019-01-26 10:37:50+08 | CPU_TIME | 6.5
2019-01-26 10:37:50+08 | Client_ClientRead | 0.2
2019-01-26 10:38:00+08 | Lock_tuple | 29.2 -- 瓶頸為行鎖衝突
2019-01-26 10:38:00+08 | LWLock_buffer_content | 15.6 -- 伴隨熱點塊
2019-01-26 10:38:00+08 | CPU_TIME | 7.9
2019-01-26 10:38:00+08 | LWLock_lock_manager | 7.2
2019-01-26 10:38:00+08 | Lock_transactionid | 3.7 秒殺的場景，優化方法
《PostgreSQL 秒殺4種方法 - 增加 批量流式加減庫存 方法》
《HTAP數據庫 PostgreSQL 場景與性能測試之 30 - (OLTP) 秒殺 - 高併發單點更新》
3、未優化SQL，全表掃描filter，CPU time瓶頸。
postgres=# create table t_bad (id int, info text);
CREATE TABLE
postgres=# insert into t_bad select generate_series(1,10000), md5(random()::Text);
INSERT 0 10000
vi test.sql
\set id random(1,10000)
select * from t_bad where id=:id;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 300 瓶頸
postgres=#
select
to_timestamp((extract(epoch from ts))::int8/10*10) ts,
coalesce(waiting, 'CPU_TIME') waiting,
count(*)/10::float8 cnt
from perf_insight
group by 1,2
order by 1,cnt desc;
2019-01-26 10:41:40+08 | CPU_TIME | 61.3
2019-01-26 10:41:40+08 | Client_ClientRead | 0.9
2019-01-26 10:41:50+08 | CPU_TIME | 61.7
2019-01-26 10:41:50+08 | Client_ClientRead | 0.1
2019-01-26 10:42:00+08 | CPU_TIME | 60.7
2019-01-26 10:42:00+08 | Client_ClientRead | 0.5 perf insight 的基準線
如果要設置一個基準線，用於報警。那麼：
1、基準線跟QPS沒什麼關係。
2、基準線跟avg active sessions有莫大關係。avg active sessions大於實例CPU核數時，說明有性能問題。
perf insight 不是萬能的
perf insight 發現當時的問題是非常迅速的。
神醫華佗說，不治已病治未病才是最高境界，perf insight實際上是發現已病，而未病是發現不了的。
未病還是需要對引擎的深刻理解和豐富的經驗積累。
例如：
1、年齡
2、FREEZE風暴
3、sequence耗盡
4、索引推薦
5、膨脹
6、安全風險
7、不合理索引
8、增長趨勢
9、碎片
10、分區建議
11、冷熱分離建議
12、TOP SQL診斷與優化
13、擴容(容量、計算資源、IO、內存...)建議
14、分片建議
15、架構優化建議
等。
除此之外，perf insight對於這類情況也是發現不了的：
1、long query （waiting (ddl, block one session)），當long query比較少，總體avg active session低於基準水位時，實際上long query的問題就無法暴露。
然而long query是有一些潛在問題的，例如可能導致膨脹。
perf insight + 經驗型監控、診斷，可以使得你的數據庫監測系統更加強壯。
其他知識點、內核需改進點
1、會話ID，使用backend的啟動時間，backend pid兩者結合，就可以作為PG數據庫的唯一session id。
有了session id，就可以基於SESSION維度進行性能診斷和可視化展示。
select extract(epoch from backend_start)||'.'||pid as sessid
from pg_stat_activity ;
sessid
------------------------
1547978042.41326.13447
1547978042.41407.13450 2、對於未使用綁定變量的SQL，要做SQL層的統計透視，就會比較悲劇了，因為只要輸入的變量不同在pg_stat_activity的query中看起來都不一樣，所以為了更好的統計展示，可能需要內核層面優化。
可以借鑑pg_stat_statements的代碼進行內核的修改，pg_stat_statements裡面是做了變量替換處理的。（即使是未使用綁定變量的語句）
contrib/pg_stat_statements/pg_stat_statements.c
如果不想改內核，或者你可以等PG發佈這個PATCH，可能12會發布。
《PostgreSQL 11 preview - 強制auto prepared statment開關(自動化plan cache)(類似Oracle cursor_sharing force)》
3、udf調用，使用pg_stat_activity打點的方法，無法獲取到當前UDF裡面調用的SQL是哪個，所以對於大量使用UDF的用戶來說，perf insight當前的方案，可能無法鑽取到UDF裡面的SQL瓶頸在哪裡。
這種情況可以考慮使用AWR，perf，或者plprofile。
《PostgreSQL 函數調試、診斷、優化 & auto_explain & plprofiler》
《PostgreSQL 源碼性能診斷(perf profiling)指南 - 珍藏級》
《PostgreSQL 代碼性能診斷之 - OProfile & Systemtap》
4、PostgreSQL 的兼容oracle商用版（阿里雲PPAS），內置AWR功能，waiting event的粒度更細，不需要人為打點，可以生成非常體系化的報告，歡迎使用。
《PostgreSQL AWR報告(for 阿里雲ApsaraDB PgSQL)》
5、如果你需要對很多PG實例實施perf insight，並且想將perf insight的打點採樣存儲到一個大的PG數據庫（例如citus）中，由於我們查詢都是按單個instance來查詢的，那麼就要注意IO放大的問題。
可以使用udf，自動切分INSTANCE的方法。另一方面由於時間字段遞增，與HEAP存儲順序線性相關，可以使用brin時間區間索引，解決ts字段btree索引大的問題。知識點如下：
《PostgreSQL 時序最佳實踐 - 證券交易系統數據庫設計 - 阿里雲RDS PostgreSQL最佳實踐》
《PostgreSQL 在鐵老大訂單系統中的schemaless設計和性能壓測》
6、如果將perf insight數據存在當前數據庫中，需要耗費多少空間呢？
正常情況下，一次打點採集到的active session記錄是很少的（通常小於CPU核數，甚至是0）。
較壞情況，例如每次打點都採集到60條記錄，每隔5秒採集一次，30天大概3000萬條記錄，每天一個分區，每天才100萬條記錄，完全可以直接保存在本地。
參考
[《[未完待續] PostgreSQL 一鍵診斷項 - 珍藏級》](https://github.com/digoal/blog/blob/master/201806/20180613_05.md)
《PostgreSQL 實時健康監控 大屏 - 低頻指標 - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標(服務器) - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標 - 珍藏級》
《PostgreSQL pgmetrics - 多版本、健康監控指標採集、報告》
《PostgreSQL pg_top pgcenter - 實時top類工具》
《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL優化內容) - 珍藏級》
《PostgreSQL、Greenplum 日常監控 和 維護任務 - 最佳實踐》
《PostgreSQL 鎖等待監控 珍藏級SQL - 誰堵塞了誰》
https://sourceforge.net/projects/pgstatsinfo/
https://github.com/cybertec-postgresql/pgwatch2
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html
https://github.com/postgrespro/pg_wait_sampling
免費領取阿里雲RDS PostgreSQL實例、ECS虛擬機
大量阿里雲PG解決方案: 任意維度實時圈人; 時序數據實時處理; 時間、空間、業務 多維數據實時透視; 獨立事件相關性分析; 海量關係實時圖式搜索; 社交業務案例; 流式數據實時處理案例; 物聯網; 全文檢索; 模糊、正則查詢案例; 圖像識別; 向量相似檢索; 數據清洗、採樣、脫敏、批處理、合併; GIS 地理信息空間數據應用; 金融業務; 異步消息應用案例; 海量數據 冷熱分離; 倒排索引案例; 海量數據OLAP處理應用;
德哥的 / digoal's PostgreSQL文章入口 - 努力做成PG資源最豐富的個人blog
