Greenplum数据库的碎片空间监测与回收

程序员文章站 2022-06-24 16:48:54

一、数据碎片一般来讲，PG/GP表中的数据在删除后会被标记为dead，除非进行自动的autovacuum(据数Greenplum禁用了autovacuum)或者是手动的vacuum，否则数据块不会被回收，直观的看来就是表的体积大，操作系统里表的文件臃肿不减。问题描述可参考：https://my.oschina.net/courtzjl/blog/1602713二、碎片查询通过查询pg_stat_user_tables，可以基本清除哪些表的全表扫描次数较多，表中DML哪种操作多，也可以了解垃圾数据的数...

一、数据碎片

一般来讲，PG/GP表中的数据在删除后会被标记为dead，除非进行自动的autovacuum(据数Greenplum禁用了autovacuum)或者是手动的vacuum，否则数据块不会被回收，直观的看来就是表的体积大，操作系统里表的文件臃肿不减。

问题描述可参考：https://my.oschina.net/courtzjl/blog/1602713

二、碎片查询

通过查询pg_stat_user_tables，可以基本清除哪些表的全表扫描次数较多，表中DML哪种操作多，也可以了解垃圾数据的数量。该表的各个字段描述如下：

--------------------+------------------------------
relid               | 16440        #表oid
schemaname          | public       #模式名
relname             | t1           #表名
seq_scan            | 50           #这个表进行全表扫描的次数
seq_tup_read        | 1867763      #全表扫描的数据行数，如果这个值很大说明操作这个表的SQL语句很可能是全表扫描，需要结合执行计划分析
idx_scan            |              #索引扫描的次数
idx_tup_fetch       |              #通过索引扫描返回的行数
n_tup_ins           | 1130502      #插入的数据行数
n_tup_upd           | 0            #更新的数据行数
n_tup_del           | 81920        #删除的数据行数
n_tup_hot_upd       | 0            #hot update的数据行数，这个值与n_tup_upd接近说明更新性能较好，不需要更新索引
n_live_tup          | 655366       #活的行数量
n_dead_tup          | 0            #死记录个数
n_mod_since_analyze | 6            #上次analyze的实际
last_vacuum         | 2019-04-07 00:22:00.955542-07 #上次手动vacuum的实际
last_autovacuum     |              #上次autovacuum的实际
last_analyze        |              #上次analyze时间
last_autoanalyze    | 2019-04-07 00:26:07.668391-07 #上次自动analyze时间
vacuum_count        | 2            #vacuum次数
autovacuum_count    | 0            #自动vacuum次数
analyze_count       | 0            #analyze次数
autoanalyze_count   | 10           #自动analyze次数
--------------------+------------------------------

注：参考文章：http://blog.itpub.net/31493717/viewspace-2643152/

其中字段n_dead_tup记录了垃圾数据的数量，可通过n_dead_tup>0筛选出还有垃圾数据的表，即SQL如下：

select relname,n_dead_tup from pg_stat_user_tables where n_dead_tup>0;

当查到表后，可使用：

select pg_size_pretty(pg_relation_size('t_test_table'));

查看到表所占用的物理空间的大小。

三、垃圾回收

当找到存在碎片垃圾数据的表后，可使用vacuum进行垃圾回收，它的作用是删除那些已经标示为删除的数据并释放空间。

1、碎片垃圾产生原因

（1）Greenplum底层的PostgreSQL中已经 DELETE 的元组或者被 UPDATE 过后过时的元组是没有从它们所属的表中物理删除的；

（2）数据库的每个事务对应着一个ID，当ID个数超过数据库限定的阈值时，就会重复用前面的ID，造成混乱。每个数据库每2百万个事务的时候，对每张表执行VACUUM是很有必要的。

（3）大量的CREATE和DROP命令会导致系统表的迅速膨胀，以至于影响系统性能。

（4）过期的记录会被存放在叫做*空间映射的地方；超出*空间映射空间的过期记录所占用的空间无法回收；VACUUM FULL命令将回收所有过期记录，但是耗时长；使用CREATE TABLE AS来处理*空间溢出的情况，例如t_table01*空间溢出：

create table "t_table02" as select from "t_table01"，drop table "t_table01"；

2、使用vacuum

VACUUM 回收已删除元组占据的存储空间。在完成VACUUM 之前它们仍然存在。因此我们有必须周期地运行 VACUUM，特别是在常更新的表上。

重要：PostgreSQL有一个单独的可选服务器进程，称为autovacuum daemon，其目的是自动执行VACUUM和ANALYZE命令。 Greenplum数据库开启autovacuum守护程序仅在Greenplum数据库模板数据库template0上执行VACUUM操作。为template0启用了autovacuum，因为不允许连接到template0。 autovacuum守护程序在template0上执行VACUUM操作以管理事务ID（XID），并帮助避免template0中的事务ID环绕问题。

文档地址：http://docs-cn.greenplum.org/v6/ref_guide/sql_commands/VACUUM.html

语法格式：

VACUUM [({ FULL | FREEZE | VERBOSE | ANALYZE } [, ...])] [table [(column [, ...] )]]
        
VACUUM [FULL] [FREEZE] [VERBOSE] [table]

VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE
              [table [(column [, ...] )]]

参数

FULL
选择full vacuum，这可以回收更多空间，但是需要更长的时间排他锁定表。此方法还需要额外的磁盘空间，因为它会写入表的新副本，并且在操作完成之前不会释放旧副本。通常，仅在需要从表中回收大量空间时才应使用此选项。
FREEZE
指定FREEZE等效于将vacuum_freeze_min_age服务器配置参数设置为零来执行VACUUM。请参阅服务器配置参数以获取有关vacuum_freeze_min_age的信息。
VERBOSE
为每个表打印详细的vacuum活动报告。
ANALYZE
更新优化器使用的统计信息，以确定执行查询的最快方法。
table
要vacuum的表的名称（可以用schema修饰）。默认为当前数据库中的所有表。
column
要分析的特定列的名称。默认为所有列。如果指定了列列表，则意味着ANALYZE。

四、参考与推荐文章：

https://blog.csdn.net/xfg0218/article/details/83031550
https://www.cnblogs.com/orangeform/archive/2012/05/23/2304155.html
https://github.com/digoal/blog/blob/master/201708/20170817_01.md
http://blog.chinaunix.net/uid-20726500-id-4864672.html
https://www.cnblogs.com/orangeform/archive/2012/05/23/2304155.html

本文地址：https://blog.csdn.net/inrgihc/article/details/110958571