全部产品
弹性计算 会员服务 网络 安全 移动云 数加·大数据分析及展现 数加·大数据应用 管理与监控 云通信 阿里云办公 培训与认证 更多
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 智能硬件

定期维护任务

更新时间:2017-12-12 11:49:38

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

不锁表回收垃圾

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

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

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

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

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

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

维护窗口回收垃圾

在业务暂停的维护窗口,可以回收所有垃圾。具体方式如下:

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

    1. 执行REINDEX SYSTEM <database name>
    2. 对每张数据表(非系统表),执行VACUUM FULL <table name>REINDEX TABLE <table name>
  • 频率:至少每周执行一次。如果每天会更新几乎所有数据,需要每天做一次。

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

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

  1. #!/bin/bash
  2. export PGHOST=myinst.gpdb.rds.tbsite.net
  3. export PGPORT=3432
  4. export PGUSER=myuser
  5. export PGPASSWORD=mypass
  6. #do not echo command, just get a list of db
  7. dblist=`psql -d postgres -c "copy (select datname from pg_stat_database) to stdout"`
  8. for db in $dblist ; do
  9. #skip system databases
  10. if [[ $db == template0 ]] || [[ $db == template1 ]] || [[ $db == postgres ]] || [[ $db == gpdb ]] ; then
  11. continue
  12. fi
  13. echo processing db "$db"
  14. #do a normal vacuum
  15. psql -d $db -e -a -c "VACUUM;"
  16. #reindex system tables firstly
  17. psql -d $db -e -a -c "REINDEX SYSTEM $db;"
  18. #use a temp file to store the table list, which could be vary large
  19. cp /dev/null tables.txt
  20. #query out only the normal user tables, excluding partitions of parent tables
  21. 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
  22. while read line; do
  23. #some table name may contain the $ sign, so escape it
  24. line=`echo $line |sed 's/\\\$/\\\\\\\$/g'`
  25. echo processing table "$line"
  26. #vacuum full this table, which will lock the table
  27. psql -d $db -e -a -c "VACUUM FULL $line;"
  28. #reindex the table to reclaim index space
  29. psql -d $db -e -a -c "REINDEX TABLE $line;"
  30. done <tables.txt
  31. done
本文导读目录