<div dir="ltr">Hi everyone. I'm new to this mailing list. Greetings to the pgpool-II community :-)<br><br>There goes my question:<br><br>I would like to use the load balancing capabilities of pgpool-II in a existing production setup but I have several concerns...<br>
<br>This is the scenario (simplified):<br><br>* 1 Web Server running Tomcat<br>* 2 DB Servers running Postgres 9.3. Master-Slave replication using Hot-Standby. Slave server runs in read-only mode and can be queried<br>* 2 applications runs in the same Tomcat <br>
* Each application connects to Postgres through its own JDBC connection pool<br><br>These 2 applications serve to different purposes but write into the same database.<br>The JDBC connection pool allows me to have control of the connection management (flow, min/max idle connections, validation queries, expiration time of running queries, request queues, expiration time of awaiting requests, etc)<br>
Each application has different pool configuration values.<br><br>Currently the postgres slave server are used to perform backups and as a safeguard in case of master crash but I would like to perform load balancing because the applications run long read queries and it would be beneficial.<br>
<br>As far as I know, it can be only achieved using an intermediate layer, like pgpool-II. From the point of view of the application, it is transparent. Pgpool talks to the database, application talks to pgpool by simply changing the connection string.<br>
Load balancing is configured in pgpool but, like its name points, is also a connection pool so I end up with 2 pool layers. Is it correct?<br><br>I could leave out Tomcat JDBC Connection pool and let the pooling tasks only to pgpool but I don't want to lose the capabilities that I currently have (different values for each application, visual monitoring of the pool status, dynamic modification of the pool through JMX, etc...).<br>
<br>So, I guess that I need to setup a pool of connections in pgpool greater than the sum of the max connections of each JDBC pool and adjusts the timeout values in order to avoid undesired connection behaviours.<br>I suspect that, despite the benefits of load balance read queries, "regular" performance will decrease because of the overhead caused by this extra step to reach the database.<br>
<br>My question is: what is the best way to achieve load balancing with pgpool-II, bearing in mind that the applications has their own pooling strategy?<br><br>Thank you in advance<br></div>