ITPub博客

首页 > 数据库 > 国内数据库 > 从Oracle到PostgreSQL:动态性能视图 vs 标准统计视图

从Oracle到PostgreSQL:动态性能视图 vs 标准统计视图

原创 国内数据库 作者:云和恩墨 时间:2019-07-15 17:52:38 0 删除 编辑


原文: https://www.enmotech.com/web/detail/1/687/1.html   


Oracle数据库的性能视图几乎可以说是最引以为骄傲的功能,在那样细粒度的采样统计强度下,依然保持卓越的性能,基于这些性能数据采样之后形成的AWR,更是Oracle DBA分析数据库性能问题的最重要手段之一。

 

那么在誉为最接近 Oracle 的开源数据库 PostgreSQL 中,如果要诊断性能问题,又有哪些视图可以使用呢?作为 Oracle DBA ,在学习 PostgreSQL 的时候,不可避免地会将 PostgreSQL Oracle 进行比较。

 

以下 SQL 命令,在 mydb=# 提示符下的均为在 PostgreSQL 中执行的,在 SQL> 提示符下的均为在 Oracle 中执行的。

 

先看一下在 PostgreSQL 中存在那些统计信息视图。 PostgreSQL 中数据字典的命名还是很规范的,所有统计信息基本上都以 pg_stat_ 开头。


mydb=# select relname from pg_class where relname like 'pg_stat_%';             relname             

----------------------------------

pg_statistic

pg_stats

pg_stat_all_tables

pg_stat_xact_all_tables

pg_stat_sys_tables

pg_stat_xact_sys_tables

pg_stat_user_tables

pg_stat_xact_user_tables

pg_statio_all_tables

pg_statio_sys_tables

pg_statio_user_tables

pg_statio_all_indexes

pg_statio_sys_indexes

pg_statio_user_indexes

pg_statio_all_sequences

pg_statio_sys_sequences

pg_statio_user_sequences

pg_stat_activity

pg_stat_replication

pg_stat_database

pg_stat_database_conflicts

pg_stat_user_functions

pg_stat_xact_user_functions

pg_stat_archiver

pg_stat_bgwriter

pg_stat_all_indexes

pg_stat_sys_indexes

pg_stat_user_indexes

pg_statistic_relid_att_inh_index

(29 rows)

pg_stat_activity

 

该视图显示了连接入一个Cluster下所有数据库的会话的统计信息,每个会话一行记录,类似于Oracle中的V$SESSION视图。

 

pg_stat_activity.query字段直接显示了该会话 正在执行的 SQL 或者上次执行的 SQL 语句文本。在 Oracle 中检查一个会话正在执行的 SQL 语句文本,则需要通过 V$SESSION V$SQL 视图 Join 才可以。

 

pg_stat_activity.pid字段直接显示了该会话在操作系统上的进程ID,这样通过top命令看到的繁忙操作系统进程,可以很简单地通过该字段定位,来作进一步的诊断。在Oracle中则需要通过V$SESSION和V$PROCESS视图Join才可以。

 

pg_stat_archiver

该视图始终只有一条记录,显示了负责一个cluster下所有数据库的重做日志(PostgreSQL中称为WAL file)归档进程的统计信息,记录项比较简单。last_archived_wal和last_archived_time分别显示了最近一次归档的文件名和最近一次归档时间。类似于Oracle中的V$ARCHIVE_DEST_STATUS。由于PostgreSQL中的归档实现实在是太简单了,所以几乎跟Oracle没有太多可比性。

 

pg_stat_bgwriter

该视图始终只有一条记录,显示了负责一个 cluster 下所有数据库的后台写进程的统计信息,也就是在操作系统中看到的 postgres: writer process 。该进程每隔 bgwriter_delay 初始化参数定义的间隔(默认 200ms )会唤醒,将 Buffer Pool 中修改过的页写入到磁盘。跟 Oracle 的后台进程 DBWR 非常相仿。

 

在Oracle中没有专门记录DBWR进程的性能视图 V$BGPROCESS视图也同样没有提供类似的信息,但是在V$SYSSTAT却记录了DBWR的统计信息,这部分跟pg_stat_bgwriter中记录的信息相仿。Oracle 11gR2中有超过600项的统计信息记录在V$SYSSTAT视图中。

 

SQL> select NAME,VALUE from v$sysstat where upper(name) like '%DBWR%';


NAME                                                                                      VALUE                                   

 -----------------------------------------------------------------------------------------  

flash cache insert skip: DBWR overloaded                                       0

DBWR checkpoint buffers written                                          1564210

DBWR thread checkpoint buffers written                                          0

DBWR tablespace checkpoint buffers written                             2852

DBWR parallel query checkpoint buffers written                               0

DBWR object drop buffers written                                                 324

DBWR transaction table writes                                                  81619

DBWR undo block writes                                                         485016

DBWR revisited being-written buffer                                                  0

DBWR lru scans                                                                                 0

DBWR checkpoints                                                                       4158

DBWR fusion writes                                                                           0

12 rows selected.

 

pg_stat_database

该视图对于每个 database 显示一行记录, PostgreSQL 中的 Cluster 类似于 Oracle 的一个 Instance ,一个 Cluster 下可以创建多个 database

 

该视图中记录了每个数据库提交了多少事务,回滚了多少事务,读了多少数据块,查询、插入、更新、删除了多少记录(在 PostgreSQL 中用 Tuple 这个奇怪的词表示跟 Row 相同的概念),产生过多少死锁。总之这是一个数据库级别相对很简单的统计信息。

 

但是,在 Oracle 中还真没有与此类似的性能视图,实际上 Oracle 没有一个视图简单地记录了一个 Schema 下面总共查询或者 DML 了多少条记录,但是却有 DBA_TAB_MODIFICATIONS 这样的视图详细记录每一张表的 DML 数量。查询了多少数据?可能 Oracle 认为这个数字是太不重要了,或者说实在是太大了,完全没必要记录。

 

对于事务级别的统计,同样可以在 Oracle V$SYSSTAT 视图中查询包含“ ROLLBACK ”和“ COMMIT ”字样的统计值,远比 PostgreSQL 中记录地要更多样。

 

pg_stat_all_tables/pg_stat_sys_tables/pg_stat_user_tables

 

PostgreSQL 的统计信息视图中, all 表示一个数据库下所有的表, sys 表示所有的系统表, user 表示所有用户创建的表,这三个配套的视图我们放在一起看。以下类似的也相同。

 

该视图对于每张表显示一条记录,显示了一张表上进行过多少全表扫描,多少索引扫描,查询、插入、更新、删除过多少记录,表中现在有多少记录,表的分析时间等。

 

Oracle 中表的分析信息存储在 DBA_TABLES 中,而对于每个表上 DML 的信息如前所述,可以从 DBA_TAB_MODIFICATIONS 视图中查询,而经历过怎样的 IO 则又可以从 V$SEGSTAT 视图中查询。好吧,实际上,在 Oracle 中根本也不关注一个表上读取过多少记录这样的数字,所以在 PostgreSQL 中但凡跟 Tuple 相关的统计值在 Oracle 中都找不到对应的记录。对于 Oracle 来说, IO 都以 Block 为单位,所以读取一条记录还是读取一个块,在 IO 消耗上没有区别。而至于对于返回记录数等的优化,则归结到 SQL 层面,那则可以通过 V$SQLSTAT 等一系列视图作更详细的分析。

 

Oracle 在视图层面从 Table 概念和 Segment 概念上做了详细的区分,看似复杂,实际清晰而且详尽,而在 PostgreSQL 中则混为一谈了,当然在 PostgreSQL 中通过后面会谈到的 pg_statio_ 系列视图又对表和索引上的 IO 统计信息进行了记录。

 

pg_stat_xact_all_tables/pg_stat_xact_sys_tables/pg_stat_xact_user_tables

 

该系列视图与上述相仿,只是增加了 xact 前缀, xact 表示 transaction ,统计的是当前会话对于表操作的信息,这部分信息通常还没有更新到 pg_stat_all_tables 视图中。

 

Oracle 中由于性能数据的抓取粒度是如此之细,所以并未区分当前会话还是已经结束的会话,要知道 V$SEGSTAT 中的信息几乎是 real-time 在更新的。所以,在 Oracle 中无需此类视图。

 

pg_stat_all_indexes/pg_stat_sys_indexes/pg_stat_user_indexes

 

该视图对于每个索引显示一条记录,显示的信息如下:

 

mydb=# select * from pg_stat_all_indexes where relname='t1';   

relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -------+------------+------------+---------+--------------+----------+--------------+---------------

24604 |      24613 | public     | t1      | t1_index     |        3 |        58960 |   47817

(1 row)

 

可见记录的信息非常简单,就是一个索引上进行过多少次扫描,通过这个索引扫描读取了多少记录,返回了多少记录。

 

Oracle 中,由于索引是 Segment 的一种,因此类似的统计信息都可以从 V$SEGSTAT 中获取

 

pg_statio_all_tables/pg_statio_sys_tables/pg_statio_user_tables

pg_statio_all_indexes/pg_statio_sys_indexes/pg_statio_user_indexes

 

这两部分放在一起描述,具有 statio 前缀的视图显示的是表或索引在数据块级别的 IO 统计信息,而 stat 前缀的视图(如前面看到的)则显示的是表或索引在记录级别的 IO 统计信息。以 pg_statio_all_indexes 为例:

 

mydb=# select * from pg_statio_all_indexes where relname='t1';

relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit -------+------------+------------+---------+--------------+---------------+--------------

24604 |      24613 | public     | t1      | t1_index     |           150 |          453

(1 row)

 

显示了读取过多少个数据块,这些读取中有多少数据块是直接命中缓存的。

 

Oracle 中是我们提到了多次的 V$SEGSTAT 视图。

 

pg_statio_all_sequences/pg_statio_sys_sequences/pg_statio_user_sequences

 

PostgreSQL对sequence上的IO独立给出了一系列视图,PostgreSQL中的sequence跟Oracle中的sequence概念基本一致,为存储序列号等的字段生成序列值。

 

该视图对于每个序列显示一条记录,显示的信息如下:

 

mydb=# select * from pg_statio_all_sequences;

 

 relid | schemaname |   relname    | blks_read | blks_hit 

-------+------------+--------------+-----------+----------

24614 | public     | users_id_seq |         1 |        3

(1 row)

 

非常简单,显示了读取过多少个数据块,多少数据块的读取是直接命中缓存的。

 

Oracle 中,由于序列是系统自身对象的一部分,因此如果要诊断跟序列相关的问题,通常要依赖等待事件,比如“ enq: SQ contention ”或者“ row cache lock ”,另外在 V$ROWCACHE 视图中存储了与序列相关的整体统计值。

 

SQL> select PARAMETER,GETS,GETMISSES from v$rowcache where PARAMETER='dc_sequences';


PARAMETER                              GETS           GETMISSES   

----------------------------------------------------

dc_sequences                                2145           54       

pg_stat_user_functions/pg_stat_xact_user_functions

 

xact 前缀和没有前缀的区别在前面描述 pg_stat_xact_all_tables 系列视图时已经提过,因此放在一起描述。

 

该视图对于每个指定要跟踪的用户自定义函数显示一条记录,这通过初始化参数 track_functions 来控制,默认不开启任何跟踪,视图结构如下:

 

mydb=# \d pg_stat_user_functions

 

 View "pg_catalog.pg_stat_user_functions"  

Column          |       Type             | Modifiers

------------------+----------------------+-----------

funcid             | oid                     |  

schemaname  | name                  | 

funcname       | name                 | 

calls               | bigint                 | 

total_time       | double precision | 

self_time        | double precision |

 

calls 字段记录了对于用户函数进行过多少次调用;

total_time字段记录了运行这个函数总共消耗了多长时间(毫秒为单位),包括调用其它函数的时间;

self_time字段记录了运行这个函数本身消耗了多长时间(毫秒为单位),不包括调用其它函数的时间。

 

Oracle 中没有类似的视图, Oracle 的关于函数或者存储过程的执行统计信息,都是详细到其中每一条 SQL 语句的,实际上如果像 PostgreSQL 这样能有一个函数或者存储过程级别的性能统计值,也是极好的

 

pg_stat_replication

在设置了复制的环境中,该视图对于每个 WAL sender 进程( WAL sender 进程负责将本机的重做日志发送到远端复制环境)显示一条记录,显示内容大致如下

 

postgres=# select pid,application_name,client_addr,state,sent_location,replay_location from pg_stat_replication; 

pid  | application_name |  client_addr   |   state   | sent_location | replay_location

-------+------------------+----------------+-----------+---------------+-----------------

27855 | walreceiver      | 192.168.56.105 | streaming | 0/50188CE8    | 0/50188CE8

(1 row)

 

每个视图中都能直接显示操作系统进程ID,实在是很方便的事情。在操作系统上可以直接查看pid=27855的进程。

 

[root@pg1-enmotech-com ~]# ps -ef|grep 2785|grep postgres

postgres 27855  1119  0 00:45 ?        00:00:00 postgres: wal sender process postgres 192.168.56.105(57046) streaming 0/50188CE8

 

从操作系统的ps命令中看到实际上已经将视图中的这些字段内容更新到了该进程描述中,在进程描述中会更新一些很有用的信息(比如server进程的状态,是等待还是空闲等),这也是PostgreSQL非常方便的一个地方。

 

Oracle 中与 PostgreSQL 的复制相类似的功能是 Physical Data Guard ,在 DG 中重做日志的传输是通过归档路径来完成的,因此类似的信息可以从 V$ARCHIVE_DEST_STATUS V$MANAGED_STANDBY 视图中获取。

 

pg_stat_database_conflicts

该视图仅对于 Standby 数据库有效,对于每个数据库显示一条记录,显示内容如下

 

postgres=# select * from pg_stat_database_conflicts;

datid |  datname  | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock 

-------+-----------+------------------+------------+----------------+-----------------+------------

             1 | template1 |                       0 |          0 |              0 |               0 |            0  

0 13051 | template0   |                       0 |          0 |              0 |               0 |            0 

0 13056 | postgres    |                       0 |          0 |              0 |               0 |            0 

0 16384 | mydb        |                       0 |          0 |              0 |               0 |            0 

0 24587 | mydb_bak    |                       0 |          0 |              0 |               0 |            0

(5 rows)

 

由于 PostgreSQL 的机制,在备库上的查询会跟一些诸如删除表空间、删除数据库、 vacuum cleanup 的操作相冲突,为了不让备库的 WAL replay 操作延时太久, PostgreSQL 内建了强制取消当前备库上运行的查询以避免跟应用重做日志这样更重要的动作相冲突的机制。而该视图则是记录由于不同原因取消掉的查询的次数。对于每个数据库显示一条记录。

 

Oracle 中不会出现这样的问题,因此也没有相应的视图

 

总结

  



当然,PostgreSQL中除了这些统计信息视图之外,还有不少类似于pg_tables,pg_users这样与Oracle中的数据字典视图相仿的视图,另外还有比如pg_locks这样用于记录锁信息的诊断视图。但是仅仅用一篇文章的长度就可以将所有的统计信息视图全部介绍完毕,PostgreSQL确实是很简洁的数据库。

 


想了解更多关于数据库、云技术的内容吗?

快来关注“数据和云”公众号、“云和恩墨”官方网站,我们期待与大家一同学习和进步!




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29715045/viewspace-2650611/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-06-19

  • 博文量
    93
  • 访问量
    79792