摘要： 本文次要介绍如何进行失常的 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) 最佳实际》，原文作者：独孤求败马？。
点击关注，第一工夫理解华为云陈腐技术~