PostgreSQL 数据库统计信息概览
一 数据库统计信息概览
PostgreSQL 的两种统计信息:
在MySQL 中，统计信息这个术语是描述字段数据值的分布状态的一种信息，由后台线程或者人为命令触发更新。MySQL中的一些术语不同，PostgreSQL 中的stats分为两类如下两类:
第一类是类似于MySQL 中的性能计数器和系统表，这些数据用来描述数据库的负载，或者数据库对象使用/访问情况，MySQL中的 performance_schema中的的信息
第二类是类似于 MySQL 中的统计信息直方图，用来描述字段数据值的分布，为优化器生成执行计划提供依据。统计信息基本上等价于MySQL中用来指导执行计划的统计信息 “统计信息” 在 PostgreSQL 中的含义，与MySQL相比，第一类信息的称呼上存在一些不同。
二 相关统计信息表
pg_stat_database
postgres=# select * from pg_stat_database where datname='postgres';
-[ RECORD 1 ]-----+------------------------------
datid | 13510 #数据库oid
datname | postgres #数据库名
numbackends | 98 #访问当前数据库连接数量
xact_commit | 14291309 #该数据库事务提交总量
xact_rollback | 0 #该数据库事务回滚总量
blks_read | 536888 #总磁盘物理读的块数，这里read也可能是从page cache读取，如果这里很高需要结合blk_read_time看是否真的存在很多实际从磁盘读取的情况。
blks_hit | 261717850 #在shared_buffer命中的块数
tup_returned | 58521416 #对于表来说是全表扫描的行数，对于索引是通过索引方法返回的索引行数，如果这个值数量明显大于tup_fetched，说明当前数据库存在大量全表扫描的情况。
tup_fetched | 57193639 #指通过索引返回的行数
tup_inserted | 14293061 #插入的行数
tup_updated | 42868451 #更新的行数
tup_deleted | 98 #删除的行数
conflicts | 0 #与恢复冲突取消的查询次数(只会在备库上发生)
temp_files | 0 #产生临时文件的数量，如果这个值很高说明work_mem需要调大
temp_bytes | 0 #临时文件的大小
deadlocks | 0 #死锁的数量，如果这个值很大说明业务逻辑有问题
blk_read_time | 0 #数据库中花费在读取文件的时间，这个值较高说明内存较小，需要频繁的从磁盘中读入数据文件
blk_write_time | 0 #数据库中花费在写数据文件的时间，pg中脏页一般都写入page cache，如果这个值较高，说明page cache较小，操作系统的page cache需要更积极的写入。
stats_reset | 2022-04-09 10:06:43 #统计信息重置的时间
通过 pg_stat_database
我们就可以大概了解数据库的历史情况，比如
如果 tup_returned
值远大于 tup_fetched
，说明数据库历史执行的sql很多都是全表扫描，说明存在很多没有走索引的sql，这时候可以结合 pg_stat_statments
来查找慢sql，也可以通过 pg_stat_user_tables
找到全表扫描次数和行数最多的表。
如果 tup_updated
很高说明数据库有很频繁的更新，这个时候就需要关注一下 vacuum 相关的指标和长事务，如果没有及时进行垃圾回收会造成数据膨胀的比较厉害。
如果temp_files
较高的话说明存在很多的排序，hash，或者聚合这种操作，可以通过增大 work_mem
减少临时文件的产生，并且同时这些操作的性能也会有较大的提升。
pg_stat_user_tables 关键指标
通过 pg_stat_user_tables
可以查看哪些表的全表扫描的次数较多，表中是插入还是更新，删除比较多。也可以了解当前表中垃圾数据的数量。
relid | 16390 #表的oid
schemaname | public #模式名称
relname | pgbench_accounts #表名
seq_scan | 0 #这个表进行全表扫描的次数
seq_tup_read | 0 #全表扫描的数据行数，如果这个值很大说明对这个表进行sql很有可能都是全表扫描，需要结合具体的执行计划来看
idx_scan | 29606482 #索引扫描的次数
idx_tup_fetch | 29606482 #通过索引扫描返回的行数
n_tup_ins | 0 #插入的数据行数
n_tup_upd | 14803241 #更新的数据行数
n_tup_del | 0 #删除的数据行数
n_tup_hot_upd | 14638544 #hot update的数据行数，这个值与n_tup_upd越接近说明update的性能较好，更新数据时不会更新索引。
n_live_tup | 100012319 #活着的行数量
n_dead_tup | 2403437 #死亡的行数量
n_mod_since_analyze | 0 #上次analyze的时间
last_vacuum | #上次手动vacuum的时间
last_autovacuum | #上次autovacuum的时间
last_analyze | #上次analyze的时间
last_autoanalyze | 2019-04-09 14:12:30.402387+08 #上次自动analyze的时间
vacuum_count | 0 #vacuum的次数
autovacuum_count | 0 #autovacuum的次数
analyze_count | 0 #analyze的次数
autoanalyze_count | 1 #自动analyze的次数
pg_stat_user_indexes 关键指标
通过 pg_stat_user_indexes
我们可以知道当前数据库中哪些是用的很频繁的索引，哪些是无效索引，无效索引可以进行删除，可以减少磁盘空间的使用和提升insert，update，delete性能。
relid | 16390 #相关表的oid
indexrelid | 16404 #索引的oid
schemaname | public #模式名
relname | pgbench_accounts #表名
indexrelname | pgbench_accounts_pkey #索引名
idx_scan | 29606482 #通过索引扫描的次数，如果这个值很小，说明这个索引很少被用到，可以考虑进行删除
idx_tup_read | 29949698 #通过任意索引方法返回的索引行数
idx_tup_fetch | 29606482 #通过索引方法返回的数据行数
pg_statio_user_tables关键指标
通过pg_statio_user_tables关键指标
，我们可以知道用户表的访问情况。
relid | 16390
schemaname | public
relname | pgbench_accounts
heap_blks_read | 414012 #指从page cache或者磁盘中读入表的块数
heap_blks_hit | 44710713 #指在shared_buffer中命中表的块数
idx_blks_read | 67997 #指从page cache或者磁盘中读入索引的块数
idx_blks_hit | 89424015 #在shared_buffer中命中的索引的块数
toast_blks_read | #从page cache或者磁盘中读入toast表的块数
toast_blks_hit | #指在shared_buffer中命中toast表的块数
tidx_blks_read | #从page cache或者磁盘中读入toast表索引的块数
tidx_blks_hit | #指在shared_buffer中命中toast表索引的块数
如果 heap_blks_read
，idx_blks_read
很高说明 shared_buffer
较小，存在频繁需要从磁盘或者page cache读取到shared_buffer 中。
pg_stat_bgwriter 关键指标
通过 pg_stat_bgwriter
我们可以判断 checkpoint 以及 max_wal_size
的相关参数是否合理。也可以判断 bgwriter 相关的参数是否合理。
checkpoints_timed | 1050 #指超过checkpoint_timeout的时间后触发的检查点
checkpoints_req | 1 #指手动触发的检查点或者因为wal文件数量到达max_wal_size大小时也会增加，如果这个值大于checkpoints_timed，说明checkpoint_timeout设置的不合理。
checkpoint_write_time | 659728 #指从shared_buffer中write到page cache花费的时间
checkpoint_sync_time | 549 #指checkpoint调用fsync将脏数据同步到磁盘花费的时间，如果这个时间很长容易造成IO的抖动，这时候需要增加checkpoint_timeout或者增加checkpoint_completion_target。
buffers_checkpoint | 122383 #checkpoint写入的脏块的数量
buffers_clean | 60723 #通过bgwriter写入的块的数量
maxwritten_clean | 583 #指bgwriter超过bgwriter_lru_maxpages时停止的次数，如果这个值很高说明需要增加bgwriter_lru_maxpages的大小
buffers_backend | 306521 #通过backend写入的块数量
buffers_backend_fsync | 0 #指backend需要fsync的次数
buffers_alloc | 317113 #被分配的缓冲区数量
stats_reset | 2022-03-28 16:54:45.678617+08
pg_stat_replication 关键指标
pg_stat_replication
记录当前数据库流复制的状态。检查备库replay的进度。
pid | 40638
usesysid | 16384
usename | replicator
application_name | standby1
client_addr | x.x.x.x
client_hostname |
client_port | 64546
backend_start | 2019-03-27 14:05:47.891967+08
backend_xmin |
state | streaming
sent_location | 62/D8BB46A8
write_location | 62/D8BB46A8
flush_location | 62/D8BB46A8
replay_location | 62/D8BB4338
sync_priority | 0
sync_state | async
pg_stat_statements
pg_stat_statements
提供在实例所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的资源开销，分析TOP SQL。
userid | 10 #用户id
dbid | 12917 #数据库oid
queryid | 4390283800491518311 #sql进行归一化后的hash值
query | select version() #sql归一化后的内容
calls | 1 #执行次数
total_time | 0.208 #sql总共的执行时间
min_time | 0.208 #sql最小的执行时间
max_time | 0.208 #sql最大的执行时间
mean_time | 0.208 #sql平均的执行时间
stddev_time | 0 #sql花费时间的表中偏差
rows | 1 #sql返回或者影响的行数
shared_blks_hit | 0 #sql在shared_buffer中命中的块数
shared_blks_read | 0 #sql从page cache或者磁盘中读取的块数
shared_blks_dirtied | 0 #sql语句弄脏的shared_buffer的块数
shared_blks_written | 0 #sql语句写入的块数
local_blks_hit | 0 #临时表中命中的块数
local_blks_read | 0 #临时表需要读的块数
local_blks_dirtied | 0 #临时表弄脏的块数
local_blks_written | 0 #临时表写入的块数
temp_blks_read | 0 #从临时文件读取的块数
temp_blks_written | 0 #从临时文件写入的数据块数
blk_read_time | 0 #从磁盘或者读取花费的时间
blk_write_time | 0 #从磁盘写入花费的时间
免责声明：
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源，请您支持作者，购买正版内容。
6、资源失效，请下方留言，欢迎分享资源链接
文章评论