Postgres 9.0 Streaming Replication and Load Balancing?
Hi!
I have a Postgres 9.0 on CentOs 5.6 with a functional asynchronous
streaming replication to a hot-standby slave (over a TCP connection).
At the moment, the slave only makes the replication, but it accepts
read-only queries.
I need to load-balance the DB requests to both servers and was trying
pgPool-II but it seems it has some problems if we already have
connection pooling elsewhere.
I have some application servers that already have their own connection
pool functionalities and I wonder if anyone found a solution for that.
Imagine I have some different deploys on a Glassfish or a Tomcat server,
using the server connection pooling facilities and from that we would
access the database, or the balancer.
Has anyone managed to do this with pgPool-II? Any other options?
Best regards,
Paulo Correia
Hello all!
Still haven't found any solution to this problem.
Having a Postgres 9.0 with assynchronous streaming replication to a
hot-standby slave, both with CentOs 5.6, how can I use both DB instances
for query load balancing?
I've tried with pgPool-II but the pooling mechanism is disruptive with
the existing pool on the application servers.
Has anyone had this issue before? Any suggestions?
Best regards,
Paulo Correia
Show quoted text
On 13/04/12 14:38, Paulo Correia wrote:
Hi!
I have a Postgres 9.0 on CentOs 5.6 with a functional asynchronous
streaming replication to a hot-standby slave (over a TCP connection).At the moment, the slave only makes the replication, but it accepts
read-only queries.I need to load-balance the DB requests to both servers and was trying
pgPool-II but it seems it has some problems if we already have
connection pooling elsewhere.
I have some application servers that already have their own connection
pool functionalities and I wonder if anyone found a solution for that.Imagine I have some different deploys on a Glassfish or a Tomcat
server, using the server connection pooling facilities and from that
we would access the database, or the balancer.Has anyone managed to do this with pgPool-II? Any other options?
Best regards,
Paulo Correia
On 14 May 2012 17:28, Paulo Correia <paulo.correia@pdmfc.com> wrote:
Hello all!
Having a Postgres 9.0 with assynchronous streaming replication to a
hot-standby slave, both with CentOs 5.6, how can I use both DB instances for
query load balancing?
I've tried with pgPool-II but the pooling mechanism is disruptive with the
existing pool on the application servers.
Is the application RW or read only? What is the disruption being
caused? Are you seeing specific exceptions?
- Sumit
Hello Sumit,
At the given point there are no exceptions since the tests for using
pgPool-II with the application using a master and a slave resulted in
all connections being done on the master and none on the slave.
As the application as it's own connection pool, eventually all
connections will have a RW operation and as so all these connections
will be linked to the master.
As so, I cannot benefit from load balancing since all my connections
will be redirected to the master by pgPool-II, even if they are RO.
Is it possible to have pgPool-II making only load balance with no
connection pool?
Best regards,
Paulo Correia
Show quoted text
On 15/05/12 09:01, Sumit Raja wrote:
On 14 May 2012 17:28, Paulo Correia<paulo.correia@pdmfc.com> wrote:
Hello all!
Having a Postgres 9.0 with assynchronous streaming replication to a
hot-standby slave, both with CentOs 5.6, how can I use both DB instances for
query load balancing?
I've tried with pgPool-II but the pooling mechanism is disruptive with the
existing pool on the application servers.Is the application RW or read only? What is the disruption being
caused? Are you seeing specific exceptions?- Sumit
Unsure you can achieve this without a read only and a read write
application set up, I've always had RW application servers separate
from RO ones.
You could disable the application connection pool completely and let
pg-pool do the pooling for you (not sure of performance impact, if
any) as the session needs to be terminated after an insert for pg-pool
to load balance correctly (see
http://www.pgpool.net/mediawiki/index.php/FAQ, 5th one down for more
details).
- Sumit
On 16 May 2012 16:34, Paulo Correia <paulo.correia@pdmfc.com> wrote:
Hello Sumit,
At the given point there are no exceptions since the tests for using
pgPool-II with the application using a master and a slave resulted in all
connections being done on the master and none on the slave.As the application as it's own connection pool, eventually all connections
will have a RW operation and as so all these connections will be linked to
the master.As so, I cannot benefit from load balancing since all my connections will be
redirected to the master by pgPool-II, even if they are RO.Is it possible to have pgPool-II making only load balance with no connection
pool?Best regards,
Paulo CorreiaOn 15/05/12 09:01, Sumit Raja wrote:
On 14 May 2012 17:28, Paulo Correia<paulo.correia@pdmfc.com> wrote:
Hello all!
Having a Postgres 9.0 with assynchronous streaming replication to a
hot-standby slave, both with CentOs 5.6, how can I use both DB instances
for
query load balancing?
I've tried with pgPool-II but the pooling mechanism is disruptive with
the
existing pool on the application servers.Is the application RW or read only? What is the disruption being
caused? Are you seeing specific exceptions?- Sumit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Raja Consulting Ltd.
Incorporated in England and Wales No. 06454814, Registered Office: 4
Calder Court, Shorebury Point, Amy Johnson Way, Blackpool FY4 2RH
Yes, that was the results of our tests ...
It seems we'll have to do a lot of work on the application to separate
the queries in order to achieve the load-balancing.
Thanks anyway,
Best regards,
Paulo Correia
Show quoted text
On 17/05/12 09:32, Sumit Raja wrote:
Unsure you can achieve this without a read only and a read write
application set up, I've always had RW application servers separate
from RO ones.You could disable the application connection pool completely and let
pg-pool do the pooling for you (not sure of performance impact, if
any) as the session needs to be terminated after an insert for pg-pool
to load balance correctly (see
http://www.pgpool.net/mediawiki/index.php/FAQ, 5th one down for more
details).- Sumit
On 16 May 2012 16:34, Paulo Correia<paulo.correia@pdmfc.com> wrote:
Hello Sumit,
At the given point there are no exceptions since the tests for using
pgPool-II with the application using a master and a slave resulted in all
connections being done on the master and none on the slave.As the application as it's own connection pool, eventually all connections
will have a RW operation and as so all these connections will be linked to
the master.As so, I cannot benefit from load balancing since all my connections will be
redirected to the master by pgPool-II, even if they are RO.Is it possible to have pgPool-II making only load balance with no connection
pool?Best regards,
Paulo CorreiaOn 15/05/12 09:01, Sumit Raja wrote:
On 14 May 2012 17:28, Paulo Correia<paulo.correia@pdmfc.com> wrote:
Hello all!
Having a Postgres 9.0 with assynchronous streaming replication to a
hot-standby slave, both with CentOs 5.6, how can I use both DB instances
for
query load balancing?
I've tried with pgPool-II but the pooling mechanism is disruptive with
the
existing pool on the application servers.Is the application RW or read only? What is the disruption being
caused? Are you seeing specific exceptions?- Sumit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general