PostgreSQL "表膨胀" 的救世主

ClickHouse周边 2021-04-23

1901

近期，生产环境出现不同库下的同一表名（小表-几十条数据）的占用size已达近G,频繁修改，删除记录，但是空间一直未释放，是何原因？

原因就在于vacuum，而vacuum怎么存储，清理数据的可参考官方文档进行查看。

PG vacuum官方文档
https://www.postgresql.org/docs/current/routine-vacuuming.html

操作数据时，PostgreSQL会为每一个客户端提供单独的快照。客户端对快照中的数据进行更新和删除，一些数据最终变成过期数据，但它们仍存在于数据库中，还占用表数据文件和索引数据文件的空间。因此数据文件中会存在碎片，引起整体数据库性能下降。这时候vacuum出来干活了，vacuum的主要任务就是清理表和索引中不需要的数据（死数据），为新加入的数据清理出来空间。

vacuum也会存在缺陷。比如1，vacuum完成清理工作后，那些空间并没有真正被释放掉，只能被vacuum清理过的表和索引所利用。虽然看上去表和索引大小都已经减少了，但是实际上和vacuum清理前的大小是一样。这让很多刚开始使用Postgres的用户感到困惑。比如2，假设有一张表上有大量索引，表上还有密集的更新操作。遇到这样的情况，大量更新会触发一次新的vacuum，有可能会发现这张表上有无穷无尽的vacuum在执行。无论怎么修改vacuum的配置都没有用，结果导致——表膨胀了。比如3，执行空事务，vacuum会推迟清理死数据导致表和索引膨胀。空事务，甚至经常执行长事务都会对数据库有影响，应避免这些操作。

出现表一直膨胀，该如何处理？开源社区的魅力就在于很多大神会提供很多工具来解决对应的问题，而本问题则有2种主要的工具：pg_repack和pgcompacttable。

1 工具对比

1.1 pg_repack

pg_repack的处理方式是创建一张新表，再将历史数据从原表中拷贝一份到新表。在拷贝过程中为了避免表被锁定，会创建了一个额外的日志表来记录原表的改动，并添加了一个涉及INSERT、UPDATE、DELETE操作的触发器将变更记录同步到日志表。当原始表中的数据全部导入到新表中，索引重建完毕以及日志表的改动全部完成后，pg_repack会用新表替换旧表，并将原旧表Drop掉。此工具过程简单且靠谱，单需要额外的磁盘空间来报错临时创建的中间表。

1.2 pgcompacttable

pgcompacttable利用了PostgreSQL的一个有趣特性：在执行INSERT和UPDATE操作时，会将所有新版本的行移到表最开始的可用空间。此为pgcompacttable工具的关键，因为如果从末端反向开始更新所有行，最终所有可用空间被这些行填充，并将表尾部的空间全部释放以便让定期vacuum进行truncate。这样一来，pgcompacttable通过批量更新和vacuum强制移动，最终整个表被重新整理，达到压缩的效果。此工具对磁盘空间要求低，且性能影响可控。

1.3 对比

为了便于大家选择工具，简单做了一个对比说明供参考。

	pg_repack	pgcompacttable
是否需要保证性能	否	是
是否移动表/索引	是	否
是否有足够空间	是	否
压缩速率是否高	是	否

小结：磁盘空间有限，因而经常选择使用pgcompacttable较多，那演示一下pgcompacttable吧。

2. pgcompacttable部署及使用实例

2.1 添加pgstattuple

pgcompacttable工具使用过程中需要依赖pgstattuple，因此需先添加pgstattuple。如果是源码安装的postgresql,则源码里包含了postgresql-contrib。

cat etc/redhat-release 
Red Hat Enterprise Linux Server release 7.4 (Maipo)

su - postgres
psql -c "select version();"
                                            version                                             
------------------------------------------------------------------------------------------------
 PostgreSQL 12.4,FlyingDB on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1 20210220, 64-bit
(1 row)


# FlyingDB 飞象数据

find home/postgres/ -name *stattuple* 

/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple.so
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple.control
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--1.4.sql
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--1.4--1.5.sql
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--1.3--1.4.sql
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--1.2--1.3.sql
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--1.1--1.2.sql
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--1.0--1.1.sql
/home/postgres/FlyingDB12.4/xxxxxx/pgstattuple--unpackaged--1.0.sql
/home/postgres/xxxxxxxxxxxxxxxxxxx/pgstattuple.so

psql 
select * from pg_available_extensions where name like 'pgstat%';
    name     | default_version | installed_version |           comment           
-------------+-----------------+-------------------+-----------------------------
 pgstattuple | 1.5             |                   | show tuple-level statistics
(1 row)

postgres# \c old                     
old=# create extension pgstattuple;
old=# \dx
                   List of installed extensions
    Name     | Version |   Schema   |         Description          
-------------+---------+------------+------------------------------
 pgstattuple | 1.5     | public     | show tuple-level statistics
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

old=#  \dxS+ pgstattuple
  Objects in extension "pgstattuple"
          Object description           
---------------------------------------
 function pg_relpages(regclass)
 function pg_relpages(text)
 function pgstatginindex(regclass)
 function pgstathashindex(regclass)
 function pgstatindex(regclass)
 function pgstatindex(text)
 function pgstattuple_approx(regclass)
 function pgstattuple(regclass)
 function pgstattuple(text)
(9 rows)


yum install perl-Time-HiRes perl-DBI perl-DBD-Pg -y
su - postgres
git clone https://github.com/dataegret/pgcompacttable.git
unzip 解压

2.2 准备环境

create database old;
use old;

begin;
insert into test values(generate_series(1,10000),repeat( chr(int4(random()*26)+65),1),repeat( chr(int4(random()*26)+65),6),repeat( chr(int4(random()*26)+65),30),repeat( chr(int4(random()*26)+65),30));
commit; 

create index on test(id,sex);
create index on test(name,now_address,address);

old=# select count(*) from test ;
 count 
-------
 10000
(1 row)


old=# \d+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description 
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 1096 kB | 
(1 row)

old=# select * from test limit 10 ;
 id | sex |  name  |          now_address           |            address             
----+-----+--------+--------------------------------+--------------------------------
  1 | P   | WWWWWW | [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ | YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
  2 | X   | ZZZZZZ | KKKKKKKKKKKKKKKKKKKKKKKKKKKKKK | QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ
  3 | U   | SSSSSS | QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ | KKKKKKKKKKKKKKKKKKKKKKKKKKKKKK
  4 | R   | ZZZZZZ | MMMMMMMMMMMMMMMMMMMMMMMMMMMMMM | BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
  5 | B   | [[[[[[ | LLLLLLLLLLLLLLLLLLLLLLLLLLLLLL | ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ
  6 | I   | XXXXXX | UUUUUUUUUUUUUUUUUUUUUUUUUUUUUU | PPPPPPPPPPPPPPPPPPPPPPPPPPPPPP
  7 | W   | EEEEEE | TTTTTTTTTTTTTTTTTTTTTTTTTTTTTT | UUUUUUUUUUUUUUUUUUUUUUUUUUUUUU
  8 | F   | JJJJJJ | MMMMMMMMMMMMMMMMMMMMMMMMMMMMMM | BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
  9 | X   | EEEEEE | ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ | HHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
 10 | T   | ZZZZZZ | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | PPPPPPPPPPPPPPPPPPPPPPPPPPPPPP

2.3 模拟修改数据

#!/bin/bash
#version:  1.0
#function: bulk update data
#author:   ansel.zhang

for((i=1;i<=10000;i++));  
do   
    a=`tr -dc A-Z[ < dev/urandom | head -c1`
    psql -Upostgres -h 127.0.0.1 -d old -c "update  test set name=repeat( chr(int4(random()*26)+65),6),now_address=repeat( chr(int4(random()*26)+65),30),address=repeat( chr(int4(random()*26)+65),30)  where sex='$a';" >>/dev/null
    if [ $? == 0 ]
       then
           echo "$a is ok" >>update.log
       else
           echo "$a is close" >>update.err.log
       exit 1;
    fi
done

2.4 膨胀再现

old=# \x 
Expanded display is on.
old=# SELECT * FROM pgstattuple('public.test');
-[ RECORD 1 ]------+---------
table_len          | 93306880
tuple_count        | 10396
tuple_len          | 1029204
tuple_percent      | 1.1            tuple_len/table_len*100%
dead_tuple_count   | 16735
dead_tuple_len     | 1656765          
dead_tuple_percent | 1.78           dead_tuple_len/table_len*100%
free_space         | 73878732          
free_percent       | 79.18          free_space/table_len*100%

pgstattuple 输出列如下：
    字段              类型            描述
table_len           bigint       物理关系长度，以字节计
tuple_count         bigint       活的元组的数量
tuple_len           bigint       活的元组的总长度，以字节计
tuple_percent       float8       活的元组的百分比
dead_tuple_count    bigint       死的元组的数量
dead_tuple_len      bigint       死的元组的总长度，以字节计
dead_tuple_percent  float8       死的元组的百分比
free_space          bigint       空闲空间总量，以字节计
free_percent        float8       空闲空间的百分比

old=# \d+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | test | table | postgres | 89 MB | 
(1 row)

存1w条数据原表大小1096 KB，目前89MB

2.5 pgcompacttable使用

pgcompacttable可以对database级别、schema级别、table级别进行压缩

./pgcompacttable -h localhost -U postgres -d testdb
./pgcompacttable -h localhost -U postgres -d testdb  -n public
./pgcompacttable -h localhost -U postgres -d old -n public -t test

[Thu Apr 22 11:29:16 2021] (old) Connecting to database
[Thu Apr 22 11:29:16 2021] (old) Postgres backend pid: 9022
[Thu Apr 22 11:29:16 2021] (old) Handling tables. Attempt 1
[Thu Apr 22 11:30:50 2021] (old:public.test) Statistics: 11390 pages (52732 pages including toasts and indexes), it is expected that ~86.490% (9850 pages) can be compacted with the estimated space saving being 76.959MB.
[Thu Apr 22 11:31:50 2021] (old:public.test) Progress: 67%,  6690 pages completed.
[Thu Apr 22 11:32:50 2021] (old:public.test) Progress: 107%,  10570 pages completed.
[Thu Apr 22 11:33:50 2021] (old:public.test) Reindex: public.test_id_sex_idx, initial size 6411 pages(50.086MB), has been reduced by 99% (49.852MB), duration 0 seconds, attempts 0.
[Thu Apr 22 11:33:50 2021] (old:public.test) Reindex: public.test_name_now_address_address_idx, initial size 34925 pages(272.852MB), has been reduced by 99% (271.922MB), duration 0 seconds, attempts 0.
[Thu Apr 22 11:33:50 2021] (old:public.test) Processing results: 150 pages left (303 pages including toasts and indexes), size reduced by 87.812MB (409.602MB including toasts and indexes) in total.
[Thu Apr 22 11:33:50 2021] (old) Processing complete.
[Thu Apr 22 11:33:50 2021] (old) Processing results: size reduced by 87.812MB (409.602MB including toasts and indexes) in total.
[Thu Apr 22 11:33:50 2021] (old) Disconnecting from database
[Thu Apr 22 11:33:50 2021] Processing complete: 1 retries to process has been done
[Thu Apr 22 11:33:50 2021] Processing results: size reduced by 87.812MB (409.602MB including toasts and indexes) in total, 87.812MB (409.602MB) old.


old=# \d+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description 
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 1356 kB | 
(1 row)

上一篇： CllickHouse 部署架构和国内大厂应用实践

近期文章推荐：

MergeTree的Merge和Mutation机制

MergeTree的存储结构和查询加速

ClickHouse (MATERIALIZED) VIEW

更多精彩内容欢迎关注微信公众号

postgresql

文章转载自 ClickHouse周边，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL "表膨胀" 的救世主

评论