pg_profile_pro
Scenarios
pg_profile_pro is an extension 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 extension 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.
![](https://support.huaweicloud.com/intl/en-us/usermanual-rds/public_sys-resources/caution_3.0-en-us.png)
This extension 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 extension.
Procedure
- 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.
- 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>.htmlTable 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
Parameter |
Default Value |
Restart Required |
Function |
Remarks |
---|---|---|---|---|
pg_profile_pro.topn |
20 |
No |
Controls the number of objects (such as statements and relationships) to be reported in each sorting report. |
This parameter affects the sample size. If you want to display more objects in the report, more objects need to be retained in the sample. |
pg_profile_pro.max_sample_age |
3 |
No |
Indicates the sample retention period, in days. Samples whose retention period has exceeded the value of this parameter will be automatically deleted during the next sampling. |
The minimum parameter granularity is day. |
pg_profile_pro.track_sample_timings |
off |
No |
Controls whether pg_profile_pro traces the detailed sampling time. |
After this parameter is set to on, the time consumed by each sampling is recorded, which increases the space usage. |
pg_profile_pro.period |
3600 |
No |
Indicates the sampling period, in seconds. Controls the sampling period. |
This parameter affects the sample size. The shorter the sampling period is, the more objects the report reserves due to more samples in unit time. |
pg_profile_pro.enable |
on |
No |
Controls whether the extension collects samples.
|
If this parameter is set to off, the latest report cannot be generated, but the historical report can be viewed. |
![](https://support.huaweicloud.com/intl/en-us/usermanual-rds/public_sys-resources/note_3.0-en-us.png)
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).
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot