当前位置：
  1. 首页
  2. 数据库
  3. 正文
本文介绍: 通过pg_stat_statements监控数据库情况

如果需要使用以下运维信息，需要如下几步

#shared_preload_libraries = ''  # (change requires restart)

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1. 统计信息的收集维度配置

1.1. 关键配置

1.2 其他配置

在这里插入图片描述

字段名 字段类型 字段信息描述
pg_stat_get_backend_idset() setof integer 当前活动后端ID号的集合(从1到活动后端数量)
pg_stat_get_backend_activity(integer) text 此后端最近查询的文本
pg_stat_get_backend_activity_start(integer) timestamp with time zone 最近一次查询开始的时间
pg_stat_get_backend_client_addr(integer) inet 与此后端连接的客户端的IP地址
pg_stat_get_backend_client_port(integer) integer 客户端用于通信的TCP端口号
pg_stat_get_backend_dbid(integer) oid 此后端所连接的数据库的OID
pg_stat_get_backend_pid(integer) integer 后端进程ID
pg_stat_get_backend_start (integer) timestamp with time zone 此过程开始的时间
pg_stat_get_backend_userid(integer) oid 登录到该后端用户的OID
pg_stat_get_backend_waiting(integer) boolean 如果此后端当前正在等待锁，则为True
pg_stat_get_backend_xact_start(integer) timestamp with time zone 如果此后端当前正在等待锁，则为True

2. 常用的监控数据库活动的sql

2.1. 调用次数倒序输出

select * from pg_stat_statements order by calls desc limit 1 offset 0;

在这里插入图片描述

2.2. 单次SQL执行时间倒序输出

select * from pg_stat_statements order by total_exec_time/calls desc limit 10 offset 0;

在这里插入图片描述

2.3. 按shared buffer “未命中块读” 倒序输出

select * from pg_stat_statements order by shared_blks_read desc limit 10 offset 0;

在这里插入图片描述

2.4. 获取CPU time Top20的统计结果

export PGPORT=1921 
export PGDATA=/data01/pgdata/1921/pg_root 
export LANG=en_US.utf8 
export PGHOME=/opt/pgsql 
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib 
export DATE=`date +"%Y%m%d%H%M"` 
export PATH=$PGHOME/bin:$PATH:. 
export PGHOST=$PGDATA 
export PGDATABASE=postgres 
psql -A -x -c "select row_number() over() as rn, * from (select query,' calls:'||calls||' total_exec_time_s:'||round(total_exec_time::numeric,2)||' avg_time_ms:'||round(1000*(total_exec_time::numeric/calls),2) as stats from pg_stat_statements order by total_exec_time desc limit 20) t;" >/tmp/stat_query.log 2>&1 
echo -e "$DATE avcp TOP20 query report yest"|mutt -s "$DATE avcp TOP20 query report yest" -a /tmp/stat_query.log digoal@126.com 
psql -c "select pg_stat_statements_reset()

crontab -e

1 8 * * * /home/postgres/script/report.sh

在这里插入图片描述

2.5. 查看数据库级统计信息

如数据库的 事务提交次数, 回滚次数, 未命中数据块读, 命中读, 行的统计信息(扫描, 输出,插入,更新,删除), 临时文件, 死锁, IOTIME等统计信息.

select tup_returned,tup_fetched from pg_stat_database where datname ='generalquery_frame';

在这里插入图片描述

字段名 字段类型 字段信息描述
datid id 数据库OID
dataname name 数据库Name
numbackends integer 当前连接到此数据库的后端数量。这是该视图中唯一返回反映当前状态的值的列;所有其他列返回自上次重置以来的累积值
xact_commit bigint 此数据库中已提交的事务数
xact_rollback bigint 此数据库中已回滚的事务数
blks_read bigint 在此数据库中读取的磁盘块数目
blks_hit bigint 磁盘块已经在缓冲缓存中被发现的次数，因此不需要读取(这只包括在PostgreSQL缓冲区中的命中)缓存，而不是操作系统的文件系统缓存)
tup_returned bigint 在此数据库中查询返回的行数
tup_fetched bigint 在此数据库中查询获取的行数
tup_inserted bigint 查询在此数据库中插入的行数
tup_updated bigint 此数据库中查询更新的行数
tup_deleted bigint 查询在此数据库中删除的行数
conflicts bigint 由于与此数据库中的恢复冲突而取消的查询数。冲突只发生在备用服务器上;参见pa_stat_database_conflicts获取细节。)
temp_files bigint 查询在此数据库中创建的临时文件数。所有临时文件都会被计算在内，而不管临时文件是为什么创建的(例如:排序或散列)，而不考虑日志临时文件的设置。
temp_bytes bigint 在此数据库中查询写入临时文件的数据总量。所有临时文件都会被计算在内，而不管临时文件的原因是什么创建，而不考虑日志临时文件设置。
deadlocks bigint 在此数据库中检测到的死锁数目
blk_read_time double precision 后端在此数据库中读取数据文件块所花费的时间，以毫秒为单位
blk_write_time double precision 后端在此数据库中写入数据文件块所花费的时间，以毫秒为单位
stats_reset timestamp_with 这些统计数据最后重置的时间

2.6. 查看表级统计信息

区分全表扫描和索引扫描的次数和输出的行, 以及DML的行数, 评估的当前活动行数和垃圾行数

在这里插入图片描述

字段名 字段类型 字段信息描述
relid id 数据库OID
schemaname name 该表所在的模式名称
relname name 该表的名称
heap_blks_read bigint 从该表中读取的磁盘块数量
heap_blks_hit bigint 该表中的缓冲区命中数
idx_blks_read bigint 从该表的所有索引中读取的磁盘块数
idx_blks_hit bigint 该表上所有索引中的缓冲区命中数
toast_blks_read bigint 从该表的TOAST表中读取的磁盘块数量(如果有的话)
toast_blks_hit bigint 该表的TOAST表中的缓冲区命中次数(如果有的话)
tidx_blks_read bigint 从该表的TOAST表索引中读取的磁盘块数量(如果有的话)
tidx_blks_hit bigint 该表的TOAST表索引中的缓冲区命中数(如果有的话)

2.7. 查看索引级统计信息

在这里插入图片描述

字段名 字段类型 字段信息描述
relid oid 索引表的OID
indexrelid oid 该索引的OID
schemaname name 此索引所在的模式的名称
relname name 此索引的表名
indexrelname name 该索引的名称
idx_scan bigint 在该索引上启动的索引扫描次数
idx_tup_read bigint 扫描该索引返回的索引条目数
idx_tup_fetch bigint 使用该索引进行简单索引扫描获取的活动表行数

2.8. 表的IO级统计信息

如heap主存储的块读(区分未命中shared buffer和命中shared buffer的统计)

在这里插入图片描述

字段名 字段类型 字段信息描述
relid oid 表的OID
schemaname name 该表所在的模式名称
relname name 该表的名称
heap_blks_read bigint 从该表中读取的磁盘块数量
heap_blks_hit bigint 该表中的缓冲区命中数
idx_blks_read bigint 从该表的所有索引中读取的磁盘块数
idx_blks_hit bigint 该表上所有索引中的缓冲区命中数
toast_blks_read bigint 从该表的TOAST表中读取的磁盘块数量(如果有的话)
toast_blks_hit bigint 该表的TOAST表中的缓冲区命中次数(如果有的话)
tidx_blks_read bigint 从该表的TOAST表索引中读取的磁盘块数量(如果有的话)
tidx_blks_hit bigint 该表的TOAST表索引中的缓冲区命中数(如果有的话)

2.9. 索引的IO级统计信息

索引的块读(区分未命中shared buffer和命中shared buffer的统计)

在这里插入图片描述

字段名 字段类型 字段信息描述
relid oid 索引表的OID
indexrelid oid OID of this index
schemaname name 该索引的OID
relname name 此索引的表名
indexrelname name 该索引的名称
idx_blks_read bigint 从该索引读取的磁盘块数目
idx_blks_hit bigint 这个索引中的缓冲区命中数

2.10. 序列的IO级统计信息

序列的块读(区分未命中shared buffer和命中shared buffer的统计)

在这里插入图片描述

字段名 字段类型 字段信息描述
relid oid 序列的OID
schemaname name 此序列所在的模式名称
relname name 这个序列的名称
blks_read bigint 从这个序列中读取的磁盘块的数目
blks_hit bigint 这个序列中缓冲区命中的次数

2.11. , 函数的统计信息

调用次数, 总的时间开销.
必须要先打开track_functions参数.

在这里插入图片描述

字段名 字段类型 字段信息描述
funcid oid 函数的OID
schemaname name 此函数所在的模式名称
funcname name 此函数的名称
calls bigint 这个函数被调用的次数
total time double precision 在这个函数和它调用的所有其他函数中花费的总时间，以毫秒为单位
self_time double precision 在这个函数本身中花费的总时间，不包括它调用的其他函数，以毫秒为单位

原文地址:https://blog.csdn.net/a13407142317/article/details/134675633

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。

如若转载，请注明出处：http://www.7code.cn/show_7435.html

如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！

上一篇 案例研究｜北京交通大学基于DataEase开展多场景校园数据分析与展示
下一篇 C语言——数字金字塔

相关文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

插入图片
浏览器会保存昵称、邮箱和网站cookies信息，下次评论时使用。