Re: Connection Pooling

On 07/10/11 06:57, Brandon Phelps wrote:
> Can anyone recommend a good solution for connection pooling? Here is our
> setup:
>
> 2 PostgreSQL 9.1 servers (1 master, 1 hot standby).
> 1 Apache 2.2.17
>
> We have a pretty extensive web application running on the apache server
> that talks to both of the database servers. Updates and small tasks
> (simple selects, etc) are directed to the master DB server while large
> reports that can take a while to run are directed to the hot standby, as
> not to affect performance of the master.
>
> Each page of the web app generally make a single connection to the
> database with the exception being the complicated reports which first
> make a connection to the master, verify that the user is allowed to
> access the page in question, close that connection, then open another
> connection to the hot standby for the report itself.
>
> One connection per page is not all that bad however the end users who
> make use of the web app are quite familiar with it and often fly through
> the pages very fast. We would like to implement some type of connection
> pooling so that these database connections (from web server to the DB
> servers) do not have to get created and torn down constantly.
>
> I have checked out the pg_pool website however was not very impressed
> with the documentation provided. Is pg_pool going to be our best
> solution or is there something better? Any advice would be appreciated.

Depending on what software your application is written in, there will
probably be support for persistent, pooled database connections.
I think you should look at using those for at least the master
connections, if not both.

I know the DB connections in things like the Catalyst or Dancer
frameworks will do that by default; if you've rolled your own web
framework then you may have some more work to do though.

Cheers,
Toby