PostgreSQL实战之物理复制和逻辑复制（三）

目录

PostgreSQL实战之物理复制和逻辑复制（三）

3.1 单实例、异步流复制、同步流复制性能测试

3.1 读性能测试

3.2 写性能测试

PostgreSQL实战之物理复制和逻辑复制（三）

3.1 单实例、异步流复制、同步流复制性能测试

根据PostgreSQL异步流复制、同步流复制原理分析，同步流复制方式的事务响应时间比异步流复制方式的响应时间高，推测同步流复制的主库性能损耗比异步流复制要大些，实际情况如何呢?这一小节将通过一个读场景和一个写场景对单实例、异步流复制、同步流复制进行压力测试，使用的压力测试工具为pgbench，测试机配置为4逻辑核CPU、8GB内存的虚拟机。
单实例主库的postgresql.conf主要参数如下所示:

wal_level = replica         #minimal, replica, or logical
synchronous_commit = off    #synchronization level;

异步流复制主库的postgresql.conf主要参数如下所示:

wal_level = replica            # minimal, replica,or logical
synchronous_commit = off       # synchronization level;
wal_keep_segments = 512        # in logfile segments, 16MB each; 0 disables
 

同步流复制主库的postgresql.conf主要参数如下所示:

wal_level = replica                    # minimal, replica, or logical
synchronous__commit = on               # synchronization level;
synchronous_standby_names = 'node2'    # standby servers that provide sync rep wal_keep_segments = 512                # in logfile segments,16MB each; 0 disables

以上仅列出单实例、异步流复制、同步流复制模式的主要postgresql.conf参数，其他postgresql.conf参数配置一样。

3.1 读性能测试

先对单实例、异步流复制、同步流复制进行读性能对比，选择基于主键的查询场景进行读性能测试，创建测试表test_per1并插入1000万测试数据，如下所示:

postgres=# CREATETABLE test_per1 (
id int4,
name text,
create_time timestamp(0) without time zone default clock_timestamp( ) ) ;
CREATETABLE
postgres=# INSERTINTO test_per1 (id, name)
SELECT n,n || 'per1'
FROM generate_series ( 1 ,100000000) n;
INSERT 0 10000000

之后添加主键并做表分析，如下所示。

postgres=# ALTER TABLE test_perl ADD PRIMARY KEY(id);
ALTERTABLE
postgres=# ANALYZE test_per1;
ANALYZE

编写压力测试查询SQL脚本，脚本名为select_per1.sql，如下所示:

\set v_id random( 1,10000000)
SELECT name FROM test_perl WHERE id= :v_id;

变量v_id从1到1000万范围内随机获取一个整数，根据主键查询表test _per1，之后测试并发连接数分别为2、4、8、16的TPS情况，pgbench测试脚本如下所示:

pgbench -c 2 -T 120 -d postgres -U postgres -n N -M prepared -f select_per1.sql
> select_2.out 2>&1 &
pgbench -c 4 -T 120 -d postgres -U postgres -n N -M prepared -f elect_per1.sql
> select_4.out 2>&1 &
pgbench -c 8 -T 120 -d postgres -U postgres -n N -M prepared -f select_per1.sql
> select_8.out 2>&1 &
pgbench -c 16 T 120 -d postgres -U postgres -n N -M prepared -f select_per1.sql
> select_16.out 2>&1 &

每次 pgbench测试时间为120秒，-M设置repared表示启用prepared statements，-n表示不做VACUUM操作，根据以上 pgbench测试脚本对单实例、异步流复制、同步流复制模式进行读压力测试，测试结果汇总如表所示。

 从以上测试看出，并发连接数为4时性能最高，这与设备CPU核数有关，本测试虚机CPU逻辑核为4，并发连接数上升到8和16时，读性能降低。以上是根据主键查询的场景，在连接数小于4时，异步流复制和同步流复制比单实例读性能略有降低，降幅在5%以内，总体来说，单实例、异步流复制、同步流复制在基于主键的读场景下性能差异较小。
注意：测试过程中我们发现CPU使用率大部分情况都在50%以下，如果一个pgbench进程没有充分消耗虚拟机的所有计算资源，可以在系统上跑多个pgbench进程测试这台设备此查询场景的最高tps。

3.2 写性能测试

接着对单实例、异步流复制、同步流复制模式进行写性能测试，测试场景为基于主键的更新操作，创建测试表test_per2并插入1000万数据，如下所示:

postgres=# CREATE TABLE test_per2(id int4, name text,flag char(1) ) ;
CREATE TABLE
postgres=# INSERT INTO test_per2 (id, name)
SELECT n,n || 'per2'
FROM generate_series( 1,10000000)n;
INSERT 0 10000000

添加主键并做表分析,如下所示

postgres=# ALTER TABLE test_per2 ADD PRIMARY KEY(id) ;
ALTER TABLE
postgres=# ANALYZE test_per2 ;
ANALYZE

编写压力测试脚本，脚本名为update_per2.sql 如下所示:

\set v_id random (1,1000000)
update test_per2 set flag=' l' where id= : v_id;

变量v_id从1到1000万范围内随机获取一个整数，根据主键更新表test_per2的 flag字段，之后测试并发连接数分别为2、4、8、16的TPS情况，pgbench测试脚本如下所示:

pgbench -c 2 -T 120 -d postgres -U postgres -n N -M prepared -f update_per2.sql
> update_2.out 2>&1 &
pgbench -c 4 -T 120 -d postgres -U postgres -n N -M prepared -f update_per2.sql
> update_4.out 2>&1 &
pgbench -c 8 -T 120 -d postgres -0 postgres -n N -M prepared -f pdate_per2.sql
> update_8.out 2>&1 &
pgbench -c 16 -T 120 -d postgres -U postgres -n N -M prepared -f update_per2.sql
> update_16.out 2>&1 &

每次pgbench测试时间为120秒，根据以上pgbench测试脚本对单实例、异步流复制、同步流复制模式进行写压力测试，测试结果汇总如表所示。

 从以上测试看出，基于主键更新的场景，异步流复制和单实例写性能几乎无差异，而同步流复制相比异步流复制和单实例场景性能大幅下降，在测试过程中，我们发现同步流复制主库上出现了大量UPDATE阻塞的情况，主要由于主库上提交事务时需等待备库接收并写入WAL日志后才向主库返回确认信息，这个过程消耗了大量通信时间，此过程消耗的时间越多，主库写场景TPS越小。