[pgpool-general: 7691] Re: Pgbench test: direct connection vs. pgpool connect
Bo Peng
pengbo at sraoss.co.jp
Thu Sep 9 15:39:43 JST 2021
Hello,
> Yes, it's a simple test, but the difference is *huge* IMHO. Repeating
> several times the same test get same results.
>
> I'd like to know your experience and comments and what could be the problem.
> Is there any specific tuning to do in PGPool-II ?
Pgpool-II improves the performance, if many users run read-only queries concurrently.
You are testing pgpool using one user.
In this case, you can't gain benefit of pgpool's load balance and connection cache.
Try to increase the scale factor of pgbench, and run pgbench with 50 clients and SELECT-only queries.
For example:
num_init_children = 100
load_balance_mode = on
connection_cache = on
pgbench -S -c 50 -T 60 ...
> we setup a 3 nodes cluster with PGPool-II version 4.2.3 on 3 physical
> servers with CentOS 7 and PostgreSQL 12.7, like described here:
>
> https://www.pgpool.net/docs/latest/en/html/example-cluster.html
>
> Cluster status:
> [postgres at server14 ~]$ psql -h PGPOOL-VIP -p 9999 -U pgpool postgres -c
> "show pool_nodes"
> node_id | hostname | port | status | lb_weight | role |
> select_cnt | load_balance_node | replication_delay | replication_state |
> replication_sync_state | last_status_change
> ---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
> 0 | server10 | 5432 | up | 0.333333 | standby | 1
> | false | 0 | streaming | async
> | 2021-09-07 17:01:29
> 1 | server14 | 5432 | up | 0.333333 | primary | 55832
> | false | 0 | |
> | 2021-09-07 16:43:37
> 2 | server15 | 5432 | up | 0.333333 | standby | 1
> | true | 0 | streaming | async
> | 2021-09-07 16:43:37
> (3 rows)
>
>
> Performing some stress test with pgbench we noted a great difference
> connecting directly to the Priamry DB versus connecting to the PGPool VIP:
>
> Direct Connection on the Primary PostgreSQL:
> [postgres at server14 ~]$ pgbench -h server14 -p5432 test -T60
> Password:
> starting vacuum...end.
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 60 s
> number of transactions actually processed: 54441
> *latency average = 1.102 ms*
> tps = 907.336709 (including connections establishing)
> tps = 907.584536 (excluding connections establishing)
>
> Connection to the PGPool VIP:
> [postgres at server14 ~]$ pgbench -h PGPOOL-VIP -p9999 test -T60
> Password:
> starting vacuum...end.
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> duration: 60 s
> number of transactions actually processed: 21244
> *latency average = 2.824 ms*
> tps = 354.065350 (including connections establishing)
> tps = 354.562738 (excluding connections establishing)
>
>
> Thanks in advance.
> Regards,
> Luca
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/
More information about the pgpool-general
mailing list