GaussDB for DWS表膨胀原理(下)

DWS表膨胀原理(下)

3  我们应该如何减少或避免PostgreSQL数据膨胀呢？

一定要开启autovacuum；

提高系统的IO能力，越高越好；

调整触发阈值，让触发阈值和记录数匹配；

增加autovacuum_max_workers，同时增加autovacuum_work_mem，同时增加系统内存；

对于IO没有问题的系统，关闭autovacuum_vacuum_cost_delay；

调整autovacuum_naptime参数到最低(但是也要慎重，比如有长事务导致某些垃圾无法回收时，会不断的唤醒VACUUM WORKER去扫描垃圾页，然后 发现无法回收，循环往复，浪费IO和CPU。比如本地有LONG SQL或者STANDBY开启了feedback并有LONG SQL时，都是问题；

应用程序设计时，避免使用大批量的更新，删除操作，可以切分为多个事务进行；

使用大的数据块，对于现代的硬件水平，32KB是比较好的选择，fillfactor实际上不需要太关注，100就可以了，调低它其实没有必要，因为数据库总是  有垃圾，也就是说每个块在被更新后实际上都不可能是满的；

万一真的膨胀了，可以通过table rewrite来回收(如vacuum full, cluster)，但是需要迟排他锁。建议使用pg_reorg或者pg_repack来回收，实际上用到  了交换 filenode可以缩短需要持有排他锁的时间；

应用程序设计时，尽量避免如下：

（1）LONG SQL（包括查，增，删，改，DDL所有的SQL）；

（2）或者打开游标后不关闭；

（3）或者在不必要的场景使用repeatable read或serializable事务隔离级别；

（4）或者对大的数据库执行pg_dump进行逻辑备份（隐式repeatable read隔离级别的全库备份）；

（5）或者长时间不关闭申请了事务号的事务（增，删，改，DDL的SQL）。

4  相关空间回收参数说明

开启系统自动清理进程（autovacuum）自动执行VACUUM和ANALYZE命令，回收被标识为删除状态的记录空间，并更新表的统计数据。

autovacuum = on

参数说明：控制数据库自动清理进程（autovacuum）的启动。自动清理进程运行的前提是将track_counts设置为on。

log_autovacuum_min_duration = 0

参数说明：当自动清理的执行时间大于或者等于某个特定的值时，向服务器日志中记录自动清理执行的每一步操作。设置此选项有助于追踪自动清理的行为。

autovacuum_max_workers = 10

参数说明：设置能同时运行的自动清理线程的最大数量。

autovacuum_naptime = 1

参数说明：设置两次自动清理操作的时间间隔。

autovacuum_vacuum_threshold = 5

参数说明：设置触发VACUUM的阈值。当表上被删除或更新的记录数超过设定的阈值

时才会对这个表执行VACUUM操作

autovacuum_analyze_threshold = 5

参数说明：设置触发ANALYZE操作的阈值。当表上被删除、插入或更新的记录数超过

设定的阈值时才会对这个表执行ANALYZE操作。

autovacuum_vacuum_scale_factor = 0.002

参数说明：设置触发一个VACUUM时增加到autovacuum_vacuum_threshold的表大小的

缩放系数。

autovacuum_analyze_scale_factor = 0.001

参数说明：设置触发一个ANALYZE时增加到autovacuum_analyze_threshold的表大小的

缩放系数。

autovacuum_vacuum_cost_delay = 0

参数说明：设置在自动VACUUM操作里使用的开销延迟数值。

5  测试验证表膨胀

通过计算行平均占用空间计算出live tuple占用的空间, 计算出膨胀。表以page(大小为BLCKSZ)为单位表示在磁盘上的大小，Page又由tuple组成。每个 page 头包含24字节固定长度. 每条tuple对应一条ItemIdData，4字节。

Table 58-2. Overall Page Layout

Item

Description

PageHeaderData

24 bytes long. Contains general information   about the page, including free space pointers.

ItemIdData

Array   of (offset,length) pairs pointing to the actual items. 4 bytes per item.

Free space

The unallocated space. New item pointers are   allocated from the start of this area, new items from the end.

Items

The actual items themselves.

Special space

Index access method specific data. Different   methods store different data. Empty in ordinary tables.

tuple头部则占用27字节. 如下:

Table 58-4. HeapTupleHeaderData Layout

Field

Type

Length

Description

t_xmin

TransactionId

4 bytes

insert XID stamp

t_xmax

TransactionId

4 bytes

delete XID stamp

t_cid

CommandId

4 bytes

insert and/or delete CID stamp (overlays with   t_xvac)

t_xvac

TransactionId

4 bytes

XID for VACUUM operation moving a row version

t_ctid

ItemPointerData

6 bytes

current TID of this or newer row version

t_infomask2

uint16

2 bytes

number of attributes, plus various flag bits

t_infomask

uint16

2 bytes

various flag bits

t_hoff

uint8

1 byte

offset to user data

因此，1条记录真实的占用空间应该是ItemIdData+tupleHead+tupledata=4+27+?字节。

1> 创建测试表

postgres=# create table deadtuple_teste (id int, info text);

CREATE TABLE

2> 插入测试数据

postgres=# insert into deadtuple_teste select generate_series(1,100000),md5(random()::text);

INSERT 0 100000

3> 查看当前表的空间占用情况

已占用834个page :

postgres =# select relpages from pg_class where relname='deadtuple_teste';

relpages

----------

834

(1 row)

4> 删除100000条数据

postgres=# delete from deadtuple_teste;

DELETE 100000

5> 然后再插入100000条数据

postgres=# insert into deadtuple_teste select generate_series(1,100000),md5(random()::text);

INSERT 0 100000

Time: 37422.191 ms

6> 更新统计信息

postgres=# analyze deadtuple_teste;

ANALYZE

7> 查看当前表的空间占用情况

已占用1668个page，并没有删掉原来的834个page，而是新增了834个page :

postgres=# select relpages from pg_class where relname='deadtuple_teste';

relpages

----------

1668

(1 rows)

8> 更新数据

postgres=# update deadtuple_teste set info=md5(random()::text) where id>10000;

UPDATE 90000

9> 更新统计信息

postgres=# analyze deadtuple_teste;

ANALYZE

10> 查看当前表的空间占用情况

已占用1585个page :

postgres=# select relpages from pg_class where relname='deadtuple_teste';

relpages

----------

1585

(1 rows)

postgres=# select pg_relation_size('deadtuple_teste'::regclass);

pg_relation_size

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

12984320

(1 row)

11> 回收空间

测试，所以手动回收，正常环境应该配置自动回收：

postgres=# vacuum full deadtuple_teste;

VACUUM

12> 更新统计信息

postgres=# analyze deadtuple_teste;

ANALYZE

13> 查看当前表的空间占用情况

已占用834个page，vacuum回收了增删改数据导致的无效的page：

postgres=# select relpages from pg_class where relname='deadtuple_teste';

relpages

----------

834

14> 计算一个tuple的空间占用情况

postgres=# select pg_column_size(id),pg_column_size('info') from deadtuple_teste limit 1;

pg_column_size | pg_column_size

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

4 |              5

(1 row)

因为1条记录真实的占用空间应该是ItemIdData+tupleHead+tupledata=4+27+?字节，所以该表一条tuple的空间是4+27+4+5=40字节。

15>  live tuple占用的空间如下

有834个page：

postgres=# select n_live_tup*40 from pg_stat_all_tables where relname='deadtuple_teste';

?column?

----------

4000000

(1 row)

16> page头占用的空间如下

每个page占用固定长度的24个字节，共有834个page：

postgres=# select relpages from pg_class where relname='deadtuple_teste';

relpages

----------

834

(1 rows)

postgres=# select 24*relpages from pg_class where relname='deadtuple_teste';

?column?

----------

38040

(1 row)

17> 表的真实空间占用如下

postgres=# select pg_relation_size('deadtuple_teste'::regclass);

pg_relation_size

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

6832128

(1 row)

18> 计算表数据占用的空间

pg_relation_size -(n_live_tup*(ItemIdData+tupleHead+tupledata)+ PageData*PageNum=6832128-(100000*（4+27+9）+24*834)= 6832128 -(4000000+20016)= 2812112;

即数据所占用的空间为2812112个字节。

4000000+20016=4020016，这部分数据为page页和tuple所占用的空间，记录page和tuple的信息，无法回收。

由3.11可知，update后该表所占空间为12984320个字节。

所以表膨胀所占用的空间为12984320-2812112=10172208个字节。

10172208-(4000000+20016)= 6152192,即可回收的空间为6152192个字节。

19> 膨胀比例

Vacuum后，计算膨胀比：

(6832128-4000000-38040)/ 6832128=0.408963063923861

即数据所占空间占用总空间的比例约为41%。

数据仓库服务 GaussDB(DWS) 计算

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。