實踐系列-GaussDB(DWS) 空間釋放(vacuum full) 最佳實踐

關于DWS 空間釋放(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任務運行具有一定影響。建議從以下幾個角度考慮：

u 系統表

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

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

u 普通表

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

建議：

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

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

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

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

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

u 索引

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

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? ? 改進后臟頁統計方式比較

序號

對比內容

1

CN+DN掃描

DN掃描

2

數據有效性

超過50%無效數據，其中包括'pg_toast','pg_catalog','information_schema'系統schema下危險表，以及索引信息

不統計系統表、索引信息

3

性能

基于6T數據測試，耗時610秒

基于6T數據測試，耗時14秒

4

成功率

統計中若任務使用臨時表將導致統計報錯

排除掉臨時表統計，成功率更高

5

統計范圍

統計非常全面，包括具體插入、刪除、更新等一些列信息

從性能角度考慮僅提供臟頁統計

6

穩定性

系統提供，經過測試

人工編寫，未進行大量測試

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? ? 根據給定表返回臟頁率

step1 ：創建獲取臟頁的函數

CREATE OR REPLACE function public.funckang_get_dirty_tuples_from_name(in out schemaname text,in out tablename text ,out v_livetup float8 ,out v_deadtup float8) returns setof record

LANGUAGE plpgsql

NOT FENCED NOT SHIPPABLE

AS $function$

DECLARE

/*

-- =============================================================================

-- Program Name: ?根據schemaname，tablename獲取數據臟頁率

-- Program ID： funckang_get_dirty_tuples_from_name

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

and n.nspname='''''||schemaname||'''''

and c.relname='''''||tablename||'''''

GROUP BY c.oid, n.nspname, c.relname'' ';

DBMS_OUTPUT.PUT_LINE(query_str);

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;

schemaname:=row_data.schemaname;

tablename:=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 ：查詢給出表的臟頁信息。下面為dbadmin.hedi2 示例。注釋部分為大小信息，可根據需要決定是否使用

SELECT

funckang_get_dirty_tuples_from_name.schemaname,

funckang_get_dirty_tuples_from_name.tablename,

-- pg_table_size(funckang_get_dirty_tuples_from_name.schemaname||'.'||funckang_get_dirty_tuples_from_name.tablename),

sum(funckang_get_dirty_tuples_from_name.n_live_tup) AS n_live_tup,

sum(funckang_get_dirty_tuples_from_name.n_dead_tup) AS n_dead_tup,

(sum(funckang_get_dirty_tuples_from_name.n_dead_tup) / sum((funckang_get_dirty_tuples_from_name.n_dead_tup + funckang_get_dirty_tuples_from_name.n_live_tup)::numeric + .0001) * 100::numeric)::numeric(5,2) AS dirty_page_rate

FROM public.funckang_get_dirty_tuples_from_name('dbadmin','hedi2') funckang_get_dirty_tuples_from_name(schemaname,tablename,n_live_tup,n_dead_tup)

GROUP BY funckang_get_dirty_tuples_from_name.schemaname,funckang_get_dirty_tuples_from_name.tablename;

5.3? ? 系統表大小統計

select

pt.schemaname

,pt.tablename

,getdistributekey(pt.schemaname||'."'||pt.tablename||'"') as distribute_key

,pg_size_pretty(pg_relation_size(pt.schemaname||'."'||pt.tablename||'"')) as tablesize

,case when pt.hasindexes = 't' then pg_size_pretty(pg_indexes_size(pt.schemaname||'."'||pt.tablename||'"')) else '' end as indexsize

,pc.reloptions

,pg_stat_get_last_analyze_time(pc.oid) as lastanalyze

,pg_stat_get_last_vacuum_time(pc.oid) as lastvacuum

,pc.parttype

from

pg_tables pt

,pg_class pc

where

(pt.schemaname||'."'||pt.tablename||'"')::regclass::oid=pc.oid and pt.schemaname not in ('mppdbpermission','information_schema','cstore','pg_catalog','pmk')

order by

pg_relation_size((pt.schemaname||'."'||pt.tablename||'"')) desc;

EI企業智能 Gauss AP 數據倉庫服務 GaussDB(DWS)

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。