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 其他配置

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';

2.6. 查看表级统计信息

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

2.7. 查看索引级统计信息

2.8. 表的IO级统计信息

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

2.9. 索引的IO级统计信息

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

2.10. 序列的IO级统计信息

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

2.11. , 函数的统计信息

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