PostgreSQL 16逻辑复制改进 （译）

原文地址：https://www.pgedge.com/blog/postgresql-16-logical-replication-improvements-in-action

 

我之前简要介绍了PostgreSQL中的复制方法，并概述了PostgreSQL 16中逻辑复制的一些关键特性。在这篇博客中，我将深入探讨逻辑复制的几个性能特性，展示这些特性在实际操作中的步骤，并分享性能测试的结果。

博客将重点关注PostgreSQL 16中的并行应用和二进制复制特性。并行应用特性允许订阅节点使用并行后台工作进程来处理大型进行中的事务的变更。用于从发布者应用变更的并行工作进程的数量由max_parallel_apply_workers_per_subscription参数控制。第二个性能特性是二进制复制。此特性允许逻辑复制以二进制格式进行初始数据复制，这在复制包含二进制列的表时提供了良好的性能提升。

 

Parallel Apply

并行apply是一种性能特性，它为复制大型、正在进行的事务提供了性能上的优势。为了实现这一点，我们首先开始将更改流式传输到订阅者节点，然后在订阅者节点上利用并行后台工作程序来应用这些更改，而与此同时，这些更改还在从发布者节点流式传输过来。您可以使用max_parallel_apply_workers_per_subscription配置参数来设置订阅者节点上用于应用更改的并行工作程序的数量。

 

下面的示例展示了如何使用这一令人兴奋的逻辑复制功能。我们还提供了在不同区域的几个AWS实例上运行测试时所得到的示例性能数据。

在本示例中，发布者运行在AWS的us-east-1区域，而订阅者节点则运行在AWS的us-west-2区域。

Publisher

要配置发布者节点，请连接到该节点并执行以下操作：

1. 使用initdb创建一个新的PostgreSQL集群（实例），并设置以下配置参数。请根据您的服务器规格指定合适的值：

shared_buffers=2GB
wal_level = logical
client_min_messages = warning
checkpoint_timeout = 30min
max_wal_size = 20GB
min_wal_size = 10GB
autovacuum = off
logical_decoding_work_mem = 64MB

2. 创建一个用于发布的表；我们使用了以下命令：

CREATE TABLE large_test (id int primary key, num1 bigint, num2 double precision, num3 double precision );

3. 以FOR ALL TABLES方式创建发布 ; 你可以选择只为上一步中创建的large_test表创建一个发布：

CREATE PUBLICATION pub FOR ALL TABLES 

Subscriber

要配置订阅者节点，请连接到该节点并执行以下操作：

1. 使用initdb创建一个新的集群（实例），并设置以下配置参数。这些参数需要根据您的服务器规格进行设置：

shared_buffers=2GB
wal_level = logical
client_min_messages = warning
checkpoint_timeout = 30min
max_wal_size = 20GB
min_wal_size = 10GB
autovacuum = off
logical_decoding_work_mem = 64MB
max_parallel_apply_workers_per_subscription = 4

对于我们的测试服务器，我将max_parallel_apply_workers_per_subscription设置为4，以生成4个并行worker，用于将更改应用到订阅者节点。

2. 创建用于发布的表，以接收来自发布者的复制流：

CREATE TABLE large_test (id int primary key, num1 bigint, num2 double precision, num3 double precision );

3. 创建具有到发布者的连接属性的订阅：

CREATE SUBSCRIPTION sub CONNECTION 'host=ec2-44-209-69-249.compute-1.amazonaws.com user=admin password=password dbname=demo' PUBLICATION pub WITH (copy_data = off, streaming=parallel, synchronous_commit=remote_apply);

请注意，为了这个测试的目的，我们将copy_data参数设置为off，这样我们就可以流式传输表更改，而不是进行初始数据复制。我们还将流类型设置为并行；这将启用并行应用特性，并将更改应用到具有指定数量的工作者的订阅者节点。

译者注：copy_data为off时，订阅端不会初始化原始数据。

 

 

Publisher

为了设置我们的测试场景，我们连接到发布者节点并执行以下操作：

1. 将synchronous_standby_names设置为订阅者的名称；要使用并行应用功能，你其实无需执行此操作；这一步仅出于本次测试的目的。设置此参数可以确保后端等待订阅者节点上的应用，这样我们就可以测量时间：

cat << EOF >> /opt/pgedge/data/pg16/postgresql.conf
synchronous_standby_names = 'sub'
EOF

2、重启PostgreSQL服务。

3，psql运行以下命令。该命令在发布者节点上启动并计时一个大事务：

\timing
EXPLAIN (ANALYZE, BUFFERS) INSERT INTO large_test (id, num1, num2, num3)
        SELECT i, round(random()*10), random(), random()*142
        FROM generate_series(1, 5000000) s(i);

Results

如果将流设置为并行，则需要58887.540 ms（00:58.888）来完成事务并在订阅者节点上应用更改。

将流设置为关闭时，   则需要106909.268 ms（01:46.909）来完成事务并在订阅者节点上应用更改。

这为使用并行应用的大型正在进行的事务提供了高达50-60%的性能增益。

 

 

Binary Copy

二进制复制是PostgreSQL 16中新增的逻辑复制的性能特性之一。二进制复制功能使得能够以二进制格式进行表数据的初始复制。
虽然之前的版本已经添加了二进制格式的流式数据功能，但在PostgreSQL 16之前并不支持以二进制模式进行表的初始复制。
我使用了两台AWS实例进行了测试，以展示这一特性所带来的性能提升。以下示例展示了如何启用此功能，并提供了使用二进制格式与非二进制格式进行初始数据加载测试的性能数据。

 

为了设置我们的二进制复制测试场景，请连接到发布者节点并执行以下操作：

1，设置以下配置参数，以最大化您的系统性能：

shared_buffers=2GB
wal_level = logical
client_min_messages = warning
checkpoint_timeout = 30min
max_wal_size = 20GB
min_wal_size = 10GB
autovacuum = off
logical_decoding_work_mem = 64MB

2. 创建一张包含 bytea 字段的表:

CREATE TABLE large_test (id int primary key, num1 bytea, num2 bytea, num3 double precision );

3. 创建一个发布，并指定FOR ALL TABLES 选项:

CREATE PUBLICATION pub FOR ALL TABLES

4. 往表中写入数据:

\timing
EXPLAIN (ANALYZE, BUFFERS) INSERT INTO large_test (id, num1, num2, num3)
        SELECT i, md5(round(random()*10)::text)::bytea, md5(random()::text)::bytea, random()*142
        FROM generate_series(1, 50000000) s(i);

5. 初始化数据之后检查表的大小

SELECT pg_size_pretty (pg_relation_size('large_test'));
 pg_size_pretty 
----------------
 5208 MB
(1 row)
 

Subscriber

连接到订阅服务器执行以下操作

1. 请根据您的系统设置以下配置参数：

shared_buffers=2GB
wal_level = logical
client_min_messages = warning
checkpoint_timeout = 30min
max_wal_size = 20GB
min_wal_size = 10GB
autovacuum = off
logical_decoding_work_mem = 64MB

2. 创建与发布节点一样结构的表:

CREATE TABLE large_test (id int primary key, num1 bytea, num2 bytea, num3 double precision );

3.创建订阅;设置binary 参数为true 并且 copy_data 参数为 on 用于初始数据传输。

CREATE SUBSCRIPTION sub CONNECTION 'host=ec2-44-209-69-249.compute-1.amazonaws.com user=admin password=password dbname=demo' PUBLICATION pub WITH (copy_data=on, binary=true);

4. 创建以下函数，对从发布者到订阅者的初始数据拷贝进行计时：

CREATE OR REPLACE PROCEDURE wait_for_rep() LANGUAGE plpgsql AS $$ BEGIN WHILE (SELECT count(*) != 0 FROM pg_subscription_rel WHERE srsubstate <> 'r') LOOP COMMIT; END LOOP; END; $$;

5. 调用函数来计算传输时间：

\timing
call wait_for_rep();

Results

• 如果没有binary load (binary set to false), 需要383884.913 ms（06:23.885）才能完成事务并在订阅者节点上应用更改。
• 如果使用 binary load (binary set to true)，需要267149.655 ms（04:27.150）才能完成事务并在订阅者节点上应用更改。

当以binary load格式执行初始表复制时，这将提升32%的性能

 

Conclusion

分布式PostgreSQL数据库的使用正在迅速增长，而复制功能是任何分布式系统的关键核心部分。随着每个主要版本的发布，PostgreSQL中的复制功能正逐渐变得更加成熟且功能丰富。逻辑复制的基础工作在PostgreSQL 10之前就已奠定，但逻辑复制功能本身在PostgreSQL 10中才发展成为一个可用的形式。自那以后，复制支持得到了极大的增强，每个版本中添加的主要功能都值得单独写一篇博客来介绍，我将在适当的时候进行覆盖。本博客将介绍PostgreSQL 16中添加的新逻辑复制性能功能；请持续关注我们的博客，我们将继续讨论PostgreSQL 16中剩余的逻辑复制功能。