使用pg_buffercache查詢與分析共用緩衝區-雲原生資料倉儲AnalyticDB-阿里雲

pg_buffercache外掛程式提供了可觀測視圖，視圖中包含了表或索引的被訪問資訊和佔用共用緩衝區（shared buffer）的比例等。通過這些資訊可分析資料庫的訪問情況，以最佳化查詢，從而提高資料庫效能。

功能介紹

AnalyticDB for PostgreSQL通過pg_buffercache外掛程式，提供共用緩衝區中緩衝的不同粒度頁面（Page）資訊的可觀測視圖。DBA可以通過監控這些可觀測視圖實現：

識別熱點資料。頻繁出現在共用緩衝區中的表或索引可能代表了系統中的訪問熱點，對這些表或索引做進一步的查詢最佳化可顯著提升資料庫效能。
最佳化緩衝配置。根據緩衝的實際使用方式，調整共用緩衝區的大小，確保關鍵資料得到充分緩衝，減少磁碟I/O操作。
預防效能瓶頸。通過定期分析，可以在緩衝資源成為瓶頸之前，採取措施例如增加緩衝資源或調整資料存取原則，以鑑效組資料庫的高效能運行。

結合pg_prewarm外掛程式的使用，可以主動將未來可能出現高頻率訪問的表或索引資料預先載入到共用緩衝區中。尤其是在系統啟動後預測到訪問模式變化時，有效減少了因資料未在緩衝中而導致的首次訪問延遲，保證了資料庫響應的穩定性。

版本限制

當前pg_buffercache外掛程式支援的執行個體版本和核心版本必須滿足以下條件：

AnalyticDB PostgreSQL 6.0版執行個體且核心版本為v6.6.2.5及以上。
AnalyticDB PostgreSQL 7.0版執行個體且核心版本為v7.1.0.0及以上。
Serverless版本暫不支援。

說明

如何查看核心版本，請參見查看核心小版本。如您的執行個體不滿足上述版本要求，建議您升級核心小版本。

安裝與卸載

安裝外掛程式

使用pg_buffercache外掛程式之前，您需要在AnalyticDB for PostgreSQL執行個體外掛程式管理中安裝pg_buffercache外掛程式。具體操作，請參見安裝、升級與卸載外掛程式。

卸載外掛程式

當您不需要pg_buffercache外掛程式時，可以在外掛程式管理頁面卸載pg_buffercache外掛程式。具體操作，請參見安裝、升級與卸載外掛程式。

使用樣本

說明

為了避免對執行個體的運行產生影響，本文中所提供的表、索引等均為無鎖版本，對業務無影響。在業務並發較大時，擷取的統計結果可能會出現一定程度的誤差。

擷取協調（master）節點緩衝使用方式

SELECT * FROM shared_buffer_stat_master_lockfree;

返回結果如下。

           relname         | percentage_in_shared_buffer | pages_in_shared_buffer
---------------------------+-----------------------------+------------------------
 others                    |                     0.53125 |                     85
 pg_depend_reference_index |                     0.09375 |                     15
 pg_depend                 |                     0.05625 |                      9
 ...

relname：在共用緩衝區中佔用空間的表或索引的名稱。這有助於分析哪些資料庫物件最頻繁被訪問，影響緩衝效率和查詢效能。
percentage_in_shared_buffer：該表或索引所佔共用緩衝區的比例。通過該指標可瞭解哪些表或索引佔據了緩衝區的主要部分，進而可分析是否需要調整緩衝分配策略或最佳化訪問模式以平衡資源使用。
pages_in_shared_buffer：在共用緩衝區中緩衝的表或索引的頁面數量。頁面是資料庫儲存的基本單位，瞭解各對象的快取頁面面數量有助於評估資料訪問的局部性與快取命中率。

說明

對於部分臨時存在的對象，若查詢時對應的中繼資料已不可見，其佔用會被統計到others項中。

擷取計算（segment）節點緩衝使用方式

SELECT * FROM shared_buffer_stat_segments_lockfree;

返回結果如下。

        relname         | percentage_in_shared_buffer
------------------------+-----------------------------
 gp_distribution_policy |                     0.00625
 pg_namespace_oid_index |                      0.0125
 pg_rewrite             |                     0.00625
...

返回結果與master節點的緩衝使用方式結果相似，但percentage_in_shared_buffer返回的是對應表或索引在各個計算節點共用緩衝區中的佔用平均值。

擷取協調（master）節點緩衝整體使用方式

SELECT * FROM shared_buffer_summary_master_lockfree;

返回結果如下。

 pages_in_use | pages_marked_dirty | pages_total
--------------+--------------------+-------------
          594 |                594 |       16000

pages_in_use：當前正在被使用的緩衝頁面數量。
pages_marked_dirty：被標記為“髒”的頁面的數量。即指在緩衝區內被修改過但尚未同步回磁碟的資料頁面。該數值反映了寫操作的活躍程度及潛在的I/O寫回壓力。
pages_total：共用緩衝區配置的最大頁面數，即資料庫可以利用的緩衝總量。

擷取計算（segment）節點緩衝整體使用方式

SELECT * FROM shared_buffer_summary_segments_lockfree;

返回結果如下。

 pages_in_use | pages_marked_dirty | pages_total
--------------+--------------------+-------------
        11328 |              11328 |       98304
...

與master節點整體使用方式結果相似，但返回的結果為所有計算節點的統計資料匯總。

擷取特定表或索引快取命中情況

文法

SELECT * FROM relation_shared_buffer_stat_lockfree('<查詢的表或索引名>'::regclass);

使用樣本

SELECT * FROM relation_shared_buffer_stat_lockfree('adbpg_autoanalyze_test_table'::regclass);

返回結果如下。

           relname            | total_pages | pages_in_shared_buffer
------------------------------+-------------+------------------------
 adbpg_autoanalyze_test_table |       20055 |                   3327
(1 row)

relname：本次查詢的表或索引的名稱。
total_pages：指定表或索引在資料庫中的總頁面數量。
pages_in_shared_buffer：該表或索引當前在共用緩衝區中緩衝的頁面數量。通過比較total_pages和pages_in_shared_buffer，可以直觀地瞭解到該資料結構的快取命中潛力及實際緩衝效率，進而分析是否需要調整緩衝策略或最佳化資料訪問模式以提高緩衝利用率和資料庫查詢效能。

擷取特定表及其相關的索引和TOAST表快取命中情況

文法

SELECT * FROM relation_detail_shared_buffer_stat_lockfree('<查詢的表或索引名>'::regclass);

參數說明

當傳入值是表的名稱，此時返回的結果包含表自身、表的索引、依賴於該表的TOAST表的快取命中情況。
當傳入值是索引名稱（通常索引沒有子依賴），返回結果只有索引本身的快取命中情況。

使用樣本

SELECT * FROM relation_detail_shared_buffer_stat_lockfree('adbpg_autoanalyze_test_table'::regclass);

返回結果如下。

           relname            | total_pages | pages_in_shared_buffer
------------------------------+-------------+------------------------
 adbpg_autoanalyze_test_table |       20055 |                   3327
(1 row)

通過該方法，不僅可以擷取指定表自身的緩衝情況，還能深入瞭解與之相關的索引或TOAST表在共用緩衝區中的緩衝情況。這一資訊對於全面分析和最佳化資料庫效能十分關鍵。

例如，在上述樣本中，儘管表adbpg_autoanalyze_test_table有相當一部分頁面（3327/20055）位於共用緩衝區中，提高了查詢效率。但與其關聯的索引adbpg_autoanalyze_test_table_i_idx卻沒有任何頁面被緩衝。這可能導致在使用索引查詢時，出現效能不佳的情況。