PostgreSQL的日志和统计信息

pg_stat_activity#

pg_stat_activity系统表记录了当前数据库活跃的连接信息，可以使用户清楚当前数据库系统正在做什么，包括使用的数据库和当前哪些用户是在连接和使用中。
例如通过查看pid,query_start,state_change,state,query几列，可以获取到当前哪些进程在执行何种查询，以及查询开始的时间和当前的状态。这样在看到一条糟糕的命令时，可以及时地中断掉它的执行。pg提供了pg_cancel_backend和pg_terminal_backend两个函数来执行中断查询的操作，正如函数名字面意思那样，pg_cancel_backend会取消查询操作，但不会断开用户的连接；而pg_terminal_backend更加暴力，直接将查询和数据库的连接全部清除。
其他pg_stat_activity中记录的数据如下所示。

ubuntu=> \d pg_stat_activity
                      View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          |
 datname          | name                     |           |          |
 pid              | integer                  |           |          |
 leader_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 |           |          |
 wait_event_type  | text                     |           |          |
 wait_event       | text                     |           |          |
 state            | text                     |           |          |
 backend_xid      | xid                      |           |          |
 backend_xmin     | xid                      |           |          |
 query_id         | bigint                   |           |          |
 query            | text                     |           |          |
 backend_type     | text                     |           |          |

pg_stat_database#

pg_stat_database系统表中记录了更底层的数据库的相关信息。通过列名大体可以知道表中记录的信息类型，比如blks_对应了数据块的信息；tup_记录了元组的信息，反映了系统中是否存在有大数据量的读写操作；sessions_是会话层面的信息。

ubuntu=> \d pg_stat_database
                          View "pg_catalog.pg_stat_database"
          Column          |           Type           | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
 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                   |           |          |
 checksum_failures        | bigint                   |           |          |
 checksum_last_failure    | timestamp with time zone |           |          |
 blk_read_time            | double precision         |           |          |
 blk_write_time           | double precision         |           |          |
 session_time             | double precision         |           |          |
 active_time              | double precision         |           |          |
 idle_in_transaction_time | double precision         |           |          |
 sessions                 | bigint                   |           |          |
 sessions_abandoned       | bigint                   |           |          |
 sessions_fatal           | bigint                   |           |          |
 sessions_killed          | bigint                   |           |          |
 stats_reset              | timestamp with time zone |           |          |

其中temp_files和temp_bytes记录了临时数据的使用情况，这两列的信息非常重要，因为其反映了数据库是否不得不在磁盘上写入临时文件，这必然大大拖慢操作的效率。而临时文件的产生原因可能是多种的，比如过小的work_mem设置、低效的查询操作或是索引和其他的DDL操作等。

pg_stat_user_table和pg_statio_user_table#

在了解了整体的数据库信息后，有时不得不深入具体的表中去查看相关的信息，这就要用上这两张表了。

ubuntu=> \d pg_stat_user_tables
                      View "pg_catalog.pg_stat_user_tables"
       Column        |           Type           | Collation | Nullable | Default
---------------------+--------------------------+-----------+----------+---------
 relid               | oid                      |           |          |
 schemaname          | name                     |           |          |
 relname             | name                     |           |          |
 seq_scan            | bigint                   |           |          |
 last_seq_scan       | timestamp with time zone |           |          |
 seq_tup_read        | bigint                   |           |          |
 idx_scan            | bigint                   |           |          |
 last_idx_scan       | timestamp with time zone |           |          |
 idx_tup_fetch       | bigint                   |           |          |
 n_tup_ins           | bigint                   |           |          |
 n_tup_upd           | bigint                   |           |          |
 n_tup_del           | bigint                   |           |          |
 n_tup_hot_upd       | bigint                   |           |          |
 n_tup_newpage_upd   | bigint                   |           |          |
 n_live_tup          | bigint                   |           |          |
 n_dead_tup          | bigint                   |           |          |
 n_mod_since_analyze | bigint                   |           |          |
 n_ins_since_vacuum  | 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                   |           |          |

ubuntu=> \d pg_statio_user_tables
          View "pg_catalog.pg_statio_user_tables"
     Column      |  Type  | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 relid           | oid    |           |          |
 schemaname      | name   |           |          |
 relname         | name   |           |          |
 heap_blks_read  | bigint |           |          |
 heap_blks_hit   | bigint |           |          |
 idx_blks_read   | bigint |           |          |
 idx_blks_hit    | bigint |           |          |
 toast_blks_read | bigint |           |          |
 toast_blks_hit  | bigint |           |          |
 tidx_blks_read  | bigint |           |          |
 tidx_blks_hit   | bigint |           |          |

pg_stat_user_tables记录了表中数据的重要统计数据，pg_statio_user_tables记录了表的缓冲使用情况，包括表(heap_)，索引(idx_)和toast技术的缓冲行为。
仔细分析和比较pg_stat_user_tables中记录的统计信息，对性能优化会有很大的帮助。

pg_stat_bgwriter#

很多情况下，数据库连接并不会直接将数据写入到磁盘中，而是交给后台写入者去执行，如果要查看数据是如何被写入的，就可以通过pg_stat_bgwriter来获取相关统计信息。

ubuntu=> \d pg_stat_bgwriter
                        View "pg_catalog.pg_stat_bgwriter"
        Column         |           Type           | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
 checkpoints_timed     | bigint                   |           |          |
 checkpoints_req       | bigint                   |           |          |
 checkpoint_write_time | double precision         |           |          |
 checkpoint_sync_time  | double precision         |           |          |
 buffers_checkpoint    | bigint                   |           |          |
 buffers_clean         | bigint                   |           |          |
 maxwritten_clean      | bigint                   |           |          |
 buffers_backend       | bigint                   |           |          |
 buffers_backend_fsync | bigint                   |           |          |
 buffers_alloc         | bigint                   |           |          |
 stats_reset           | timestamp with time zone |           |          |

其他系统表#

• pg_stat_archiver：有关归档进程的重要信息，它会报告有关已经被归档的事务日志文件的数量，以及最后一个归档文件和归档时间；
• pg_stat_replication：提供从主机到从机的流进程信息；
• pg_stat_wal_receiver：提供的是复制端的信息；
• pg_stat_ssl：记录用户通过ssl加密连接的信息；
• pg_stat_xact_user_tables：只记录当前事务的相关信息；
• pg_stat_progess_vacuum：提供清理进程的相关信息；

最后要介绍的是pg_stat_statements，它对于性能优化非常重要，因为其记录了系统上查询的重要信息，有助于找出哪种类型的查询执行很慢以及多久执行一次。

ubuntu=> \d pg_stat_statements  
                       View "public.pg_stat_statements"
         Column         |       Type       | Collation | Nullable | Default
------------------------+------------------+-----------+----------+---------
 userid                 | oid              |           |          |
 dbid                   | oid              |           |          |
 toplevel               | boolean          |           |          |
 queryid                | bigint           |           |          |
 query                  | text             |           |          |
 plans                  | bigint           |           |          |
 total_plan_time        | double precision |           |          |
 min_plan_time          | double precision |           |          |
 max_plan_time          | double precision |           |          |
 mean_plan_time         | double precision |           |          |
 stddev_plan_time       | double precision |           |          |
 calls                  | bigint           |           |          |
 total_exec_time        | double precision |           |          |
 min_exec_time          | double precision |           |          |
 max_exec_time          | double precision |           |          |
 mean_exec_time         | double precision |           |          |
 stddev_exec_time       | double precision |           |          |
 rows                   | bigint           |           |          |
 shared_blks_hit        | bigint           |           |          |
 shared_blks_read       | bigint           |           |          |
 shared_blks_dirtied    | bigint           |           |          |
 shared_blks_written    | bigint           |           |          |
 local_blks_hit         | bigint           |           |          |
 local_blks_read        | bigint           |           |          |
 local_blks_dirtied     | bigint           |           |          |
 local_blks_written     | bigint           |           |          |
 local_blks_hit         | bigint           |           |          |
 local_blks_read        | bigint           |           |          |
 local_blks_dirtied     | bigint           |           |          |
 local_blks_written     | bigint           |           |          |
 temp_blks_read         | bigint           |           |          |
 temp_blks_written      | bigint           |           |          |
 blk_read_time          | double precision |           |          |
 blk_write_time         | double precision |           |          |
 temp_blk_read_time     | double precision |           |          |
 temp_blk_write_time    | double precision |           |          |
 wal_records            | bigint           |           |          |
 wal_fpi                | bigint           |           |          |
 wal_bytes              | numeric          |           |          |
 jit_functions          | bigint           |           |          |
 jit_generation_time    | double precision |           |          |
 jit_inlining_count     | bigint           |           |          |
 jit_inlining_time      | double precision |           |          |
 jit_optimization_count | bigint           |           |          |
 jit_optimization_time  | double precision |           |          |
 jit_emission_count     | bigint           |           |          |
 jit_emission_time      | double precision |           |          |

pg的默认配置中没有添加这个模块，要启用的话需要在配置文件postgresql.conf中的shared_preload_libraries内加上pg_stat_statements，然后重启服务器，在要添加的数据库中执行CREATE EXTENSION pg_stat_statements。

设置日志#

pg的日志设置都可以在postgresql.conf配置文件中找到，包括log日志输出到哪里，或者日志文件的大小和旋转周期等等，都能找到详细的配置项。