实践系列-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小时内删除侵权内容。