PostgreSQL AWR report
AWR must be known to Automatic Workload Repository who are familiar with Oracle. Typically, the output of a database over a certain period of time must be evaluated. From the dynamic view of the database and other statistical information documents, you can produce a statistical analysis report for that time.
It includes common analysis of waiting events, TOP SQL, TOP events, etc.
PostgreSQL is an open source product with almost Oracle-like functionality, and many research and reporting tools are available, such as pgstatsinfo, pgsnap, pgtop, pgfouine, ….. Quite numerous.
I don’t want to add too many resources, but based on my experience with PG, I have written a very basic and easy-to-use one. You don’t need a lot of plug-ins to add, just regularly take a snapshot. The use is quite comparable to AWR.
The main aim of this article is to convert a pre-written, reasonably complete inspection script into AWR with a SQL interface, which is easier to use and does not need to log in to the database host to obtain the report.
A new statistical information table will be introduced by PG in the future and I will continue to add it to this basic method.
I hope that together, everybody can use and strengthen, and you can give me any questions.
1. Usage
1. Create a plug-in
create extension pg_awr;
2. Take a snapshot (global)
select __rds_pg_stats__.snap_global(); — Take a snapshot of the global (note that it contains global information, not snapshot information of each library).
3. Take a snapshot (library level)
select __rds_pg_stats__.snap_database(); — Take a snapshot of the current database.
4. View the snapshot list
select * from __rds_pg_stats__.snap_list;
5. Specify the start ID of the snapshot and generate a global report (text mode)
select * from __rds_pg_stats__.snap_report_global($begin_snap_id, $end_snap_id);
6. Specify the start ID of the snapshot and generate a library-level report (text mode)
select * from __rds_pg_stats__.snap_report_database($begin_snap_id, $end_snap_id);
7. Specify the start ID of the snapshot and generate a global report (md mode)
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. Specify the start ID of the snapshot and generate a library-level report (md mode)
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. Interpretation of the report (omitted, the report comes with recommendations)
10. Clear historical snapshots
10.1 Delete the snapshot before the specified snap_ID.
select __rds_pg_stats__.snap_delete(10::int8); — Delete the previous snapshot of the specified SNAP ID
10.2 Delete snapshots before the specified time.
select __rds_pg_stats__.snap_delete(‘2019–11–23 12:00:00’); — Delete the snapshot before the specified time.
10.3 Keep the most recent snapshots, delete others.
select __rds_pg_stats__.snap_delete(10::int4); — Keep the most recent 10 snapshots, and delete others.
Example:
1. Snapshot list
In fact, it is a list of snapshots. Every time you click a snapshot, a new record will be added.
postgres=# select * from __rds_pg_stats__.snap_list;
id | snap_ts | snap_level
— — + — — — — — — — — — — — — — — + — — — — — —
1 | 2019–11–23 19:59:10.321282 | database
3 | 2019–11–23 22:29:55.139357 | global
4 | 2019–11–23 22:30:42.602292 | database
5 | 2019–11–23 22:30:42.602292 | database
6 | 2019–11–23 22:30:42.602292 | database
7 | 2019–11–23 22:29:55.139357 | global
8 | 2019–11–23 22:29:55.139357 | global
9 | 2019–11–23 22:29:55.139357 | global
10 | 2019–11–23 23:00:31.796333 | global
11 | 2019–11–23 22:29:55.139357 | global
12 | 2019–11–23 23:02:36.590308 | database
13 | 2019–11–23 23:03:51.727333 | global
14 | 2019–11–23 23:03:51.727333 | global
15 | 2019–11–23 23:03:51.727333 | global
16 | 2019–11–23 23:03:51.727333 | global
17 | 2019–11–23 23:03:51.727333 | global
18 | 2019–11–23 23:03:51.727333 | global
19 | 2019–11–23 23:03:51.727333 | global
20 | 2019–11–23 23:03:51.727333 | global
21 | 2019–11–23 23:02:36.590308 | database
22 | 2019–11–23 23:08:50.900675 | global
23 | 2019–11–23 23:08:53.153526 | global
24 | 2019–11–23 23:08:55.816379 | global
25 | 2019–11–23 23:09:11.242692 | database
26 | 2019–11–23 23:09:32.270733 | database
(25 rows)
Snapshot historical data table
When taking a snapshot, the statistics of the system will be recorded in these historical tables, and the data will be used when generating a diagnostic report based on the time period.
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. Build a snapshot
It is to create a snapshot, as the name implies. Here I have two kinds of snapshots, one is global and one is library-level.
It doesn’t matter where you create the global extension (first create pg awr;), but you only need to create it once, and in order to create a snapshot, the library level needs to be linked to the research library.
select __rds_pg_stats__.snap_database(); — library-level snapshot select __rds_pg_stats__.snap_global(); — global snapshot
I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.
