<div dir="ltr">Hi there.<div><br></div><div>We have enabled connection pooling and that works well ;-)</div><div><br></div><div>The concern I have is that in an environment where we have multiple slaves with one geographically separated - no matter what type of replication you are running there will always be a delay in syncing to the geo separated db. Based on current operation if I load a database to the primary and then go to perform an action on that db I get a response from PGPool saying the db doesn't exist on node 2 (geo separated) node and the query fails - even though that query was directed to the master that does have the database as it was loaded there.</div><div><br></div><div>I would have assumed that the operation would be that I can query the master without this issue. In non load balancing mode where I am solely relying on comms with the master and using the slaves to failover when required I see this behaviour as not in keeping with the non load balancing setting as it still relies on connections to the slaves.</div><div><br></div><div>This also severely impacts on anyones ability to use PGPool in an environment where one slave node is kept geographically separated as it relies on replication being completed prior to the database being used at all - even on the master where it was loaded initially. We have deliberately geographically separated the 3rd node (node2) and understand that sync delay will always be an issue but shouldn't penalise overall operation.</div><div><br></div><div>There should also not be a penalty in initial connection to the Master (if cached connection doesn't exist) just because you have a slave that is 200ms away. Avoiding connections to the slave in this scenario would avoid this completely. </div><div><br></div><div>Cheers!</div><div><br></div><div>Jonathan</div><div><br></div><div><br></div><div class="gmail_extra">
<br><div class="gmail_quote">On Wed, Jul 22, 2015 at 6:50 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">> Hi there.<br>
><br>
> We're running PGPoolII in a Master-Slave setup with 3 servers - one being<br>
> the primary and 2 as slaves.<br>
><br>
> We have it running in non-loadbalance mode using Postgresql Streaming<br>
> replication.<br>
><br>
> What we've noticed though is that when a connection is opened to PGPool it<br>
> is in turn opening a connection to each server - the Master and both<br>
> Slaves.<br>
><br>
> This seems odd - and in our setup - where one of the slaves is<br>
> geographically removed (distanced from the master) causes each connection<br>
> to slow down dramatically.<br>
><br>
> Preferably we'd like to see PGPool only connect to the Master - and not the<br>
> other two slaves if that can be set - or alternatively is there a way of<br>
> reducing the speed impact of connecting to geo separated slave?<br>
><br>
> Any help or advice on dealing with this appreciated ;-)<br>
<br>
</span>This sounds a little odd, since pgpool-II keeps connections to backend<br>
(unless you disable connection pooling). Thus the possible slowdown<br>
could only occur at the connection establishing time. Once the<br>
connections are established, you shouldn't notice the slowdown because<br>
pgpool-II does not access standbys through the slow wires. How do you<br>
observe the slowness?<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div><br></div></div>
<br>
--