PolarDB:pg_profile (Resource Monitoring)

Scope

The following versions of PolarDB for PostgreSQL are supported:

• PostgreSQL 16 (minor engine version 2.0.16.9.9.0 or later)

• PostgreSQL 15 (minor engine version 2.0.15.14.6.0 or later)

• PostgreSQL 14 (minor engine version 2.0.14.10.18.0 or later)

Notes

• Data collection requires the pg_stat_statements and pg_stat_kcache extensions. Ensure that they are added to the shared_preload_libraries parameter of the sampled database. They are loaded by default.

  Note

  For more information about how to modify the shared_preload_libraries parameter, see Set cluster parameters. Modifying this parameter restarts the cluster. Plan your business operations carefully before you proceed.

• In the sampled database, you must create the pg_stat_statements extension. You can also create the pg_stat_kcache extension to collect more information.

• Storing sample information consumes disk space. This extension has an automatic cleanup mechanism, but you must still manage data retention.

• A single sampling operation is time-consuming. Therefore, do not perform sampling too frequently. The recommended frequency is one to two times per hour.

Usage

The following steps describe how to create a server database and a profile database.

During sampling, the profile database sends a sampling request to the server database. After the server database receives the request, it returns its statistics to the profile database. The statistics are then stored in a table. This lets you retrieve 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 statistics extensions:

\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=<privileged_account_of_the_server_database> password=<password_for_the_account>'
);

The following table describes the parameters.

The following example shows the output:

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

Modify the sampling connection information

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

The following table describes the parameters.

Perform the initial data collection

SELECT take_sample();

The following example shows the output:

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

Collect data again after stress testing the sampled database

Use the pgbench tool to run 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.

Connect to the profile database and collect data again:

SELECT take_sample();

View the collected data:

SELECT * FROM show_samples('server');

The following example shows the output:

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 a report from the collected results

You can export the collected data to a local file using one of the following two methods.

• Method 1: Run the following SQL statement in the interactive command line of the psql client tool to generate the output file.

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

• Method 2: Use the command line to instruct psql to write the data 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>