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

不锁表回收垃圾

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

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

  • 频率:至少每天做一次。

    • 如果有实时更新的情况(即不断执行INSERT VALUES、UPDATE、DELETE等操作),最好每两个小时执行一次。
    • 如果更新是每天一次批量进行的,可以在每天批量更新后做一次。
  • 对系统影响:不会锁表,表可以正常读写。会导致CPU、I/O使用率增加,可能影响查询的性能。

  • 示例的脚本文件:可以使用如下的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>
  • 频率:至少每周执行一次。如果每天会更新几乎所有数据,需要每天做一次。

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

  • 示例的脚本文件:可以使用如下的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