ApsaraDB RDS:Use the pg_profile extension to collect statistics on resource-intensive activities

Set up the server database

Run the following statements using a privileged account.

1. Create the server database.

   CREATE DATABASE server;

2. Connect to the server database and install the required extensions.

   Both pg_stat_statements and pg_stat_kcache must be loaded at database startup. Add both to the shared_preload_libraries parameter before creating these extensions. pg_stat_kcache depends on pg_stat_statements, so both must be present in shared_preload_libraries. For instructions, see Set parameters.

   \c server
   CREATE EXTENSION pg_stat_statements;
   CREATE EXTENSION pg_stat_kcache;

3. Verify the extensions are installed.

   \dx

   Expected output:

   Name                | Version |   Schema   |                              Description
   --------------------+---------+------------+------------------------------------------------------------------------
    pg_stat_kcache     | 2.2.1   | public     | Kernel statistics gathering
    pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
    ...

Set up the profile database

Run the following statements using a privileged account.

1. Create the profile database.

   CREATE DATABASE profile;

2. Connect to the profile database and install the required extensions.

   \c profile
   CREATE EXTENSION plpgsql;
   CREATE EXTENSION dblink;
   CREATE EXTENSION pg_profile;

3. Verify the extensions are installed.

   \dx

   Expected output:

   Name        | Version |   Schema   |                         Description
   ------------+---------+------------+--------------------------------------------------------------
    dblink     | 1.2     | public     | connect to other PostgreSQL databases from within a database
    pg_profile | 4.0     | public     | PostgreSQL load profile repository and report builder
    plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
    ...

Register the server database connection

From the profile database, register the server database as a monitoring target. Run the following statement using a privileged account.

SELECT create_server('server', 'host=<host> dbname=server port=<port> user=<user> password=<password>');

`host` values by network topology:

To get the internal and public endpoints of an instance, see View and modify the endpoint and port number of an instance.

Expected output:

show_servers
---------------------------------------------------------------------------------------
 (local,"dbname=profile port=3002",t,)
 (server,"host=127.0.0.1 dbname=server port=3002 user=test_user password=****",t,)
(2 rows)

Creating a server connection also creates a local connection representing the profile database itself. You can see it in the output above.

Configure the profile database connection

After the local connection is created, configure its credentials. Run the following statement using a privileged account.

SELECT set_server_connstr('local','host=127.0.0.1 dbname=profile port=<port> user=<user> password=<password>');

Collect a snapshot

From the profile database, run take_sample() to collect a snapshot from all registered servers. Use a privileged account.

SELECT take_sample();

Expected output:

 take_sample
-------------------------
 (server,OK,00:00:00.52)
 (local,OK,00:00:00.51)
(2 rows)

Collect at least two snapshots before generating a report—the report covers the activity between two snapshot points.

View collected snapshots

SELECT * FROM show_samples('server');

Example output:

 sample |      sample_time       | sizes_collected | dbstats_reset | bgwrstats_reset | archstats_reset
--------+------------------------+-----------------+---------------+-----------------+-----------------
      1 | 2022-09-30 02:14:07+00 | t               |               |                 |
      2 | 2022-09-30 02:16:59+00 | t               |               |                 |
      3 | 2022-09-30 02:17:58+00 | t               |               |                 |

Generate a report

A report covers the activity between two snapshots—specify the start and end sample numbers.

Option 1: From psql in the profile database

\o report_2_3.html
SELECT get_report('server',2,3);

Option 2: From the command-line interface (CLI)

psql -Aqtc "SELECT profile.get_report('server',2,3)" \
  -o report_server_2_3.html \
  -d profile \
  -h <endpoint of the profile database instance> \
  -p 5432 \
  -U <privileged account of the profile database instance>

Both methods export the report as an HTML file to your local machine.