PostgreSQL 如何查找需要收集的vacuum 表信息

前言

通常，在PostgreSQL中，由于经常需要对表进行 UPDATE 和 DELETE，因此表会产生碎片空间。

在 PostgreSQL中，使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用，以便以后重用这些空间，但是不能立即将占用的空间返还给操作系统，因此需要使用 VACUUM FULL，才可以释放空间，并立即将空间返还给操作系统。

实现脚本

记录收集表创建

1 2	`CREATE` `TABLE` `IF` `NOT` `EXISTS tab_vacuum_record` `(sqltext text);`

收集需要VACUUM 表函数

									CREATE OR REPLACE FUNCTION f_vacuum_tables()

									RETURNS void AS

									$FUNCTION$

									 DECLARE

									 v_tablename text;

									 v_dead_cond bigint;

									 v_sql    text;

									 cur_tablename REFCURSOR;

									 v_vacuum_record text;

									 BEGIN

									 v_vacuum_record := 'tab_vacuum_record';

									 OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql';

									 LOOP

									  FETCH cur_tablename INTO v_tablename;

									   SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;

									     IF v_dead_cond > 0 THEN

									      v_sql := 'INSERT INTO ' || v_vacuum_record || ' VALUES(' || chr(39) ||'VACUUM FULL ' || v_tablename ||';'|| chr(39) ||')';

									    EXECUTE v_sql;

									     END IF;

									   EXIT WHEN NOT FOUND;

									 END LOOP;

									 CLOSE cur_tablename;

									 END;

									$FUNCTION$

									LANGUAGE PLPGSQL;

SHELL脚本

									#!/bin/bash

									#获取环境变量

									CURRDIR=$(cd "$(dirname $0)";pwd)

									TOPDIR=$(cd $CURRDIR/..;pwd)

									CONFIG=$TOPDIR/conf/host.ini

									CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql

									CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql

									source $CONFIG

									CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"

									function check_status()

									{

									    echo "检查数据库服务器状态是否正常 !"

									    stat=`$CONNINFO -Aqt -c 'SELECT 1'`

									    if [ "${stat}" == "1" ];then

									        echo "服务器连接正常"

									    else

									        echo "服务器连接异常,退出"

									        exit -1;

									    fi

									}

									function create_table()

									{

									    echo "创建收集需要vacuum的表"

									    $CONNINFO -f $CT_FILE

									}

									function create_function()

									{

									    echo "创建收集需要 vacuum 表的函数"

									    $CONNINFO -f $CT_FUNCTION

									}

									check_status

									create_table

									create_function

执行方式

									postgres=# SELECT * FROM f_vacuum_tables();

									 f_vacuum_tables 

									-----------------

									(1 row)

									--创建测试表

									postgres=# CREATE TABLE tab_test(id int);

									--插入数据

									postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;

									INSERT 0 100000

									--删除数据

									postgres=# DELETE FROM tab_Test WHERE id <= 10000;

									DELETE 10002

									postgres=# SELECT * FROM tab_vacuum_record ;

									    sqltext    

									-----------------------

									 VACUUM FULL tab_test;

									(1 row)

该脚本也可以自己根据需要进行修改，详细见github

补充：PostgreSQL中 Vacuum 略谈

VACUUM doc

路由清理

PostgreSQL 需要定期维护清理，一般都是由守护进程自动清理的，我们只是需要参数调优，也可以执行脚本定时去清理回收。

Vacuumming Basics

PG不得不对每张表进行 Vacuum 命令，原因如下：

1、为了回收和再利用通过更新或者删除行所占用的磁盘空间

2、为了更新被PG查询计划所使用的数据分析

3、为了更新只读索引扫描的可见的集合

4、避免由于事务ID或者混合事务ID丢失历史数据

由于这些原因，在进行频繁的 VACUUM 操作时进行规定：

标准 VACUUM

进行回收时，生产环境不影响数据库库的正常使用(SELECT、INSERT、UPDATE、DELETE),并行使用，清理时不允许对表结构进行修改（ALTER TABLE）推荐使用该方案

VACUUM FULL

a、可以回收大量空间，但是比标准回收执行慢

b、运行时需要锁表

VACUUM 运行会导致读写性能比较差，所以需要调整一些参数降低影响

1 2	`temp_file_limit = -1 #默认-1表示不限制每个进程可使用的最大临时文件限制，单位kb` `#max_files_per_process = 1000 #每个子进程允许同时打开文件的最大数量`

在执行 VACUUM 和 ANYLYZE 期间，系统会维护一个用于估算各种I/O操作所消耗的内部计数器,当该值达到vacuum_cost_limit的值时，该进程会休眠 vacuum_cost_delay 指定的时间，并重置计数器的值，继续运行 VACUM 或者 ANYLYZE 操作

1 2	`vacuum_cost_limit = 200` `vacuum_cost_delay = 0 # 单位微秒，默认为 0 没有开启`

该参数 vacuum_cost_delay 主要用于并发时降低I/O的影响,推荐为10

									vacuum_cost_page_hit = 1 # 代表从缓存池查找共享的hash table并扫描 该`页`的内容

									             #的估计值

									vacuum_cost_page_miss = 10   # 0-10000 credits

									vacuum_cost_page_dirty = 20