維護定期回收空間任務 - AnalyticDB for PostgreSQL

當系統有更新操作（包括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

維護視窗回收垃圾

在業務暫停維護視窗，可以回收所有垃圾。具體方式如下：

命令：串連每個資料庫，以資料庫的所有者身份登入（需要對所有操作對象有所有者許可權）。
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使用率增加。
樣本的指令檔：可以使用如下的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