pg_profile 拡張機能の使用方法、注意事項、および例 - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL は、データベース内のリソース消費量の多いアクティビティの統計を収集し、データベースを分析および最適化するための pg_profile 拡張機能を提供します。

拡張機能の説明

この拡張機能は、PostgreSQL 統計ビュー（pg_stat_statements）とコミュニティプラグイン（pg_stat_kcache）に基づいています。これは完全に PL/pgSQL で記述されており、外部ライブラリやソフトウェアは必要ありません。pg_cron プラグインと併用すると、リソース消費量の多いアクティビティの統計を収集できます。

始める前に

RDS インスタンスは、次の要件を満たしている必要があります。

RDS インスタンスで PostgreSQL 10 以降が実行されている。
説明
PostgreSQL 17 はまだサポートされていません。
RDS インスタンスで 20230830 以降のマイナーエンジンバージョンが実行されている。
重要
特権アカウントが作成されている。

注意事項

このプラグインを使用するには、最初に pg_stat_statements プラグインを有効にする必要があります。また、pg_stat_kcache プラグインを有効にして、より多くの情報を収集することもできます。
収集された情報の保存にはディスク容量が使用されます。 pg_profile 拡張機能には、自動データクリーンアップメカニズムが用意されています。データが適切に保存されていることを確認してください。
pg_profile 拡張機能が統計を収集するには、長い時間がかかります。収集頻度は 1 時間に 1 ～ 2 回に設定することをお勧めします。

拡張機能の作成と削除

説明

このセクションで提供されている文を実行するには、特権アカウントを使用することをお勧めします。

拡張機能を作成する
```
CREATE EXTENSION pg_profile;
```
拡張機能を削除する
```
DROP EXTENSION pg_profile;
```

使用方法

この例では、server および profile という名前のデータベースが作成されます。

データ収集中、profile データベースは server データベースにリクエストを送信します。 server データベースはリクエストを受信すると、自身の情報を収集し、収集結果を profile データベースに返します。 profile データベースは、返された結果をテーブルに保存します。このようにして、profile データベースから他のデータベースに関する情報を取得できます。

説明

profile データベースと server データベースは、異なる RDS インスタンスまたは同じ RDS インスタンスに作成できます。

Server データベース

Server データベースを作成します。
```
CREATE DATABASE server;
```
Server データベースに接続し、拡張機能を作成します。
```
\c server
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;
```
説明
pg_stat_kcache 拡張機能は、pg_stat_statements 拡張機能に依存しています。データベースの起動時に両方の拡張機能を読み込む必要があります。 pg_stat_statements と pg_stat_kcache が [shared_preload_libraries] パラメーターに追加されていることを確認してください。 [shared_preload_libraries] パラメーターの変更方法については、「パラメーターの設定」をご参照ください。

\dx コマンドを実行して、作成された拡張機能の詳細を表示します。

        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 データベース

Profile データベースを作成します。
```
CREATE DATABASE profile;
```

Profile データベースに接続し、拡張機能を作成します。

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

\dx コマンドを実行して、作成された拡張機能の詳細を表示します。

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

サーバー接続を作成します。

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

パラメーター	例	説明
server	server	接続の名前。カスタム名を使用できます。
host	127.0.0.1	server データベースが作成されている RDS インスタンスのエンドポイント。 profile データベースと server データベースが同じ RDS インスタンスに作成されている場合は、このパラメーターを `127.0.0.1` に設定します。 profile データベースと server データベースが同じ VPC 内の異なる RDS インスタンスに作成されている場合は、このパラメーターを server データベースが作成されている RDS インスタンスの内部エンドポイントに設定します。 profile データベースと server データベースが異なる VPC 内の異なる RDS インスタンスに作成されている場合： profile データベースが作成されている RDS インスタンスは、NAT ゲートウェイで構成され、パブリック IP アドレスに関連付けられている必要があります。 server データベースが作成されている RDS インスタンスには、パブリックエンドポイントが必要です。このパラメーターを server データベースが作成されている RDS インスタンスのパブリックエンドポイントに設定します。説明 RDS インスタンスの内部エンドポイントとパブリックエンドポイントを取得する方法については、「インスタンスのエンドポイントとポート番号の表示と変更」をご参照ください。
dbname	server	データベースの名前。
port	3002	server データベースが作成されている RDS インスタンスのポート番号。 profile データベースと server データベースが同じ RDS インスタンスに作成されている場合は、`SHOW PORT;` SQL コマンドを実行することで、このパラメーターを取得できます。 profile データベースと server データベースが同じ VPC 内の異なる RDS インスタンスに作成されている場合は、このパラメーターを server データベースが作成されている RDS インスタンスの内部ポート番号に設定します。 profile データベースと server データベースが異なる VPC 内の異なる RDS インスタンスに作成されている場合は、このパラメーターを server データベースが作成されている RDS インスタンスのパブリックポート番号に設定します。
user	test_user	server データベースが作成されている RDS インスタンスの特権アカウントのユーザー名。
password	T123456!	アカウントのパスワード。

出力例：

                                     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)

説明

サーバー接続を作成すると、profile データベースへの接続が自動的に作成されます。前の出力例では、この接続の名前は local です。

profile データベースの接続情報を変更し、パスワードを設定します。

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

パラメーター	例	説明
server	local	接続の名前。名前を変更する必要はありません。
host	127.0.0.1	このパラメーターを 127.0.0.1 に設定します。
dbname	profile	データベースの名前。
port	3002	`SHOW PORT;` SQL コマンドを実行することで、このパラメーターを取得できます。
user	test_user	profile データベースが作成されている RDS インスタンスの特権アカウントのユーザー名。
password	T123456!	アカウントのパスワード。

初めてデータを収集します。

SELECT take_sample();

出力例：

       take_sample
-------------------------
 (server,OK,00:00:00.52)
 (local,OK,00:00:00.51)
(2 rows)

server データベースでストレステストを実行し、再度データを収集します。

CLI で次のコマンドを実行して、server データベースでストレステストを実行します。

説明

次のコマンドは CLI で実行する必要があります。 PostgreSQL クライアントと pgbench がインストールされていることを確認してください。 pgbench は、PostgreSQL でベンチマークテストを実行するシンプルなプログラムです。このコマンドの使用方法については、PostgreSQL ドキュメントをご参照ください。

pgbench -s 100 -i server -U test_user -h pgm-****.pg.rds.aliyuncs.com -p 5432

パラメーター	例	説明
-s	100	作成するデータの行数。たとえば、値 100 は、pgbench_accounts テーブルに 10,000,000 行のデータが作成されることを示します。
-i dbname	server	ストレステストを実行するデータベースの名前。
-U	test_user	server データベースが作成されている RDS インスタンスの特権アカウントのユーザー名。
-h	pgm-****.pg.rds.aliyuncs.com	server データベースが作成されている RDS インスタンスのエンドポイント。
-p	5432	インスタンスのポート。

profile データベースが作成されている RDS インスタンスに接続し、再度データを収集します。
```
SELECT take_sample();
```

収集されたデータを表示します。

SELECT * FROM show_samples('server');

次の結果が返されます。

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

収集結果からレポートを生成します。
次のいずれかの方法を使用して、収集結果をコンピューターにエクスポートできます。
- profile データベースの psql で次のコマンドを実行します。
```
\o report_2_3.html
SELECT get_report('server',2,3);
```
- コンピューターの CLI で次のコマンドを実行します。
```
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> 
```