[pgpool-hackers: 4395] Re: Tps not increasing after enabling Load Balancing

[Bo Peng]

Hi,

> We are facing an issue with our 3-Node HA PostgreSQL cluster: TPS remains
> similar with or without load balancing. Sometimes it is even lesser with
> load balancing enabled, compared to when load balancing is disabled.
>
> We then executed the following query from client machine:
> pgbench -p 9999 -j 2 -c 10 -t 1000 -h 172.30.237.3 -U postgres -d postgres
> 
> This was the output of query mentioned above:
> 
> transaction type: <builtin: TPC-B (sort of)>

pgbench executes TPC-B by default and UPDATEs executed by TPC-B cannot be load balanced.
If you are using pgbench, we recommend running pgbench with the "-S, --select-only" option.

For example:

  pgbench -p 9999 -j 2 -c 10 -t 1000 -h 172.30.237.3 -U postgres -d postgres -S

On Wed, 13 Sep 2023 15:58:39 +0500
Salman Ahmed <salman.ahmed at stormatics.tech> wrote:

> Dear pgpool-hackers,
> 
> 
> We are facing an issue with our 3-Node HA PostgreSQL cluster: TPS remains
> similar with or without load balancing. Sometimes it is even lesser with
> load balancing enabled, compared to when load balancing is disabled.
> 
> We ran the test in two phases. First we disabled load balancing by turning
> load_balance_mode to off and changing backend_weight0 to 1, backend_weight1
> to 0 and backend_weight2 to 0.
> 
> We executed the the following command from client machine:
> pgbench -i -p 9999 -h 172.30.237.3 -U postgres -d postgres
> 
> This was the output of the query mentioned above.
> 
> dropping old tables...
> 
> NOTICE:  table "pgbench_accounts" does not exist, skipping
> 
> NOTICE:  table "pgbench_branches" does not exist, skipping
> 
> NOTICE:  table "pgbench_history" does not exist, skipping
> 
> NOTICE:  table "pgbench_tellers" does not exist, skipping
> 
> creating tables...
> 
> generating data (client-side)...
> 
> 100000 of 100000 tuples (100%) done (elapsed 9.53 s, remaining 0.00 s)
> 
> vacuuming...
> 
> creating primary keys...
> 
> done in 19.25 s (drop tables 0.10 s, create tables 0.82 s, client-side
> generate 17.39 s, vacuum 0.49 s, primary keys 0.45 s).
> 
> We then executed the following command from client machine:
> 
> pgbench -p 9999 -j 2 -c 10 -t 1000 -h 172.30.237.3 -U postgres -d postgres
> 
> This was the output of the query mentioned above
> 
> transaction type: <builtin: TPC-B (sort of)>
> 
> scaling factor: 1
> 
> query mode: simple
> 
> number of clients: 10
> 
> number of threads: 2
> 
> maximum number of tries: 1
> 
> number of transactions per client: 1000
> 
> number of transactions actually processed: 10000/10000
> 
> number of failed transactions: 0 (0.000%)
> 
> latency average = 1258.453 ms
> 
> initial connection time = 1584.503 ms
> 
> tps = 7.946265 (without initial connection time)
> 
> We then enabled load balancing by turning  load_balance_mode to on and
> changing backend_weight0 to 0, backend_weight1 to 0.5 and backend_weight2
> to 0.5.
> 
> 
> We executed the following query from client machine:
> pgbench -i -p 9999 -h 172.30.237.3 -U postgres -d postgres
> 
> This was the output of the query mentioned above
> 
> dropping old tables...
> 
> NOTICE:  table "pgbench_accounts" does not exist, skipping
> 
> NOTICE:  table "pgbench_branches" does not exist, skipping
> 
> NOTICE:  table "pgbench_history" does not exist, skipping
> 
> NOTICE:  table "pgbench_tellers" does not exist, skipping
> 
> creating tables...
> 
> generating data (client-side)...
> 
> 100000 of 100000 tuples (100%) done (elapsed 1.50 s, remaining 0.00 s)
> 
> vacuuming...
> 
> creating primary keys...
> 
> done in 13.21 s (drop tables 0.21 s, create tables 0.54 s, client-side
> generate 11.34 s, vacuum 0.55 s, primary keys 0.57 s).
> 
> We then executed the following query from client machine:
> pgbench -p 9999 -j 2 -c 10 -t 1000 -h 172.30.237.3 -U postgres -d postgres
> 
> This was the output of query mentioned above:
> 
> transaction type: <builtin: TPC-B (sort of)>
> 
> scaling factor: 1
> 
> query mode: simple
> 
> number of clients: 10
> 
> number of threads: 2
> 
> maximum number of tries: 1
> 
> number of transactions per client: 1000
> 
> number of transactions actually processed: 10000/10000
> 
> number of failed transactions: 0 (0.000%)
> 
> latency average = 1595.227 ms
> 
> initial connection time = 4172.761 ms
> 
> tps = 6.268699 (without initial connection time)
> 
> I have attached our pgpool.conf file with and without load balancing
> enabled. I have also attached the postgresql.conf file.
> 
> To view our source code please go to:
> https://github.com/stormatics/pg_cirrus
> 
> While the queries mentioned above were being executed, we used htop to
> monitor system stats on our pgpool node and we saw these logs which seem
> strange.
> 
> pgpool: wait for connection request
> 
> pgpool: PCP: wait for connection request
> 
> pgpool: postgres postgres 115.186.134.230 (49986) idle
> 
> pgpool: postgres postgres 115.186.134.230 (49979) idle in transaction
> 
> We were using 4 VMs all with 1 core, 2 gb ram and ubuntu 22.04.
> 
> One VM is used as pgpool node, one is used as primary node, one is used as
> standby node 1 and one is used as standby node 2.
> 
> 
> Kindly help us in resolving this issue. Thanks!
> 
> Salman Ahmed
> Stormatics


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/