当你的数据库快速增长的时候,一定需要注意一件事,那就是“表膨胀”。内置的方法是使用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/
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/130090.html
摘要:前言我是,如果你还不认识我,不妨先看看技术的前世今生一平静的生活已经有一段日子了。传送门技术的前世今生一技术的前世今生三 前言:我是JavaScript,如果你还不认识我,不妨先看看《Web技术的前世今生(一)》 平静的生活已经有一段日子了。 这一天,HTML大哥面露不悦地走过来问我: Js,你是打算和我们分家吗? 大哥,您这说的哪里话,我什么地方做的不对么?我一脸茫然地回答道。 哼,...
摘要:前言我是,如果你还不认识我,不妨先看看技术的前世今生一平静的生活已经有一段日子了。传送门技术的前世今生一技术的前世今生三 前言:我是JavaScript,如果你还不认识我,不妨先看看《Web技术的前世今生(一)》 平静的生活已经有一段日子了。 这一天,HTML大哥面露不悦地走过来问我: Js,你是打算和我们分家吗? 大哥,您这说的哪里话,我什么地方做的不对么?我一脸茫然地回答道。 哼,...
阅读 1346·2023-01-11 13:20
阅读 1684·2023-01-11 13:20
阅读 1132·2023-01-11 13:20
阅读 1858·2023-01-11 13:20
阅读 4099·2023-01-11 13:20
阅读 2704·2023-01-11 13:20
阅读 1385·2023-01-11 13:20
阅读 3594·2023-01-11 13:20