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

[Tatsuo Ishii]

Hi Usama,

I think we can do what you want today.

database_redirect_preference_list = '.*:standby'

This will redirect all read queries to any of standby servers.

> 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