[pgpool-hackers: 4006] Proposal with POC patch for specifying different load-balancing weight for primary backend node

[Muhammad Usama]

Hi

I was working on best practices for configuring the Pgpool-II load
balancing and IMHO using a
smaller backend_weight for a primary backend node is a very good option to
ensure we keep
the primary server to handle all the WRITES while offloading the  maximum
READ load to the
standby servers.

So to achieve that, what we can do is to identify which backend node in
pgpool-II configuration
file corresponds to the primary node and set a smaller or even 0
backend_weight for that node.
The problem is the setup works as expected till the point the primary and
standby nodes keep
working without any failover. As soon as primary fail and one of the
standby becomes the new
primary all the load weight distribution becomes less than ideal.

For example, consider setting backend_weight0 = 0 (backend-node 0 is
supposed to be primary),
But for some reason failover happens and now backend-1 becomes primary
while backend-0 gets
attached back as standby. Now that would effectively mean that Pgpool-II
will be routing the maximum
READ load to new primary while backend0 standby node (old primary) will sit
idle.

I have been thinking about the possible solutions for that and I think one
of the ways is to add a new
configuration parameter for each backend i.e. backend_weight_primary[n]
that sets the weight for the
node when it is in primary mode and when it becomes standby it acquires
back the
standard backend_weight.

I have also cooked up a POC patch for that, to showcase how the feature
will work.

The idea is when backend_weight_primary is set for any backend then for
primary mode Pgpool-II will
use backend_weight_primary as a load balancing weight for that node and for
standby role it will
use its backend_weight for load balancing ratio.
When backend_weight_primary value is not set for any node then
backend_weight will be used
for both roles ( standby and primary) that is the existing behaviour

Example behaviour with the patch
===================
3 node cluster with  backend_weight =1 and backend_weight_primary=0 for
each node.

postgres=# pgpool show backend_weight;

      item       | value |           description

-----------------+-------+---------------------------------

 backend_weight0 | 1     | load balance weight of backend.

 backend_weight1 | 1     | load balance weight of backend.

 backend_weight2 | 1     | load balance weight of backend.

(3 rows)


postgres=# pgpool show backend_weight_primary;

          item           | value |                   description


-------------------------+-------+-------------------------------------------------

 backend_weight_primary0 | 0     | load balance weight of backend in
primary role.

 backend_weight_primary1 | 0     | load balance weight of backend in
primary role.

 backend_weight_primary2 | 0     | load balance weight of backend in
primary role.

(3 rows)


postgres=#

postgres=# show pool_nodes;

 node_id | hostname  | port | status | pg_status | lb_weight |  role   |
pg_role |...

---------+-----------+------+--------+-----------+-----------+---------+---------+----

 0       | localhost | 5432 | up     | up        | 0.333333  | standby |
standby |

 1       | localhost | 5444 | up     | up        | 0.000000  | primary |
primary |

 2       | localhost | 5555 | up     | up        | 0.333333  | standby |
standby |

(3 rows)


postgres=#

After failover
========

postgres=# show pool_nodes;

 node_id | hostname  | port | status | pg_status | lb_weight |  role   |
pg_role |...

---------+-----------+------+--------+-----------+-----------+---------+---------+----

 0       | localhost | 5432 | up     | up        | 0.000000  | primary |
primary |

 1       | localhost | 5444 | up     | up        | 0.333333  | standby |
standby |

 2       | localhost | 5555 | up     | up        | 0.333333  | standby |
standby |

(3 rows)


postgres=#


Thoughts and suggestions?


Best regards
Muhammad Usama
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20210829/3fcce3cb/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: backend_weihgt_primary_option.diff
Type: application/octet-stream
Size: 16250 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20210829/3fcce3cb/attachment-0001.obj>