文档

维护定期回收空间任务

更新时间:

云原生数据仓库AnalyticDB PostgreSQL版会默认开启自动回收空间功能(Auto Vacuum),您可以通过设置合理的回收频率、使用自动清理工具和定期检查磁盘空间等方法保持系统的整洁和高效运行。在正常情况下无需维护定期回收空间任务,若当前业务涉及大量更新删除或需要定期彻底释放脏数据占用空间时,您可以执行回收空间任务,本文介绍不同场景下如何维护定期空间回收任务。

背景介绍

由于数据的增删改查操作,会产生一些临时文件和日志文件等,这些文件会占用一定的磁盘空间。如果不及时清理,可能会导致磁盘空间不足,影响系统的正常运行。因此,需要定期进行空间回收任务,以保证系统的稳定运行。

维护回收空间任务

空间回收任务主要包括以下几个方面:

  1. 清理临时文件:在数据库的操作过程中,可能会产生一些临时文件,如事务日志、备份文件等。这些文件在完成其使命后,需要及时被清理。

  2. 回收已删除的数据:在数据库中删除数据时,实际上只是标记了该数据为已删除,但并未真正从磁盘上删除。因此,需要定期进行空间回收,将这些已被标记为删除的数据真正从磁盘上删除。

  3. 优化表结构:对于一些大型的表,可以通过优化表结构来节省磁盘空间。例如,可以考虑使用分区表、索引等方式来提高数据访问效率,同时也可以减少磁盘空间的占用。

  4. 定期备份:定期对数据库进行备份,不仅可以防止数据丢失,还可以通过删除旧的备份来释放磁盘空间。

以下为两种不同场景下如何维护回收空间任务。

不锁表回收脏数据

在进行数据更新或者删除操作时不锁定整个表,而是只对需要修改的部分进行锁定。这种做法可能会导致一些脏数据产生,优点是可以提高系统的并发性能,减少用户等待时间等。具体方式如下:

  • 命令:连接每个数据库,以数据库的所有者身份登录,执行VACUUM命令。

  • 频率:

    • 如果有大批量实时更新的情况(即不断执行INSERT VALUES、UPDATE、DELETE等操作),建议每天执行一次,或每周至少一次。

    • 如果更新是每天一次批量进行的,建议每周执行一次,或不要超过一个月执行一次。

  • 对系统影响:不会锁表,表可以正常读写。会导致CPU、I/O使用率增加,可能影响查询的性能。

  • 执行方法:

    • 可以使用pg_cron插件创建定时任务执行VACUUM,具体内容,请参见pg_cron

    • 可以使用如下的Linux Shell脚本文件连接数据库,作为crontab定期任务来执行。

    #!/bin/bash
    export PGHOST=myinst.gpdb.rds.tbsite.net
    export PGPORT=3432
    export PGUSER=myuser
    export PGPASSWORD=mypass
    #do not echo command, just get a list of db
    dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
    for db in $dblist ; do
        #skip the system databases
        if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
            continue
        fi
        echo processing $db
        #vacuum all tables (catalog tables/user tables)
        psql -d $db -e -a -c "VACUUM;"
    done

维护窗口回收脏数据

在业务暂停的维护窗口,可以回收脏数据并释放脏数据所占的空间。具体方式如下:

  • 命令:连接每个数据库,以数据库的所有者身份登录(需要对所有操作对象有所有者权限)。

    1. 执行REINDEX SYSTEM <database name>

    2. 对每张数据表,执行VACUUM FULL <table name>,对列存表还需要执行REINDEX TABLE <table name>

    3. 对于系统表(包括pg_class, pg_attribute, pg_index等),当有频繁建删表,建删索引等操作时,也建议执行 VACUUM FULL <table name>进行定期维护。注意:该操作需要业务停止访问数据库。

  • 频率:至少每周执行一次。如果每天会更新几乎所有数据,需要每天做一次。

  • 对系统影响:会对正在进行VACUUM FULL或REINDEX的表进行锁定,无法读写。会导致CPU、I/O使用率增加。

  • 执行方法:

    • 可以使用pg_cron插件创建定时任务执行VACUUM FULL和REINDEX,具体内容,请参见pg_cron

    • 可以使用如下的Linux Shell脚本文件,作为crontab定期任务来执行。

#!/bin/bash
export PGHOST=myinst.gpdb.rds.tbsite.net
export PGPORT=3432
export PGUSER=myuser
export PGPASSWORD=mypass
#do not echo command, just get a list of db
dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
for db in $dblist ; do
    #skip system databases
    if [[ $db == template0 ]] ||  [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
        continue
    fi
    echo processing db "$db"
    #do a normal vacuum
    psql -d $db -e -a -c "VACUUM;"
    #reindex system tables firstly
    psql -d $db -e -a -c "REINDEX SYSTEM $db;"
    #use a temp file to store the table list, which could be vary large
    cp /dev/null tables.txt
    #query out only the normal user tables, excluding partitions of parent tables
    psql -d $db -c "copy (select '\"'||tables.schemaname||'\".' || '\"'||tables.tablename||'\"' from (select nspname as schemaname, relname as tablename from pg_catalog.pg_class, pg_catalog.pg_namespace, pg_catalog.pg_roles where pg_class.relnamespace = pg_namespace.oid and pg_namespace.nspowner = pg_roles.oid and pg_class.relkind='r' and (pg_namespace.nspname = 'public' or pg_roles.rolsuper = 'false' ) ) as tables(schemaname, tablename) left join pg_catalog.pg_partitions on pg_partitions.partitionschemaname=tables.schemaname and pg_partitions.partitiontablename=tables.tablename where pg_partitions.partitiontablename is null) to stdout;" > tables.txt
    while read line; do
        #some table name may contain the $ sign, so escape it
        line=`echo $line |sed 's/\\\$/\\\\\\\$/g'`
        echo processing table "$line"
        #vacuum full this table, which will lock the table
        psql -d $db -e -a -c "VACUUM FULL $line;"
        #reindex the table to reclaim index space
        psql -d $db -e -a -c "REINDEX TABLE $line;"
    done <tables.txt
done