If you have worked on oracle, then you must of aware of AWR report, which is used to generate workload performance report of the database for a specific duration.
But in postgres there is no inbuilt function to generate performance report. However there is an extension called pg_profile can be used to generate similar performance report.
In this article, we will explain how can we achieve that.
1. Download and install pg_profile
Download the extensions file from github link – > https://github.com/zubkov-andrei/pg_profile/releases
Copy to server and unzip to the below location.
[root@~]# cd /usr/edb/as12/share/extension/
[root@extension]# ls -ltr *gz
-rw-r--r-- 1 root root 186808 Jun 1 14:06 pg_profile--0.3.6.tar.gz
[root@extension]# tar xzf pg_profile--0.3.6.tar.gz --directory /usr/edb/as12/share/extension/
[root@extension]# ls -ltr
total 3928
-rw-r--r-- 1 root root 2259 Nov 24 2019 pldbgapi--unpackaged--1.1.sql
--Connect with super user and create the extension:
postgres=# CREATE EXTENSION pg_profile;
2. verify the parameter setting:
Make sure highlighted parameters are set as below.
postgres=# select name,setting from pg_settings where name like 'track%';
name | setting
---------------------------+---------
track_activities | on
track_activity_query_size | 1024
track_commit_timestamp | off
track_counts | on
track_functions | all
track_io_timing | on
(6 rows)
3. Create dependent extension:
pg_stat_statement extension:
For a better performance report with sql statement details, you need to have pg_stat_statement extension in your postgres setup.
Please use the link to know the steps to install pg_stat_statement extension
dblink:
Using pg_profile, you can generate performance report of remote server also. So you need to have dblink extension also.
So create the extension using below statement:
CREATE EXTENSION dblink;
4. Verify that all extensions are present now:
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+----------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
pg_profile | 0.3.6 | public | PostgreSQL load profile repository and report builder
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
(14 rows)
5. Verify server details:
By default local server will be added in the server list.
postgres=# select * from show_servers();
server_name | connstr | enabled | description
-------------+---------------------------+---------+-------------
local | dbname=postgres port=5444 | t |
(1 row)
6. Gather sample for local:
To generate a report, you need atleast two sample.
postgres=# select * from show_samples;
sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+-------------+-----------------+---------------+----------------+-----------------
(0 rows)
postgres=# select * from take_sample();
server | result | elapsed
--------+--------+-------------
local | OK | 00:00:00.48
(1 row)
postgres=# select * from show_samples;
sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+----------------+-----------------
1 | 01-JUN-22 14:13:23 +03:00 | t | | |
(1 row)
--- after doing some transaction.
postgres=# select * from take_sample();
server | result | elapsed
--------+--------+-------------
local | OK | 00:00:00.44
(1 row)
postgres=# select * from show_samples;
sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+----------------+-----------------
1 | 01-JUN-22 14:13:23 +03:00 | t | | |
2 | 01-JUN-22 14:13:56 +03:00 | t | | |
(2 rows)
7. Generate performance report:
1 and 2 are the sample id.
-bash-4.2$ psql -d postgres -Aqtc "SELECT get_report('local',1,2)" -o 1st_report.html
Below is the html performance file generated.
8. Creating performance report for remote servers:
If you want to generate report of postgres cluster on remote server, then you need to add that in server list .
First you need to add the server.
postgres=# SELECT create_server('elmtest','host=10.20.30.131 dbname=postgres port=5444');
create_server
---------------
2
(1 row)
Update the .pgpass file, with remote server details(ip,port,super user ,password)
-bash-4.2$ cat .pgpass
10.20.30.131:5444:*:enterprisedb:elm#912345
postgres=# select * from show_servers();
server_name | connstr | enabled | description
-------------+----------------------------------------------+---------+-------------
elmtest | host=10.20.30.131 dbname=postgres port=5444 | t |
local | dbname=postgres port=5444 | t |
(2 rows)
Now take sample
-- This will generate sample for all servers.
postgres=# select * from take_sample();
server | result | elapsed
---------+--------+-------------
elmtest | OK | 00:00:00.63
local | OK | 00:00:00.51
(2 rows)
-- If you need to generate sample for specific server, then pass that value in take_sample() like,
postgres=# select * from take_sample('elmtest');
server | result | elapsed
---------+--------+-------------
elmtest | OK | 00:00:00.63
(1 rows)
-- Show sample id for remote server elmtest:
postgres=# select * from show_samples('elmtest');
sample | sample_time | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+-----------------+-----------------
1 | 03-JUN-22 21:55:05 +03:00 | t | | |
(1 row)
--- Show sample id for all servers:
postgres=# select * from show_samples;
sample | sample_time | sizes_collected | dbstats_reset | clustats_reset | archstats_reset
--------+---------------------------+-----------------+---------------+----------------+-----------------
1 | 01-JUN-22 14:13:23 +03:00 | t | | |
2 | 01-JUN-22 14:13:56 +03:00 | t | | |
3 | 01-JUN-22 14:55:35 +03:00 | t | | |
4 | 01-JUN-22 22:41:08 +03:00 | t | | |
5 | 01-JUN-22 22:42:18 +03:00 | t | | |
6 | 03-JUN-22 19:02:35 +03:00 | t | | |
7 | 03-JUN-22 21:55:05 +03:00 | t | | |
(7 rows)
9.Setting retention period of samples:
We cannot keep the sample ids forever as it will consume storage space. So better to set retention period like 30 days/60 days.
So you can set max_sample_age for servers using below command.
- 30 means 30 days.
postgres=# select set_server_max_sample_age('elmtest',30);
set_server_max_sample_age
---------------------------
1
(1 row)
However there is another concept called baseline, i.e support you want to keep a specific range of sample for a specific duration , then you can create baseline as below.
NOTE- - Baseline retention overwrites the max_sample_age setting
-- Create baseline,
elmtest - server_name,
plm_base1 - baseline_name,
1 and 2 are start and end sample range.
30 - retention period.
postgres=# select create_baseline('elmtest','plm_base1',1,2,30);
create_baseline
-----------------
5
(1 row)
-- Show baselines:
postgres=# select * from show_baselines();
baseline | min_sample | max_sample | keep_until_time
----------+------------+------------+---------------------------
local2 | 1 | 4 | 01-JUL-22 22:44:44 +03:00
local | 1 | 3 | 05-JUN-22 15:15:39 +03:00
(2 rows)
postgres=# select * from show_baselines('elmtest');
baseline | min_sample | max_sample | keep_until_time
-----------+------------+------------+---------------------------
plm_base1 | 1 | 1 | 05-JUL-22 17:59:33 +03:00
(1 row)
-- Modify baseline retention period:
postgres=# select keep_baseline('elmtest','plm_base1',50);
keep_baseline
---------------
1
(1 row)
postgres=# select * from show_baselines('elmtest');
baseline | min_sample | max_sample | keep_until_time
-----------+------------+------------+---------------------------
plm_base1 | 1 | 1 | 25-JUL-22 18:00:52 +03:00
(1 row)
-- Drop a baseline:
postgres=# select drop_baseline('elmtest','plm_base1');
drop_baseline
---------------
1
(1 row)
REFERENCE – https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md
