Greenplum表膨胀问题 – 记忆角落

Greenplum表膨胀问题

/ 0评 / 0

最近发现一个奇怪的问题,因为需要备份公司省份数据,使用pg_dump备份表,发现备份后的大小只有几个G大小,但是统计了表,发现居然是备份出来的几倍以上。刚开始还以为备份过程中中断出现问题了,重新备份一次大小还是一样。后来才发现是出现了膨胀非常厉害的表导致的。

如果不及时处理可能会出现 transaction wraparound的错误!详情请看:GreenPlum维护年龄的处理

膨胀表的产生

首先Greenplum的核心其实就是pg,而说到pg需要从pg的MVCC机制说起,MVCC即多版本并发控制,也就是说,在PostgreSQL中,一次行的update和delete不会立即删除旧的版本,而是被标记为删除,一旦被标记删除的行数非常多后,统计信息发现表中预期的行数和实际的行数相差很大,表的膨胀程度就很大。

膨胀表的维护

Greenplum是基于MVCC版本控制的,所有的delete并没有删除数据,而是将这一行数据标记为删除,

而且update其实就是delete加insert。所以,随着操作越来越多,表的大小也会越来越大。对于OLAP

Greenplum的VACUUM工具的介绍及使用

介绍

Greenplum的VACUUM工具,可以回收已经删除行占据的存储空间。

语法:

vacuum table;

简单的vacuum table只是简单的回收空间且令其可以再次使用。可以缓解表的增长。

这个命令执行的时候,其他操作仍可以对标的读写并发操作,没有请求排他锁。

vacuum full table;

vacuum full执行更广泛的处理,包括跨块移动行,把表压缩到最少的磁盘块数目存储。

这个命令执行的时候,需要加排他锁。

执行vacuum后,最好对表上的索引进行重建

使用

数据库层的使用:

在适当的数据库上执行建议的VACUUM; 这里我们使用默认数据库lte_mr,因此用适当的数据库名称替换该名称。

psql -d lte_mr -c "vacuum freeze"

数据表层-批量操作:

select 'psql -d lte_mr -c' || ' ' || '"VACUUM FREEZE -c'|| schemaname || '.' ||tablename || '";' 
from pg_tables;

--或者精确到schemaname

select 'psql -d lte_mr -c' || ' ' || '"VACUUM FREEZE '|| schemaname || '.' ||tablename || '";' 
from pg_tables where schemaname = 'smartinsight'  order by tablename

先执行这个sql 把这个sql 的执行结果重定向到一个sql文件中,然后执行这个文件即可。

消除数据膨胀建议:

大型update和delete操作之后务必运行vacuum ,vacuum full 不建议使用

如果一个表膨胀严重,对于小表可以通过vacuum full table_name 回收页空间

对于重度膨胀的大表有以下方法处理:

创建大表拷贝,删掉原表,然后重命名拷贝

BEGIN;
LOCK TABLE tablename;
CREATE TABLE tablename_tmp  SELECT * FROM tablename;
DROP TABLE tablename;
ALTER TABLE tablename_tmp RENAME TO tablename;
COMMIT;

可在每日空闲时定时执行:

#!/bin/bash
source /home/gpadmin/.bash_profile
psql -d postgres -c "vacuum freeze"
psql -d lte_mr -c "vacuum freeze"
psql -d pe2e_lte -c "vacuum freeze"

在直接执行nohup sh vacuum.sh > vacuum.log 2>&1 &命令

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注