PostgreSQL 效能分析利器：pg_profile 佈署 SOP (v4.11)
#
一、 系統環境與檔案佈署
#
- 下載與解壓縮：
將
pg_profile 延伸模組檔案解壓縮至 PostgreSQL 的指定目錄。
1
2
|
wget https://github.com/zubkov-andrei/pg_profile/releases/download/4.11/pg_profile--4.11.tar.gz
sudo tar xzf pg_profile-4.11.tar.gz --directory $(pg_config --sharedir)/extension
|
- 修改 PostgreSQL 配置 (
postgresql.conf)：
pg_profile 依賴於核心統計模組，需在啟動時預先載入。
1
2
3
4
5
6
7
8
9
10
11
|
# 必須重啟資料庫生效
shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache, pg_wait_sampling'
#------------------------------------------------------------------------------
# pg_profile
#------------------------------------------------------------------------------
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
|
- 更新驗證規則 (
pg_hba.conf)：
確保監控帳號可以透過密碼從本地連線。
1
|
host all profile_collector 127.0.0.1/32 scram-sha-256
|
二、 資料庫環境初始化
#
建議將監控物件安裝在獨立的 profile schema 中以利管理。
- 建立基礎擴充與 Schema：
1
2
3
4
5
6
7
|
\c postgres postgres
CREATE SCHEMA pgss;
CREATE SCHEMA pgsk;
CREATE SCHEMA pgws;
CREATE EXTENSION pg_stat_statements SCHEMA pgss;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;
CREATE EXTENSION pg_wait_sampling SCHEMA pgws;
|
1
2
3
4
5
6
7
|
-- 以超級使用者執行
CREATE EXTENSION dblink;
CREATE EXTENSION pg_stat_statements SCHEMA pgss;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;
CREATE EXTENSION pg_wait_sampling SCHEMA pgws;
CREATE SCHEMA profile;
CREATE EXTENSION pg_profile SCHEMA profile;
|
- check
1
2
3
|
\df *.pg_stat_kcache_reset
\df *.pg_stat_statements_reset
\df *.pg_wait_sampling_reset_profile
|
結果如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
\c postgres postgres
postgres=# \df *.pg_stat_kcache_reset
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------------------+------------------+---------------------+------
pgsk | pg_stat_kcache_reset | void | | func
(1 row)
postgres=# \df *.pg_stat_statements_reset
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------------+------------------+--------------------------------------------------------------------+------
pgss | pg_stat_statements_reset | void | userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0 | func
(1 row)
postgres=# \df *.pg_wait_sampling_reset_profile
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------------------+------------------+---------------------+------
pgws | pg_wait_sampling_reset_profile | void | | func
(1 row)
|
- 建立專用監控帳號 (Collector)：
此帳號用於定期連線收集數據，需具備讀取統計資訊的權限。
1
2
3
4
5
6
7
8
9
10
|
CREATE USER profile_collector WITH PASSWORD '你的密碼';
GRANT pg_read_all_stats TO profile_collector;
-- 授權重置統計資訊的權限 (以確保數據準確)
GRANT USAGE ON SCHEMA pgss TO profile_collector;
GRANT USAGE ON SCHEMA pgsk TO profile_collector;
GRANT USAGE ON SCHEMA pgws TO profile_collector;
GRANT EXECUTE ON FUNCTION pgsk.pg_stat_kcache_reset TO profile_collector;
GRANT EXECUTE ON FUNCTION pgss.pg_stat_statements_reset TO profile_collector;
GRANT EXECUTE ON FUNCTION pgws.pg_wait_sampling_reset_profile TO profile_collector;
|
三、 伺服器配置與連線設定
#
pg_profile 預設會建立一個名為 local 的伺服器項目。
- 更新連線字串：
指定使用剛剛建立的
profile_collector 進行連線。
1
2
3
4
|
SELECT profile.set_server_connstr(
'local'::name,
'host=127.0.0.1 dbname=postgres user=profile_collector password=你的密碼'::text
);
|
四、 自動化排程設定 (Systemd Timer)
#
使用 Systemd Timer 取代傳統 Cron，提供更佳的監控與日誌紀錄能力。
- 建立 Service 檔案：
sudo vi /etc/systemd/system/pg_profile_sample.service
1
2
3
4
5
6
7
8
|
[Unit]
Description=Take PostgreSQL pg_profile sample
After=postgresql-16.service
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/psql -d postgres -c "SELECT profile.take_sample()"
|
- 建立 Timer 檔案 (每 30 分鐘執行一次)：
sudo vi /etc/systemd/system/pg_profile_sample.timer
1
2
3
4
5
6
7
8
9
|
[Unit]
Description=Run pg_profile sample every 30 minutes
[Timer]
OnBootSec=5min
OnUnitActiveSec=30min
[Install]
WantedBy=timers.target
|
- 啟用排程：
1
2
|
sudo systemctl daemon-reload
sudo systemctl enable --now pg_profile_sample.timer
|
五、 驗證與報告產出
#
- 手動取樣驗證：
1
2
|
SELECT * FROM profile.take_sample();
-- 確認 Result 欄位顯示 OK
|
- 查看樣本列表：
1
|
SELECT * FROM profile.show_samples();
|
- 生成 HTML 效能報告：
報告需要兩個樣本 ID 作為區間（例如樣本 1 與樣本 2）。
1
2
|
# 在 Linux Shell 執行
psql -d postgres -Aqtc "SELECT profile.get_report(1, 2)" -o pg_report_$(date +%F).html
|
六、 日常維護建議
#
- 數據保留政策：
pg_profile 預設保留 7 天樣本。若需調整，可使用 profile.set_server_max_sample_age('local', 14) 改為 14 天。
- 基準線 (Baseline)：對於重要的測試時段，建議建立 Baseline 以防止數據被自動清理刪除。
1
2
|
-- 建立名為 'Release_v1' 的基準線
SELECT profile.create_baseline('local', 'Release_v1', 10, 20);
|
注意：當 pg_stat_statements.max 設定過小時，報告會發出警告，提醒您可能遺失了部分 SQL 數據。
Referance
#
