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

桃扇骨 2022-10-28 10:08 165阅读 0赞

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

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

实现脚本  
记录收集表创建  
1  
2  
CREATE TABLE IF NOT EXISTS tab\_vacuum\_record  
(sqltext text);  
收集需要VACUUM 表函数  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
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脚本  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
\#!/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  
执行方式  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
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

1  
2  
3  
4  
vacuum\_cost\_page\_hit = 1 \# 代表从缓存池查找共享的hash table并扫描 该\`页\`的内容  
\#的估计值  
vacuum\_cost\_page\_miss = 10 \# 0-10000 credits  
vacuum\_cost\_page\_dirty = 20  
NOTE  
当一张表中包含了大量数据时，同时进行删除或者更新操作时，VACUUM 并不是最好的方案,

如果有该情况，则应该使用 VACUU FULL ,当执行 ALTER TABLE 时，会重新 COPY整

个表和重新构建索引，会进行执行锁，临时占用和原始表大小的磁盘空间，直到新数据COPY完成。

升级执行计划  
执行计划通过自己或者 VACUUM调用命令 ANALYZE 收集统计，

创建 表达式索引 能够提高查询执行计划

1  
default\_statistics\_target = 100 \#提高查询的 析计划  
以上为个人经验，希望能给大家一个参考