[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