如何使用pg_profile外掛程式,有哪些注意事項和樣本 - ApsaraDB RDS

RDS PostgreSQL支援使用pg_profile外掛程式來統計資料庫中資源密集型活動，用於分析最佳化資料庫。

外掛程式簡介

此擴充基於PostgreSQL的統計視圖（pg_stat_statements）和社區外掛程式（pg_stat_kcache），完全使用PL/pgSQL編寫，無需任何外部庫或軟體，配合pg_cron外掛程式即可實現資源密集活動的統計。

前提條件

RDS PostgreSQL執行個體需滿足以下要求：

執行個體大版本為RDS PostgreSQL 10或以上版本。
說明
暫不支援RDS PostgreSQL 17。
執行個體核心小版本為20230830或以上。
重要
20230830核心小版本之前已支援此外掛程式，但為了規範外掛程式管理，提升RDS PostgreSQL在外掛程式側的安全防護，RDS計劃在核心版本迭代中陸續對部分存在安全風險的外掛程式進行最佳化，部分外掛程式在低核心小版本無法建立，更多資訊，請參見【產品/功能變更】RDS PostgreSQL限制建立外掛程式說明。
- 如果您的執行個體核心小版本低於20230830，且已經使用了此外掛程式，則不影響使用。
- 如果您首次建立或重新建立此外掛程式，請升級核心小版本到最新。
已建立高許可權帳號。

注意事項

該外掛程式必須先啟用pg_stat_statements外掛程式，同時可以啟用pg_stat_kcache外掛程式來提供更多資訊收集功能。
儲存採樣資訊將會佔用磁碟空間，該外掛程式有自動清理機制，請注意資料儲存。
一次採樣較為耗時，無需頻繁採樣，推薦頻率為每小時1~2次。

建立和刪除外掛程式

說明

推薦使用高許可權帳號可以執行如下命令。

建立外掛程式
```
CREATE EXTENSION pg_profile;
```
刪除外掛程式
```
DROP EXTENSION pg_profile;
```

使用樣本

本樣本需要分別建立server資料庫和profile資料庫。

在採樣時，profile資料庫會向server資料庫發出一個請求，server資料庫收到請求後採樣自己的資訊，再將採樣結果返回給profile，profile將收到的結果儲存在表中。這樣就可以從profile資料擷取其他資料庫的統計資訊。

說明

profile資料庫和server資料庫可以建立在不同RDS PostgreSQL執行個體中，也可建立在同一執行個體中。

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

建立server串連。

SELECT create_server('server', 'host=<server資料庫所在執行個體的串連地址> dbname=server port=<連接埠號碼> user=<server資料庫的高許可權帳號> password=<帳號對應的密碼>');

參數	樣本值	參數說明
server	server	串連名，自訂。
host	127.0.0.1	server資料庫所在執行個體的串連地址。如果profile資料庫與server資料庫在同一執行個體中，則此參數固定配置為`127.0.0.1`。如果profile資料庫與server資料庫在相同VPC下的不同執行個體中，則此參數配置為server資料庫所在執行個體的內網地址。如果profile資料庫與server資料庫在不同VPC下的不同執行個體中： profile資料庫所在執行個體需要配置NAT Gateway並綁定公網IP。 server資料庫所在執行個體需要開通外網地址，此參數配置為server資料庫所在執行個體的外網地址。說明如何擷取執行個體的內外網地址，請參見查看或修改串連地址和連接埠。
dbname	server	資料庫名。
port	3002	server資料庫所在執行個體的連接埠。如果profile資料庫與server資料庫在同一執行個體，此參數通過SQL命令`SHOW PORT;`查詢擷取。如果profile資料庫與server資料庫在相同VPC下的不同執行個體中，則此參數配置為server資料庫所在執行個體的內網連接埠。如果profile資料庫與server資料庫在不同VPC下的不同執行個體中，則此參數配置server資料庫所在執行個體的公網連接埠。
user	test_user	server資料庫所在執行個體的高許可權帳號。
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)

說明

建立server串連後，會自動建立一個profile資料庫的串連，即上述結果樣本中的名為local的串連。

修改profile資料庫連接資訊，配置密碼。

SELECT set_server_connstr('local','host=127.0.0.1 dbname=profile port=3002 user=<profile資料庫的高許可權帳號> password=<帳號對應的密碼>');

參數	樣本值	參數說明
server	local	串連名，無需修改。
host	127.0.0.1	固定為127.0.0.1。
dbname	profile	資料庫名。
port	3002	此參數通過SQL命令`SHOW PORT;`查詢擷取。
user	test_user	profile資料庫所在執行個體的高許可權帳號。
password	T123456!	帳號對應的密碼。

初次進行資料擷取。

SELECT take_sample();

結果樣本：

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

對server資料庫進行壓測後，再次進行採集。

在命令列視窗執行如下命令，對server資料庫進行壓測。

說明

如下命令需要在命令列視窗執行，請確保已安裝PostgreSQL用戶端，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資料庫所在執行個體的高許可權帳號。
-h	pgm-****.pg.rds.aliyuncs.com	server資料庫所在執行個體的串連地址。
-p	5432	執行個體連接埠。

串連profile資料庫所在執行個體，再次進行資料擷取。
```
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               |               |                 |

將採集結果產生報告。

您可以通過如下兩種方式將採集結果匯出到本地。

通過psql在profile資料庫中執行如下命令：
```
\o report_2_3.html
SELECT get_report('server',2,3);
```

在本地命令列中執行如下命令：

psql -Aqtc "SELECT profile.get_report('server',2,3)" -o report_server_2_3.html -d profile -h <profile資料庫所在執行個體的串連地址> -p 5432 -U <profile資料庫所在執行個體的高許可權帳號>