12

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

 3 years ago
source link: https://www.daqianduan.com/17339.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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的方式来清理。

#感谢您访问本站#
#本文转载自互联网，若侵权，请联系删除，谢谢！657271#qq.com#

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK