Thread: Connection Pooling
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. Updatesand small tasks (simple selects, etc) are directed to the master DB server while large reports that can take a whileto 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 reportswhich first make a connection to the master, verify that the user is allowed to access the page in question, closethat 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 itand often fly through the pages very fast. We would like to implement some type of connection pooling so that these databaseconnections (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 goingto be our best solution or is there something better? Any advice would be appreciated. Thanks, Brandon
On Thu, Oct 6, 2011 at 3:57 PM, Brandon Phelps <bphelps@gls.com> 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.
Thanks,
Brandon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If you want to do load balancing between your database servers (split reads between the master and slave) or have auto failover then Pgpool-II is going to be your best bet. I just did a similar setup and the documentation isn't the best, but some googling turned up a few guides that I was able to jump between to get it setup and going.
If you just want all of the DB traffic to go to the master (and deal with failover on your own) you can use pgbouncer, which is much simpler, but also less feature-rich.
Adam
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
So we decided to go with pgpool-II. The documentation is a little lacking for pgpool-II so I have one question: How are connections handled once the default levels are reached? Here are my pgpool settings: num_init_children = 32 max_pool = 4 This creates 32 child processes when we start pgpool which I understand. Each time I browse to a page from our web app anddo a netstat -an on the web server (running pgpool) I see an additional connection to the database server, which looksgood. I assume that once 32 connections are opened at once then pgpool will start re-using them, based on the num_init_children* max_pool... But since 32 * 4 = 128, what will happen on the 129th connection? Will a new child get created,allowing for 4 more connections (1 * max_pool), or will that connection be denied? Thanks in advance, Brandon On 10/06/2011 04:07 PM, Adam Cornett wrote: > > On Thu, Oct 6, 2011 at 3:57 PM, Brandon Phelps <bphelps@gls.com <mailto:bphelps@gls.com>> 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 cantake 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 complicatedreports 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 withit and often fly through the pages very fast. We would like to implement some type of connection pooling so that thesedatabase 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_poolgoing to be our best solution or is there something better? Any advice would be appreciated. > > Thanks, > Brandon > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/__mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general> > > > > If you want to do load balancing between your database servers (split reads between the master and slave) or have autofailover then Pgpool-II is going to be your best bet. I just did a similar setup and the documentation isn't the best,but some googling turned up a few guides that I was able to jump between to get it setup and going. > > If you just want all of the DB traffic to go to the master (and deal with failover on your own) you can use pgbouncer,which is much simpler, but also less feature-rich. > > Adam
On Fri, 2011-10-07 at 13:51 -0400, Brandon Phelps wrote: > So we decided to go with pgpool-II. The documentation is a little lacking for pgpool-II so I have one question: > > How are connections handled once the default levels are reached? Here are my pgpool settings: > > num_init_children = 32 > max_pool = 4 > This configuration means you can have at most 32 clients sending queries at the same time to PostgreSQL via pgpool. With a max_pool of 4, each pgpool process can handle four different connections to the same server: they could differ either by the database name or by the user name. > This creates 32 child processes when we start pgpool which I understand. Each time I browse to a page from our web appand do a netstat -an on the web server (running pgpool) I see an additional connection to the database server, which looksgood. I assume that once 32 connections are opened at once then pgpool will start re-using them, based on the num_init_children* max_pool... But since 32 * 4 = 128, what will happen on the 129th connection? Will a new child get created,allowing for 4 more connections (1 * max_pool), or will that connection be denied? > Connection is not denied (that would be the behaviour of PostgreSQL). Connection is on hold waiting for a pgpool process to be available. Meaning you don't want long connections. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Forgive me, I'm still a bit confused by how max_pool works with num_init_children. First you said that at most I can have32 clients sending queries, but then you said that each process can handle 4 different connections... so does this meanthat I can have 128 connections from pgpool to my postgresql database? Sorry, not quite understanding. On 10/07/2011 02:05 PM, Guillaume Lelarge wrote: > On Fri, 2011-10-07 at 13:51 -0400, Brandon Phelps wrote: >> So we decided to go with pgpool-II. The documentation is a little lacking for pgpool-II so I have one question: >> >> How are connections handled once the default levels are reached? Here are my pgpool settings: >> >> num_init_children = 32 >> max_pool = 4 >> > > This configuration means you can have at most 32 clients sending queries > at the same time to PostgreSQL via pgpool. With a max_pool of 4, each > pgpool process can handle four different connections to the same server: > they could differ either by the database name or by the user name. > >> This creates 32 child processes when we start pgpool which I understand. Each time I browse to a page from our web appand do a netstat -an on the web server (running pgpool) I see an additional connection to the database server, which looksgood. I assume that once 32 connections are opened at once then pgpool will start re-using them, based on the num_init_children* max_pool... But since 32 * 4 = 128, what will happen on the 129th connection? Will a new child get created,allowing for 4 more connections (1 * max_pool), or will that connection be denied? >> > > Connection is not denied (that would be the behaviour of PostgreSQL). > Connection is on hold waiting for a pgpool process to be available. > Meaning you don't want long connections. > >
On Fri, 2011-10-07 at 15:45 -0400, Brandon Phelps wrote: > Forgive me, I'm still a bit confused by how max_pool works with num_init_children. First you said that at most I can have32 clients sending queries, but then you said that each process can handle 4 different connections... so does this meanthat I can have 128 connections from pgpool to my postgresql database? > That's right. You'll have, from pgpool, a maximum of num_init_children*max_pool connections (IOW, 128 with your connections). Out of these 128 connections, only 32 will be active at one time. I'll try to explain it another way. When client C1 connects to PostgreSQL via pgpool, it will use one of the four possible connections of a pooler process. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com