北在南方

V1

2022/09/07阅读:7主题:默认主题

pg 数据库统计信息概览

一 数据库统计信息概览

PostgreSQL 的两种统计信息:

在MySQL 中,统计信息这个术语是描述字段数据值的分布状态的一种信息,由后台线程或者人为命令触发更新。MySQL中的一些术语不同,PostgreSQL 中的stats分为两类如下两类:

  1. 第一类是类似于MySQL 中的性能计数器和系统表,这些数据用来描述数据库的负载,或者数据库对象使用/访问情况,MySQL中的 performance_schema中的的信息

  2. 第二类是类似于 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 相关的指标和长事务,如果没有及时进行垃圾回收会造成数据膨胀的比较厉害。 by yangyidba 如果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_readidx_blks_read很高说明 shared_buffer 较小,存在频繁需要从磁盘或者page cache读取到shared_buffer 中。by yangyidba

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                       #从磁盘写入花费的时间

分类:

后端

标签:

后端

作者介绍

北在南方
V1