Pgsql表膨胀的产生及处理

表膨胀的产生

我们知道Oracle的多版本并发控制是基于块级的,利用回滚段(UNDO)机制。在回滚段中保存了某个数据被修改之前的前映像的数据。而PostgreSQL中没有UNDO这一概念,PostgreSQL中的多版本并发是通过在表中数据行的多个版本来实现的,例如在一张表中我们要更新一条记录,PG并不是直接修改该数据,而是通过插入一条全新的数据,同时对老数据加以标识。

PostgreSQL的MVCC机制在数据更新时会产生死元组(dead tuples),如果果不清理掉那些dead tuples(对任何事务都是不可见的)将会永远留在数据文件中,浪费磁盘空间,对于表来说,有过多的删除和更新,dead tuples很容易占绝大部分磁盘空间。而且dead tuples也会在索引中存在,更加加重磁盘空间的浪费。这在PostgreSQL中称之为膨胀(bloat)。自然的,需要处理的数据查询越多,查询的速度就越慢。

表膨胀的查询

查询表膨胀可以使用插件pgstattuple,提供了pgstatetuple()和pgstatindex()两个统计表和索引的方法。

初次使用这个插件时需要创建:

Pgsql表膨胀的产生及处理

创建测试表:

Pgsql表膨胀的产生及处理

查看表的死元组,可以看到刚建的表死元组为0,表膨胀系数0:

Pgsql表膨胀的产生及处理
Pgsql表膨胀的产生及处理

做些DML操作,观察表的死元组占比13%,表的膨胀系数34%:

Pgsql表膨胀的产生及处理
Pgsql表膨胀的产生及处理

表膨胀的清理

1. 执行 vacuum清理死元组,清理后表的死元组为0,表的膨胀系数还是34%:

Pgsql表膨胀的产生及处理
Pgsql表膨胀的产生及处理

可见vacuum命令通常不会将磁盘空间返回给操作系统,但它将使它对新行可用。这时我们需要使用vacuum full 命令来回收空间。

Pgsql表膨胀的产生及处理
Pgsql表膨胀的产生及处理

注意:vacuum full 会回收空间并返回给操作系统,但是它有很多缺点。首先,它会产生独立锁,阻塞所有操作(包括select).其次,它实际上创建了一个表的副本,使所需的磁盘空间加倍,因此当系统可用磁盘空间很少时,就不太实用。

2. 插件实现:

vacuum full会产生独立锁,阻塞所有操作(包括select),我们可以使用插件pg_squeeze来完成这个操作,它使用REDO和logical replication实现增量重组,不需要建立触发器,但是要求表上面有PK或者UK。

由于pg_squeeze需要使用logical replication,所以必须设置足够多的slots,而且必须注意可能与STANDBY争抢SLOTS,必须预留足够的SLOTS。

使用示例 squeeze.squeeze_table(tabchema name, tabname name,

clustering_index name, rel_tablespace name, ind_tablespaces name[]):

Pgsql表膨胀的产生及处理
Pgsql表膨胀的产生及处理

另外由于pg_squeeze可以自动,也可以不设置自动的收缩。对于自动的收缩,建议不要对繁忙的数据库开启,以免在高峰期触发,带来一定的性能影响。

–注册任务(示例表示:public下的表test,在每天的23:10分检查,当表的空闲空间超过10时对表重建)insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values (‘public’, ‘test’, (‘{10}’, ‘{23}’, NULL, NULL, NULL), ’10’);–查看任务select * from squeeze.tables;–开启进程select squeeze.start_worker();–查看任务记录select * from squeeze.tables_internal;–关闭进程select squeeze.stop_worker();

任务完成后可以查看任务记录:

Pgsql表膨胀的产生及处理
Pgsql表膨胀的产生及处理

发表评论

登录后才能评论
服务中心
服务中心
联系客服
联系客服
返回顶部