Zeuk, Zozo's father

PostgreSQL 定期 vacuum

2019.03.04

在 PostgreSQL 中,使用 delete 和 update 语句删除或更新的数据行并没有被实际删除,而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。 因此当数据表中的数据变化极为频繁时,那么在一段时间之后该表所占用的空间将会变得很大,然而数据量却可能变化不大。

要解决该问题,需要定期对数据变化频繁的数据表执行垃圾清理,而 vaccum 就是维护数据库磁盘空间的工具,其重要的作用是删除那些已经标示为删除的数据并释放空间。

虽然在新版本的 PostgreSQL 中有自动的 vacuum ,但是如果是大批量的数据IO可能会导致自动执行很慢,需要配合手动执行以及自己的脚本来清理数据库。

vacuumdb 工具是 SQL 命令 vaccum 的封装,所以用 vacuumdb 和 vacuum 来清理数据库都可以,效果是一样的。

vacuumdb 重要参数说明,可以用 vacuumdb --help 查询

-a # 清理所有数据库
-d dbname # 只清理指定的数据库
-f # 执行 full 的 vacuum
-t tablename # 只清理指定的表
-z # 更新优化器统计信息
-v # 打印输出

手动 vacuum

# 执行 shell 命令的用户需要是数据库超级用户,我这里是 postgres 用户
# 切换至 postgres 用户
su - postgres
# 执行数据垃圾清理
vacuumdb -d dbname -f -z -v

定期 vacuum

每次手动执行 vacuum 也是挺麻烦的,可以使用 Linux 系统的 crontab 定时执行清理任务并保存清理日志到文件。

1.首先,使用 postgres 用户在用户目录下新建 vacuum 目录,并在目录下新建 log 目录和 vacuumdb.sh 脚本文件。

# 切换至 postgres 用户
su - postgres
# 创建 vacuum 和 log 目录
mkdir -p ~/vacuum/log
# 创建 vacuumdb.sh 并编辑
nano ~/vacuum/vacuumdb.sh
# 赋予 vacuumdb.sh 可执行权限
chmod a+x ~/vacuum/vacuumdb.sh

vacuumdb.sh 文件内容如下

#!/bin/bash
##############
. /etc/profile
. ~/.profile
##############
echo -------- start vacuum --------
export Today=`date +%Y%m%d`
# 将每天的清理信息记录到以当天日期为名称的log文件中
vacuumdb -d zozo_timesun -f -z -v >> ~/vacuum/log/${Today}.log 2>&1
echo -------- end vacuum --------

2.使用 postgres 用户创建定时任务

crontab -e

# 在文件内添加如下行: 代表每天凌晨2点执行任务 
00 02   * * *  /bin/sh ~/vacuum/vacuumdb.sh
发表评论