ApsaraDB RDS:Use the pg_profile extension

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, and Use the pg_cron extension.

Prerequisites

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.

• 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.

server database

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. These extensions must be loaded when the database starts. Make sure that pg_stat_statements and pg_stat_kcache are added to the value of the shared_preload_libraries parameter. For more information, see Use the pg_stat_kcache extension and pg_stat_statements. For more information about how to modify 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
    ...

profile database

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>');

   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. 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 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>');

   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.

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

      Parameter	Example	Description
      -s	100	The 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 dbname	server	The name of the database on which you want to perform stress testing.
      -U	test_user	The username of the privileged account for the RDS instance on which the server database is created.
      -h	pgm-****.pg.rds.aliyuncs.com	The endpoint of the RDS instance on which the server database is created.
      -p	5432	The 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>