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

Your RDS instance meets the following requirements:

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

Note We recommend that you use a privileged account to execute the statements that are provided in this section.
  • 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.

Note The profile database and the server databases can be created on different RDS instances or the same RDS instance.

Database server

  1. Create a database named server. 
    CREATE DATABASE server;
  2. 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 the pg_stat_statements and pg_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.
  3. 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

  1. Create a database named profile. 
    CREATE DATABASE profile;
  2. Connect to the profile database and create extensions. 
    \c profile
CREATE EXTENSION plpgsql;
CREATE EXTENSION dblink;
CREATE EXTENSION pg_profile;
  3. 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
 ...
  4. 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>');
    ParameterExampleDescription
    serverserverThe name of the connection. You can use a custom name.
    host127.0.0.1The 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:
    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.
    dbnameserverThe name of the database.
    port3002The 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.
    usertest_userThe username of the privileged account for the RDS instance on which the server database is created.
    passwordT123456!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 named local in the sample output.
  5. 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>');
    ParameterExampleDescription
    serverlocalThe name of the connection. You do not need to change the name.
    host127.0.0.1Set this parameter to 127.0.0.1.
    dbnameprofileThe name of the database.
    port3002You can execute the SHOW PORT; statement to obtain the value of this parameter.
    usertest_userThe username of the privileged account for the RDS instance on which the profile database is created.
    passwordT123456!The password of the privileged account for the RDS instance on which the profile database is created.
  6. Collect statistics. 
    SELECT take_sample();
    Sample output:
           take_sample
-------------------------
 (server,OK,00:00:00.52)
 (local,OK,00:00:00.51)
(2 rows)
  7. Perform stress testing on the server database and collect statistics again.
    1. Run the following command in the CLI to perform stress testing on the server database:
      Note The following command must be run in the CLI. Make sure that the PostgreSQL client and pgbench are installed. pgbench is a simple program that runs benchmark tests on PostgreSQL. For more information, see PostgreSQL official documentation. 
      pgbench -s 100 -i server -U test_user -h pgm-****.pg.rds.aliyuncs.com -p 5432
      ParameterExampleDescription
      -s100The number of rows of data that you want to create. For example, the value 100 indicates that 10,000,000 rows of data are created in the pgbench_accounts table.
      -i dbnameserverThe name of the database on which you want to perform stress testing.
      -Utest_userThe username of the privileged account for the RDS instance on which the server database is created.
      -hpgm-****.pg.rds.aliyuncs.comThe endpoint of the RDS instance on which the server database is created.
      -p5432The port that is used to connect to the RDS instance.
    2. Connect to the RDS instance on which the profile database is created and collect statistics again. 
      SELECT take_sample();
    3. View the details about the collected data. 
      SELECT * FROM show_samples('server');
      Sample collection results:
       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               |               |                 |
  8. 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>