PostgreSQL统计信息的几个重要视图

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: PostgreSQL统计信息的几个重要视图

1、pg_stat_database

yzs=# select *from pg_stat_database;
-[ RECORD 1 ]--+------------------------------
datid          | 13156                            #数据库的oid
datname        | postgres                         #数据库名
numbackends    | 0                                #访问当前数据库的连接数量
xact_commit    | 2357                             #该数据库事务提交总量:和下面的rollback和作为TPS统计
xact_rollback  | 17                               #该数据库事务rollback总量,如果特别多,需要看业务是否有问题了
blks_read      | 1946                             #总磁盘物理读的块数,这里的read可能是从 cache中读取,如果很高需要结合blk_read_time看是否真的存在从磁盘读取的情况
blks_hit       | 103625                           #从shared buffer命中块数
tup_returned   | 1413113                          #对于表来说,是全表扫描的行数;对于索引是通过索引返回的索引行数,如果这个值明显大于tup_fetched,说明当前数据库存在大量的全表扫描。查看执行计划,这个是databas全局级别的
tup_fetched    | 36041                            #指通过索引返回的行数
tup_inserted   | 104                              #插入的行数
tup_updated    | 0                                #更新的行数
tup_deleted    | 19                               #删除的行数
conflicts      | 0                                #与恢复冲突取消的查询次数,只会在备机上发生
temp_files     | 0                                #产生临时文件的数量,如果这个值很高,需要调大work_mem
temp_bytes     | 0                                #临时文件的大小
deadlocks      | 0                                #死锁的数量,如果这个值很大说明业务逻辑有问题
blk_read_time  | 0                                #数据库中花费在读取文件的时间,这个值很高说明内存较小,需要频繁从磁盘读入数据文件
blk_write_time | 0                                #数据库中花费在写数据文件的时间,pg中脏页一般写入page cache,如果这个值较高,则说明cache较小,操作系统的cache需要更积极的写入
stats_reset    | 2019-02-11 23:42:37.526743-08    #统计信息重置的时间

通过pg_stat_database可以大概了解数据库的历史情况。

比如tup_returned值明显大于tup_fetched,历史SQL语句很多是全表扫描,存在没有使用索引的SQL,可结合pg_stat_statments查找慢SQL,也可结合pg_stat_user_table找全表扫描次数和行数最多的表;

通过看tup_updated很高,可以说明数据库有频繁的更新,这个时候需要关注vaccum相关的指标和长事务,如果没有及时进行垃圾回收,会引起表膨胀;

temp_files较高说明存在很多排序,hash,或者聚合这种操作,可以增大work_mem减少临时文件的产生,并且同时这些操作的性能也会有较大的提升。

2、pg_stat_user_tables

yzs=# select *from pg_stat_user_tables;
-[ RECORD 1 ]-------+------------------------------
relid               | 16440        #表oid
schemaname          | public       #模式名
relname             | t1           #表名
seq_scan            | 50           #这个表进行全表扫描的次数
seq_tup_read        | 1867763      #全表扫描的数据行数,如果这个值很大说明操作这个表的SQL语句很可能是全表扫描,需要结合执行计划分析
idx_scan            |              #索引扫描的次数
idx_tup_fetch       |              #通过索引扫描返回的行数
n_tup_ins           | 1130502      #插入的数据行数
n_tup_upd           | 0            #更新的数据行数
n_tup_del           | 81920        #删除的数据行数
n_tup_hot_upd       | 0            #hot update的数据行数,这个值与n_tup_upd接近说明更新性能较好,不需要更新索引
n_live_tup          | 655366       #活的行数量
n_dead_tup          | 0            #死记录个数
n_mod_since_analyze | 6            #上次analyze的实际
last_vacuum         | 2019-04-07 00:22:00.955542-07 #上次手动vacuum的实际
last_autovacuum     |              #上次autovacuum的实际
last_analyze        |              #上次analyze时间
last_autoanalyze    | 2019-04-07 00:26:07.668391-07 #上次自动analyze时间
vacuum_count        | 2            #vacuum次数
autovacuum_count    | 0            #自动vacuum次数
analyze_count       | 0            #analyze次数
autoanalyze_count   | 10           #自动analyze次数

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

3、pg_stat_user_indexes

yzs=# select *from pg_stat_user_indexes;
-[ RECORD 1 ]-+----------
relid         | 16447      #相关表的oid
indexrelid    | 16450      #索引的oid
schemaname    | public     #模式名
relname       | t3         #表名
indexrelname  | t3_id_idx  #索引名
idx_scan      | 0          #通过索引扫描的次数,如果该值很小,说明该索引很少被用到,可以考虑删除
idx_tup_read  | 0          #通过任意索引方法返回的索引行数
idx_tup_fetch | 0          #通过索引方法返回的数据行数

可以知道当前哪些索引频繁使用,哪些是无效索引。无效索引可以删除掉,减少磁盘空间的使用和提升insert、delete、update的性能。

4、pg_statio_user_tables

yzs=# select *from pg_statio_user_tables;
-[ RECORD 1 ]---+--------
relid           | 16447    
schemaname      | public
relname         | t3
heap_blks_read  | 1    #从page cache或磁盘读取表的块数
heap_blks_hit   | 1    #从shared buffer命中的块数
idx_blks_read   | 0    #从page cache或磁盘读取的索引的块数
idx_blks_hit    | 0    #从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中命中toast表的块数。

5、 pg_stat_bgwriter

yzs=# select *from pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 206     #指超过checkpoint_timeout的时间后触发的检查点次数
checkpoints_req       | 8       #手动触发checkpoint或者因为WAL文件数量达到max_wal_size时也会增加,如果这个值大于checkpoints_req说明checkpoint_timeout设置的不合理
checkpoint_write_time | 306582  #从shared buffer 中write到page cache花费的时间
checkpoint_sync_time  | 367     #checkpoint调用fsync将脏数据刷到磁盘花费的时间,如果这个值很长,容易造成IO抖动,需要增加checkpoint_timeout或者checkpoint_completion_target
buffers_checkpoint    | 6671    #通过checkpoint写入脏块的数量
buffers_clean         | 0       #通过bgwriter写入块的数量
maxwritten_clean      | 0       #bgwriter超过bgwriter_lru_maxpages时停止的次数,如果这个值很高,需要增加bgwriter_lru_maxpages
buffers_backend       | 7953    #通过backend写入的块数量
buffers_backend_fsync | 0       #backend需要fsync的次数
buffers_alloc         | 11613   #被分配的缓冲区数量
stats_reset           | 2019-02-11 23:42:35.273758-08

通过这个视图,可以判断checkpoint以及max_wal_size是否合理

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
SQL 弹性计算 关系型数据库
HTAP数据库 PostgreSQL 场景与性能测试之 3.1 - (OLAP) 大表JOIN统计查询-10亿 join 1亿 agg
标签 PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试 背景 PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖Michael_Stonebraker 操刀设计,PostgreSQL具备与Oracle类似的功能、性能、架构以及稳定性。 PostgreSQL社区的贡献者众多
2013 0
|
3月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 如何通过身份证号码进行年龄段的统计?
【8月更文挑战第20天】PostgreSQL 如何通过身份证号码进行年龄段的统计?
358 2
|
4月前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之如何查看PolarDB for PostgreSQL的备份信息
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
6月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL【SQL 01】根据条件更新字段值或追加信息STRPOS(string, substring)函数使用及LIKE函数对比
PostgreSQL【SQL 01】根据条件更新字段值或追加信息STRPOS(string, substring)函数使用及LIKE函数对比
146 0
|
缓存 监控 关系型数据库
[译]PostgreSQL16-新特性-新增IO统计视图:pg_stat_io
[译]PostgreSQL16-新特性-新增IO统计视图:pg_stat_io
227 0
|
SQL 监控 Oracle
PostgreSQL pgcenter - 采样、统计、性能诊断、profile、cli小工具
标签 PostgreSQL , pgcenter , pg_top , awr , perf insight , 等待事件 , perf , profile , 采样 , 统计信息 背景 PostgreSQL 性能诊断的方法很多: 例如: 1、函数的性能诊断,PROFILE。 《PostgreSQL 函数调试、诊断、优化 & auto_explain & plpro
2053 0
|
SQL 缓存 监控
PostgreSQL 监控1统计进程和统计信息的解读|学习笔记(二)
快速学习PostgreSQL 监控1统计进程和统计信息的解读
421 0
PostgreSQL 监控1统计进程和统计信息的解读|学习笔记(二)
|
SQL 监控 IDE
PostgreSQL监控1统计进程和统计信息的解读|学习笔记(一)
快速学习PostgreSQL监控1统计进程和统计信息的解读
594 0
PostgreSQL监控1统计进程和统计信息的解读|学习笔记(一)
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
358 0
|
存储 缓存 关系型数据库