Postgresql数据库运维统计信息

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

• 修改postgresql.conf文件

#shared_preload_libraries = ''  # (change requires restart)

shared_preload_libraries = 'pg_stat_statements'

• 重启数据库
• 创建扩展

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

1.1. 关键配置

• track_activities : 收集SQL执行开始时间以及SQL语句的内容，默认打开
• track_activity_query_size : 指定统计信息中允许存储的SQL长度，超出长度的SQL被截断，默认1024
• track_counts : 收集数据库的活动信息（如新增的行数和删除的行数等）
• track_io_timing : 收集IO操作的时间开销，因为需要不断的调用系统当前时间，所以某些系统中会带来极大的开销，从而带来极大的负面影响。
• track_functions : 跟踪函数的调用次数和时间开销
• update_process_title : 每次服务端process接收到新的SQL时更新command状态。
• log_statement_stats (boolean) – 类似unix的getrusage()操作系统函数, 用于收集SQL语句级的资源开销统计. 包含以下3种层面的 全部. 因此配置了log_statement_stats就不需要配置以下选项.
• log_parser_stats (boolean) – 同上, 但是只包含SQL parser部分的资源开销统计.
• log_planner_stats (boolean) – 同上, 但是只包含SQL planner部分的资源开销统计.
• log_executor_stats (boolean) – 同上, 但是只包含SQL executor部分的资源开销统计.

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	在这个函数本身中花费的总时间，不包括它调用的其他函数，以毫秒为单位