如何生成和阅读EnterpriseDB (PPAS)诊断报告-阿里云开发者社区

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

如何生成和阅读EnterpriseDB (PPAS)诊断报告

简介: PPAS是基于PostgreSQL的高度兼容Oracle的商业产品。 不仅语法和Oracle兼容,功能也和Oracle很类似。 例如它也支持生成类似statspack或者AWR报告。 如何创建快照 配置参数timed_statistics=true或者在客户端会话中设置timed

PPAS是基于PostgreSQL的高度兼容Oracle的商业产品。

不仅语法和Oracle兼容,功能也和Oracle很类似。

例如它也支持生成类似statspack或者AWR报告。

写在前面

rds用户请使用rds_snap, rds_get_snaps, rds_report, rds_truncsnap代替本文的函数。

PPAS AWR不是全局的

注意,PPAS的AWR不是全局的报告,所以需要分析哪个库,就到哪个库去打快照。

应该分析哪个库

首先我们需要创建pg_stat_statements扩展包,从中了解total_time最多的库,去对应的数据库分析。

edb=# select datname,sum(total_time) from pg_stat_statements t1, pg_database t2 where t1.dbid=t2.oid group by 1 order by 2 desc;
    datname    |       sum        
---------------+------------------
 a | 70971703.7060002
 b |       951986.866
 c |         1437.653
 d |             49.9
 e |             1.06
(5 rows)

显然a库的耗时最多,所以我们应该去分析a库。

连接到a库,继续往下看。

如何创建快照

配置参数timed_statistics=true或者在客户端会话中设置timed_statistics=true.
然后创建一个快照

edb=# SELECT * FROM edbsnap();
       edbsnap        
----------------------
 Statement processed.
(1 row)

你可以周期性的创建快照。

例如在ECS上创建一个crontab, 每一个小时打一次快照,并删除7天前的快照。

从而在出现问题时,可以指定时间段生成AWR报告进行分析。

查看已创建的快照

edb=# SELECT * FROM get_snaps();
          get_snaps           
------------------------------
 1  11-FEB-10 10:41:05.668852
 2  11-FEB-10 10:42:27.26154
 3  11-FEB-10 10:45:48.999992
 4  11-FEB-10 11:01:58.345163
 5  11-FEB-10 11:05:14.092683
 6  11-FEB-10 11:06:33.151002
 7  11-FEB-10 11:11:16.405664
 8  11-FEB-10 11:13:29.458405
 9  11-FEB-10 11:23:57.595916
 10 11-FEB-10 11:29:02.214014
 11 11-FEB-10 11:31:44.244038
(11 rows)

如何生成诊断报告

然后选择两个快照,产生这两个快照时间点之间的数据库运行诊断报告。

SELECT * FROM edbreport(beginning_id, ending_id);

返回指定快照范围内,TOP N的系统等待信息

sys_rpt(beginning_id, ending_id, top_n)

edb=# SELECT * FROM sys_rpt(9, 10, 10);
                                   sys_rpt                                   
-----------------------------------------------------------------------------
 WAIT NAME                                COUNT      WAIT TIME       % WAIT
 ---------------------------------------------------------------------------
 wal write                                21250      104.723772      36.31
 db file read                             121407     72.143274       25.01
 wal flush                                84185      51.652495       17.91
 wal file sync                            712        29.482206       10.22
 infinitecache write                      84178      15.814444       5.48
 db file write                            84177      14.447718       5.01
 infinitecache read                       672        0.098691        0.03
 db file extend                           190        0.040386        0.01
 query plan                               52         0.024400        0.01
 wal insert lock acquire                  4          0.000837        0.00
(12 rows)

返回指定快照范围内,TOP N的会话等待信息

sess_rpt(beginning_id, ending_id, top_n)

SELECT * FROM sess_rpt(18, 19, 10);
 
                              sess_rpt                                       
-----------------------------------------------------------------------------
ID    USER       WAIT NAME              COUNT TIME(ms)   %WAIT SES  %WAIT ALL
 ----------------------------------------------------------------------------
 
 17373 enterprise db file read           30   0.175713   85.24      85.24
 17373 enterprise query plan             18   0.014930   7.24       7.24
 17373 enterprise wal flush              6    0.004067   1.97       1.97
 17373 enterprise wal write              1    0.004063   1.97       1.97
 17373 enterprise wal file sync          1    0.003664   1.78       1.78
 17373 enterprise infinitecache read     38   0.003076   1.49       1.49
 17373 enterprise infinitecache write    5    0.000548   0.27       0.27
 17373 enterprise db file extend         190  0.04.386   0.03       0.03
 17373 enterprise db file write          5    0.000082   0.04       0.04
 (11 rows)

返回指定PID的诊断信息

sessid_rpt(beginning_id, ending_id, backend_id)

SELECT * FROM sessid_rpt(18, 19, 17373);
 
                                sessid_rpt                                 
-----------------------------------------------------------------------------
 ID    USER       WAIT NAME             COUNT TIME(ms)  %WAIT SES   %WAIT ALL
 ----------------------------------------------------------------------------
 17373 enterprise db file read           30   0.175713  85.24       85.24
 17373 enterprise query plan             18   0.014930  7.24        7.24
 17373 enterprise wal flush              6    0.004067  1.97        1.97
 17373 enterprise wal write              1    0.004063  1.97        1.97
 17373 enterprise wal file sync          1    0.003664  1.78        1.78
 17373 enterprise infinitecache read     38   0.003076  1.49        1.49
 17373 enterprise infinitecache write    5    0.000548  0.27        0.27
 17373 enterprise db file extend         190  0.040386  0.03        0.03
 17373 enterprise db file write          5    0.000082  0.04        0.04
(11 rows)

返回指定PID的等待信息

sesshist_rpt(snapshot_id, session_id)

edb=# SELECT * FROM sesshist_rpt (9, 5531);
                              sesshist_rpt                                  
----------------------------------------------------------------------------
 ID    USER       SEQ  WAIT NAME                
   ELAPSED(ms)   File  Name                 # of Blk   Sum of Blks 
 ----------------------------------------------------------------------------
 5531 enterprise 1     db file read 
   18546        14309  session_waits_pk     1          1           
 5531 enterprise 2     infinitecache read       
   125          14309  session_waits_pk     1          1           
 5531 enterprise 3     db file read             
   376          14304  edb$session_waits    0          1           
 5531 enterprise 4     infinitecache read       
   166          14304  edb$session_waits    0          1           
 5531 enterprise 5     db file read             
   7978         1260   pg_authid            0          1           
 5531 enterprise 6     infinitecache read       
   154          1260   pg_authid            0          1           
 5531 enterprise 7     db file read             
   628          14302  system_waits_pk      1          1           
 5531 enterprise 8     infinitecache read       
   463          14302  system_waits_pk      1          1           
 5531 enterprise 9     db file read             
   3446         14297  edb$system_waits     0          1           
 5531 enterprise 10    infinitecache read       
   187          14297  edb$system_waits     0          1           
 5531 enterprise 11    db file read             
   14750        14295  snap_pk              1          1           
 5531 enterprise 12    infinitecache read       
   416          14295  snap_pk              1          1           
 5531 enterprise 13    db file read             
   7139         14290  edb$snap             0          1           
 5531 enterprise 14    infinitecache read       
   158          14290  edb$snap             0          1           
 5531 enterprise 15    db file read             
   27287        14288  snapshot_num_seq     0          1           
 5531 enterprise 16    infinitecache read       
(17 rows)

清除指定范围内的快照

purgesnap(beginning_id, ending_id)

SELECT * FROM purgesnap(6, 9);
 
             purgesnap              
------------------------------------
 Snapshots in range 6 to 9 deleted.
(1 row)

edb=# SELECT * FROM get_snaps();
          get_snaps           
------------------------------
 1  11-FEB-10 10:41:05.668852
 2  11-FEB-10 10:42:27.26154
 3  11-FEB-10 10:45:48.999992
 4  11-FEB-10 11:01:58.345163
 5  11-FEB-10 11:05:14.092683
 10 11-FEB-10 11:29:02.214014
 11 11-FEB-10 11:31:44.244038
(7 rows)

清除所有快照

truncsnap()

SELECT * FROM truncsnap();
 
      truncsnap       
----------------------
 Snapshots truncated.
(1 row)

SELECT * FROM get_snaps();
 get_snaps 
-----------
(0 rows)

生成AWR报告

全面的系统报告

edbreport(beginning_id, ending_id)

数据库报告

stat_db_rpt(beginning_id, ending_id)

指定范围的表级报告

stat_tables_rpt(beginning_id, ending_id, top_n, scope)
scope=ALL, USER, SYS

指定范围的表级IO报告

statio_tables_rpt(beginning_id, ending_id, top_n, scope)

指定范围的索引级报告

stat_indexes_rpt(beginning_id, ending_id, top_n, scope)

指定范围的索引级IO报告

statio_indexes_rpt(beginning_id, ending_id, top_n, scope)

范围

scope determines which tables the function returns statistics about. Specify SYS, USER or ALL:

SYS indicates that the function should return information about system defined tables. 
A table is considered a system table if it is stored in one of the following schemas: 
  pg_catalog, information_schema, sys, or dbo.

USER indicates that the function should return information about user-defined tables.

ALL specifies that the function should return information about all tables.

rds ppas用户注意

rds ppas用户是普通用,如果要使用以上的函数,需要在前面加rds_前缀,如下方法可以查看有哪些rds函数。

找到对应的rds函数就可以执行了。

postgres=# \df rds*
                                                                                                                                                                                                                                             
                                List of functions
 Schema |           Name           |     Result data type     |                                                                                                                                                                              
                                                          Argument data types                                                                                                                                                                
                                                                        |  Type  
--------+--------------------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+--------
 sys    | rds_add_policy           | void                     | object_schema text DEFAULT NULL::text, object_name text, policy_name text, function_schema text DEFAULT NULL::text, policy_function text, statement_types text DEFAULT 'inser
t,update,delete,select'::text, update_check boolean DEFAULT false, enable boolean DEFAULT true, static_policy boolean DEFAULT false, policy_type integer DEFAULT NULL::integer, long_predicate boolean DEFAULT false, sec_relevant_cols text 
DEFAULT NULL::text, sec_relevant_cols_opt integer DEFAULT NULL::integer | normal
 sys    | rds_drop_policy          | void                     | object_schema text DEFAULT NULL::text, object_name text, policy_name text                                                                                                    
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_enable_policy        | void                     | object_schema text DEFAULT NULL::text, object_name text, policy_name text, enable boolean                                                                                    
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_get_snaps            | SETOF text               |                                                                                                                                                                              
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_manage_extension     | boolean                  | operation text, pname text, schema text DEFAULT NULL::text, logging boolean DEFAULT false                                                                                    
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_pg_cancel_backend    | boolean                  | upid integer                                                                                                                                                                 
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_pg_stat_activity     | SETOF pg_stat_activity   |                                                                                                                                                                              
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_pg_stat_statements   | SETOF pg_stat_statements |                                                                                                                                                                              
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_pg_terminate_backend | boolean                  | upid integer                                                                                                                                                                 
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_report               | SETOF text               | beginsnap bigint, endsnap bigint                                                                                                                                             
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_snap                 | text                     |                                                                                                                                                                              
                                                                                                                                                                                                                                             
                                                                        | normal
 sys    | rds_truncsnap            | text                     |                                                                                                                                                                              
                                                                                                                                                                                                                                             
                                                                        | normal
(12 rows)

参考

https://www.enterprisedb.com/docs/en/9.5/eeguide/toc.html
https://www.enterprisedb.com/docs/en/9.5/eegui
de/Postgres_Plus_Enterprise_Edition_Guide.1.141.html

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

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

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

官方博客
链接