PG统计信息和系统表

2024-10-21 06:13:57

一、PG统计信息概述

pg的统计信息主要分为两种：

第一类统计信息是是负载指标“统计信息”（Monitoring stats），通过stat collector进程进行实时采集更新的负载指标，记录一些对磁盘块、表、索引相关的统计信息，SQL语句执行代价信息等。

第二类统计信息是数据分布状态描述“统计信息”（Data distribution stats），这些统计信息为优化器选择最优执行计划提供依据。该类统计信息采集的方式有两种：

后台进程autovacuum lancher触发的统计信息采集

autovacuum : 历史无效数据、冻结事务、xid信息的清理都是由该进程处理。

vacuum : 标准形式的 vacuum 可以和生产数据库操作并行运行（select、insert、update、delete等命令将继续正常工作，但在清理期间你无法使用alter table等命令来更新表的定义）。
vacuum full : vacuum full 类似于表的重建或者说碎片整理，以收回更多磁盘空间但是运行起来更慢，而且vacuum full操作执行期间无法和对此表上的其他操作并发执行。vacuum full不会有后台进程主从触发（只能手动执行）。

手动执行analyze table进行手动采集更新统计信息

二、负载指标统计信息

1、pg_stat_database 指标含义

通过pg_stat_database我们可以大致的了解一个数据库的历史运行情况，比较常见的一个问题定位有：

当tup_returned值远大于tup_fetched时，说明该数据库下存在较多全表扫描SQL，结合pg_stat_statments来定位具体慢SQL或者结合pg_stat_user_tables来定位全表扫描相关表
当tup_updated的数值比较大时，说明数据库有很频繁的更新，这个时候就需要关注一下vacuum相关的指标和长事务，如果没有及时进行垃圾回收会造成数据膨胀的比较厉害，一定程度会响应表查询效率
当temp_files的数值比较大时，说明存在很多的排序，hash，或者聚合这种操作，可以通过增大work_mem减少临时文件的产生，并且同时这些操作的性能也会有较大的提升

2、pg_stat_user_tables 指标含义

通过 pg_stat_user_tables，我们可以知道当前数据库下哪些表发生全表扫描频繁，哪些表变更比较频繁，对于变更较频繁的表可多关注其vacuum相关的指标，避免表膨胀。

3、pg_stat_user_indexes 指标含义

通过pg_stat_user_indexes我们可以查看对应索引的使用情况，可以协助我们判断哪些索引当前基本不使用，对这些无效的冗余索引，可进行索引删除。

4、pg_statio_user_tables 指标含义

通过对pg_statio_user_tables的查询，如果heap_blks_read，idx_blks_read很高说明shared_buffer较小，存在频繁需要从磁盘或者page cache读取到shared_buffer中。

5、pg_stat_bgwriter 指标含义

6、pg_stat_replication 指标含义

pg_stat_replication仅仅在主从架构下才会显示相关数据。根据对pg_stat_replication表的查询可以查看当前复制的模式、复制配置信息、复制位点信息等。

7、pg_stat_statement 指标含义

pg_stat_statements模块提供一种跟踪执行统计服务器执行的所有SQL语句的手段。该模块默认是不开启的，如果需要开启需要我们手动对其进进行编译安装，修改配置文件并重启数据库，并在使用前手动载入该模块。

8、pg_stat_activity视图

9、pg_statio_all_tables

pg_statio_all_tables视图将为当前数据库中的每个表（包括 TOAST 表）包含一行，该行显示指定表上有关 I/O 的统计信息。pg_statio_user_tables和pg_statio_sys_tables视图包含相同的信息，但是被过滤得分别只显示用户表和系统表。

10、pg_statio_all_indexes

pg_statio_all_indexes视图将为当前数据库中的每个索引包含一行，该行显示指定索引上有关 I/O 的统计信息。pg_statio_user_indexes和pg_statio_sys_indexes视图包含相同的信息，但是被过滤得分别只显示用户索引和系统索引。

11、pg_stat_database

12、pg_stat_user_tables

通过查询pg_stat_user_tables，可以基本清除哪些表的全表扫描次数较多，表中DML哪种操作多，也可以了解垃圾数据的数量。

三、数据分布类统计信息

1、pg_stats

通过对pg_stats的查询，可以查看每个字段的数据分析统计信息，类似SQL Server的直方图，为优化器选择最佳执行计划提供依据，pg_stats只有管理员账号才可以访问。

2、pg_statistic

pg_statistic 是基于pg_stats的视图，以更加友好以及可读的方式展现统计信息，普通用户可以访问。

最新文章

热门文章