当系统有更新操作(包括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。
说明 云原生数据仓库AnalyticDB PostgreSQL版仅20210527及以后版本支持AUTO VACUUM功能,如何升级小版本,请参见版本升级。
不锁表回收垃圾
在不锁表的情况下,可以回收部分垃圾。具体方式如下:
- 命令:连接每个数据库,以数据库的所有者身份登录,执行
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
维护窗口回收垃圾
在业务暂停的维护窗口,可以回收所有垃圾。具体方式如下:
- 命令:连接每个数据库,以数据库的所有者身份登录(需要对所有操作对象有所有者权限)。
- 执行
REINDEX SYSTEM <database name>
。 - 对每张数据表,执行
VACUUM FULL <table name>
,对列存表还需要执行REINDEX TABLE <table name>
。 - 对于系统表(包括pg_class, pg_attribute, pg_index等),当有频繁建删表,建删索引等操作时,也建议执行 VACUUM FULL <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