AnalyticDB：維護定期回收空間任務

背景介紹

由於資料的增刪改查操作，會產生一些臨時檔案和記錄檔等，這些檔案會佔用一定的磁碟空間。如果不及時清理，可能會導致磁碟空間不足，影響系統的正常運行。因此，需要定期進行空間回收任務，以保證系統的穩定運行。

維護回收空間任務

空間回收任務主要包括以下幾個方面：

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