文章详情页_AnalyticDB PostgreSQL 空间回收

表中的数据被删除或更新后（UPDATE/DELETE），物理存储层面并不会直接删除数据，而是标记这些数据不可见，所以会在数据页中留下很多“空洞”，在读取数据时，这些“空洞”会随数据页一起加载，拖慢数据扫描速度，需要定期回收删除的空间。

空间回收方法

使用 VACUUM 命令，可以对表进行重新整理，回收空间，以便获取更好的数据读取性能。VACUUM 命令如下：

VACUUM [FULL] [FREEZE] [VERBOSE] [table];

VACUUM 会在页内进行整理，VACUUM FULL 会跨数据页移动数据。 VACUUM 执行速度更快， VACUUM FULL 执行地更彻底，但会请求排他锁。建议定期对系统表进行 VACUUM（每周一次）。

注：执行 VACUUM 命令后，由于系统采集信息需要一定时间，您需要在执行命令后稍微等待一段时间才能看到回收的存储空间。

使用建议

什么情况下做 VACUUM？

不锁表回收空间，只能回收部分空间。
频率：对于有较多实时更新的表，每天做一次。
如果更新是每天一次批量进行的，可以在每天批量更新后做一次。
对系统影响：不会锁表，表可以正常读写。会导致 CPU、I/O 使用率增加，可能影响查询的性能。

什么情况下做 VACUUM FULL？

锁表，通过重建表，回收所有空洞空间。对做了大量更新后的表，建议尽快执行 VACUUM FULL。
频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。
对系统影响：会对正在进行 VACUUM FULL 的表锁定，无法读写。会导致 CPU、I/O 使用率增加。建议在维护窗口进行操作。

查询需要执行 VACUUM 的表

AnalyticDB PostgreSQL 提供了一个 gp_bloat_diag 视图，统计当前页数和实际需要页数的比例。通过 analyze table 来收集统计信息之后，查看该视图。

gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_diag;

其结果只包括发生了中度或者显著膨胀的表。当实际页面数和预期页面的比率超过4但小于10时，就会报告为中度膨胀。当该比率超过10时就会报告显著膨胀。对于这些表，可以考虑进行 VACUUM FULL 来回收空间。

VACUUM FREEZE 的使用

AnalyticDB PostgreSQL 执行的所有事务都有唯一的事务 ID(XID)，XID 是单调递增的，上限是20亿。

随着数据库执行事务的增多，为防止 XID 超过极限，在 XID 接近 xid_stop_limit-xid_warn_limit（默认500000000）时，AnalyticDB PostgreSQL 会对执行事务的 sql 返回 warning 信息，提醒用户：

WARNING: database "database_name" must be vacuumed within number_of_transactions transactions

用户可通过手动执行 VACUUM FREEZE 当前 database 来缩小 XID。

如果忽略这个 warning 信息，在 XID 继续增长到超过xid_stop_limit（默认1000000000）时，AnalyticDB PostgreSQL会拒绝新的事务执行，并返回报错信息：

FATAL: database is not accepting commands to avoid wraparound data loss in database "database_name"

维护定期回收空间任务

当系统有更新操作（包括 INSERT VALUES、UPDATE、DELETE、ALTER TABLE ADD COLUMN 等），会在系统表和被更新的数据表中留存不再使用的垃圾数据，造成系统性能下降，并占用大量磁盘空间，因此需要定期进行垃圾回收。

AUTO VACUUM

AUTO VACUUM 可以自动执行 VACUUM 命令。AUTO VACUUM 将检查具有大量插入、更新或删除的表，并在需要的时候主动对表执行 VACUUM 来回收表中的垃圾数据，提升查询速度。默认情况下，当表被删除行数超过一半时，AUTO VACUUM 会对表执行 VACUUM 操作来清除垃圾数据。

对于 MULTI MASTER 实例，当前暂时只能追踪主 MASTER 上发生的改动行为，辅助 MASTER 发生的改动行为将不会触发 AUTO VACUUM。

不锁表回收垃圾

在不锁表的情况下，可以回收部分垃圾。具体方式如下：

命令：连接每个数据库，以数据库的所有者身份登录，执行 VACUUM 命令。
频率：如果有大批量实时更新的情况（即不断执行INSERT VALUES、UPDATE、DELETE等操作），建议每天执行一次，或每周至少一次；如果更新是每天一次批量进行的，建议每周执行一次，或不要超过一个月执行一次。
对系统影响：不会锁表，表可以正常读写。会导致 CPU、I/O 使用率增加，可能影响查询的性能。

维护窗口回收垃圾

在业务暂停的维护窗口，可以回收所有垃圾。具体方式如下：

命令：连接每个数据库，以数据库的所有者身份登录（需要对所有操作对象有所有者权限）。

对每张数据表，执行 VACUUM FULL <table name>，对列存表还需要执行 REINDEX TABLE <table name>
对于系统表（包括 pg_class, pg_attribute, pg_index 等），当有频繁建删表，建删索引等操作时，也建议执行 VACUUM FULL <table name> 进行定期维护。注意：该操作需要业务停止访问数据库。
频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。
对系统影响：会对正在进行 VACUUM FULL 或 REINDEX 的表进行锁定，无法读写。会导致 CPU、I/O 使用率增加

创建资源

export db_ip="ip_address"
export db_name="dbname"
export db_port="3432"
export adb_username="username"
export adb_password=password

# adb连接字符串
export pub_adb_params_ConStr="host=$db_ip port=$db_port dbname=$db_name user=$adb_username password=$adb_password"

创建 Shell 脚本

#!/bin/bash

# Step1: 读取连接字符串
source ./pub_adb_params.sh

# Step2: 获取数据库列表
dblist=`psql "$pub_adb_params_ConStr" -c "copy (select datname from pg_stat_database where datname='cjdmdb') to stdout"`
# echo "$dblist"

for db in $dblist ; do
    # skip system databases
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] || [[ $db == cjdm_adb_ower ]] ; then
        continue
    fi
    echo processing db "$db"

    tblist=`psql "$pub_adb_params_ConStr" -c "copy (
        select
            table_name
        from (
            select
                table_name,
                pg_table_size(table_name) as table_size,
                pg_indexes_size(table_name) as indexes_size,
                pg_total_relation_size(table_name) as total_size
            from (
                select
                    (table_schema || '.' || table_name) as table_name
                from
                    information_schema.tables
            ) as all_tables
            order by
                total_size desc
        ) as pretty_sizes 
        where table_name not like 'cjdm_invest.stg%'
            and table_name not like 'cjdm_invest.temp%'
            and table_name not like 'cjdm_invest.test%'
            and table_name not like 'cjdm_invest.v_%'
            and table_name like '%cjdm_invest%'
            and (
                total_size > 100 * 1024 * 1024 or 
                indexes_size > 100 * 1024 * 1024
            )
    ) to stdout"`
    # echo "$tblist"
    for tb in $tblist ; do    
        echo processing table "$tb" 

        # vacuum full this table, which will lock the table
        vacuum="vacuum full $tb"
        eval "psql \"$pub_adb_params_ConStr\" -c \"$vacuum\""

        # reindex the table to reclaim index space
        reindex="reindex table $tb"
        eval "psql \"$pub_adb_params_ConStr\" -c \"$reindex\""

        analyze="analyze $tb"
        eval "psql \"$pub_adb_params_ConStr\" -c \"$analyze\""
    done 
done

参考资料

空间回收

原创文章，转载请注明出处：http://www.opcoder.cn/article/64/