摘要：本文次要介绍如何进行失常的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 依据给定表返回脏页率
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;
本文分享自华为云社区《对于DWS 空间开释(vacuum full) 最佳实际》，原文作者： 独孤求败马？ 。
点击关注，第一工夫理解华为云陈腐技术~