postgres pg_squeeze pg_repack

postgres数据库表空间收缩之pg_squeeze,pg_repack

下半年一直忙于NP的sybase,通过大家的共同努力,NP年底比较稳定。很久没有弄过pg相关的知识了,最近经常看到有人问如何用工具自动清理pg的坏元组。

除了我们经常手动使用vacuum之外,生产环境还有两个比较常用的工具一个是pg_squeeze,另外一个是pg_repack

pg_squeeze1.2

项目地址:https://github.com/cybertec-postgresql/pg_squeeze

原理

pg_squeeze是一个扩展,它从表中删除未使用的空间,并且可以选择根据特定索引对元组进行排序,一般当一个表膨胀时一般使用vacuum full或者cluster进行表重建,在这一过程中会加排他锁,导致该表无法进行读写,只有等整个过程完成后才可以进行正常使用

优点

相比pg_repack或者pg_reorg,pg_squeeze不需要建触发器,所以在重组时对原表的DML几乎没有性能影响。pg_squeeze支持自动重组,可以设置定时清理时间以及根据空闲空间比例来进行清理表。该过程会自动启动worker进程,将数据复制到重组表,然后加锁,切换filenode。

安装

1、下载安装包后,解压后修改MakeFile,在MakeFile中加入pg_config

PG_CONFIG =/home/thunisoft5/arterybase/5.0/bin/pg_config

2、安装

make && make install

3、修改postgresql.conf配置文件

wal_level = logical

max_replication_slots = 1 # 大于等于1

shared_preload_libraries = \'pg_squeeze\'

4、重启数据库

使用

1、创建扩展

postgres=# create extension pg_squeeze;
CREATE EXTENSION

postgres=# \dx
                            List of installed extensions
    Name    | Version |   Schema   |                  Description                   
------------+---------+------------+------------------------------------------------
 pg_squeeze | 1.2     | squeeze    | A tool to remove unused space from a relation.
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

2、安装完成后会有一个对应的系统表

postgres=# \d squeeze.tables
                                            Table "squeeze.tables"
      Column      |         Type          | Collation | Nullable |                  Default                   
------------------+-----------------------+-----------+----------+--------------------------------------------
 id               | integer               |           | not null | nextval(\'squeeze.tables_id_seq\'::regclass)
 tabschema        | name                  |           | not null | 
 tabname          | name                  |           | not null | 
 clustering_index | name                  |           |          | 
 rel_tablespace   | name                  |           |          | 
 ind_tablespaces  | name[]                |           |          | 
 schedule         | time with time zone[] |           | not null | 
 free_space_extra | integer               |           | not null | 50
 min_size         | real                  |           | not null | 8
 vacuum_max_age   | interval              |           | not null | \'01:00:00\'::interval
 max_retry        | integer               |           | not null | 0
 skip_analyze     | boolean               |           | not null | false
Indexes:
    "tables_pkey" PRIMARY KEY, btree (id)
    "tables_tabschema_tabname_key" UNIQUE CONSTRAINT, btree (tabschema, tabname)
Check constraints:
    "tables_free_space_extra_check" CHECK (free_space_extra >= 0 AND free_space_extra < 100)
    "tables_min_size_check" CHECK (min_size > 0.0::double precision)
Referenced by:
    TABLE "squeeze.tables_internal" CONSTRAINT "tables_internal_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
    TABLE "squeeze.tasks" CONSTRAINT "tasks_table_id_fkey" FOREIGN KEY (table_id) REFERENCES squeeze.tables(id) ON DELETE CASCADE
Triggers:
    tables_internal_trig AFTER INSERT ON squeeze.tables FOR EACH ROW EXECUTE PROCEDURE squeeze.tables_internal_trig_func()

squeeze.tables表字段说明

  • tabschema:表的模式名。
  • tabname:表名。
  • clustering_index:表示重建表时,表数据的物理顺序按照该索引进行聚簇。
  • rel_tablespace:表示表重建时,移动到哪个表空间中。
  • ind_tablespace:这个一个二维数组,表示索引和表空间的映射关系。
  • schedule:vacuum在一天中运行的时间范围
  • free_space_extra:表示空闲空间超过多少时就会对表进行重建,默认是50。
  • min_size:表必须占用的最小磁盘空间(兆字节)才有资格进行处理,默认值为8。
  • vacuum_max_age:当进行一次vacuum后,认为fsm是有效的最大时间,默认1小时。
  • max_retry:当重建表失败时最大的重新尝试的次数,默认是0.
  • skip_analyse:跳过对表进行analyse,默认是false。

3、创建测试表

--创建表
postgres=# create table test(n_id int,c_name varchar(300),primary key(n_id));
CREATE TABLE

--初始化数据
postgres=# insert into test select generate_series(1,4000000),\'zhangsan\';
INSERT 0 4000000
--查看表大小:169MB
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description 
--------+------+-------+-------+--------+-------------
 public | test | table | sa    | 169 MB | 
(1 row)

4、给表test创建squeeze任务

--需要在表squeeze.tables插入一条记录。添加后,将定期检查表的统计信息。只要满足‘压缩’的太偶见,就会将‘任务’添加到队列中,任务按照创建爱女顺序依次处理
--schedule标识该任务在晚上八点到24点执行,并且free_space_extra表示空闲空间超过10时就会对表进行重建
postgres=# insert into squeeze.tables (tabschema, tabname, schedule, free_space_extra) values (\'public\', \'test\', \'{20:00, 24:00}\', \'10\');
INSERT 0 1

--如果需要取消注册表,只需要从‘squeeze.tables’表删除响应的行即可
--查看任务

postgres=# select * from squeeze.tables;
 id | tabschema | tabname | clustering_index | rel_tablespace | ind_tablespaces |         schedule          | free_space_extra | min_size | vacuum_max_age | max_retry | skip_analyze 
----+-----------+---------+------------------+----------------+-----------------+---------------------------+------------------+----------+----------------+-----------+--------------
  2 | public    | test    |                  |                |                 | {20:00:00+08,24:00:00+08} |               10 |        8 | 01:00:00       |         0 | f
(1 row)


5、启动和关闭pg_squeeze进程

select squeeze.start_worker();
select squeeze.stop_worker();

6、验证

--更新数据
postgres=# update test set c_name = \'张三-1\' where n_id <2000000;
UPDATE 1999999

--更新后表大小
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description 
--------+------+-------+-------+--------+-------------
 public | test | table | sa    | 253 MB | 
(1 row)

--查看空闲空间已经30
postgres=# select * from squeeze.tables_internal;
 table_id | class_id | class_id_toast |    free_space    |       last_task_created       | last_task_finished 
----------+----------+----------------+------------------+-------------------------------+--------------------
        2 |    16528 |              0 | 30.2095497833996 | 2021-01-05 20:57:10.874252+08 | 
(1 row)

--启动pg_squeeze
postgres=# select squeeze.start_worker();
 start_worker 
--------------
        53433
(1 row)

--清理完成后查看表大小:
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner |  Size  | Description 
--------+------+-------+-------+--------+-------------
 public | test | table | sa    | 169 MB | 
(1 row)
--处理的结束时间last_task_finished时间已经更新了
postgres=# select * from squeeze.tables_internal;
 table_id | class_id | class_id_toast | free_space |       last_task_created       |      last_task_finished       
----------+----------+----------------+------------+-------------------------------+-------------------------------
        2 |          |                |            | 2021-01-05 20:57:10.874252+08 | 2021-01-05 20:57:10.916349+08
(1 row)

删除200w数据

--会自动清理
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner | Size  | Description 
--------+------+-------+-------+-------+-------------
 public | test | table | sa    | 85 MB | 
(1 row)

如果执行vacuum full表还会变小吗?

postgres=# vacuum full test;
VACUUM
postgres=# \dt+ test
                  List of relations
 Schema | Name | Type  | Owner | Size  | Description 
--------+------+-------+-------+-------+-------------
 public | test | table | sa    | 84 MB | 
(1 row)

执行vacuum full后表的大小没有实质性改变,说明pg_squeeze清理比较彻底。

pgstattuple

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 空闲空间的百分比
postgres=# create extension pgstattuple;
CREATE EXTENSION

postgres=# select * from pgstattuple(\'test\');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percen
t | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+------------------
--+------------+--------------
  88563712 |     2000001 |  74000037 |         83.56 |                0 |              0 |                  
0 |     260960 |         0.29
(1 row)

临时处理

还可以手动“压缩”表,而无需注册,跳过任何时间和膨胀检查。

功能签名: squeeze.squeeze_table(tabchema name, tabname name, clustering_index name, rel_tablespace name, ind_tablespaces name[])

示例执行:

SELECT squeeze.squeeze_table(\'public\', \'test\', null, null, null);

监控方式

\'squeeze.log\'表在每个成功压缩的表中包含一个条目。
\'squeeze.errors\'包含在压缩期间发生的错误。这里报告的一个常见问题是有人更改了正在处理表的定义(例如,添加或删除的列)。

注意事项

pg_squeeze需要使用logical replication,所以需要设置足够的slots,而且必须注意可能与standby或者使用了逻辑复制功能争抢slots,要保证slots够用。

pg_squeeze可以自动收缩,对于比较繁忙的数据库,建议不要在业务高峰期启用,避免带来性能风险

首先,确保您的表具有主键或唯一约束。这是处理“ pg_squeeze”工作时其他事务可能进行的更改所必需的。

squeeze1.2和低版本的区别

新版本的squeeze有个更好的功能是:

  • squeeze.tables表可以指定schedule:也就是指定气你的时间范围。你可以放到晚上来运行。

低版本pg_squeeze支持时间间隔的

  • task_interval:表示检查表膨胀是否超过阀值的时间间隔

  • first_check:表示第一次检查时间
    相对来说直接在晚上定时执行vacuum full的方式更加简便

pg_repack

自述文件:和pg_squeeze一样pg_repack也是一个扩展,可以从表和索引中消除膨胀,并且可以选择恢复簇索引的物理顺序,与cluster和vacuum full不同,该工具可以在线工作,并且在处理过程中不需要在表上面持有排它锁(vacuum full工作需要access exclusive lock,导致任何操作都不能执行),pg_repack的启动效率很高,其性能与直接使用cluster相当

pg_repack老版本叫pg_reorg

原理

pg_repack原理和vacuum full原理类似,都是新建一个文件,然后将老文件拷贝过来,然后进行文件切换。不阻塞读写的秘诀就是新建文件和拷贝的过程是在线做的,在没有完成拷贝之前,原来的文件还是可以读写,只有在切表的一瞬间会有影响。

源库的数据文件一直在变化,pg_repack是如何拷贝的呢?表文件分为两部分,一部分是基础数据,一部分是增量数据,基础数据的拷贝是正常拷贝,增量数据是通过创建触发器来捕获该表上的读写操作来实现,基础数据拷贝完之后再将触发器捕获的增量sql进行应用,完成切换。

具体步骤:

  1. 创建一个日志表来记录对原始表所做的更改
  2. 在原始表上添加触发器,将INSERT,UPDATE和DELETE记录到我们的日志表中
  3. 创建一个新表,包含旧表中所有的行
  4. 在这个新表上建立索引
  5. 将日志表中产生的所有更改应用到新表中
  6. 使用系统目录交换表,包括索引和Toast表
  7. 删除原始表

当然我们在执行过程中从pg_stat_activity中也可以看到一些

  • 执行过程中会给对应的表加上ACCESS SHARE MODE

  • 然后执行数据拷贝的工作:INSERT INTO repack.table_16588 SELECT n_id,c_name FROM ONLY public.repack_test

  • 最后创建索引:CREATE UNIQUE INDEX index_16595 ON repack.table_16588 USING btree (n_id) TABLESPACE pg_default

安装

wget https://github.com/reorg/pg_repack/archive/ver_1.4.4.zip

[thunisoft5@localhost pg_repack-ver_1.4.4]$ make && make install

create extension pg_repack;

使用方法

选项:

参数 描述
-a, –all 重新包装所有数据库
-t, –table=TABLE 仅重新包装特定表
-I, –parent-table=TABLE 重新打包特定的父表及其继承者
-c, –schema=SCHEMA 仅在特定架构中重新打包表
-s, –tablespace=TBLSPC 将重新打包的表移动到新表空间
-S, –moveidx 将重新打包的索引也移动到TBLSPC
-o, –order-by=COLUMNS 按列而不是集群键排序
-n, –no-order 真空吸尘而不是吸尘
-N, –dry-run 打印将重新包装的内容并退出
-j, –jobs=NUM 每个表使用这么多并行作业
-i, –index=INDEX 仅移动指定的索引
-x, –only-indexes 仅移动指定表的索引
-T, –wait-timeout=SECS 超时以取消冲突中的其他后端
-D, –no-kill-backend 超时时不要杀死其他后端
-Z, –no-analyze 最后不要分析
-k, –no-superuser-check 跳过客户端中的超级用户检查
-C, –exclude-extension 不要重新打包属于特定扩展名的表

连接选项:

参数 描述
-d, –dbname=DBNAME 数据库连接
-h, –host=HOSTNAME 数据库服务器主机或套接字目录
-p, –port=PORT 数据库服务器端口
-U, –username=USERNAME 连接的用户名
-w, –no-password 从不提示输入密码
-W, –password 强制输入密码提示

通用选项:

参数 描述
-e, –echo 回显查询
-E, –elevel=LEVEL 设置输出消息级别
–help 显示此帮助,然后退出
–version 输出版本信息,然后退出

测试

postgres=# create table repack_test(n_id int,c_name varchar(3000));
CREATE TABLE
--初始化数据
postgres=#  insert into repack_test select generate_series(1,4000000),\'张三\';
INSERT 0 4000000
--使用pg_stattuple查看表情况
postgres=# select * from pgstattuple(\'repack_test\');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percen
t | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+------------------
--+------------+--------------
 177127424 |     4000000 | 140000000 |         79.04 |                0 |              0 |                  
0 |     522008 |         0.29
(1 row)
--查看表大小
postgres=# select pg_size_pretty(pg_total_relation_size(\'repack_test\') );
 pg_size_pretty 
----------------
 169 MB
(1 row)
--查看表文件路径
postgres=# select pg_relation_filepath(\'repack_test\');
 pg_relation_filepath 
----------------------
 base/13214/16588
(1 row)

--表必须有主键或者唯一约束,这快和pg_squeeze一样
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --table repack_test
WARNING: relation "public.repack_test" must have a primary key or not-null unique keys
--添加主键
postgres=# alter table repack_test add primary key(n_id);
ALTER TABLE

--更新200w数据
postgres=# update repack_test set c_name = \'张三-1\' where n_id <=2000000;
UPDATE 2000000
更新后表达小变大了
postgres=# select pg_size_pretty(pg_total_relation_size(\'repack_test\') );
 pg_size_pretty 
----------------
 425 MB
(1 row)

--再次执行pg_repack
[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --table repack_test --elevel=info
INFO: repacking table "public.repack_test"
--更新后查看表大小,表已经缩小了
postgres=#  select pg_size_pretty(pg_total_relation_size(\'repack_test\') );
 pg_size_pretty 
----------------
 255 MB
(1 row)
--并且数据文件的路径也发生了变化
postgres=# select pg_relation_filepath(\'repack_test\');
 pg_relation_filepath 
----------------------
 base/13214/16659
(1 row)

系统表

repack.primary_keys

  • indrelid代表表的oid,第二列indexrelid代表主键或者唯一索引的oid

repack.tables

  • tables表记录了创建trigger以及捕获的相关语句,语句按一条条的record进行记录
postgres=# select * from repack.primary_keys limit 10;
 indrelid | indexrelid 
----------+------------
      826 |        828
     1136 |       1137
     1213 |       2697
     1247 |       2703
     1249 |       2658
     1255 |       2690
     1259 |       2662
     1260 |       2677
     1261 |       2694
     1262 |       2672
(10 rows)

在线pg_repack

repack数据库

[thunisoft5@localhost bin]$ pg_repack -p 8543 -d postgres --no-order --jobs 8  --elevel=info

repack模式

 pg_repack -p 8543 -d postgres --schema=public --no-order --jobs 8  --elevel=info

repack表和索引

pg_repack -p 8543 -d postgres --no-order --table public.repack_test --elevel=info

repack所有索引

pg_repack -p 8543 -d postgres --no-order --only-indexes --table public.repack_test --elevel=info

repack指定索引

 pg_repack -p 8543 -d postgres  --index public.repack_test_pkey --elevel=info

pg_repack限制

1、无法重组临时表

2、不能通过gist索引集群表

3、如果使用1.1.8或者更早的版本,则在运行pg_repack时,切勿尝试在目标表上面执行任何ddl命令。许多情况下,pg_repack会失败并正确回滚,但是在早期版本中,有一些情况可能会导致数据损坏

总结

pg_squeeze和pg_repack都需要表有主键或者非空唯一约束才行

pg_repack重组时,触发器会带来一定的开销,对被重组的表,有一定的DML性能影响。

pg_squeeze不需要建触发器,所以在重组时对原表的DML几乎没有性能影响。

pg_squeeze支持自动的重组,即通过设置阈值、比较用户表与阈值,自动启动WORKER进程,将数据复制到重组表,最后加锁,切换FILENODE。

pg_squeeze需要清理的表都需要在squeeze.tables表中插入对应的记录,并且可以对不同的表设置阈值和清理时间段。pg_repack可以针对库,schema以及表和索引分别清理

两个工具都可圈可点,pg_squeeze对系统的性能影响更小一些。当然也可以在晚上系统空闲时间直接使用vacuum full的方式来清理。

版权声明:本文为zhangfx01原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/zhangfx01/p/14267667.html