Thread: 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.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 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 Correia > > 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 >> > > > -- > 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 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 Correia >> >> 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 >>> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > >