ApsaraDB RDS:Use the pg_profile extension to collect statistics on resource-intensive activities

Extension description

This extension is based on PostgreSQL statistics views (pg_stat_statements) and community extensions (pg_stat_kcache). It is entirely written in PL/pgSQL and does not require any external libraries or software. When used with the pg_cron extension, it can collect statistics on resource-intensive activities.

Before you begin

Notes

• This extension requires the pg_stat_statements extension to be enabled first. You can also enable the pg_stat_kcache extension to collect more information.

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

Usage

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 the server database.

   CREATE DATABASE server;

2. Connect to the server database and create the 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. Both extensions need to be loaded when the database starts. Make sure that pg_stat_statements and pg_stat_kcache are added to the shared_preload_libraries parameter. For information about how to modify the shared_preload_libraries parameter, see Set parameters.

3. Run the \dx command to view the details of the created extensions.

           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 the profile database.

   CREATE DATABASE profile;

2. Connect to the profile database and create the extensions.

   \c profile
   CREATE EXTENSION plpgsql;
   CREATE EXTENSION dblink;
   CREATE EXTENSION pg_profile;

3. Run the \dx command to view the details of the created extensions.

       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. Create a server connection.

   SELECT create_server('server', 'host=<endpoint of the RDS instance on which the server database is created> dbname=server port=<port number> user=<privileged account of the server database> password=<password of the account>');

   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 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: The RDS instance on which the profile database is created must be configured with a NAT gateway and attached to a public IP address. The RDS instance on which the server database is created must have a public endpoint. Set this parameter to the public endpoint of the RDS instance on which the server database is created. Note For information about how to obtain the internal and public endpoints of an RDS instance, see View and modify the endpoint and port number of an 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 obtain this parameter by running the SHOW PORT; SQL command. 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 account.

   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 you create a server connection, a connection to the profile database is automatically created. This connection is named local in the preceding sample output.

5. Modify the connection information of the profile database and configure a password.

   SELECT set_server_connstr('local','host=127.0.0.1 dbname=profile port=3002 user=<privileged account of the profile database> password=<password of the account>');

   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 obtain this parameter by running the SHOW PORT; SQL command.
   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 account.

6. Collect data for the first time.

   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 data 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 about how to use this command, see the PostgreSQL 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 of the instance.

   2. Connect to the RDS instance on which the profile database is created and collect data again.

      SELECT take_sample();

   3. View the collected data.

      SELECT * FROM show_samples('server');

      The following result is returned.

       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 from 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 <privileged account of the RDS instance on which the profile database is created>