PostgreSQL AWR报告-阿里云开发者社区

开发者社区> 德哥> 正文

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 库级报告样本

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

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
4014 0
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
6367 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
2179 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
7586 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
5688 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
4964 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
3810 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
724 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
16285 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载