[pgpool-general: 3888] Re: Master Slave Node Connection Issues

[Tatsuo Ishii]

Oh I see your point now. Thanks for the explantion!

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi there.
> 
> We have enabled connection pooling and that works well ;-)
> 
> 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.
> 
> 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.
> 
> 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.
> 
> 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.
> 
> Cheers!
> 
> Jonathan
> 
> 
> 
> On Wed, Jul 22, 2015 at 6:50 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > Hi there.
>> >
>> > We're running PGPoolII in a Master-Slave setup with 3 servers - one being
>> > the primary and 2 as slaves.
>> >
>> > We have it running in non-loadbalance mode using Postgresql Streaming
>> > replication.
>> >
>> > What we've noticed though is that when a connection is opened to PGPool
>> it
>> > is in turn opening a connection to each server - the Master and both
>> > Slaves.
>> >
>> > This seems odd - and in our setup - where one of the slaves is
>> > geographically removed (distanced from the master) causes each connection
>> > to slow down dramatically.
>> >
>> > Preferably we'd like to see PGPool only connect to the Master - and not
>> the
>> > other two slaves if that can be set - or alternatively is there a way of
>> > reducing the speed impact of connecting to geo separated slave?
>> >
>> > Any help or advice on dealing with this appreciated ;-)
>>
>> This sounds a little odd, since pgpool-II keeps connections to backend
>> (unless you disable connection pooling). Thus the possible slowdown
>> could only occur at the connection establishing time. Once the
>> connections are established, you shouldn't notice the slowdown because
>> pgpool-II does not access standbys through the slow wires. How do you
>> observe the slowness?
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
> 
> -- 
> --