关于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    改进后脏页统计方式比较

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;