Using pg_profile_pro

Scenarios

pg_profile_pro is a plugin used to monitor the performance and status of RDS for PostgreSQL DB instances. It provides monitoring data reports for SQL statements, tables, indexes, functions, transactions, and vacuum operations to detect existing or potential performance problems of databases.

Based on the pg_stat_statements view of PostgreSQL, this plugin creates historical statistics in your DB instance and generates statistics samples. Periodic statistics samples are used to generate monitoring reports, helping identify resource-consuming activities.

This plugin is not supported temporarily due to its defects. It will be open to you after the defects are rectified. We are sorry for any inconvenience caused.

Constraints

Only RDS for PostgreSQL 12 supports this plugin.

Procedure

1. Run the following command to connect to the postgres database as user root and obtain the sample list.

   # psql --host=<RDS_ADDRESS> --port=<DB_PORT>--dbname=postgres --username=root -c "select * from profile.show_samples();"

   Table 1 Parameter description

   Parameter	Description
   RDS_ADDRESS	Indicates the IP address of the RDS DB instance.
   DB_PORT	Indicates the port of the RDS DB instance.

   Enter the password of user root when prompted.

   Password for user root:

   Information similar to the following is displayed:

    sample |      sample_time       | dbstats_reset | clustats_reset | archstats_reset
   --------+------------------------+---------------+----------------+-----------------
         1 | 2021-04-02 17:15:49+08 |               |                |
         2 | 2021-04-02 17:25:57+08 |               |                |
         3 | 2021-04-02 17:36:04+08 |               |                |
   (3 rows)

   Use the actual query result.

2. Connect to the postgres database as user root and obtain the report using either of the following methods:

   Method 1: Obtain the report based on the sample ID.

   # psql --host=<RDS_ADDRESS> --port=<DB_PORT> --dbname=postgres --username=root -Aqtc "select profile.get_report(<sample_start_id>, <sample_end_id>)" -o <filename>.html

   Table 2 Parameter description

   Parameter	Description
   RDS_ADDRESS	Indicates the IP address of the RDS DB instance.
   DB_PORT	Indicates the port of the RDS DB instance.
   sample_start_id	Indicates the start sample ID contained in the report.
   sample_end_id	Indicates the end sample ID contained in the report.
   filename	Indicates the name of the file where the report content is to be saved. You can specify a relative path or an absolute path for the file.

   

   The value of sample_start_id must be smaller than that of sample_end_id. Otherwise, the report cannot be generated.

   Method 2: Obtain the report by specifying a time period.

   # psql --host=<RDS_ADDRESS> --port=<DB_PORT> --dbname=postgres --username=root -Aqtc "select profile.get_report(tstzrange('sample_start_time', 'sample_end_time'))" -o <filename>.html

   Table 3 Parameter description

   Parameter	Description
   RDS_ADDRESS	Indicates the IP address of the RDS DB instance.
   DB_PORT	Indicates the port of the RDS DB instance.
   sample_start_time	Indicates the sample start time contained in the report.
   sample_end_time	Indicates the sample end time contained in the report.
   filename	Indicates the name of the file where the report content is to be saved. You can specify a relative path or an absolute path for the file.

   

   Retain the default values of the following parameters:

   • Sampling period (unit: minute): 10
   • Sample retention period (unit: day): 7
   • Number of displayed records: 20

Parameter Configuration

The default values in the table are for the latest RDS version and may vary in other versions.

Report Example

A report consists of title, contents, and tables.

• Title

  The title contains start and end sample IDs, pg_profile_pro kernel version, server name, and report interval.

  Figure 1 Report title
  
• Contents

  Each layer of contents is linked to a table. You can get an overview of all tables from the contents or click the links to view each table.

  The following figure shows a contents example.

  

• Tables

  The tables display the database performance from different dimensions and focuses.

  • Each table is followed by suggestions on the focuses.
  • In each table, you can move the mouse pointer to a column title to view the comments (both in Chinese and English).