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

四叶草 发表于 2020/06/17 10:30:07 2020/06/17
【摘要】 定期清理数据库中垃圾数据、更新统计信息可以提升系统整体运行效率。本文旨在对3种常用的清理和收集命令进行讲解,并汇总整理自动化清理脚本。

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

 

摘要:

定期清理数据库中垃圾数据、更新统计信息可以提升系统整体运行效率。本文旨在对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

 


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区),文章链接,文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:cloudbbs@huaweicloud.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。