<div dir="ltr">Hi<div><br></div><div>I was working on best practices for configuring the Pgpool-II load balancing and IMHO using a</div><div>smaller backend_weight <span style="background-color:transparent">for a primary backend node is a very good option to ensure we keep</span></div><div><span style="background-color:transparent">the primary server to handle all the WRITES </span><span style="background-color:transparent">while offloading the maximum READ load to the</span></div><div><span style="background-color:transparent">standby servers.</span></div><div><span style="background-color:transparent"><br></span></div><div>So to achieve that, what we can do is to identify which backend node in pgpool-II configuration</div><div>file corresponds to the <span style="background-color:transparent">primary node and set a smaller or even 0 backend_weight for that node.</span></div><div>The problem is the setup works as expected till the point the primary and standby nodes keep</div><div>working without any failover. <span style="background-color:transparent">As soon as primary </span>fail<span style="background-color:transparent"> and one of the standby becomes the new</span></div><div><span style="background-color:transparent">primary all the load weight distribution becomes less than ideal.</span></div><div><br></div><div>For example, consider setting backend_weight0 = 0 (backend-node 0 is supposed to be primary),</div><div>But for some reason failover happens and now backend-1 becomes primary while backend-0 gets</div><div>attached back as standby. Now that would effectively mean that Pgpool-II will be routing the maximum</div><div>READ <span style="background-color:transparent">load </span>to new<span style="background-color:transparent"> primary </span>while backend0<span style="background-color:transparent"> standby node (old primary) will sit idle.</span></div><div><br>I have been thinking about the possible solutions for that and I think one of the ways is to add a new</div><div>configuration <span style="background-color:transparent">parameter for each backend i.e. backend_weight_primary[n] that sets the weight for the</span></div><div><span style="background-color:transparent">node when it is </span><span style="background-color:transparent">in primary mode and when it becomes standby it acquires back the</span></div><div><span style="background-color:transparent">standard backend_weight.</span></div><div><br>I have also cooked up a POC patch for that, to showcase how the feature will work.</div><div><br></div><div>The idea is when backend_weight_primary is set for any backend then for primary mode Pgpool-II will</div><div>use <span style="background-color:transparent">backend_weight_primary as a load balancing weight for that node and for standby role it will</span></div><div><span style="background-color:transparent">use its </span><span style="background-color:transparent">backend_weight for load balancing ratio.</span></div><div><span style="background-color:transparent">When </span><span style="background-color:transparent">backend_weight_primary value is not set for any node then backend_weight will be used</span></div><div><span style="background-color:transparent">for both roles ( standby and primary) that is the existing behaviour</span></div>
<div><br></div><div>Example behaviour with the patch</div><div>===================</div><div>3 node cluster with backend_weight =1 and backend_weight_primary=0 for each node.</div><div>
<p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace" style="">postgres=# pgpool show backend_weight;</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>item <span class="gmail-Apple-converted-space"> </span>| value | <span class="gmail-Apple-converted-space"> </span>description<span class="gmail-Apple-converted-space"> </span></font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">-----------------+-------+---------------------------------</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>backend_weight0 | 1 <span class="gmail-Apple-converted-space"> </span>| load balance weight of backend.</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>backend_weight1 | 1 <span class="gmail-Apple-converted-space"> </span>| load balance weight of backend.</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>backend_weight2 | 1 <span class="gmail-Apple-converted-space"> </span>| load balance weight of backend.</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">(3 rows)</font></span></p><p class="gmail-p2" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0);min-height:22px"><font face="monospace" style="background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"></span><br></font></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">postgres=# pgpool show backend_weight_primary;</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>item <span class="gmail-Apple-converted-space"> </span>| value | <span class="gmail-Apple-converted-space"> </span>description<span class="gmail-Apple-converted-space"> </span></font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">-------------------------+-------+-------------------------------------------------</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>backend_weight_primary0 | 0 <span class="gmail-Apple-converted-space"> </span>| load balance weight of backend in primary role.</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>backend_weight_primary1 | 0 <span class="gmail-Apple-converted-space"> </span>| load balance weight of backend in primary role.</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>backend_weight_primary2 | 0 <span class="gmail-Apple-converted-space"> </span>| load balance weight of backend in primary role.</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">(3 rows)</font></span></p><p class="gmail-p2" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0);min-height:22px"><font face="monospace" style="background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"></span><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)">
</p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">postgres=#<span class="gmail-Apple-converted-space"> </span></font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">postgres=# show pool_nodes;</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>node_id | hostname<span class="gmail-Apple-converted-space"> </span>| port | status | pg_status | lb_weight |<span class="gmail-Apple-converted-space"> </span>role <span class="gmail-Apple-converted-space"> </span>| pg_role |...<span class="gmail-Apple-converted-space"> </span></font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">---------+-----------+------+--------+-----------+-----------+---------+---------+----</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>0 <span class="gmail-Apple-converted-space"> </span>| localhost | 5432 | up <span class="gmail-Apple-converted-space"> </span>| up<span class="gmail-Apple-converted-space"> </span>| 0.333333<span class="gmail-Apple-converted-space"> </span>| standby | standby | </font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>1 <span class="gmail-Apple-converted-space"> </span>| localhost | 5444 | up <span class="gmail-Apple-converted-space"> </span>| up<span class="gmail-Apple-converted-space"> </span>| 0.000000<span class="gmail-Apple-converted-space"> </span>| primary | primary | </font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>2 <span class="gmail-Apple-converted-space"> </span>| localhost | 5555 | up <span class="gmail-Apple-converted-space"> </span>| up<span class="gmail-Apple-converted-space"> </span>| 0.333333<span class="gmail-Apple-converted-space"> </span>| standby | standby | </font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">(3 rows)</font></span></p><p class="gmail-p2" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0);min-height:22px"><font face="monospace" style="background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"></span><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><span class="gmail-Apple-converted-space" style="background-color:rgb(255,255,255)"><font face="monospace">
</font></span></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">postgres=#<span class="gmail-Apple-converted-space"> </span></font></span></p></div><div><font face="monospace" style="background-color:rgb(255,255,255)"><br></font></div><div><font style="background-color:rgb(255,255,255)" face="arial, sans-serif">After failover </font></div><div><font style="background-color:rgb(255,255,255)" face="arial, sans-serif">========</font></div><div><font face="monospace" style="background-color:rgb(255,255,255)"><br></font></div><div><div><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">postgres=# show pool_nodes;</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>node_id | hostname<span class="gmail-Apple-converted-space"> </span>| port | status | pg_status | lb_weight |<span class="gmail-Apple-converted-space"> </span>role <span class="gmail-Apple-converted-space"> </span>| pg_role |...<span class="gmail-Apple-converted-space"> </span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">---------+-----------+------+--------+-----------+-----------+---------+---------+----</font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>0 <span class="gmail-Apple-converted-space"> </span>| localhost | 5432 | up <span class="gmail-Apple-converted-space"> </span>| up<span class="gmail-Apple-converted-space"> </span>| <span class="gmail-Apple-converted-space">0.000000 </span>| primary | primary | </font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>1 <span class="gmail-Apple-converted-space"> </span>| localhost | 5444 | up <span class="gmail-Apple-converted-space"> </span>| up<span class="gmail-Apple-converted-space"> </span>| <span class="gmail-Apple-converted-space">0.333333 </span>| standby | standby | </font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace"><span class="gmail-Apple-converted-space"> </span>2 <span class="gmail-Apple-converted-space"> </span>| localhost | 5555 | up <span class="gmail-Apple-converted-space"> </span>| up<span class="gmail-Apple-converted-space"> </span>| 0.333333<span class="gmail-Apple-converted-space"> </span>| standby | standby | </font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">(3 rows)</font></span></p><p class="gmail-p2" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0);min-height:22px"><font face="monospace" style="background-color:rgb(255,255,255)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"></span><br></font></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures"><font face="monospace"><span class="gmail-Apple-converted-space" style="background-color:rgb(255,255,255)"></span></font></span></p><p class="gmail-p1" style="margin:0px;font-variant-numeric:normal;font-variant-east-asian:normal;font-stretch:normal;font-size:14px;line-height:normal;color:rgb(0,0,0)"><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font face="monospace">postgres=#<span class="gmail-Apple-converted-space"> </span></font></span></p></div><div><br></div></div><div><br></div><div>Thoughts and suggestions?</div><div><br></div><div><br></div><div>Best regards</div><div>Muhammad Usama</div><div><br><div><br></div></div></div>