DWS表膨胀原理(下)

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

1. 一定要开启autovacuum；

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

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

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

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

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

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

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

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

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

      （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

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

     Table 58-4. HeapTupleHeaderData Layout

因此，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%。