PostgreSQL统计信息
转：PostgreSQL统计信息-阿里云开发者社区 (aliyun.com)
(96条消息) PG统计信息_pg_stats_三思呐三思的博客-CSDN博客
1.数据库统计信息概览
2.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 | 2019-04-09 14:06:53.416473+08 #统计信息重置的时间
通过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减少临时文件的产生，并且同时这些操作的性能也会有较大的提升。
3.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_tables，可以基本清楚哪些表的全表扫描的次数较多，表中是插入还是更新，删除比较多。也可以了解当前表中垃圾数据的数量。
4.pg_stat_user_indexes关键指标
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_stat_user_indexes可以知道当前数据库中哪些是用的很频繁的索引，哪些是无效索引，无效索引可以进行删除，可以减少磁盘空间的使用和提升insert，update，delete性能。
5.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中。
6.pg_stat_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 | 2019-03-28 16:54:45.678617+08
通过这个视图我们可以判断checkpoint以及max_wal_size的相关参数是否合理。也可以判断bgwriter相关的参数是否合理。
7.pg_stat_replication关键指标
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
通过这个视图可以检查当前流复制的状态。检查备库replay的进度。
8.pg_stat_statements
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 #从磁盘写入花费的时间
9.外部插件介绍
9.1 pg_wait_sampling
可以收集数据库中产生的所有等待事件的信息，通过等待事件可以了解数据库的一些瓶颈，适合用于压测中发现性能瓶颈，平时也可以用于定位分析问题。
https://github.com/postgrespro/pg_wait_sampling
9.2 pg_stat_kcache
可以获取些更底层的信息，从文件系统中读写花费的时间，cpu使用的时间等等，可以结合pg_stat_statements得到更多的信息。
https://github.com/powa-team/pg_stat_kcache
9.3 pgcenter
一个命令工具可以进行性能问题排查和分析，pgcenter结合pg内部的统计信息视图，方便快速查找和定位问题。
https://github.com/lesovsky/pgcenter
9.4 pg_activity
监控pg系统状态的命令工具
活到老，学到老。
