PostgreSQL表膨胀的前世今生

IT那活儿发布于2023-01-11 13:20 / 1219人阅读

PostgreSQL表膨胀的前世今生

当你的数据库快速增长的时候，一定需要注意一件事，那就是“表膨胀”。内置的方法是使用VACUUM或者VACUUMFULL来解决表膨胀问题，但是有一些缺点。

[

一、什么是表膨胀

]

PostgreSQL使用多版本模型MVCC。实现的方法和Oracle和MySQL不同，当执行update或者是delete的时，Oracle和MySQL会在undo中维护前镜像，用于实现数据库的一致性(C)。例如一个旧事务依赖于已删除的行，此行仍然对其可见，因为它的前镜像依然保存在undo中。而Oracledba经常会遇到ORA-01555错误，这个错误就是事务需要的前镜像已经被覆盖了。

但是这种问题不会在PostgreSQL中出现，因为PostgreSQL是在自己表中维护数据过去的版本和最新的版本。这也就是说在PG的概念中，Undo是存在自己的表里。

下面我们通过Greenplum的官方文档的图来说明，在PostgreSQL中磁盘存储和内存中最小管理的单位都是Page。而Page中包含Tuple(元组)。元组是一种比较学术的说法，实际上可以理解成是数据库中的行或者记录。当数据库插入一条记录的时候，就会使用page中unused的空间，新增Tuple(元组)。如果Page空间满了，就会使用新的Page。

delete操作，直接就是把元组标记为dead，并不会真正的从物理上删除。Update操作会使用unused的空间创建一个新的元组，然后把旧的数据直接标记为dead。如果这个表上很频繁的做事务，则会出现很多的deadtuple（元组），逐步堆积的deadtuple会将空间耗尽，同时当做全表扫描的时候会产生非常多的额外I/O，对查询速度产生影响。

PostgreSQL具有VACUMM功能，主要有两种方式，一种是VACUMM，另外一种是VACUMMFull。

VACUMM命令可以删除deadtuple。如果删除的记录位于表的末端，其所占用的空间将会被物理释放并归还给操作系统。如果不是末端数据，VACUMM会将死元组所占用空间重置为可用状态，那么在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑使用新增的磁盘页面。

而VACUMMFULL不论被删除的数据是否处于数据表的末端，这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时，将分配新的磁盘页面以供使用。同时，VACUMMFULL会上排他锁。当你的表很大的时候，可能会锁上几个小时，任何基于该表的操作都会挂起。

[

二、观察表膨胀

]

为了进一步观察表膨胀现象，可以安装pageinspect插件。

postgres=# create extension pageinspect;

CREATE EXTENSION

create table test

(

id numeric,

name character varying(30)

);

postgres=# insert into test select generate_series(1,10),A||generate_series(1,10);

INSERT 0 10

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

----------+--------+---------------------------------

17292510 | 0 | {"x0b00800100","x074131"}

17292510 | 0 | {"x0b00800200","x074132"}

17292510 | 0 | {"x0b00800300","x074133"}

17292510 | 0 | {"x0b00800400","x074134"}

17292510 | 0 | {"x0b00800500","x074135"}

17292510 | 0 | {"x0b00800600","x074136"}

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

(10 rows)

这里我们创建了一个表，并插入了10行数据。这里可以看到t_xmin代表着此行版本插入的事务ID。如果我们做update或者delete，就会产生新的行版本。下面我们来删除5行记录。

postgres=# delete from test where id <=5;

DELETE 5

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

----------+----------+---------------------------------

17292510 | 17292511 | {"x0b00800100","x074131"}

17292510 | 17292511 | {"x0b00800200","x074132"}

17292510 | 17292511 | {"x0b00800300","x074133"}

17292510 | 17292511 | {"x0b00800400","x074134"}

17292510 | 17292511 | {"x0b00800500","x074135"}

17292510 | 0 | {"x0b00800600","x074136"}

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

(10 rows)

这里可以看到，当我们删除5条记录，仍然有10条记录。而被删除的数据可以看到它的t_max事务id已经变成了删除它们的事务id。这些已经删除的记录就类似于Oracle中的undo，仍然保留在同一表中，可以提供给比t_xmax较旧的事务使用。

postgres=# update test set name=st where id=6;

UPDATE 1

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

----------+----------+---------------------------------

17292510 | 17292511 | {"x0b00800100","x074131"}

17292510 | 17292511 | {"x0b00800200","x074132"}

17292510 | 17292511 | {"x0b00800300","x074133"}

17292510 | 17292511 | {"x0b00800400","x074134"}

17292510 | 17292511 | {"x0b00800500","x074135"}

17292510 | 17292517 | {"x0b00800600","x074136"}

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

如果我们做update的话，可以看到t_xmax为0的记录仍然为5条，而多出来一条t_xmax为17292517的记录。就如我们前面理论介绍的一样，update产生了新的元组，而把旧记录做为deadtuple。

接下来我们尝试使用VACUUM来清理。

postgres=# vacuum test;

VACUUM

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

----------+--------+---------------------------------

| |

17292510 | 0 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

(11 rows)

postgres=# update test set name=test where id=7;

UPDATE 1

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

----------+----------+-----------------------------------

17292571 | 0 | {"x0b00800700","x0b74657374"}

| |

17292510 | 17292571 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

(11 rows)

postgres=# vacuum full test;

VACUUM

postgres=# SELECT t_xmin, t_xmax, tuple_data_split(test::regclass, t_data, t_infomask, t_infomask2, t_bits) FROM heap_page_items(get_raw_page(test, 0));

t_xmin | t_xmax | tuple_data_split

----------+----------+-----------------------------------

17292571 | 0 | {"x0b00800700","x0b74657374"}

17292510 | 17292571 | {"x0b00800700","x074137"}

17292510 | 0 | {"x0b00800800","x074138"}

17292510 | 0 | {"x0b00800900","x074139"}

17292510 | 0 | {"x0b00800a00","x09413130"}

17292517 | 0 | {"x0b00800600","x077374"}

(6 rows)

可以看到清理过后仍然有11条记录。如果现在我们再次执行update更新，这个元组是可以在重新用到的。而使用了vacuumfull命令之后，记录才下降到6条。把之前的deadtuple全部都清理了。

[

三、表膨胀插件

]

前面说到使用vacuum和vacuumfull的功能之后。你会发现两者都有一些缺陷。前者是不能回收空间，也就是会产生类似Oracle中的高水位的概念，而后者是能回收空间，但是会锁表，当表足够大的时候，会锁上数个小时。会导致业务长时间中断。那么有什么锁表时间短而且能回收空间的方法吗？当然，使用pg_repack和pg_squeeze插件就能解决问题。两个插件都能解决这个问题，但是使用哪个更加好呢？

pg_squeeze插件是cybertec公司贡献的，而pg_repack插件是自由软件黑客DanieleVarrazzo所主导的。两者都是C语言编写。

两者之间最大的不同就是pg_repack是通过触发器功能来实现的，在重组的时候，额外使用触发器会有一定的开销，存在一定性能上的影响。而pg_squeeze，它是建立在逻辑复制基础上的。它首先创建一个新的数据文件快照，然后使用内置复制插槽以及逻辑解码从XLOG提取对表更改的记录。然后重新构建表，构建完成之后再锁表，切换FileNode。两者实现方式不同。我个人比较倾向使用pg_squeeze插件。

插件安装较为简单。下载安装包解压，切换到postgres用户，执行make和makeinstall就安装好了。

装完后需要修改数据库参数和重启，并在数据库安装插件。

wal_level = logical

max_replication_slots = 10 # minimum 1

shared_preload_libraries = pg_squeeze

create extension pg_squeeze;

接下来创建一个表来测试一下。

drop table test;

create table test

(

id numeric,

name character varying(30)

);

insert into test select generate_series(1,5000000),A||generate_series(1,5000000);

postgres=# SELECT pg_size_pretty(pg_relation_size(test));

pg_size_pretty

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

211 MB

(1 row)

当我插入500万记录的时候，表大小是211MB，现在对表做完全更新。

postgres=# update test set name=This is a test;

UPDATE 5000000

postgres=# select pg_size_pretty(pg_relation_size(test));

pg_size_pretty

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

460 MB

(1 row)

现在全部更新完成时460MB，直接执行收缩。

postgres=# SELECT squeeze.squeeze_table(public, test, null, null, null);

ERROR: Table "public"."test" has no identity index

这里报错是表上需要主键才能执行收缩。

postgres=# alter table test add primary key(id);

ALTER TABLE

postgres=# select squeeze.squeeze_table(public, test, null, null, null);

squeeze_table

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

(1 row)

postgres=# select pg_size_pretty(pg_relation_size(test));

pg_size_pretty

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

249 MB

(1 row)

重新创建主键后再次收缩，发现表大小已经从460MB下降到了249MB。回收效果还是很明显。

pg_squeeze插件还有一个比较优秀的功能就是能做成定时任务。首先我们可以把要回收的表插入到squeeze.tables表中，该表最后有一个字段叫schedule，是一种自定义的类型，通过查询squeeze.schedule的定义，可以发现和Linux中的crontab类似。

插入记录如上图所示，schedule设置为({5},{1},null,null,{6})，代表在每个周六晚上的1点05分会定时执行。

如果重组表的时候，其他用户删除表、修改表结构、或者始终无法获取短暂的排他锁、空间不足等问题都会造成重组失败。可以通过查看squeeze.errors表来定位错误。

参考文献

PG_SQUEEZE:OPTIMIZING POSTGRESQL STORAGE

https://www.cybertec-postgresql.com/en/pg_squeeze-optimizing-postgresql-storage/

Understandingof Bloat and VACUUM in PostgreSQL

https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/

GPU云服务器云服务器 webrtc 前世今生 postgresql 表空间 postgresql表空间不足 postgresql查看表空间

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130090.html

系统架构

强力推荐！那些你不能错过的 GitHub 插件和工具以代码托管平台起家的 GitHub 网站，已然成为全球程序员工作和生活中不可或缺的一份子。从优秀的企业，到优秀的程序员，都将自己最优秀的代码作品存放在这片开源净土里，供彼此学习交流。 GitHub Trending 栏目甚至已经成为 IT 从业人员的新闻日报，每日必读。在之前的一… PostgreSQL 数据库的前世今生编辑：IT大咖说阅读字...

Gilbertat 2019-06-24 17:55 评论0 收藏0
系统架构

强力推荐！那些你不能错过的 GitHub 插件和工具以代码托管平台起家的 GitHub 网站，已然成为全球程序员工作和生活中不可或缺的一份子。从优秀的企业，到优秀的程序员，都将自己最优秀的代码作品存放在这片开源净土里，供彼此学习交流。 GitHub Trending 栏目甚至已经成为 IT 从业人员的新闻日报，每日必读。在之前的一… PostgreSQL 数据库的前世今生编辑：IT大咖说阅读字...

k00baa 2019-08-22 11:21 评论0 收藏0
Web技术的前世今生（二）

摘要：前言我是，如果你还不认识我，不妨先看看技术的前世今生一平静的生活已经有一段日子了。传送门技术的前世今生一技术的前世今生三前言：我是JavaScript，如果你还不认识我，不妨先看看《Web技术的前世今生（一）》平静的生活已经有一段日子了。这一天，HTML大哥面露不悦地走过来问我： Js，你是打算和我们分家吗？大哥，您这说的哪里话，我什么地方做的不对么？我一脸茫然地回答道。哼，...

Stardustsky 2019-08-30 15:55 评论0 收藏0
Web技术的前世今生（二）

摘要：前言我是，如果你还不认识我，不妨先看看技术的前世今生一平静的生活已经有一段日子了。传送门技术的前世今生一技术的前世今生三前言：我是JavaScript，如果你还不认识我，不妨先看看《Web技术的前世今生（一）》平静的生活已经有一段日子了。这一天，HTML大哥面露不悦地走过来问我： Js，你是打算和我们分家吗？大哥，您这说的哪里话，我什么地方做的不对么？我一脸茫然地回答道。哼，...

hyuan 2019-08-02 13:59 评论0 收藏0