Обсуждение: BUG #5995: connection pooling not working

The following bug has been logged online:

Bug reference:      5995
Logged by:          Suprabhat
Email address:      suprabhatm@gmail.com
PostgreSQL version: 8.4
Operating system:   ubuntu
Description:        connection pooling not working
Details:

Hi, we are using java jsp front end to develop a web based application which
runs fine using odbc:jdbc link for mssql. The moment we switched over to
using postgresql, it kept on telling FATAL: too many connections already and
there are too many postgres processes on the server. We have used connection
pooling methods after unsuccessfully using pgpool2. I am really wondering
how the big companies are using this database without any issues. Probably
we are doing some basics wrong.

Total number of connections we are trying to make is something around 300
concurrently.

Please suggest what should be do. This is very very urgent.

Regards,

Suprabhat

"Suprabhat" <suprabhatm@gmail.com> wrote:

> FATAL: too many connections already and there are too many
> postgres processes on the server. We have used connection
> pooling methods after unsuccessfully using pgpool2.

Is there a thread somewhere where you were trying to get help
configuring pgpool?

> Probably we are doing some basics wrong.
>
> Total number of connections we are trying to make is something
> around 300 concurrently.

Well, the main point of a connection pool is to allow multiple
client-side processes or threads to see a large number of logical
connections to the database while those funnel down to a small
number on the database side.  300 active users might do quite well
on a connection pool which maintains 10 or 20 connections to the
database.  It sounds like you haven't used the correct settings to
achieve that.

-Kevin

First, this isn't a bug... this is more of a discussion for -general or
-admin.


On Wed, Apr 27, 2011 at 9:44 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:

> "Suprabhat" <suprabhatm@gmail.com> wrote:
>
> > FATAL: too many connections already and there are too many
> > postgres processes on the server. We have used connection
> > pooling methods after unsuccessfully using pgpool2.
>
> Is there a thread somewhere where you were trying to get help
> configuring pgpool?
>

Agreed, pgpool is quite sensitive to being configured properly.
If you're using the pooler and you are still getting that error, it is
definitely a configuration issue.


>
> > Probably we are doing some basics wrong.
> >
> > Total number of connections we are trying to make is something
> > around 300 concurrently.
>
> Well, the main point of a connection pool is to allow multiple
> client-side processes or threads to see a large number of logical
> connections to the database while those funnel down to a small
> number on the database side.  300 active users might do quite well
> on a connection pool which maintains 10 or 20 connections to the
> database.  It sounds like you haven't used the correct settings to
> achieve that.
>
>
In all honesty, if you're just looking for a connection pool, I personally
prefer pgbouncer.  That being said, pgpool provides a lot of other features
(load-balancing, replication,etc...).

-Kevin
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Please keep the list copied.  I'm moving this to the pgsql-admin
list (with a blind copy to -bugs), since there is really no hint of
a PostgreSQL bug here, except possibly in terms of how we document
this issue.


Suprabhat Mohapatra <suprabhatm@gmail.com> wrote:

> Should I send you the sample code that we are using.

That's unlikely to help much.

You either need to really boost max_connections or (much better)
properly configure some connection pooler.  There are many options
for the latter.  I'm told that Tomcat comes with a good connection
pooler built in.  If you're in an environment where the software
comes with a connection pooler, that is often the best way to go.
Otherwise, pgbouncer and pgpool both have their fans.  I've looked
over the stand-alone JDBC pooler that Apache has, and it looked
pretty good, too.

The key is read the documentation carefully, and understand that the
pooler can accept 300 client connections to it (or whatever number
you need) and service those with a much smaller set of database
connections.  If you're configuring the pooler to use as many
database connections as you have clients, you're missing the point.

> I am on a production system and we have reached a stage where we
> may lose the business if this is not done in this week.

You might want to contact one of the many organizations which
provide contract support for PostgreSQL.  I'm pretty sure most or
all of them will have someone with experience configuring a good
connection pooler.

http://www.postgresql.org/support/professional_support

-Kevin