Thread: pgpool

I was considering putting pgpool in to place and was hoping to hear
some feedback from those who use it.  I am mostly concerned about the
configuration I have.

In my setup, any one database server contains between 100 and 300
databases on it, each of which may be accessed at any time by one of
several web servers.

The database servers I use are monsters - Dual Xeon 3.2 with 8GB of
RAM.  As I was querying this group earlier as to postgresql.conf
tweaks I could do to speed things up the idea of setting fewer
connections and using pgpool was brought up repeatedly.  I am
concerned that if I drop the number of connections to less than the
number of databases I have, that pgpool would open the limit of
connections, hold them open and not allow any connections to the
remaining databases.  Is this a concern?  If I set up pgpool will I
have to have the same number of connections as I have databases?

Your input is appreciated.

-John

John Cunningham wrote:
> concerned that if I drop the number of connections to less than the
> number of databases I have, that pgpool would open the limit of
> connections, hold them open and not allow any connections to the
> remaining databases.  Is this a concern?  If I set up pgpool will I
> have to have the same number of connections as I have databases?

That depends on how you configure pgpool.  pgpool is not aware of the
connection limit count in the PostgreSQL server, so it will happily
open connections until there are no more slots available.

pgpool will require max_pool * num_init_children connection slots.
max_pool should be the number of database/user combinations you use
(300 in your case, assuming only one database user account), and
num_init_children should be on the order of how many concurrent
connections you expect to each combination ("several" in your case).
So you should have at least 300 * several PostgreSQL connection slots,
which is probably more than the 1000 or so that is the default.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Hmmm - so to bring one more idea here...

The usage of each of these systems will be different on different days
- Monday may have high usage on one or two databases and Wednesday may
be high on 20 others.  This makes me thing that configuring thigs this
way would mean that pgpool would be limiting one connection for each
database / user combination - whereas leaving the connections standard
would mean that if one set was busy it could take multiple connections
simultaneously.

This seems to be another knock against pgpool - or am I way off?

-John


On Thu, 13 Jan 2005 22:06:12 +0100, Peter Eisentraut <peter_e@gmx.net> wrote:
> John Cunningham wrote:
> > concerned that if I drop the number of connections to less than the
> > number of databases I have, that pgpool would open the limit of
> > connections, hold them open and not allow any connections to the
> > remaining databases.  Is this a concern?  If I set up pgpool will I
> > have to have the same number of connections as I have databases?
>
> That depends on how you configure pgpool.  pgpool is not aware of the
> connection limit count in the PostgreSQL server, so it will happily
> open connections until there are no more slots available.
>
> pgpool will require max_pool * num_init_children connection slots.
> max_pool should be the number of database/user combinations you use
> (300 in your case, assuming only one database user account), and
> num_init_children should be on the order of how many concurrent
> connections you expect to each combination ("several" in your case).
> So you should have at least 300 * several PostgreSQL connection slots,
> which is probably more than the 1000 or so that is the default.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>

> John Cunningham wrote:
> > concerned that if I drop the number of connections to less than the
> > number of databases I have, that pgpool would open the limit of
> > connections, hold them open and not allow any connections to the
> > remaining databases.  Is this a concern?  If I set up pgpool will I
> > have to have the same number of connections as I have databases?
>
> That depends on how you configure pgpool.  pgpool is not aware of the
> connection limit count in the PostgreSQL server, so it will happily
> open connections until there are no more slots available.
>
> pgpool will require max_pool * num_init_children connection slots.
>
> max_pool should be the number of database/user combinations you use
> (300 in your case, assuming only one database user account),

Not really. If a user connects to pgpool and all onnection slots are
already full, then pgpool will release the oldest connection slot and
reuse it for the new connection. So even if there are 300
database/user combinations, it's ok to set max_pool as low as, for
example, 4. Of course this will have unwanted side effect in that
connection caches are not very well kept, though.
--
Tatsuo Ishii

>  and
> num_init_children should be on the order of how many concurrent
> connections you expect to each combination ("several" in your case).
> So you should have at least 300 * several PostgreSQL connection slots,
> which is probably more than the 1000 or so that is the default.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

So - would it then be worth doing pgpool?


On Sat, 15 Jan 2005 11:12:04 +0900 (JST), Tatsuo Ishii
<t-ishii@sra.co.jp> wrote:
> > John Cunningham wrote:
> > > concerned that if I drop the number of connections to less than the
> > > number of databases I have, that pgpool would open the limit of
> > > connections, hold them open and not allow any connections to the
> > > remaining databases.  Is this a concern?  If I set up pgpool will I
> > > have to have the same number of connections as I have databases?
> >
> > That depends on how you configure pgpool.  pgpool is not aware of the
> > connection limit count in the PostgreSQL server, so it will happily
> > open connections until there are no more slots available.
> >
> > pgpool will require max_pool * num_init_children connection slots.
> >
> > max_pool should be the number of database/user combinations you use
> > (300 in your case, assuming only one database user account),
>
> Not really. If a user connects to pgpool and all onnection slots are
> already full, then pgpool will release the oldest connection slot and
> reuse it for the new connection. So even if there are 300
> database/user combinations, it's ok to set max_pool as low as, for
> example, 4. Of course this will have unwanted side effect in that
> connection caches are not very well kept, though.
> --
> Tatsuo Ishii
>
> >  and
> > num_init_children should be on the order of how many concurrent
> > connections you expect to each combination ("several" in your case).
> > So you should have at least 300 * several PostgreSQL connection slots,
> > which is probably more than the 1000 or so that is the default.
> >
> > --
> > Peter Eisentraut
> > http://developer.postgresql.org/~petere/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>

> So - would it then be worth doing pgpool?

You could limit the number of connections to PostgreSQL.
--
Tatsuo Ishii

> On Sat, 15 Jan 2005 11:12:04 +0900 (JST), Tatsuo Ishii
> <t-ishii@sra.co.jp> wrote:
> > > John Cunningham wrote:
> > > > concerned that if I drop the number of connections to less than the
> > > > number of databases I have, that pgpool would open the limit of
> > > > connections, hold them open and not allow any connections to the
> > > > remaining databases.  Is this a concern?  If I set up pgpool will I
> > > > have to have the same number of connections as I have databases?
> > >
> > > That depends on how you configure pgpool.  pgpool is not aware of the
> > > connection limit count in the PostgreSQL server, so it will happily
> > > open connections until there are no more slots available.
> > >
> > > pgpool will require max_pool * num_init_children connection slots.
> > >
> > > max_pool should be the number of database/user combinations you use
> > > (300 in your case, assuming only one database user account),
> >
> > Not really. If a user connects to pgpool and all onnection slots are
> > already full, then pgpool will release the oldest connection slot and
> > reuse it for the new connection. So even if there are 300
> > database/user combinations, it's ok to set max_pool as low as, for
> > example, 4. Of course this will have unwanted side effect in that
> > connection caches are not very well kept, though.
> > --
> > Tatsuo Ishii
> >
> > >  and
> > > num_init_children should be on the order of how many concurrent
> > > connections you expect to each combination ("several" in your case).
> > > So you should have at least 300 * several PostgreSQL connection slots,
> > > which is probably more than the 1000 or so that is the default.
> > >
> > > --
> > > Peter Eisentraut
> > > http://developer.postgresql.org/~petere/
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>