Re: 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

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(dot)correia(at)pdmfc(dot)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

On 15/05/12 09:01, Sumit Raja wrote:
> On 14 May 2012 17:28, Paulo Correia<paulo(dot)correia(at)pdmfc(dot)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(dot)correia(at)pdmfc(dot)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 Correia
>
> On 15/05/12 09:01, Sumit Raja wrote:
>>
>> On 14 May 2012 17:28, Paulo Correia<paulo(dot)correia(at)pdmfc(dot)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(at)postgresql(dot)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

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(dot)correia(at)pdmfc(dot)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 Correia
>>
>> On 15/05/12 09:01, Sumit Raja wrote:
>>> On 14 May 2012 17:28, Paulo Correia<paulo(dot)correia(at)pdmfc(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>