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

[Salman Ahmed]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230913/9531e035/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgresql.conf
Type: application/octet-stream
Size: 29565 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230913/9531e035/attachment-0003.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: without lb pgpool.conf
Type: application/octet-stream
Size: 50112 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230913/9531e035/attachment-0004.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: with lb pgpool.conf
Type: application/octet-stream
Size: 50115 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20230913/9531e035/attachment-0005.obj>