编程器P800-ISP在线编程器接口介绍

完整文件：n459.com/file/25127180-478863159

以下内容无关：

-------------------------------------------分割线---------------------------------------------

原理
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进行应用，完成切换。

具体步骤：

创建一个日志表来记录对原始表所做的更改
在原始表上添加触发器，将INSERT，UPDATE和DELETE记录到我们的日志表中
创建一个新表，包含旧表中所有的行
在这个新表上建立索引
将日志表中产生的所有更改应用到新表中
使用系统目录交换表，包括索引和Toast表
删除原始表
当然我们在执行过程中从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的方式来清理。