PostgreSQL 中的性能视图

2016/03/29 PostgreSQL

对于 DBA 来讲,有时候需要快速定位问题,找到具体是那一条 SQL 拖慢了整个系统,具体是哪些因素导致查询速度变慢。PostgreSQL 为我们维护数据库提供了大量的视图,我在这里列举一些常用的。这些视图一般都是以 pg_stat* 开头的,我们可以通过这些视图获取数据库目前运行的状态。我在这里列举一些性能相关的视图。

最常用的视图之一是 pg_stat_activity, 这个视图展示了当前运行的服务器进程信息,包括它正在访问哪个数据库、它正在使用哪个系统进程、哪个用户正在访问这个进程、当前的查询以及查询运行的时间长度,视图的结构如下所示。

postgres=# \d pg_stat_activity
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 datid            | oid                      |
 datname          | name                     |
 pid              | integer                  |
 usesysid         | oid                      |
 usename          | name                     |
 application_name | text                     |
 client_addr      | inet                     |
 client_hostname  | text                     |
 client_port      | integer                  |
 backend_start    | timestamp with time zone |
 xact_start       | timestamp with time zone |
 query_start      | timestamp with time zone |
 state_change     | timestamp with time zone |
 waiting          | boolean                  |
 state            | text                     |
 backend_xid      | xid                      |
 backend_xmin     | xid                      |
 query            | text                     |

如果要追踪一个特定的进程,SQL 语句如下所示:

select * from pg_stat_activity where pid = 4689;

例:查询空闲连接的数目

select count(*) from pg_stat_activity where state='idle';

pg_stat_database 这个表存储了数据库静态统计数据,如连接到数据库服务器的进程数量、事务提交和回滚的数量以及块和列的统计信息,表的结构如下所示:

postgres=# \d pg_stat_database;
          View "pg_catalog.pg_stat_database"
     Column     |           Type           | Modifiers
----------------+--------------------------+-----------
 datid          | oid                      |
 datname        | name                     |
 numbackends    | integer                  |
 xact_commit    | bigint                   |
 xact_rollback  | bigint                   |
 blks_read      | bigint                   |
 blks_hit       | bigint                   |
 tup_returned   | bigint                   |
 tup_fetched    | bigint                   |
 tup_inserted   | bigint                   |
 tup_updated    | bigint                   |
 tup_deleted    | bigint                   |
 conflicts      | bigint                   |
 temp_files     | bigint                   |
 temp_bytes     | bigint                   |
 deadlocks      | bigint                   |
 blk_read_time  | double precision         |
 blk_write_time | double precision         |
 stats_reset    | timestamp with time zone |

例如,查询事务的提交与回滚的信息:

postgres=# select datid, datname, xact_commit, xact_rollback from pg_stat_database;
 datid |      datname       | xact_commit | xact_rollback
-------+--------------------+-------------+---------------
     1 | template1          |           0 |             0
 12998 | template0          |           0 |             0
 13003 | postgres           |       16989 |             1
 16385 | kjds_test_20160322 |    17104696 |         13556
 17731 | sit_camp_20160115  |       21371 |             1

pg_stat_all_tables 这个表为每个表存储了一些静态数据,其中包括顺序扫描静态数据、索引扫描静态数据以及在这个表上执行其他操作的数据。因为这个表中存储了所有现存表的数据,所以默认的 select 将会返回大量的数据。

postgres=# \d pg_stat_all_tables;
            View "pg_catalog.pg_stat_all_tables"
       Column        |           Type           | Modifiers
---------------------+--------------------------+-----------
 relid               | oid                      |
 schemaname          | name                     |
 relname             | name                     |
 seq_scan            | bigint                   |
 seq_tup_read        | bigint                   |
 idx_scan            | bigint                   |
 idx_tup_fetch       | bigint                   |
 n_tup_ins           | bigint                   |
 n_tup_upd           | bigint                   |
 n_tup_del           | bigint                   |
 n_tup_hot_upd       | bigint                   |
 n_live_tup          | bigint                   |
 n_dead_tup          | bigint                   |
 n_mod_since_analyze | bigint                   |
 last_vacuum         | timestamp with time zone |
 last_autovacuum     | timestamp with time zone |
 last_analyze        | timestamp with time zone |
 last_autoanalyze    | timestamp with time zone |
 vacuum_count        | bigint                   |
 autovacuum_count    | bigint                   |
 analyze_count       | bigint                   |
 autoanalyze_count   | bigint                   |

slect * from pg_stat_all_tables; 这条 SQL 将会返回大量的数据,如果仅仅需要查询用户表,可以查询 pg_stat_user_tables 这个视图。如果只需要查询系统表,可以查询 pg_stat_sys_tables。

关于性能视图总结如下: PostgreSQL 对象级别的统计信息视图:

pg_stat_database
pg_stat_all_tables
pg_stat_sys_tables
pg_stat_user_tables
pg_stat_all_indexes
pg_stat_sys_indexes
pg_stat_user_indexes

PostgreSQL 提供的IO情况的视图:

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

 

Search

    Table of Contents