PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)

背景

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

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

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

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

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

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

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

一、用法

1. 创建插件

create extension pg_awr;  

2. 打快照(全局)

select __rds_pg_stats__.snap_global();    --  给全局打快照（注意包含的是全局信息，并不是每个库的快照信息）。  

3. 打快照(库级)

select __rds_pg_stats__.snap_database();  --  给当前数据库打快照。  

4. 查看快照清单

select * from __rds_pg_stats__.snap_list;  

5. 指定快照起始ID，生成全局报告(text模式)

select * from __rds_pg_stats__.snap_report_global($begin_snap_id, $end_snap_id);  

6. 指定快照起始ID，生成库级报告(text模式)

select * from __rds_pg_stats__.snap_report_database($begin_snap_id, $end_snap_id);  

7. 指定快照起始ID，生成全局报告(md模式)

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

8. 指定快照起始ID，生成库级报告(md模式)

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

9. 解读报告(略, 报告内自带推荐建议)

10. 清除历史快照

10.1 删除指定snap_ID以前的快照。

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

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

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

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

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

二、接口介绍

1. 快照列表

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

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

3. 创建快照

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

全局的在哪里创建(首先创建extension pg_awr;)都可以，但是只需要创建一次就够了，而库级的需要连接到需要分析库去创建快照。

select __rds_pg_stats__.snap_database();  -- 库级快照  
  
select __rds_pg_stats__.snap_global();    -- 全局快照  

三、部署快照功能(非RDS，自己部署的话)

1 修改配置文件

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

$ vi postgresql.conf   
  
shared_preload_libraries='pg_stat_statements'   
  
$ pg_ctl restart -m fast   

2 初始化

在需要打快照的库都装上这个SQL，里面包含了(创建打快照的function, 创建清理快照的function, 创建生成报告的function)。

init.sql

四、其他

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

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

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

issue

1. 两个快照之间的WAL产生量。

digoal’s 大量PostgreSQL文章入口