PostgreSQL统计信息

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: PostgreSQL统计信息1.数据库统计信息概览2.pg_stat_database关键指标postgres=# select * from pg_stat_database where datname='postgres';-[ RECORD 1 ]-----+-----------...

PostgreSQL统计信息

1.数据库统计信息概览

image

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系统状态的命令工具

https://github.com/julmon/pg_activity

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
3月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 如何通过身份证号码进行年龄段的统计?
【8月更文挑战第20天】PostgreSQL 如何通过身份证号码进行年龄段的统计?
421 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函数对比
155 0
|
SQL 关系型数据库 Java
PostgreSQL统计信息的几个重要视图
PostgreSQL统计信息的几个重要视图
277 1
|
缓存 监控 关系型数据库
[译]PostgreSQL16-新特性-新增IO统计视图:pg_stat_io
[译]PostgreSQL16-新特性-新增IO统计视图:pg_stat_io
228 0
|
SQL 监控 Oracle
PostgreSQL pgcenter - 采样、统计、性能诊断、profile、cli小工具
标签 PostgreSQL , pgcenter , pg_top , awr , perf insight , 等待事件 , perf , profile , 采样 , 统计信息 背景 PostgreSQL 性能诊断的方法很多: 例如: 1、函数的性能诊断,PROFILE。 《PostgreSQL 函数调试、诊断、优化 & auto_explain & plpro
2059 0
|
SQL 缓存 监控
PostgreSQL 监控1统计进程和统计信息的解读|学习笔记(二)
快速学习PostgreSQL 监控1统计进程和统计信息的解读
423 0
PostgreSQL 监控1统计进程和统计信息的解读|学习笔记(二)
|
SQL 监控 IDE
PostgreSQL监控1统计进程和统计信息的解读|学习笔记(一)
快速学习PostgreSQL监控1统计进程和统计信息的解读
605 0
PostgreSQL监控1统计进程和统计信息的解读|学习笔记(一)
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
1250 1
|
数据可视化 关系型数据库 MySQL
将 PostgreSQL 迁移到 MySQL 数据库
将 PostgreSQL 迁移到 MySQL 数据库
1735 2