PolarDB:pg_profile

Prerequisites

The feature is supported on PolarDB for PostgreSQL clusters that run the following engine:

PostgreSQL 14 (revision version 14.10.18.0 or later)

SELECT version();

Precautions

• The pg_stat_statements and pg_stat_kcache extensions are required to collect data. Make sure that these extensions are added to the shared_preload_libraries parameter of the database from which you want to collect data so that these extensions are loaded by default.

  Note

  For more information about how to configure shared_preload_libraries, see Specify cluster parameters. The cluster restarts after you modify this parameter. Proceed with caution.

• The pg_stat_statements extension is created in the database from which you want to collect data. Optionally, you can create the pg_stat_kcache extension in the databases to collect more information.

• The storage of collected information occupies disk space. The pg_profile extension provides an automatic data cleanup mechanism. Make sure that your data is properly stored.

• It requires a long period of time for the pg_profile extension to collect statistics. We recommend that you set the collection frequency to once or twice every hour.

Usage

A server database and a profile database are created in the following example.

During sampling, the profile database sends sampling requests to the server database. After receiving the request, the server database returns the statistical results to the profile database and stores the data in a table. This allows you to obtain statistics for other databases from the profile database.

Server database

Create the server database:

CREATE DATABASE server;

Connect to the server database and create the extensions for collecting statistics:

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

Profile database

Create the profile database:

CREATE DATABASE profile;

Connect to the profile database and create the extension:

\c profile
CREATE EXTENSION IF NOT EXISTS pg_profile CASCADE;

Create a sampling connection

SELECT create_server(
   'server',
   'dbname=server channel_name=localhost user=<The privileged account of the server database> password=<The password of the account>'
);

The following table describes the parameters in the statement.

Sample results:

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

Modify the sampling connection

SELECT set_server_connstr(
   'server',
   'dbname=server channel_name=localhost user=<The privileged account of the profile database> password=<The password of the account>'
);

The following table describes the parameters in the statement.

Initial data collection

SELECT take_sample();

Sample results:

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

Collect data after a stress test is performed on the database

Use pgbench to perform a stress test on the sampled server database.

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

The following table describes the parameters in the statement.

Connect to the profile database to collect data again:

SELECT take_sample();

View the collected data:

SELECT * FROM show_samples('server');

Sample results:

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               |               |                 |

Generate reports based on the results

You can use one of the following methods to export the results:

• Method 1: Execute the following SQL statements in the psql interactive command-line prompt of the client tool to export the results to a file:

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

• Method 2: Run the psql command to directly export the results to a file.

  psql -Aqtc "SELECT get_report('server',1,2)" \
     -o report_server_1_2.html -d profile \
     -h <The endpoint of the cluster in which the profile database is created> \
     -p <The port of the cluster> \
     -U <The privileged account of the cluster in which the profile database is created>