Postgres空间回收遇到死数据怎么办?

最近遇到一个问题:postgres在每周六凌晨进行的数据库数据清除操作中,利用vacuum回收空间失效,本来应该回收掉的空间,却还是占有很多的磁盘空间。本文带你解读问题分析并且了解其解决过程~

最近在反馈中,遇到一个问题:
postgres在每周六凌晨进行的数据库数据清除操作中,利用vacuum回收空间失效,本来应该回收掉的空间,却还是占有很多的磁盘空间。

在测试环境中:
在数据库中查看ipslog的占用空间如下:
nsc=> select schemaname,tablename,pg_relation_size(schemaname||’.’||tablename) as tabsize from pg_tables order by 3 desc;

schemaname tablename tabsize
public ipslog 74653696
public plugin 16941056
public geo_location 9691136
public eps_oidinfo 5734400

表ipslog占用了74653696的空间。
在执行完delete * from ipslog后,再次运行,结果仍为:tablesize:74653696。
于是手动执行vacuum ipslog,结果仍为:tablesize:74653696。

结果是vacuum操作此时没有回收掉日志。
对此抱有疑问的情况下,进行了对vacuum打印日志的操作,出现如下日志:

这里可以发现,vacuum对于表IPSLOG的回收工作没有起任何作用,回收了0行数据,还有19万的数据在占用着磁盘空间没有清理掉。而对于日志描述信息,我们可以发现这没有删除掉的数据被被称之为”dead row”, 也就是死数据。

何为“死数据”?

我们在对数据表进行删除操作的时候,一般来说会用到delete from,而delete from实际上对于数据库来说是一个“假删除”状 态,数据库本身在执行这个statement的时候,是将数据库内的数据进行了一个删除标记。一旦数据被打上了这些标记,则不会再在页面上显示,但是他们 仍旧占用着空间。所以删除之后如果要清除空间,则需要使用vacuum操作。

一般来说,postgres有自身的autovacuum的进程会来处理这些数据。但是我们会拥有业务的需求人工去执行这些操作。当 vacuum命令被执行的时候,数据库会检测当前需要回收的表的数据是否真的没有用,可以被删除。vacuum会去postgres 的事务 (transaction)里查看需要被回收的数据是否有其他事务还对其有操作,如果没有,则回收掉这些数据,如果有,则略过该数据。

由于数据库的频繁大量的操作,有一些事务在长久不执行后,会形成一种空闲状态(idle),这些事务由于日积月累会造成对某些数据有关联但是再几 乎再也不会被执行。而处于该状态的事务如果一旦存在,并且和需要回收的表有关联,则vacuum会检测到这些事务,然后跳过处理这些数据,继而造成死数据 (dead row or dead tuple)。

死数据的影响

死数据看起来只是几个表的影响,但是在大量数据和日积月累面前就变成数据库的牛皮癣,甚至会影响数据库性能,严重的还会将磁盘空间占满。

规避方式

根据死数据产生的原理,建议频繁的执行vacuum操作,最少也要一天执行一次;一般在大量的删除操作后如果确保数据真的不要了,可以立即执行vacuum来回收空间。

另外一种方式是,如果删除的是整表,使用truncate table来删除,不要使用delete * from table。truncate会将整体数据全部清除,可以避免死数据的产生。

附上使用truncate之后的结果:

schemaname tablename tabsize
public plugin 16941056
public geo_location 9691136
public eps_oidinfo 5734400
public rcm_t_item 5455872

truncate table 之后,ipslog 的空间占用为0了。

最后普及一下PostgreSQL的概念:

PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL,版本4.2为基础的对象关系型数据库管理系统(ORDBMS),经常被简略念为 “postgres”。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新的数据类型、函数、操作符、聚集函数、索引。免费使用、修改、和分发 PostgreSQL,不管是私用、商用、还是学术研究使用。PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如:MySQL 和 Firebird),和专有系统(比如:Oracle、Sybase、IBM的 DB2和Microsoft SQL Server)之外的另一种选择。

如果您需要了解更多内容,可以
加入QQ群:486207500
直接询问:010-68438880-8669

发表评论