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

[Jonathan Eastgate]

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
>

-- 
--
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150722/bff79e2e/attachment.html>