开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL AWR报告

简介:
+关注继续查看

PostgreSQL AWR报告

作者

digoal

日期

2016-11-23

标签

PostgreSQL , AWR , Oracle , 数据库诊断 , 性能报告 , snapshot , 快照


背景

熟悉Oracle的童鞋一定对AWR不陌生,通常要分析一个数据库在某个时间段的性能,可以从数据库的动态视图等统计信息记录中生成一份该时段的统计分析报告。

里面包含了常见的等待事件分析,TOP SQL, TOP event等。

PostgreSQL是一个功能和Oracle几乎可以媲美的开源产品,分析报告的工具也非常多,例如pgstatsinfo, pgsnap, pgtop, pgfouine, ..... 非常的多。

我不想介绍这么多的工具,而是自己根据对PG的经验写了一个非常简单易用的,不需要安装一堆的插件,周期性的打快照即可。 用法和AWR非常类似。

本文主要是将之前写的一个比较完整的巡检脚本转换成SQL接口的AWR,易用性更强,不需要登陆数据库主机,即可获得报告。

将来PG加入新的统计信息表,我会继续追加到这个简单的工具中。

希望大家一起来使用和改进,有问题可以发给我。

接口介绍

1. 快照列表

其实就是快照的清单,每打一个快照,就会新增一条记录。

postgres=# select * from snap_list;
 id |          snap_ts           | snap_level 
----+----------------------------+------------
  1 | 2016-11-23 19:59:10.321282 | database
  3 | 2016-11-23 22:29:55.139357 | global
  4 | 2016-11-23 22:30:42.602292 | database
  5 | 2016-11-23 22:30:42.602292 | database
  6 | 2016-11-23 22:30:42.602292 | database
  7 | 2016-11-23 22:29:55.139357 | global
  8 | 2016-11-23 22:29:55.139357 | global
  9 | 2016-11-23 22:29:55.139357 | global
 10 | 2016-11-23 23:00:31.796333 | global
 11 | 2016-11-23 22:29:55.139357 | global
 12 | 2016-11-23 23:02:36.590308 | database
 13 | 2016-11-23 23:03:51.727333 | global
 14 | 2016-11-23 23:03:51.727333 | global
 15 | 2016-11-23 23:03:51.727333 | global
 16 | 2016-11-23 23:03:51.727333 | global
 17 | 2016-11-23 23:03:51.727333 | global
 18 | 2016-11-23 23:03:51.727333 | global
 19 | 2016-11-23 23:03:51.727333 | global
 20 | 2016-11-23 23:03:51.727333 | global
 21 | 2016-11-23 23:02:36.590308 | database
 22 | 2016-11-23 23:08:50.900675 | global
 23 | 2016-11-23 23:08:53.153526 | global
 24 | 2016-11-23 23:08:55.816379 | global
 25 | 2016-11-23 23:09:11.242692 | database
 26 | 2016-11-23 23:09:32.270733 | database
(25 rows)

2. 快照历史数据表

打快照时,会将系统的统计信息记录到这些历史表,后面根据时间段生成诊断报告就用到这里的数据。

postgres=# \dt __pg_stats__.snap_*
                      List of relations
    Schema    |            Name            | Type  |  Owner   
--------------+----------------------------+-------+----------
 __pg_stats__ | snap_list                  | table | postgres
 __pg_stats__ | snap_pg_conn_stats         | table | postgres
 __pg_stats__ | snap_pg_cputime_topsql     | table | postgres
 __pg_stats__ | snap_pg_database_age       | table | postgres
 __pg_stats__ | snap_pg_db_conn_limit      | table | postgres
 __pg_stats__ | snap_pg_db_rel_size        | table | postgres
 __pg_stats__ | snap_pg_db_role_setting    | table | postgres
 __pg_stats__ | snap_pg_db_size            | table | postgres
 __pg_stats__ | snap_pg_dead_tup           | table | postgres
 __pg_stats__ | snap_pg_hash_idx           | table | postgres
 __pg_stats__ | snap_pg_index_bloat        | table | postgres
 __pg_stats__ | snap_pg_long_2pc           | table | postgres
 __pg_stats__ | snap_pg_long_xact          | table | postgres
 __pg_stats__ | snap_pg_many_indexes_rel   | table | postgres
 __pg_stats__ | snap_pg_notused_indexes    | table | postgres
 __pg_stats__ | snap_pg_rel_age            | table | postgres
 __pg_stats__ | snap_pg_rel_space_bucket   | table | postgres
 __pg_stats__ | snap_pg_role_conn_limit    | table | postgres
 __pg_stats__ | snap_pg_seq_deadline       | table | postgres
 __pg_stats__ | snap_pg_stat_activity      | table | postgres
 __pg_stats__ | snap_pg_stat_archiver      | table | postgres
 __pg_stats__ | snap_pg_stat_bgwriter      | table | postgres
 __pg_stats__ | snap_pg_stat_database      | table | postgres
 __pg_stats__ | snap_pg_stat_statements    | table | postgres
 __pg_stats__ | snap_pg_statio_all_indexes | table | postgres
 __pg_stats__ | snap_pg_statio_all_tables  | table | postgres
 __pg_stats__ | snap_pg_table_bloat        | table | postgres
 __pg_stats__ | snap_pg_tbs_size           | table | postgres
 __pg_stats__ | snap_pg_unlogged_table     | table | postgres
 __pg_stats__ | snap_pg_user_deadline      | table | postgres
 __pg_stats__ | snap_pg_vacuumlo           | table | postgres
 __pg_stats__ | snap_pg_waiting            | table | postgres
(32 rows)

3. 创建快照

顾名思义,就是创建快照,我这里分为两种快照,一种是全局的,一种是库级的。

全局的在哪里创建都可以,但是只需要创建一次就够了,而库级的需要连接到需要分析库去创建快照。

select __pg_stats__.snap_database();

select __pg_stats__.snap_database();

4. 查询快照

select * from __pg_stats__.snap_list;

5. 删除快照

删除指定snap_ID以前的快照。

删除指定时间以前的快照。

保留最近的几个快照,其他删除。

select snap_delete(10::int8);  -- 删除指定SNAP ID以前的快照

select snap_delete(10::int4);  -- 保留最近的10个快照,其他删除。  

select snap_delete('2016-11-23 12:00:00');  -- 删除指定时间前的快照。

6. 生成报告

指定开始和结束snap_id, 生成报告.

生成全局报告

psql --pset=pager=off -q -h xxx.xxx.xxx.xxx -p xxxx -U superuser -d dbname -c "select * from snap_report_global(1,1)" > /tmp/global.md

生成当前数据库报告

psql --pset=pager=off -q -h xxx.xxx.xxx.xxx -p xxxx -U superuser -d dbname -c "select * from snap_report_database(2,10)" > /tmp/db.md

部署快照功能

修改配置文件

需要用到pg_stat_statements插件,统计TOP SQL。

$ vi postgresql.conf 

shared_preload_libraries='pg_stat_statements' 

$ pg_ctl restart -m fast 

初始化

在需要打快照的库都装上这个SQL。

init.sql

创建打快照的function

在需要打快照的库都装上这个SQL。

snap_functions.sql

创建清理快照的function

在需要清理快照数据的库都装上这个SQL。

snap_delete_functions.sql

创建生成报告的function

在需要生成诊断报告的库都装上这个SQL。

snap_report_functions.sql

定时打快照

连接到对应的数据库执行

select snap_database();  -- 每个库都要执行。

select snap_global();  --  只需要在一个库执行。

其他

目前还不支持从日志文件生成统计报告,这部分可以修改源码后实现,当然,如果你能访问数据库主机,那一切都简单了。

我写本文的目的是,只要能连数据库,就能生成诊断报告。

echo "|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|"
echo "|                   数据库错误日志分析                    |"
echo "|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|"
echo ""

echo "----->>>---->>>  获取错误日志信息: "
cat *.csv | grep -E "^[0-9]" | grep -E "WARNING|ERROR|FATAL|PANIC" | awk -F "," '{print $12" , "$13" , "$14}'|sort|uniq -c|sort -rn
echo "建议: "
echo "    参考 http://www.postgresql.org/docs/current/static/errcodes-appendix.html ."
echo -e "\n"

echo "----->>>---->>>  获取连接请求情况: "
find . -name "*.csv" -type f -mtime -28 -exec grep "connection authorized" {} +|awk -F "," '{print $2,$3,$5}'|sed 's/\:[0-9]*//g'|sort|uniq -c|sort -n -r
echo "建议: "
echo "    连接请求非常多时, 请考虑应用层使用连接池, 或者使用pgbouncer连接池. "
echo -e "\n"

echo "----->>>---->>>  获取认证失败情况: "
find . -name "*.csv" -type f -mtime -28 -exec grep "password authentication failed" {} +|awk -F "," '{print $2,$3,$5}'|sed 's/\:[0-9]*//g'|sort|uniq -c|sort -n -r
echo "建议: "
echo "    认证失败次数很多时, 可能是有用户在暴力破解, 建议使用auth_delay插件防止暴力破解. "
echo -e "\n"

echo "|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|"
echo "|                   数据库慢SQL日志分析                   |"
echo "|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++|"
echo ""

echo "----->>>---->>>  慢查询统计: "
cat *.csv|awk -F "," '{print $1" "$2" "$3" "$8" "$14}' |grep "duration:"|grep -v "plan:"|awk '{print $1" "$4" "$5" "$6}'|sort|uniq -c|sort -rn
echo "建议: "
echo "    输出格式(条数,日期,用户,数据库,QUERY,耗时ms). "
echo "    慢查询反映执行时间超过log_min_duration_statement的SQL, 可以根据实际情况分析数据库或SQL语句是否有优化空间. "
echo ""
echo "----->>>---->>>  慢查询分布头10条的执行时间, ms: "
cat *.csv|awk -F "," '{print $1" "$2" "$3" "$8" "$14}' |grep "duration:"|grep -v "plan:"|awk '{print $1" "$4" "$5" "$6" "$7" "$8}'|sort -k 6 -n|head -n 10
echo ""
echo "----->>>---->>>  慢查询分布尾10条的执行时间, ms: "
cat *.csv|awk -F "," '{print $1" "$2" "$3" "$8" "$14}' |grep "duration:"|grep -v "plan:"|awk '{print $1" "$4" "$5" "$6" "$7" "$8}'|sort -k 6 -n|tail -n 10
echo -e "\n"

echo "----->>>---->>>  auto_explain 分析统计: "
cat *.csv|awk -F "," '{print $1" "$2" "$3" "$8" "$14}' |grep "plan:"|grep "duration:"|awk '{print $1" "$4" "$5" "$6}'|sort|uniq -c|sort -rn
echo "建议: "
echo "    输出格式(条数,日期,用户,数据库,QUERY). "
echo "    慢查询反映执行时间超过auto_explain.log_min_duration的SQL, 可以根据实际情况分析数据库或SQL语句是否有优化空间, 分析csvlog中auto_explain的输出可以了解语句超时时的执行计划详情. "
echo -e "\n"

修改源码要达到的目的,支持rotate table日志记录

将审计日志,慢SQL,auto_explain日志,错误日志记录特殊的数据表,

该表不记录redo,使用APPEND ONLY方式,

该表保持一定记录条数,或大小限制,可以通过GUC配置记录数和SIZE.

好处,方便用户查询,方便生成诊断报告。

全局报告样本

PostgreSQL AWR 全局报告样本

库级报告样本

PostgreSQL AWR 库级报告样本

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
22 PostgreSQL 监控3PostgreSQL 性能快照和图形化分析工具 pg_stats_info 的使用|学习笔记
快速学习22 PostgreSQL 监控3PostgreSQL 性能快照和图形化分析工具 pg_stats_info 的使用
0 0
如何生成和阅读EnterpriseDB (PPAS)诊断报告
PPAS是基于PostgreSQL的高度兼容Oracle的商业产品。 不仅语法和Oracle兼容,功能也和Oracle很类似。 例如它也支持生成类似statspack或者AWR报告。 如何创建快照 配置参数timed_statistics=true或者在客户端会话中设置timed
3196 0
PostgreSQL 9.6 支持等待事件统计了
PostgreSQL 9.6 统计信息收集进程pgstat,增加了等待事件信息的收集,并且用户可以获得backend的等待事件信息。 目前支持的等待事件分类如下src/include/pgstat.h /* ---------- * Wait Classes * --------
2493 0
PostgreSQL Greenplum crash 后临时表引发的BUG - 暨年龄监控的重要性
PostgreSQL 和 Greenplum都支持临时表。在使用临时表时,如果数据库crash,临时表不会自动清除,这样可能会埋下隐患,隐患爆发时是非常危险的。问题在哪呢?因为vacuum freeze不处理其他会话创建的临时表,仅仅处理当前会话创建的临时表。也就是说,没有被清理的临时表,可能导致.
4879 0
AWR概述
整理自《OCA/OCP认证考试指南》 000 概述     Oracle收集大量有关性能和活动的统计信息。这些信息在内存中累积,并定期写入数据库:写入到构成自动工作负荷知识库(automatic workload repository,AWR)的表中。
869 0
【Oracle】如何查看sql 执行计划的历史变更
    今天中午,突然接收到active session 数目飙高的报警,查看数据库,对于一个OLTP 类型的查询本应该走index range scan 却变成全部是 direct path read ,所有的sql 走了全表扫描。
924 0
AWR 简介
原文转自:http://blog.csdn.net/tianlesoftware/article/details/4682300 一. AWR 说明             Oracle 10g之前对数据库做性能检测使用statspack工具。
1037 0
ORACLE AWR报告数据的导入导出实践
关于AWR的快照数据可以导出、导入,一直没有亲手实践过。今天动手测试了一下如何导出、导入AWR数据,将AWR的数据从一测试服务器,导入到另外一台测试服务器。   SQL> @?/rdbms/admin/awrextr.
1003 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_赵振平_PostgreSQL和Greenplum数据库故障排查
立即下载
PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
立即下载
Oracle 至PostgreSQL案例分享
立即下载