ApsaraDB RDS for PostgreSQL provides the pg_profile extension for you to collect statistics on resource-intensive activities in a database and analyze and optimize the database.
Extension description
The pg_profile extension is developed based on the pg_stat_statements view and the pg_stat_kcache extension of PostgreSQL. The pg_profile extension is written in PL/pgSQL and requires no external libraries or software. You can use the pg_profile extension together with the pg_cron extension to collect statistics on resource-intensive activities. For more information, see pg_stat_statements, Use the pg_stat_kcache extension to collect statistics on system read and write operations, and Use the pg_cron extension to configure scheduled tasks.
Prerequisites
- The RDS instance runs PostgreSQL 10 or later.
- The minor engine version of the RDS instance is 20230330 or later. For more information about how to view and update the minor engine version of an RDS instance, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance. Important This extension is supported in minor engine versions that are earlier than 20230330. However, the extensions that are supported for ApsaraDB RDS for PostgreSQL instances are changed. Starting April 17, 2023, some extensions can no longer be created for RDS instances that run minor engine versions earlier than 20230330. For more information, see [Notice] Starting April 17, 2023, some extensions can no longer be created for ApsaraDB RDS for PostgreSQL instances that run earlier minor engine versions.
- If you have created this extension for your RDS instance that runs a minor engine version earlier than 20230330, the extension is not affected.
- If this is the first time you create this extension for your RDS instance or re-create the extension, you must update the minor engine version of the RDS instance to 20230330 or later. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
- A privileged account is created. For more information, see Create an account on an ApsaraDB RDS for PostgreSQL instance.
Usage notes
- Before you can use the pg_profile extension, you must enable the pg_stat_statements extension. You can also enable the pg_stat_kcache extension to use more information collection features. For more information, see pg_stat_statements and Use the pg_stat_kcache extension to collect statistics on system read and write operations.
- 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.
Create and delete the extension
- Create the extension.
CREATE EXTENSION pg_profile;
- Delete the extension.
DROP EXTENSION pg_profile;
Examples
In this example, databases named server and profile are created.
During data collection, the profile database sends a request to the server database. After the server database receives the request, the server database collects its own information and returns the collection results to the profile database. The profile database stores the returned results in a table. This way, you can obtain information about other databases from the profile database.
Database server
- Create a database named server.
CREATE DATABASE server;
- Connect to the server database and create extensions.
\c server CREATE EXTENSION pg_stat_statements; CREATE EXTENSION pg_stat_kcache;
Note The pg_stat_kcache extension depends on the pg_stat_statements extension. When the database starts, the extensions are loaded. Make sure that the names of thepg_stat_statements
andpg_stat_kcache
extensions are added to the value of the shared_preload_libraries parameter. For more information, see Use the pg_stat_kcache extension to collect statistics on system read and write operations and pg_stat_statements. For more information about how to configure the shared_preload_libraries parameter, see Manage the parameters of an ApsaraDB RDS for PostgreSQL instance. - Run the
\dx
command to query creation details.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 ...
Database profile
- Create a database named profile.
CREATE DATABASE profile;
- Connect to the profile database and create extensions.
\c profile CREATE EXTENSION plpgsql; CREATE EXTENSION dblink; CREATE EXTENSION pg_profile;
- Run the
\dx
command to query creation details.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 ...
- Establish a connection to the server database.
SELECT create_server('server', 'host=<Endpoint of the RDS instance in which the server database resides> dbname=server port=<Port number> user=<Username of the privileged account for the server database> password=<Password of the privileged account for the server database>');
Parameter Example Description server server The name of the connection. You can use a custom name. host 127.0.0.1 The endpoint of the RDS instance on which the server database is created. - If the profile database and the server database are created on the same RDS instance, set this parameter to
127.0.0.1
. - If the profile database and the server database are created on different RDS instances in the same virtual private cloud (VPC), set this parameter to the internal endpoint of the RDS instance on which the server database is created.
- If the profile database and the server database are created on different RDS instances in different VPCs, take note of the following items:
- Configure a NAT gateway for the RDS instance on which the profile database is created and associate a public IP address with the NAT gateway. For more information, see Use an ApsaraDB RDS for PostgreSQL instance to access an external database over the Internet.
- Apply for a public endpoint for the RDS instance on which the server database is created and set this parameter to the public endpoint. For more information, see Apply for or release a public endpoint on an ApsaraDB RDS for PostgreSQL instance.
Note For more information about how to obtain the internal and public endpoints of an RDS instance, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.dbname server The name of the database. port 3002 The port number of the RDS instance on which the server database is created. - If the profile database and the server database are created on the same RDS instance, you can execute the
SHOW PORT;
statement to obtain the value of this parameter. - If the profile database and the server database are created on different RDS instances in the same VPC, set this parameter to the internal port number of the RDS instance on which the server database is created.
- If the profile database and the server database are created on different RDS instances in different VPCs, set this parameter to the public port number of the RDS instance on which the server database is created.
user test_user The username of the privileged account for the RDS instance on which the server database is created. password T123456! The password of the privileged account for the RDS instance on which the server database is created. Sample 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)
Note After the connection to the server database is established, a connection to the profile database is automatically established, such as the connection namedlocal
in the sample output. - If the profile database and the server database are created on the same RDS instance, set this parameter to
- Modify the information about the connection to the profile database and specify a password.
SELECT set_server_connstr('local','host=127.0.0.1 dbname=profile port=3002 user=<Username of the privileged account for the profile database> password=<Password of the privileged account for the profile database>');
Parameter Example Description server local The name of the connection. You do not need to change the name. host 127.0.0.1 Set this parameter to 127.0.0.1. dbname profile The name of the database. port 3002 You can execute the SHOW PORT;
statement to obtain the value of this parameter.user test_user The username of the privileged account for the RDS instance on which the profile database is created. password T123456! The password of the privileged account for the RDS instance on which the profile database is created. - Collect statistics.
SELECT take_sample();
Sample output:take_sample ------------------------- (server,OK,00:00:00.52) (local,OK,00:00:00.51) (2 rows)
- Perform stress testing on the server database and collect statistics again.
- Generate a report on the collection results.
You can use one of the following methods to export the collection results to your computer:
- Run the following commands in psql in the profile database:
\o report_2_3.html SELECT get_report('server',2,3);
- Run the following command in the CLI on your computer:
psql -Aqtc "SELECT profile.get_report('server',2,3)" -o report_server_2_3.html -d profile -h <Endpoint of the RDS instance on which the profile database is created> -p 5432 -U <Username of the privileged account for the RDS instance on which the profile database is created>
- Run the following commands in psql in the profile database: