March 6, 2024
Summary: The pg_profile extension helps you to find out most resource intensive activities in PostgreSQL databases.
Table of Contents
Introduction
If you have worked on oracle, then you must be aware of AWR report, which is used to generate workload performance report of the database for a specific duration.
But in PostgreSQL there are not built-in functions 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.
Installation
Download the extension files from the project’s release page.
1. Copy the tarball file to server and unzip to the below location.
$ tar xzf pg_profile--4.4.tar.gz --directory /usr/pgsql/share/extension/
Connect to database with a super user and create the extension:
CREATE EXTENSION pg_profile;
2. Verify the parameter setting. Make sure highlighted parameters are set as below.
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:
For a better performance report with sql statement details, you need to have pg_stat_statements extension in your PostgreSQL setup.
CREATE EXTENSION pg_stat_statements;
Using pg_profile, you can generate performance report of remote server also. So you need to have dblink extension also.
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 | 4.4 | public | PostgreSQL load profile repository and report builder
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
(14 rows)
Usage
1. Verify server details:
By default local server will be added in the server list.
# select * from show_servers();
server_name | connstr | enabled | description
-------------+---------------------------+---------+-------------
local | dbname=postgres port=5444 | t |
(1 row)
2. Gather sample for local server:
To generate a report, you need at least two samples.
# select * from take_sample();
server | result | elapsed
--------+--------+-------------
local | OK | 00:00:00.48
(1 row)
--- after doing some transaction.
# select * from take_sample();
server | result | elapsed
--------+--------+-------------
local | OK | 00:00:00.44
(1 row)
# 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)
3. Generate performance report:
1 and 2 are the sample id.
$ psql -d postgres -Aqtc "SELECT get_report('local', 1, 2)" -o 1st_report.html
Report examples:
4. Setting retention period of samples:
We cannot keep the samples 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.
# 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
# select create_baseline('elmtest', 'baseline_name', 1, 2, 30);
create_baseline
-----------------
5
(1 row)
-- Show baselines
# 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)