GaussDB(DWS)实践系列-数据仓库自动化清理功能实现

数据仓库自动化清理功能实现

摘要：

定期清理数据库中垃圾数据、更新统计信息可以提升系统整体运行效率。本文旨在对3种常用的清理和收集命令进行讲解，并汇总整理自动化清理脚本。

（1）vacuum介绍：VACUUM回收表或B-Tree索引中已经删除的行所占据的存储空间。在一般的数据库操作里，那些已经DELETE的行并没有从它们所属的表中物理删除，在完成VACUUM之前它们仍然存在。因此有必要周期地运行VACUUM，特别是在经常更新的表上。

VACUUM FULL通常要比单纯的VACUUM收缩更多的表尺寸，但是需要耗时更多。如果导入过程中，进行了大量的更新或删除行时，会产生大量的磁盘页面碎片，从而逐渐降低查询的效率。应运行VACUUM FULL命令将磁盘页面碎片恢复并交还操作系统，然后运行ANALYZE命令更新统计信息。

（2）analyze介绍：在数据库中，统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。analyze命令可收集与数据库中表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中。查询优化器会使用这些统计数据，以生成最有效的执行计划。建议在执行了大批量插入/删除操作后，例行对表或全库执行ANALYZE语句更新统计信息。

（3）reindex介绍：REINDEX功能描述为表中的数据重建索引。VACUUM FULL通常要比单纯的VACUUM收缩更多的表尺寸，但是FULL选项并不清理索引，所以推荐周期性的运行REINDEX命令。

在以下几种情况下需要使用REINDEX重建索引：

1、索引崩溃，并且不再包含有效的数据。

2、索引变得“臃肿”，包含大量的空页或接近空页。

3、为索引更改了存储参数（例如填充因子），并且希望这个更改完全生效。

自动化清理脚本如下：

#!/bin/bash

PGPORT=25308

parallel=20

source /opt/huawei/Bigdata/mppdb/.mppdbgs_profile

function do_vaccumm_full_systable

{

TABLELIST=" '\"'|| nspname || '\".\"' || relname   || '\";' FROM pg_class c

INNER JOIN   pg_namespace n ON c.relnamespace = n.oid

WHERE   c.oid < 16384 AND relkind = 'r'

AND   reloptions::text NOT LIKE '%internal_mask%'";

echo   "********Start Vacuum Full Systable For Database "$1`date   "+%Y-%m-%d %H:%M:%S"`

gsql -p   $PGPORT -d $1 -tc "SELECT 'VACUUM FULL ' || $TABLELIST" | gsql -p   $PGPORT -d $1

gsql -p   $PGPORT -d $1 -tc "SELECT 'REINDEX    table ' || $TABLELIST" | gsql -p $PGPORT -d $1

gsql -p   $PGPORT -d $1 -tc "SELECT 'ANALYZE ' || $TABLELIST" | gsql -p   $PGPORT -d $1

}

function do_vaccumm_full_udt

{

TABLELIST=" '\"'|| nspname || '\".\"' || relname   || '\";' FROM pg_class c

INNER JOIN   pg_namespace n ON c.relnamespace = n.oid

WHERE   c.oid >=16384 AND relkind = 'r'

AND   reloptions::text NOT LIKE '%internal_mask%'";

echo   "********Start Vacuum Full User-Define Table For Database "$1`date   "+%Y-%m-%d %H:%M:%S"`

echo   "\parallel on "$parallel > /dev/shm/vacuum_full_list

gsql -p   $PGPORT -d $1 -tc "SELECT 'VACUUM FULL ' || $TABLELIST"  >> /dev/shm/vacuum_full_list

echo   "\parallel off" >> /dev/shm/vacuum_full_list

gsql -p   $PGPORT -d $1 -f /dev/shm/vacuum_full_list

echo   "********Start Reinex User-Define Table For Database "$1`date   "+%Y-%m-%d %H:%M:%S"`

echo   "\parallel on "$parallel > /dev/shm/reindex_list

gsql -p   $PGPORT -d $1 -tc "SELECT 'REINDEX TABLE ' || $TABLELIST"  >> /dev/shm/reindex_list

echo   "\parallel off" >> /dev/shm/reindex_list

gsql -p   $PGPORT -d $1 -f /dev/shm/reindex_list

echo   "********Start Analyze User-Define Table For Database "$1`date   "+%Y-%m-%d %H:%M:%S"`

echo   "\parallel on "$parallel > /dev/shm/analyze_list

gsql -p   $PGPORT -d $1 -tc "SELECT 'ANALYZE ' || $TABLELIST"  >> /dev/shm/analyze_list

echo   "\parallel off" >> /dev/shm/analyze_list

gsql -p   $PGPORT -d $1 -f /dev/shm/analyze_list

}

function do_vaccumm_full

{

do_vaccumm_full_systable $1

do_vaccumm_full_udt $1

}

function main

{

do_vaccumm_full  gn_province_cz

do_vaccumm_full  gn_shb_cz

}

main @

华为云社区论坛链接：https://bbs.huaweicloud.com/forum/forum-598-1.html

EI企业智能 Gauss AP

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。