業務低穀期記憶體回收 - PolarDB

本文介紹了PolarDB PostgreSQL版的業務低穀期記憶體回收功能的使用方法以及樣本等內容。

前提條件

支援的PolarDB PostgreSQL版的版本如下：

PostgreSQL 16（核心小版本2.0.16.8.3.0及以上）
PostgreSQL 15（核心小版本2.0.15.12.4.0及以上）
PostgreSQL 14（核心小版本2.0.14.12.24.0及以上）
PostgreSQL 11（核心小版本2.0.11.15.42.0及以上）

說明

您可在控制台查看核心小版本號碼，也可以通過SHOW polardb_version;語句查看。如未滿足核心小版本要求，請升級核心小版本。

背景資訊

PolarDB PostgreSQL版與原生PostgreSQL一樣會在後台啟動自動清理（autovacuum）進程去執行記憶體回收操作，它帶來的收益包括但不限於：

回收老舊版本的資料以減少磁碟空間佔用。
更新統計資料以確保查詢最佳化工具能夠選擇最優執行計畫。
防止事務ID回卷，從而有效降低叢集停用風險。

這些記憶體回收操作比較消耗硬體資源，為了避免過於頻繁地執行自動清理，原生PostgreSQL為其設定了一些觸發條件（詳情請參見自動清理參數配置），只有滿足這些條件時才會啟動清理進程。由於觸發條件與資料變更行數和資料庫年齡相關，越是在業務的高峰期，資料變更往往越多，事務ID消耗速度越快，自動清理的觸發頻率就越高，導致了如下問題：

資源使用過高問題：在業務高峰期間自動清理進程頻繁執行記憶體回收並佔用大量CPU和I/O，與業務讀寫請求爭搶硬體資源，影響資料庫的讀寫效能。在下圖的樣本中，自動清理進程在白天業務高峰期間的CPU使用率和I/O輸送量在所有進程中排名第一。
鎖表阻塞讀寫問題：自動清理進程在回收空頁的過程中需要短暫持有排它鎖並阻塞單張表上的業務請求，雖然阻塞時間一般不長，但是在業務高峰期間即使很短暫的阻塞也無法接受。
計畫快取失效問題：自動清理進程收集統計資訊並導致原有的執行計畫緩衝（Plan Cache）失效，新的查詢需要重建執行計畫，可能發生業務高峰期間有多個串連並行產生執行計畫的情況，影響多個串連的業務請求回應時間。
說明
PolarDB PostgreSQL版的全域執行計畫緩衝功能可以一定程度減少此類問題的影響。

以上問題的核心在於原生PostgreSQL沒有業務低穀期的概念，而現實情境下的業務通常有明顯的高峰期和低穀期。PolarDB PostgreSQL版允許配置一個業務低穀期間的時間視窗，利用業務低穀期的閑置硬體資源進行積極和充分的記憶體回收，從而降低業務高峰期間的自動清理頻次，將更多的硬體資源留給業務讀寫請求，最佳化讀寫效能。

預期收益

業務低穀期內的記憶體回收預期可以緩解上文提到的各種問題，此外由於業務低穀期內的清理策略相比於原生PostgreSQL的自動清理更為積極，因此還可能有一些額外的收益。總的來說有如下收益：

資源使用率最佳化：業務低穀期內已經進行了記憶體回收，導致業務高峰期內的自動清理機率大幅下降，資源使用率也相應下降。
資料庫年齡最佳化：業務低穀期內回收更多事務ID，防止事務ID回卷造成的資料庫不可用問題。
統計資訊&慢SQL最佳化：收集更多表的統計資訊，協助最佳化器選擇更為準確的查詢計劃，減少統計資訊到期導致的慢SQL。
鎖表問題最佳化：業務高峰期內自動清理操作鎖表導致阻塞業務讀寫的問題發生機率降低。
計畫快取失效問題最佳化：業務高峰期內自動清理操作導致計畫快取失效的問題發生機率降低。

使用方法

說明

PolarDB PostgreSQL版叢集僅前提條件中支援的版本可以通過以下方法配置業務低穀期，如果叢集核心小版本低，請先在控制台升級到最新的核心小版本，具體請參見小版本管理。
如果希望在不升級叢集小版本的前提下配置業務低穀期，請聯絡我們後台進行配置。請說明業務低穀期的開始時間、結束時間、時區。這種配置方法存在局限性，會因為主備切換、變更配置、切換可用性區域等營運操作而失效，因此只是臨時性方案，如果希望永久生效，還是需要升級到最新核心小版本。

配置業務低穀期資訊

建立外掛程式。
在postgres資料庫和所有需要執行記憶體回收的資料庫上都需要建立polar_advisor外掛程式。
```
CREATE EXTENSION IF NOT EXISTS polar_advisor;
```
對於已經安裝polar_advisor外掛程式的PolarDB叢集，可通過以下命令升級：
```
ALTER EXTENSION polar_advisor UPDATE;
```
時間視窗設定。
執行如下命令設定業務低穀期時間段。
```
-- 在postgres資料庫執行
SELECT polar_advisor.set_advisor_window(start_time, end_time);
```
- start_time：表示視窗開始時間。
- end_time：表示視窗結束時間。
- 視窗預設會當天生效，之後每天都會自動在視窗時間段內執行記憶體回收操作。
說明
- 僅在postgres資料庫中配置的視窗時間會生效，在其他資料庫上設定視窗時間無效。
- 視窗時間中的時區位移量必須與PolarDB叢集的時區設定保持一致，否則視窗時間無法生效。
將北京時間（東八區，+08時區）的每天晚上23點到第二天淩晨2點這個時間段作為業務低穀期，該叢集每天都會在這個時間段內執行記憶體回收操作，樣本如下：
```
SELECT polar_advisor.set_advisor_window(start_time => '23:00:00+08', end_time => '02:00:00+08');
```

時間視窗查看。

執行如下命令查看設定的業務低穀期資訊。

-- 在 postgres 資料庫執行

-- 查看業務低穀期詳情
SELECT * FROM polar_advisor.get_advisor_window();
-- 查看業務低穀期時間長度，單位為秒
SELECT polar_advisor.get_advisor_window_length();
-- 查看目前時間是否在視窗時間內
SELECT now(), * FROM polar_advisor.is_in_advisor_window();
-- 查看目前時間距離下一次視窗開始時間的時間長度，單位為秒
SELECT * FROM polar_advisor.get_secs_to_advisor_window_start();
-- 查看目前時間距離下一次視窗結束時間的時間長度，單位為秒
SELECT * FROM polar_advisor.get_secs_to_advisor_window_end();

樣本如下：

-- 查看業務低穀期詳情
postgres=# SELECT * FROM polar_advisor.get_advisor_window();
 start_time  |  end_time   | enabled | last_error_time | last_error_detail | others
-------------+-------------+---------+-----------------+-------------------+--------
 23:00:00+08 | 02:00:00+08 | t       |                 |                   |
(1 row)

-- 查看業務低穀期時間長度
postgres=# SELECT polar_advisor.get_advisor_window_length() / 3600.0 AS "視窗長度/h";
     視窗長度/h
--------------------
 3.0000000000000000
(1 row)

-- 查看目前時間是否在視窗時間內
postgres=# SELECT now(), * FROM polar_advisor.is_in_advisor_window();
              now              | is_in_advisor_window
-------------------------------+----------------------
 2024-04-01 07:40:37.733911+00 | f
(1 row)

-- 查看目前時間距離下一次視窗開始時間的時間長度
postgres=# SELECT * FROM polar_advisor.get_secs_to_advisor_window_start();
 secs_to_window_start |      time_now      | window_start | window_end
----------------------+--------------------+--------------+-------------
         26362.265179 | 07:40:37.734821+00 | 15:00:00+00  | 18:00:00+00
(1 row)

-- 查看目前時間距離下一次視窗結束時間的時間長度
postgres=# SELECT * FROM polar_advisor.get_secs_to_advisor_window_end();
 secs_to_window_end |      time_now      | window_start | window_end
--------------------+--------------------+--------------+-------------
       36561.870337 | 07:40:38.129663+00 | 15:00:00+00  | 18:00:00+00
(1 row)

關閉/開啟視窗。

設定視窗後，視窗預設開啟，會在每天的視窗時間內執行記憶體回收。如果某一天的業務低穀期內不希望叢集執行記憶體回收（例如需要手動執行其他營運操作，擔心與記憶體回收衝突），則可以執行以下命令來關閉業務低穀期，等到營運工作結束以後再調用函數重新開啟視窗。

-- 在 postgres 資料庫執行

-- 關閉業務低穀期記憶體回收
SELECT polar_advisor.disable_advisor_window();
-- 開啟業務低穀期記憶體回收
SELECT polar_advisor.enable_advisor_window();
-- 查看視窗是否已開啟
SELECT polar_advisor.is_advisor_window_enabled();

樣本如下：

-- 視窗已開啟
postgres=# SELECT polar_advisor.is_advisor_window_enabled();
 is_advisor_window_enabled
---------------------------
    t
(1 row)

-- 關閉視窗
postgres=# SELECT polar_advisor.disable_advisor_window();
 disable_advisor_window
------------------------

(1 row)

-- 視窗已關閉
postgres=# SELECT polar_advisor.is_advisor_window_enabled();
 is_advisor_window_enabled
---------------------------
    f
(1 row)

-- 重新開啟視窗
postgres=# SELECT polar_advisor.enable_advisor_window();
 enable_advisor_window
-----------------------

(1 row)

-- 視窗已開啟
postgres=# SELECT polar_advisor.is_advisor_window_enabled();
 is_advisor_window_enabled
---------------------------
    t
(1 row)

其他配置

設定黑名單。

如果配置了業務低穀期，預設情況下資料庫會自行決定在視窗期內對哪些表執行記憶體回收操作，任何一張表都有可能被執行。如果希望某張表不要被選中，則可以通過以下命令將該表加入到黑名單中。

-- 在具體的業務資料庫中執行

-- 將表加入VACUUM & ANALYZE黑名單
SELECT polar_advisor.add_relation_to_vacuum_analyze_blacklist(schema_name, relation_name);
-- 驗證表是否在VACUUM & ANALYZE黑名單中
SELECT polar_advisor.is_relation_in_vacuum_analyze_blacklist(schema_name, relation_name);
-- 擷取VACUUM & ANALYZE黑名單
SELECT * FROM polar_advisor.get_vacuum_analyze_blacklist();

樣本如下：

-- 將 public.t1 表加入黑名單
postgres=# SELECT polar_advisor.add_relation_to_vacuum_analyze_blacklist('public', 't1');
 add_relation_to_vacuum_analyze_blacklist
---------------------------
    t
(1 row)

-- 查看該表是否在黑名單中
postgres=# SELECT polar_advisor.is_relation_in_vacuum_analyze_blacklist('public', 't1');
 is_relation_in_vacuum_analyze_blacklist
--------------------------
    t
(1 row)

-- 擷取完整的黑名單列表，查看該表是否在黑名單中
postgres=# SELECT * FROM polar_advisor.get_vacuum_analyze_blacklist();
 schema_name | relation_name |  action_type
-------------+---------------+----------------
 public      | t1            | VACUUM ANALYZE
(1 row)

設定活躍串連數閾值。

為了避免業務低穀期內的記憶體回收操作影響正常業務，系統會自動檢測業務低穀期間的活躍串連數，超出閾值時將自動取消記憶體回收操作的執行，您可以手動調整該閾值以適應您的業務特性（閾值預設為5～10，具體與叢集的CPU核心數有關）。

-- 在 postgres 資料庫執行

-- 擷取業務低穀期可以接受的串連數閾值，實際的活躍串連數高於該值就不會執行記憶體回收
SELECT polar_advisor.get_active_user_conn_num_limit();

-- 在業務低穀期內執行 SQL，擷取業務低穀期的實際活躍串連數（或者通過 PolarDB 控制台->效能監控->進階監控->標準試圖->會話串連->active_session查看）
SELECT COUNT(*) FROM pg_catalog.pg_stat_activity sa JOIN pg_catalog.pg_user u ON sa.usename = u.usename
WHERE sa.state = 'active' AND sa.backend_type = 'client backend' AND NOT u.usesuper;

-- 人為設定活躍串連數閾值，將覆蓋系統預設的閾值
SELECT polar_advisor.set_active_user_conn_num_limit(active_user_conn_limit);
-- 取消設定活躍串連數閾值，將恢複使用系統預設的閾值
SELECT polar_advisor.unset_active_user_conn_num_limit();

樣本如下：

-- 擷取執行個體預設活躍串連數閾值，該執行個體閾值為5（不同執行個體的閾值可能不同，具體與CPU核心數有關）
postgres=# SELECT polar_advisor.get_active_user_conn_num_limit();
NOTICE:  get active user conn limit by CPU cores number
 get_active_user_conn_num_limit
--------------------------------
              5
(1 row)

-- 擷取當前實際活躍串連數，結果為8，大於上面擷取的閾值5，因此系統會認為活躍串連數較多，不能在業務低穀期時間內執行記憶體回收
postgres=# SELECT COUNT(*) FROM pg_catalog.pg_stat_activity sa JOIN pg_catalog.pg_user u ON sa.usename = u.usename
postgres-# WHERE sa.state = 'active' AND sa.backend_type = 'client backend' AND NOT u.usesuper;
 count
-------
    8
(1 row)

-- 將活躍串連數閾值設為10，大於實際的活躍串連數8，系統會認為實際的活躍串連數8沒有超過閾值10，可以執行記憶體回收
postgres=# SELECT polar_advisor.set_active_user_conn_num_limit(10);
 set_active_user_conn_num_limit
--------------------------------

(1 row)

-- 查看活躍串連數閾值，顯示為10，就是上一步手動設定的值
postgres=# SELECT polar_advisor.get_active_user_conn_num_limit();
NOTICE:  get active user conn limit from table
  get_active_user_conn_num_limit
--------------------------------
            10
(1 row)

-- 取消設定活躍串連數閾值
postgres=# SELECT polar_advisor.unset_active_user_conn_num_limit();
 unset_active_user_conn_num_limit
----------------------------------

(1 row)

-- 取消設定以後，活躍串連數閾值恢複到預設值5
postgres=# SELECT polar_advisor.get_active_user_conn_num_limit();
NOTICE:  get active user conn limit by CPU cores number
 get_active_user_conn_num_limit
--------------------------------
            5
(1 row)

查看結果

業務低穀期內執行的記憶體回收操作的結果和收益都記錄在postgres資料庫的日誌表中，保留最近90天的資料。

表結構

polar_advisor.db_level_advisor_log表儲存了資料庫層級的每一輪記憶體回收操作的各項資訊。

CREATE TABLE polar_advisor.db_level_advisor_log (
    id                      BIGSERIAL PRIMARY KEY,
    exec_id                 BIGINT,
    start_time              TIMESTAMP WITH TIME ZONE,
    end_time                TIMESTAMP WITH TIME ZONE,
    db_name                 NAME,
    event_type              VARCHAR(100),
    total_relation          BIGINT,
    acted_relation          BIGINT,
    age_before              BIGINT,
    age_after               BIGINT,
    others                  JSONB
);

參數說明：

參數名稱	說明
id	表示表的主鍵，自動遞增。
exec_id	表示執行的輪次，通常每天運行一輪，一輪可以操作多個資料庫，所以當天的多條記錄的`exec_id`相同。
start_time	表示操作開始的時間。
end_time	表示操作結束的時間。
db_name	表示操作的資料庫名稱。
event_type	表示操作類型，當前僅支援`VACUUM`。
total_relation	表示表中可以被操作的表和索引數量。
acted_relation	表示實際操作的表和索引數量。
age_before	表示操作前的資料庫年齡。
age_after	表示操作後的資料庫年齡。
others	包含較多擴充的統計資料： `others->'db_size_before'`和`others->'db_size_after'`分別表示操作前後的資料庫大小。 `others->'cluster_age_before'`和`others->'cluster_age_after'`分別表示操作前後的執行個體年齡。

polar_advisor.advisor_log表儲存了表/索引層級的每一次記憶體回收操作的詳細資料，polar_advisor.db_level_advisor_log表中的一條記錄對應polar_advisor.advisor_log表的多條記錄。

CREATE TABLE polar_advisor.advisor_log (
    id                      BIGSERIAL PRIMARY KEY,
    exec_id                 BIGINT,
    start_time              TIMESTAMP WITH TIME ZONE,
    end_time                TIMESTAMP WITH TIME ZONE,
    db_name                 NAME,
    schema_name             NAME,
    relation_name           NAME,
    event_type              VARCHAR(100),
    sql_cmd                 TEXT,
    detail                  TEXT,
    tuples_deleted          BIGINT,
    tuples_dead_now         BIGINT,
    tuples_now              BIGINT,
    pages_scanned           BIGINT,
    pages_pinned            BIGINT,
    pages_frozen_now        BIGINT,
    pages_truncated         BIGINT,
    pages_now               BIGINT,
    idx_tuples_deleted      BIGINT,
    idx_tuples_now          BIGINT,
    idx_pages_now           BIGINT,
    idx_pages_deleted       BIGINT,
    idx_pages_reusable      BIGINT,
    size_before             BIGINT,
    size_now                BIGINT,
    age_decreased           BIGINT,
    others                  JSONB
);

參數說明：

參數名稱	說明
id	表示表的主鍵，自動遞增。
exec_id	表示執行的輪次，通常每天運行一輪，一輪可以操作多個資料庫，所以當天的多條記錄的`exec_id`相同。
start_time	表示操作開始的時間。
end_time	表示操作結束的時間。
db_name	表示操作的資料庫名稱。
schema_name	表示操作的資料庫模式名稱。
relation_name	表示操作的資料庫表/索引名稱。
event_type	表示操作類型，當前僅支援`VACUUM`。
sql_cmd	表示具體執行的操作命令，例如`VACUUM public.t1`。
detail	表示操作的結果詳情，例如`VACUUM VERBOSE`列印的詳細結果。
tuples_deleted	表示本次操作中表回收的死元組數量。
tuples_dead_now	表示本次操作後表中遺留的死元組數量。
tuples_now	表示本次操作後表的活元組數量。
pages_scanned	表示本次操作中掃描的頁數。
pages_pinned	表示本次操作中因為緩衝被引用而無法刪除的頁數。
pages_frozen_now	表示本次操作後被凍結的頁數。
pages_truncated	表示本次操作中刪除/截斷的空頁數。
pages_now	表示本次操作後表的頁數。
idx_tuples_deleted	表示本次操作中回收的索引死元組數量。
idx_tuples_now	表示本次操作後索引的活元組數量。
idx_pages_now	表示本次操作後索引的頁數。
idx_pages_deleted	表示本次操作中刪除的索引頁數。
idx_pages_reusable	表示本次操作中重新利用的索引頁數。
size_before	表示本次操作前的表/索引大小。
size_after	表示本次操作後的表/索引大小。
age_decreased	表示本次操作前後的表年齡下降大小。
others	表示擴充的統計資料。

統計資料

查看近期每一輪記憶體回收記錄的開始時間、結束時間、操作的表/索引數量，樣本如下：

-- 在 postgres 資料庫執行
SELECT COUNT(*) AS "表/索引數量", MIN(start_time) AS "開始時間", MAX(end_time) AS "結束時間", exec_id AS "輪次" FROM polar_advisor.advisor_log GROUP BY exec_id ORDER BY exec_id DESC;

結果顯示如下，可以看到最近3輪執行記憶體回收的表數量都在4390左右，執行時間都在淩晨1-4點。

   表/索引數量 |            開始時間             |              結束時間            | 輪次
-------------+--------------------------------+--------------------------------+------
        4391 | 2024-09-23 01:00:09.413901 +08 | 2024-09-23 03:25:39.029702 +08 |  139
        4393 | 2024-09-22 01:03:07.365759 +08 | 2024-09-22 03:37:45.227067 +08 |  138
        4393 | 2024-09-21 01:03:08.094989 +08 | 2024-09-21 03:45:20.280011 +08 |  137

查看近期每天內執行記憶體回收的表/索引數量，按日期統計，樣本如下：

-- 在 postgres 資料庫執行
SELECT start_time::pg_catalog.date AS "時間", count(*) AS "表/索引數量" FROM polar_advisor.advisor_log GROUP BY start_time::pg_catalog.date ORDER BY start_time::pg_catalog.date DESC, count(*) DESC;

結果顯示如下，可以看到最近3天內每一天執行記憶體回收的表數量都在4390左右。

    時間     | 表/索引數量
------------+-------------
 2024-09-23 |        4391
 2024-09-22 |        4393
 2024-09-21 |        4393

查看最近執行記憶體回收的表/索引數量，按日期和資料庫統計，樣本如下：

-- 在 postgres 資料庫執行
SELECT start_time::pg_catalog.date AS "時間", count(*) AS "表/索引數量" FROM polar_advisor.advisor_log GROUP BY start_time::pg_catalog.date ORDER BY start_time::pg_catalog.date DESC, count(*) DESC;

結果顯示如下，可以看到最近3天對postgres、db_123、db_12345、db_123456789這些資料庫執行過記憶體回收，每個資料庫執行了幾十個到幾百個表/索引不等。

      時間    |       DB       | 表/索引數量
-------------+----------------+-------------
  2024-03-05 | db_123456789   |     697
  2024-03-05 | db_123         |     277
  2024-03-04 | db_123456789   |     695
  2024-03-04 | db_123         |     267
  2024-03-04 | db_12345       |     174
  2024-03-03 | postgres       |      65
(6 rows)

詳細資料

查看近期執行記憶體回收的資料庫的收益資訊，樣本如下：

-- 在 postgres 資料庫執行
SELECT id, start_time AS "開始時間", end_time AS "結束時間", db_name AS "資料庫", event_type AS "操作類型", total_relation AS "資料庫總表數量", acted_relation AS "操作的表數量",
    CASE WHEN others->>'cluster_age_before' IS NOT NULL AND others->>'cluster_age_after' IS NOT NULL THEN (others->>'cluster_age_before')::BIGINT - (others->>'cluster_age_after')::BIGINT ELSE NULL END AS "年齡下降",
    CASE WHEN others->>'db_size_before' IS NOT NULL AND others->>'db_size_after' IS NOT NULL THEN (others->>'db_size_before')::BIGINT - (others->>'db_size_after')::BIGINT ELSE NULL END AS "儲存空間下降"
FROM polar_advisor.db_level_advisor_log ORDER BY id DESC;

結果顯示如下，可以看到最近三次執行的操作都是VACUUM。

    id   |            開始時間            |             結束時間            |      資料庫     | 操作類型  |  資料庫總表數量  |  操作的表數量  |  年齡下降  | 儲存空間下降
---------+-------------------------------+-------------------------------+----------------+----------+----------------+--------------+----------+--------------
    1184 | 2024-03-05 00:44:26.776894+08 | 2024-03-05 00:45:56.396519+08 | db_12345       | VACUUM   |            174 |          164 |      694 |            0
    1183 | 2024-03-05 00:43:30.243505+08 | 2024-03-05 00:44:26.695602+08 | db_123456789   | VACUUM   |            100 |           90 |      396 |            0
    1182 | 2024-03-05 00:41:47.70952+08  | 2024-03-05 00:43:30.172527+08 | db_12345       | VACUUM   |            163 |          153 |      701 |            0
(3 rows)

查看近期執行記憶體回收的表的收益資訊，樣本如下：

-- 在 postgres 資料庫執行
SELECT start_time AS "開始時間", end_time AS "結束時間", db_name AS "資料庫", schema_name AS "模式", relation_name AS "表/索引", event_type AS "操作類型", tuples_deleted AS "回收死元組數", pages_scanned AS "掃描頁數",pages_truncated AS "回收頁數", idx_tuples_deleted AS "回收索引死元組數", idx_pages_deleted AS "回收索引頁數", age_decreased AS "表年齡下降" FROM polar_advisor.advisor_log ORDER BY id DESC;

結果顯示如下，可以看到最近三次操作回收的死元組數量、回收的頁數、表年齡下降等資訊。

             開始時間           |             結束時間            |   資料庫  |  模式  |  表/索引 | 操作類型 | 回收死元組數 | 掃描頁數 | 回收頁數 | 回收索引死元組數 | 回收索引頁數 | 表年齡下降
-------------------------------+-------------------------------+----------+--------+--------+---------+------------+---------+---------+---------------+------------+------------
 2024-03-05 00:45:56.204254+08 | 2024-03-05 00:45:56.357263+08 | db_12345 | public |  cccc  | VACUUM  |        0   |      33 |      0  |             0 |         0  |    1345944
 2024-03-05 00:45:56.068499+08 | 2024-03-05 00:45:56.200036+08 | db_12345 | public |  aaaa  | VACUUM  |        0   |      28 |      0  |             0 |         0  |    1345946
 2024-03-05 00:45:55.945677+08 | 2024-03-05 00:45:56.065316+08 | db_12345 | public |  bbbb  | VACUUM  |        0   |       0 |      0  |             0 |         0  |    1345947
(3 rows)

查看資料庫年齡下降最多的操作記錄。

PolarDB PostgreSQL版共有約21億個可用的事務ID，通過資料庫年齡來衡量已經消耗的事務ID數量，年齡達到21億時將發生事務ID回卷，資料庫將不可用，因此資料庫年齡越小越好。

-- 在 postgres 資料庫執行

-- 擷取資料庫執行個體年齡下降最大的記錄對應的資料庫和操作類型
SELECT id, exec_id AS "輪次", start_time AS "開始時間", end_time AS "結束時間", db_name AS "資料庫", event_type AS "操作類型", CASE WHEN others->>'cluster_age_before' IS NOT NULL AND others->>'cluster_age_after' IS NOT NULL THEN (others->>'cluster_age_before')::BIGINT - (others->>'cluster_age_after')::BIGINT ELSE NULL END AS "年齡下降" FROM polar_advisor.db_level_advisor_log ORDER BY "年齡下降" DESC NULLS LAST;

-- 根據上一步擷取的輪次資訊擷取該輪操作中具體導致資料庫年齡下降的詳細記錄
SELECT id, start_time AS "開始時間", end_time AS "結束時間", db_name AS "資料庫", schema_name AS "模式", relation_name AS "表名", sql_cmd AS "命令", event_type AS "操作類型", age_decreased AS "年齡下降" FROM polar_advisor.advisor_log WHERE exec_id = 91 ORDER BY "年齡下降" DESC NULLS LAST;

-- 擷取當前資料庫年齡（任何一個資料庫皆可執行）（或者通過 PolarDB 控制台->效能監控->進階監控->標準試圖->Vacuum->db_age 查看）
SELECT MAX(pg_catalog.age(datfrozenxid)) AS "執行個體年齡" FROM pg_catalog.pg_database;

結果顯示如下：

-- 2024-02-22 這天對 aaaaaaaaaaaaa 這個資料庫執行的 vacuum 操作讓資料庫年齡下降了 9275406，也就是接近一千萬，執行輪次為 91
postgres=# SELECT id, exec_id AS "輪次", start_time AS "開始時間", end_time AS "結束時間", db_name AS "資料庫", event_type AS "操作類型", CASE WHEN others->>'cluster_age_before' IS NOT NULL AND others->>'cluster_age_after' IS NOT NULL THEN (others->>'cluster_age_before')::BIGINT - (others->>'cluster_age_after')::BIGINT ELSE NULL END AS "年齡下降" FROM polar_advisor.db_level_advisor_log ORDER BY "年齡下降" DESC NULLS LAST;
id      | 輪次  |           開始時間             |           結束時間             |    資料庫      | 操作類型   | 年齡下降
--------+------+-------------------------------+-------------------------------+---------------+----------+----------
    259 |   91 | 2024-02-22 00:00:18.847978+08 | 2024-02-22 00:14:18.785085+08 | aaaaaaaaaaaaa | VACUUM   |  9275406
    256 |   90 | 2024-02-21 00:00:39.607552+08 | 2024-02-21 00:00:42.054733+08 | bbbbbbbbbbbbb | VACUUM   |  7905122
    262 |   92 | 2024-02-23 00:00:05.999423+08 | 2024-02-23 00:00:08.411993+08 | postgres      | VACUUM   |   578308

-- 根據執行輪次 91 擷取詳細的 vacuum 記錄，可以看到主要是一些 pg_catalog 系統資料表的 vacuum 操作使得資料庫年齡下降
postgres=# SELECT id, start_time AS "開始時間", end_time AS "結束時間", db_name AS "資料庫", schema_name AS "模式", relation_name AS "表名", event_type AS "操作類型", age_decreased AS "年齡下降" FROM polar_advisor.advisor_log WHERE exec_id = 91 ORDER BY "年齡下降" DESC NULLS LAST;
    id    |           開始時間             |           結束時間              | 資料庫 |    模式    |        表名         | 操作類型 | 年齡下降
----------+-------------------------------+-------------------------------+-------+------------+--------------------+---------+----------
    43933 | 2024-02-22 00:00:19.070493+08 | 2024-02-22 00:00:19.090822+08 |  abc  | pg_catalog | pg_subscription    | VACUUM  | 27787409
    43935 | 2024-02-22 00:00:19.116292+08 | 2024-02-22 00:00:19.13875+08  |  abc  | pg_catalog | pg_database        | VACUUM  | 27787408
    43936 | 2024-02-22 00:00:19.140992+08 | 2024-02-22 00:00:19.171938+08 |  abc  | pg_catalog | pg_db_role_setting | VACUUM  | 27787408

-- 當前執行個體年齡為兩千多萬，距離閾值 21 億還有很遠，非常安全
postgres=> SELECT MAX(pg_catalog.age(datfrozenxid)) AS "執行個體年齡" FROM pg_catalog.pg_database;
 執行個體年齡
----------
 20874380
(1 row)

最佳化效果樣本

以下展示部分叢集在配置業務低穀期以後的資源使用量和資料庫年齡的最佳化效果。

說明

鎖表阻塞讀寫、計畫快取失效等問題的最佳化效果不太好通過圖表的方式展示，因此不作展示。
並非所有叢集都能取得樣本中這麼好的最佳化效果，實際效果與具體業務情境有關。導致提升效果不明顯的原因有很多，例如有些叢集全天業務都很繁忙且沒有明顯的業務低穀期，有些叢集的業務低穀期則配置了一些任務來執行資料分析、資料匯入、物化視圖重新整理等操作，沒有太多閑置資源可供記憶體回收操作使用。