PostgreSQL 数据写入性能测试

by · 2019-04-03


1主2从SR同步流复制测

  • 搭建环境略，可参考之前文章

    Server | Role
    10.10.56.16 | master
    10.10.56.17 | slave1
    10.10.56.19 | slave2

  • 16查询状态

pocdb=# SELECT client_addr,application_name,sync_state FROM pg_stat_replication;
 client_addr | application_name | sync_state
-------------+------------------+------------
 10.10.56.17 | slave1           | sync
 10.10.56.19 | slave2           | potential
(2 rows)

pocdb=#

当从库大于两台机器时同步状态为 potential ，表示可能会提升为 master

  • 查询用户
pocdb=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 repl      | Replication                                                | {}        |

pocdb=#
  • 16 创建同步测试表 synctest 和序列
pocdb=# create table synctest (id bigint primary key ,number bigint,date timestamp default now());
CREATE TABLE

pocdb=# create sequence seq_synctest increment by 1 minvalue 1 maxvalue 99999999999999 cache 50 no cycle;
CREATE SEQUENCE
pocdb=#
  • 查看表大小
pocdb=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | seq_synctest | sequence | postgres | 8192 bytes |
 public | synctest     | table    | postgres | 0 bytes    |
(2 rows)

pocdb=#

此时17、19服务器自动同步该表，可分别去查询

  • 16服务器编写插入脚本
postgres@clw-db1:/pgdata/10/poc/scripts> vi bench_script_for_insert_20180717.sql
postgres@clw-db1:/pgdata/10/poc/scripts> cat bench_script_for_insert_20180717.sql
\set number random(1, 100000000000000000000000)
INSERT INTO synctest(id,number) VALUES (nextval('seq_synctest'),:number);
postgres@clw-db1:/pgdata/10/poc/scripts>
  • 16、17、19 编写监控延迟脚本
postgres@clw-db1:/pgdata/10/poc/scripts> cat monior_syncSR_relay.sh
#!/bin/bash

/opt/pgsql-10/bin/psql pocdb<<EOF
select now();
select client_addr, application_name, write_lag, flush_lag, replay_lag \
from pg_stat_replication where usename='l_repl' and application_name='slave1';
 \q
EOF
postgres@clw-db1:/pgdata/10/poc/scripts>
  • 启动pgbench进行压测
postgres@clw-db1:/pgdata/10/poc/scripts> /opt/pgsql-10/bin/pgbench -T 1200 -j 600 -c 500  -f  bench_script_for_insert_20180717.sql pocdb
  • 编写slave1 的数据查询脚本
for i in {1..1000000000}
do
/pgdata/10/poc/scripts/monior_syncSR_relay_slave1.sh >> syncSR_relay_slave1_result
sleep 10
done
for i in {1..1000000000}
do
/pgdata/10/poc/scripts/monior_syncSR_relay_slave2.sh >> syncSR_relay_slave2_result
sleep 10
done
postgres@clw-db2:/pgdata/10/poc/scripts> cat query_count_slave1.sh
#!/bin/bash

/opt/pgsql-10/bin/psql pocdb<<EOF
select now();
select max(id) from synctest;
 \q
EOF
postgres@clw-db2:/pgdata/10/poc/scripts>
for i in {1..1000000000}
do
/pgdata/10/poc/scripts/query_count_result.sh >> query_count_sum
sleep 10
done

查询插入数量脚本

postgres@clw-db1:/pgdata/10/poc/scripts> cat query_count_result.sh
#!/bin/bash

/opt/pgsql-10/bin/psql pocdb<<EOF
select now();
select max(id) from synctest;
 \q
EOF
postgres@clw-db1:/pgdata/10/poc/scripts>
  • 编写内存、网络、IO性能监控脚本
/home/super/pgsoft/nmon_x86_64_sles11 -f  -c 150  -s 10
  • 17 查询数量脚本
for i in {1..1000000000}
do
/pgdata/10/poc/scripts/query_count_slave1.sh >> query_count_slave1_sum
sleep 10
done
  • 17 性能监控脚本
/home/pgsoft/nmon_x86_64_sles11 -f  -c 150  -s 10
  • 19 查询数量脚本
for i in {1..1000000000}
do
/pgdata/10/poc/scripts/query_count_slave2.sh >> query_count_slave2_sum
sleep 10
done
  • 19性能监控
/home/pgsoft/nmon_x86_64_sles11 -f  -c 150  -s 10
  • 16测试结果
could not connect to server: Resource temporarily unavailable
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
transaction type: bench_script_for_insert_20180717.sql
scaling factor: 1
query mode: simple
number of clients: 500
number of threads: 500
duration: 1200 s
number of transactions actually processed: 9723149
latency average = 61.736 ms
tps = 8098.953578 (including connections establishing)
tps = 8099.988751 (excluding connections establishing)
postgres@clw-db1:/pgdata/10/poc/scripts>

pocdb=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | seq_synctest | sequence | postgres | 8192 bytes |
 public | synctest     | table    | postgres | 377 MB     |
(2 rows)

pocdb=#

1主2从同步测试结果

测试背景：服务器10.10.56.16，10.10.56.17，10.10.56.19 CPU 8核 内存200G 网络传输3M/s

测试方法： 20 分钟 600线程 500客户端连接数 不停往 16master 服务器写入数据

测试结果：CPU 占用率 80% ,磁盘 I/O 23MB/s ,TPS:7419 ,数据量为：1880万,
同步时延大约为 3毫秒左右

  • 压测脚本（-T 1200秒 -j 600 线程 -c 客户端连并发接数500 -f 输出压测结果）
/opt/pgsql-10/bin/pgbench -T 1200 -j 600 -c 500  -f  bench_script_for_insert_20180717.sql pocdb
  • 第一次测试(1主2从同步SR)
transaction type: bench_script_for_insert_20180717.sql
scaling factor: 1
query mode: simple
number of clients: 500
number of threads: 500
duration: 1200 s
number of transactions actually processed: 8906813
latency average = 67.391 ms
tps = 7419.410395 (including connections establishing)
tps = 7420.536021 (excluding connections establishing)
postgres@clw-db1:/pgdata/10/poc/scripts>
  • 第二次测试(1主2从同步SR)
postgres@clw-db1:/pgdata/10/poc/scripts> /opt/pgsql-10/bin/pgbench -T 1200 -j 600 -c 500  -f bench_script_for_insert_20180717.sql pocdb

query mode: simple
number of clients: 500
number of threads: 500
duration: 1200 s
number of transactions actually processed: 8239738
latency average = 72.848 ms
tps = 6863.613524 (including connections establishing)
tps = 6865.035476 (excluding connections establishing)
postgres@clw-db1:/pgdata/10/poc/scripts>
  • 查询主从数据延迟,主要观察参数（write_lag、flush_lsn、replay_lag）
pocdb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 23564
usesysid         | 16393
usename          | repl
application_name | slave2
client_addr      | 10.10.56.19
client_hostname  |
client_port      | 52820
backend_start    | 2018-05-16 17:43:23.726216+08
backend_xmin     |
state            | streaming
sent_lsn         | 1/7D4FF030
write_lsn        | 1/7D4FF030
flush_lsn        | 1/7D4FEF78
replay_lsn       | 1/7D4FEF78
write_lag        | 00:00:00.003057
flush_lag        | 00:00:00.003057
replay_lag       | 00:00:00.003057
sync_priority    | 2
sync_state       | potential
-[ RECORD 2 ]----+------------------------------
pid              | 23562
usesysid         | 16393
usename          | repl
application_name | slave1
client_addr      | 10.10.56.17
client_hostname  |
client_port      | 33647
backend_start    | 2018-05-16 17:43:17.371715+08
backend_xmin     |
state            | streaming
sent_lsn         | 1/7D524110
write_lsn        | 1/7D523F30
flush_lsn        | 1/7D523570
replay_lsn       | 1/7D523570
write_lag        | 00:00:00.000329
flush_lag        | 00:00:00.000329
replay_lag       | 00:00:00.000329
sync_priority    | 1
sync_state       | sync

pocdb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 23564
usesysid         | 16393
usename          | repl
application_name | slave2
client_addr      | 10.10.56.19
client_hostname  |
client_port      | 52820
backend_start    | 2018-05-16 17:43:23.726216+08
backend_xmin     |
state            | streaming
sent_lsn         | 1/7EB37850
write_lsn        | 1/7EB36F00
flush_lsn        | 1/7EB34EE0
replay_lsn       | 1/7EB34EE0
write_lag        | 00:00:00.000841
flush_lag        | 00:00:00.000841
replay_lag       | 00:00:00.000841
sync_priority    | 2
sync_state       | potential
-[ RECORD 2 ]----+------------------------------
pid              | 23562
usesysid         | 16393
usename          | repl
application_name | slave1
client_addr      | 10.10.56.17
client_hostname  |
client_port      | 33647
backend_start    | 2018-05-16 17:43:17.371715+08
backend_xmin     |
state            | streaming
sent_lsn         | 1/7EB37F80
write_lsn        | 1/7EB27B08
flush_lsn        | 1/7EB27B08
replay_lsn       | 1/7EB27B08
write_lag        | 00:00:00.001525
flush_lag        | 00:00:00.003983
replay_lag       | 00:00:00.012568
sync_priority    | 1
sync_state       | sync
  • 查询数据量
pocdb=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | seq_synctest | sequence | postgres | 8192 bytes |
 public | synctest     | table    | postgres | 377 MB     |
(2 rows)

pocdb=# select max(id) from synctest;
   max
----------
 18812501
(1 row)

pocdb=#

单库测试

  • 48服务器创建数据库 pocdb 和表 synctest
postgres=# create database pocdb;
CREATE DATABASE

pocdb=# create table synctest (id bigint primary key ,number bigint,date timestamp default now());
CREATE TABLE
pocdb=# create sequence seq_synctest increment by 1 minvalue 1 maxvalue 99999999999999 cache 50 no cycle;
CREATE SEQUENCE
pocdb=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | seq_synctest | sequence | postgres | 8192 bytes |
 public | synctest     | table    | postgres | 0 bytes    |
(2 rows)

pocdb=#
  • 48性能监控
/home/postgres/pgsoft/nmon_x86_64_sles11 -c 180 -f -s 10
  • 48 查询数量脚本
for i in {1..100000000}
do
/pgdata/10/scripts/query_count_result.sh >> query_count_slave2_sum
sleep 10
done
  • 48 压测脚本
 /opt/pgsql-10/bin/pgbench -T 1200 -j 800 -c 400  -f bench_script_for_insert_20180717.sql pocdb

单实例库测试

  • 测试背景: 服务器 : 10.10.56.17 cpu：8核 内存：128GB

  • 测试过程: 在服务器上执行 20分钟写入数据库测试，500 线程数 500 客户端连接数

    • 测试结果: 写入数据库共3500万 ，平均响应时间 17.125ms ,TPS 为29000 ，CPU占用95% 磁盘写速率：35MB/s ，网络传输速率：3MB/s

  • 17 服务器 单库压测结果

    transaction type: bench_script_for_insert_20180717.sql
scaling factor: 1
query mode: simple
number of clients: 500
number of threads: 500
duration: 1200 s
number of transactions actually processed: 35047438
latency average = 17.125 ms
tps = 29197.286164 (including connections establishing)
tps = 29205.049038 (excluding connections establishing)
postgres@clw-db2:/pgdata/estest10/scripts>

  • 查询数据

pocdb1=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | seq_synctest | sequence | postgres | 8192 bytes |
 public | synctest     | table    | postgres | 1747 MB    |
(2 rows)

pocdb1=# select max(id) from synctest;
   max
----------
 35947438
(1 row)

pocdb1=#

主从与单库性能对比

结果分析：单裤写入性能更高，大约为1主2从的2倍，TPS 单库大约为主从的4倍，单库磁盘写入速率更高，以上仅为测试数据，仅供参考。

从节点宕机测试

  • 测试过程: kill 掉从节点，模拟意外宕机，往主节点写入数据

  • 测试结果： 同步流复制时，从节点宕机会影响主库，主库进行写入和更新时会被夯住。

  • kill掉17 slave1,主库插入数据，会夯住。

pocdb=# insert into synctest(age,date)values(nextval(seq_synctest'),28701752,now());
pocdb'#

一主一从

  • 测试背景：服务器 : 10.10.56.17 cpu：8核 内存：128GB
  • 测试过程: 在服务器上执行 20分钟写入数据库测试，500 线程数 500 客户端连接数

  • 测试结果：写入数据：3890万 CPU占用：80% 磁盘写：25Mb/s 延迟：2毫秒 ,TPS :8288

    测试结果

transaction type: bench_script_for_insert_20180717.sql
scaling factor: 1
query mode: simple
number of clients: 400
number of threads: 400
duration: 1200 s
number of transactions actually processed: 9946727
latency average = 48.258 ms
tps = 8288.726962 (including connections establishing)
tps = 8289.701749 (excluding connections establishing)
postgres@clw-db1:/pgdata/10/poc/scripts>
  • 查询插入数据量
pocdb=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | seq_synctest | sequence | postgres | 8192 bytes |
 public | synctest     | table    | postgres | 496 MB     |
(2 rows)

pocdb=# select max(id) from synctest;
   max
----------
 38918501
(1 row)

pocdb=#
  • 查询延迟，观察参数(write_lag,flush_lag,replay_lag)
pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.002566 | 00:00:00.002566 | 00:00:00.002566 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.000774 | 00:00:00.000774 | 00:00:00.000774 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.000648 | 00:00:00.000648 | 00:00:00.000648 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.000408 | 00:00:00.000408 | 00:00:00.000408 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.002627 | 00:00:00.002666 | 00:00:00.008609 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.000593 | 00:00:00.000593 | 00:00:00.000593 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.003211 | 00:00:00.009313 | 00:00:00.015688 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.001078 | 00:00:00.001078 | 00:00:00.001078 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.021852 | 00:00:00.034961 | 00:00:00.035026 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag   | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.000829 | 00:00:00.002209 | 00:00:00.00334 | sync       | streaming
(1 row)

pocdb=# select client_addr,usename, application_name, write_lag, flush_lag, replay_lag,sync_state,state from pg_stat_replication;
 client_addr | usename | application_name |    write_lag    |    flush_lag    |   replay_lag    | sync_state |   state
-------------+---------+------------------+-----------------+-----------------+-----------------+------------+-----------
 10.10.56.19 | repl    | slave2           | 00:00:00.054129 | 00:00:00.054134 | 00:00:00.054964 | sync       | streaming
(1 row)

性能对比

测试对比：1主1从写 入性能和 单库写 性能相差不大，20分钟写入数据量大约都为3700万，1主两从写入性能较差，数据只有它俩的 一半，延迟比 1主1从 高点。以上仅为测试数据，仅供参考，没有达到极限的情况

转载自：https://blog.csdn.net/yaoqiancuo3276/article/details/80376654

相关推荐

Tags:

You may also like...

公众号

微信群

人数过多只能拉人进群;

加我好友yu389741

微信小程序