技術應用丨DWS 空間釋放(vacuum full) 最佳實踐

華為雲開發者社群發表於2020-12-02

摘要：本文主要介紹如何進行正常的VACUUM FULL 維護，及時釋放磁碟儲存。

1、背景

目前根據某專案情況，其DWS的磁碟IO效能低、庫內資料量大、物件多、資料膨脹嚴重。若毫無目的性的進行空間釋放，一方面對IO壓力很大，嚴重影響當前DWS任務執行，同時預計每次執行VACUUM FULL 時間已超過執行間隔，導致維護任務無法開展；若依據髒頁率進行磁碟空間維護，每次髒頁統計花費1天之多且有極高概率出現異常，頻繁進行髒頁統計也一定程度上影響DWS執行。

本文件主要介紹如何進行正常的VACUUM FULL 維護，及時釋放磁碟儲存。

2、說明

2.1 VACUUM FULL介紹

VACUUM FULL一方面可以及時回收空間，一方面可以一定程度上提升資料庫效能。

VACUUM FULL回收表中已經刪除的行所佔據的儲存空間。在一般的資料庫操作裡，那些已經DELETE的行並沒有從它們所屬的表中物理刪除,因此有必要週期地執行VACUUM FULL，特別是在經常更新的表上。

2.2 VACUUM FULL使用建議

VACUUM FULL 對現有DWS任務執行具有一定影響。建議從以下幾個角度考慮：

系統表

針對系統表的操作比較危險，往往伴隨著阻塞DWS正常任務或連結接入。附錄的函式中已排除掉系統表的髒頁統計。

建議：根據系統表大小(參考附錄5.3章節)，半年~一年時間進行統計，若發現膨脹情況可協調視窗期做好業務暫停準備並進行釋放。這裡不做特別說明。

普通表

可單純根據髒頁率進行評估，決定是否需要進行釋放；或通過髒頁率+表大小配合方式評估，更有目的性進行釋放。

建議：

1、首先建議確定系統執行壓力較低的時間段，在該時間段內進行髒頁統計，並根據髒頁統計效果進行VACUUM FULL 維護操作。

2、其次建議根據系統資料更新頻度，選取1~2月進行一次髒頁統計。然後根據統計結果對這些表進行VACUUM FULL 操作。

3、最後建議獲取系統髒頁時配合表大小，規則自行擬定。如：髒頁率超過20%、表大小*髒頁率釋放空間達到20GB 等等。

4、補充建議依照函式說明（附錄5.1章節），對檢視資料進行固化（建立對應表）。這樣可避免二次篩選時耗時過長，只需要對錶進行篩選即可。

5、VACUUM FULL 操作建議根據系統壓力進行調整，壓力中等情況下可使用1~2個併發。無壓力情況下可適當提升併發度。

索引

針對索引需要進行重建，這裡不做過多說明。附錄的函式中已排除掉索引統計。

2.3 新版髒頁率函式使用說明

1、建立函式及檢視

DWS中根據附錄指令碼，建立funckang_get_dirty_tuples函式及v_get_dirty_tuples檢視。需要注意檢視中註釋部分，自行決定是否保留。

2、對結果進行二次分析

使用step3步驟，將檢視內容對映成物理表。然後對物理表進行規則篩選，參考2.2章節建議部分。

3、執行vacuum full

根據篩選出的schema名、table名，進行vacuum full 語句拼接，寫入SQL檔案。

4、執行vacuum full

確定時間時間段與併發度，通過 \parallel on ${number} 方式利用客戶端併發執行。

2.4 改進後髒頁統計方式比較

3 原有髒頁統計方式說明

1. 查詢 pgxc_get_stat_all_tables （viw1)

注：檢視可以獲取髒頁率。但其中包括插入、更新刪除等許多統計資訊，同時還需要與pg_namespace 關聯。

2. pgxc_stat_all_tables(func1) 函式

注：函式自身迴圈遍歷各個CN與DN上的資訊，是個無法下推函式。

3. pg_catalog.pg_stat_all_tables(view2)

注：試圖自身需要三個系統表關聯，統計了很多無用資訊。

4 新版髒頁統計方式說明

1、 funckang_get_dirty_tuples

注：函式自身只遍歷DN上的表，同時去掉冗餘資訊。通過v_get_dirty_tuples 檢視計算表髒頁資訊，提供髒頁率及表大小統計。

2、funckang_get_dirty_tuples_from_name

注：提供根據具體schemaname、tablename 方式返回具體的表的髒頁資訊。

可根據提供的SQL進行查詢。

5 附錄

5.1 統計全庫表髒頁率

step1 ：建立獲取髒頁的函式

CREATE OR REPLACE function public.funckang_get_dirty_tuples(out v_oid oid,out v_nspname text ,out v_relname text ,out v_livetup float8 ,out v_deadtup float8) returns setof record
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
/*
-- =============================================================================
-- Program Name:  獲取資料髒頁率
-- Program ID： funckang_get_dirty_tuples
-- Revision：1.0
-- Author： by kanghaifeng
-- Create date: 2020/11/04
-- =============================================================================
*/
 row_data record;
 dn_name record;
 query_str text;
 query_str_nodes text;
 BEGIN
  --Get all the node names
  query_str_nodes := 'SELECT node_name FROM pgxc_node WHERE node_type = ''D''';
  FOR dn_name IN EXECUTE(query_str_nodes) LOOP
query_str := 'EXECUTE DIRECT ON (' || dn_name.node_name || ') ''SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_live_tuples(c.oid) AS n_live_tup,pg_stat_get_dead_tuples(c.oid) AS n_dead_tup    
   FROM pg_class c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname not in (''''pg_catalog'''',''''information_schema'''',''''sys'''',''''cstore'''',''''pmk'''')
 and n.nspname not like ''''pg_temp%''''
 AND n.nspname !~ ''''^pg_toast''''
 and c.relkind=''''r'''' 
  GROUP BY c.oid, n.nspname, c.relname'' ';
   FOR row_data IN EXECUTE(query_str) LOOP
    --insert into kang_tup values(row_data.relid,row_data.schemaname,row_data.relname,row_data.n_live_tup,row_data.n_dead_tup);
    v_oid :=row_data.relid;
    v_nspname:=row_data.schemaname;
    v_relname:=row_data.relname;
    v_livetup:=row_data.n_live_tup;
    v_deadtup:=row_data.n_dead_tup;
     return next ;
   END LOOP;
  END LOOP;
     return;
 END; 
$function$ 
/

step2: 建立獲取髒頁資訊的檢視，註釋部分為表大小資訊，可根據需要決定是否需要。

drop view if exists public.v_get_dirty_tuples;
create view public.v_get_dirty_tuples as
SELECT 
  funckang_get_dirty_tuples.nspname,
  funckang_get_dirty_tuples.relname, 
 -- pg_table_size(funckang_get_dirty_tuples.nspname||'.'||funckang_get_dirty_tuples.relname),
  sum(funckang_get_dirty_tuples.n_live_tup) AS n_live_tup, 
  sum(funckang_get_dirty_tuples.n_dead_tup) AS n_dead_tup, 
 (sum(funckang_get_dirty_tuples.n_dead_tup) / sum((funckang_get_dirty_tuples.n_dead_tup + funckang_get_dirty_tuples.n_live_tup)::numeric + .0001) * 100::numeric)::numeric(5,2) AS dirty_page_rate
FROM public.funckang_get_dirty_tuples() funckang_get_dirty_tuples(oid,nspname,relname,n_live_tup,n_dead_tup)
GROUP BY funckang_get_dirty_tuples.nspname,funckang_get_dirty_tuples.relname;

step3: 因檢視查詢耗時，建議建立一個表將檢視內容固話下來做進一步分析。

create table public.zangye as select * from public.v_get_dirty_tuples;

5.2 根據給定表返回髒頁率