postgresql垃圾清理插件pg_repack

最新推荐文章于 2024-08-22 09:34:34 发布

gguxxing008

最新推荐文章于 2024-08-22 09:34:34 发布

阅读量1.2k

点赞数 1

分类专栏： PostgreSql

本文链接：https://blog.csdn.net/gguxxing008/article/details/106522257

版权

PostgreSql 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

pg_repack插件部署：

wget http://api.pgxn.org/dist/pg_repack/1.4.5/pg_repack-1.4.5.zip
source /home/postgres/.bash_profile
make
make install

create extension pg_repack;--到指定数据库下执行

Postgresql通过数据多版本实现mvcc，删除数据并不会真正删除数据，而是修改标识，更新是通过删除+插入的方式进行，所以在频繁更新的系统，数据膨胀是个头疼的问题，如果不进行处理，数据膨胀倍数可能达到十几倍。为了处理膨胀问题，pg提供了vacuum工具，vacuum分为普通vacuum（共享锁）和vacuumfull（排它锁），普通vacuum会清理死元组，但是不会进行空间重组，磁盘上的空间不会释放，但是会释放死元组的空间，后续插入的元组会根据空闲空间管理fsm优先插入空闲空间。Vacuum full清理会释放磁盘空间，但是会获取八级锁，因为vacuum full的原理是新建一个表数据文件，然后从老表中拷贝数据到新文件中，这个过程会阻塞select。因为影响业务，pg社区开发了pg_repack工具，老版本叫pg_reorg。Pg_repack以extension的方式存在，用户可以自己安装该插件。本文简单介绍一下pg_repack的使用。

经过测试，在执行pg_repack的同时对表进行并发查询，性能下降大概只有10%到20%，读取操作可以正常进行。并且表的oid没有发生变化，repack执行完成后，通过pg_relation_filepath()函数查询发现表的数据文件发生了改变，同时会删除原来的数据文件，其实执行vacuum full数据文件也会发生改变。

repack原理：

下面聊聊repack的原理吧，原理其实和vacuum full是类似的，都是新建一个文件，然后将老文件数据拷贝过来，然后进行文件切换，它不阻塞读写的秘诀就是新建文件和拷贝的过程是在线做的，在没有完成拷贝之前，原来的文件还是可以读写的，只有在切表那一瞬间可能会有影响。那么它是怎么做到在线拷贝的呢？源库的数据文件一直在变，所以表文件其实分为两部分，一部分是基础数据，一部分是增量数据，基础数据的拷贝就是正常的拷贝，增量数据是通过创建触发器来捕获在该表上的读写操作来实现的，待基础数据拷贝完后再将trigger捕获的增量sql进行应用，达到最终结果。

我们其实可以发现，pg_repack会在库里创建名为repack的schema，里面有两张表：primary_keys和tables。Primarys分为两列，第一列indrelid代表表的oid，第二列indexrelid代表主键或唯一索引的oid。Tables表记录了创建trigger以及捕获的相关语句，语句按一条条的record进行记录。

下面我们就是要模拟一个表膨胀的案例，然后再用 pg_repack 来解决一些问题

1 我们在postgres 数据库中创建一张表

CREATE TABLE test (id serial primary key,num1 bigint, num2 double precision, num3 double precision);

2 插入测试数据

INSERT INTO test (num1, num2, num3)

SELECT round(random()*10), random(), random()*142

FROM generate_series(1, 2000000) s(i);

下面我们就开始进行一个表膨胀的操作，我们开启两个事物

1第一个事物往表里面在插入 200万的数据

2 第二个事物更新表里面的某个字段的值

补充：oid2name -d d2 -t test oid2name -d d2 -f 文件名 -x

repack应用：

pg_repack -d postgres --table public.test;

补充用法：

pg_repack --no-order --table test_1 test
pg_repack --wait-timeout 3600 --jobs 10 --no-order -d test
pg_repack --wait-timeout 3600 --jobs 10 --no-order --schema=test -d test
pg_repack --wait-timeout 3600 --jobs 10 --only-indexes --table test.test_1 --no-order -d test
pg_repack --wait-timeout 3600 --jobs 10 --index test.idx1 --no-order -d test

这相当于重新写了一个新的文件，将原来的物理文件踢掉，重新对表进行了一次整理。那这样的好处不光是表的占用空间变小了，收益的还有访问表的速度也会更快。最后这个命令还可以并行运行，后面加参数 J 和你的并行数。