PolarDB:pg_profile (Resource Monitoring)

Step 1: Create the server database

Connect to your cluster and create the server database:

CREATE DATABASE server;

Connect to the server database and install the statistics extensions:

\c server
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;

pg_stat_kcache is optional. Omit it if you do not need the additional metrics it provides in your reports.

Step 2: Create the profile database

Create the profile database:

CREATE DATABASE profile;

Connect to the profile database and install pg_profile:

\c profile
CREATE EXTENSION IF NOT EXISTS pg_profile CASCADE;

Step 3: Create a sampling connection

Run the following from the profile database to register the server database as a monitoring target:

SELECT create_server(
   'server',
   'dbname=server channel_name=localhost user=<privileged_account_of_the_server_database> password=<password_for_the_account>'
);

The expected output is similar to:

                                    show_servers
-------------------------------------------------------------------------------------
 (server,"dbname=server channel_name=localhost user=test_user password=****",t,)
(1 row)

To update the connection information later, use set_server_connstr():

SELECT set_server_connstr(
   'server',
   'dbname=server channel_name=localhost user=<privileged_account_of_the_profile_database> password=<password_for_the_account>'
);

Do not change the first argument (server). It identifies the existing connection by name.

Step 4: Take an initial sample

Run the following from the profile database to take the first sample:

SELECT take_sample();

The output has three columns: the server name, the result (OK or an error message), and the elapsed time:

      take_sample
-------------------------
 (server,OK,00:00:02.81)
(1 row)

Step 5: Generate a workload and take a second sample

To capture a meaningful workload difference between samples, run a stress test on the server database. The following example uses pgbench:

pgbench -s 100 -i server -U test_user -h pc-****.pg.rds.aliyuncs.com -p 5432

After the test completes, take a second sample from the profile database:

SELECT take_sample();

View all collected samples:

SELECT * FROM show_samples('server');

The expected output is similar to:

 sample |      sample_time       | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+------------------------+-----------------+---------------+-----------------+-----------------
      1 | 2024-02-23 07:29:53+00 | t               |               |                 |
      2 | 2024-02-23 08:11:39+00 | t               |               |                 |

Step 6: Generate a report

Export the data between two samples to an HTML report using one of the following methods.

Method 1: Use the psql interactive command line to write the output to a local file.

\o report_1_2.html
SELECT get_report('server',1,2);

Method 2: Run psql from the command line to write the output directly to a file.

psql -Aqtc "SELECT get_report('server',1,2)" \
   -o report_server_1_2.html -d profile \
   -h <endpoint_of_the_profile_database_cluster> \
   -p <port_of_the_database_cluster> \
   -U <privileged_account_for_the_profile_database_cluster>

Replace the placeholders with the actual values for your profile database cluster.