PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级

简介: PostgreSQL , perf insight , 等待事件 , 采样 , 发现问题 , Oracle 兼容性

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

《AWS performance insight》

简单来说就是对系统不停的打点,例如每秒一个采样,仅记录这一秒数据库活跃的会话(包括等待中的会话),等待事件,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 实现讲解

pic

pic

pic

pic

pic

pic

pic

举例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) 秒杀 - 高并发单点更新》

《聊一聊双十一背后的技术 - 不一样的秒杀技术, 裸秒》

《PostgreSQL 秒杀场景优化》

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

德哥的微信 / digoal's wechat

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接