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

[Bo Peng]

Hi,

> Hi Bo Peng
> we have set disable_load_balance_on_write = off and now our read queries
> are successfully executing on standby nodes hence achieved the load
> balancing via pgbench as well
> but issue is still the same TPS with load balancing is better as compared
> to without load balancing

I think the reason is that the overhead of pgbench's built-in read queries is low,
and the overhead of Pgpool-II's load balancing is higher
than the overhead of the executed read queries.

To increase the READ overhead, you can use pgbench's "-f" option
to use a custom sql script which causes large overhead, such as "select * from pgbench_accounts".
You also need to increase the scale factor.

Best regards,

On Tue, 3 Oct 2023 15:04:42 +0500
Dev BotX <devbotx5 at gmail.com> wrote:

> On Fri, 15 Sept 2023 at 10:30, Bo Peng <pengbo at sraoss.co.jp> wrote:
> 
> > 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.
> >
> Hi Bo Peng
> we have set disable_load_balance_on_write = off and now our read queries
> are successfully executing on standby nodes hence achieved the load
> balancing via pgbench as well
> but issue is still the same TPS with load balancing is better as compared
> to without load balancing
> 
> >
> > 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/
> >


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