ApsaraDB RDS：資源密集活動統計（pg_profile）

外掛程式簡介

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

前提條件

注意事項

• 該外掛程式必須先啟用pg_stat_statements外掛程式，同時可以啟用pg_stat_kcache外掛程式來提供更多資訊收集功能。

• 儲存採樣資訊將會佔用磁碟空間，該外掛程式有自動清理機制，請注意資料儲存。

• 一次採樣較為耗時，無需頻繁採樣，推薦頻率為每小時1~2次。

建立和刪除外掛程式

• 建立外掛程式

  CREATE EXTENSION pg_profile;

• 刪除外掛程式

  DROP EXTENSION pg_profile;

使用樣本

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

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

server資料庫

1. 建立server資料庫。

   CREATE DATABASE server;

2. 串連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參數方法請參見設定執行個體參數。

3. 執行\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資料庫

1. 建立profile資料庫。

   CREATE DATABASE profile;

2. 串連profile資料庫並建立外掛程式。

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

3. 執行\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
    ...

4. 建立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的串連。

5. 修改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!	帳號對應的密碼。

6. 初次進行資料擷取。

   SELECT take_sample();

   結果樣本：

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

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

   1. 在命令列視窗執行如下命令，對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	執行個體連接埠。

   2. 串連profile資料庫所在執行個體，再次進行資料擷取。

      SELECT take_sample();

   3. 查看採集資料。

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

8. 將採集結果產生報告。

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

   • 通過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資料庫所在執行個體的高許可權帳號>