Skip site navigation (1) Skip section navigation (2)

Re: Pooling in Core WAS: Need help in performance tuning.

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Greg Smith <greg(at)2ndquadrant(dot)com>, Matthew Wakeling <matthew(at)flymine(dot)org>, Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>, pgsql-performance(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Pooling in Core WAS: Need help in performance tuning.
Date: 2010-07-10 01:25:15
Message-ID: 4C37CBFB.1000800@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
> It *is* the last place you want to put it, but putting it there can
> be much better than not putting it *anywhere*, which is what we've
> often seen.

Well, what you proposed is an admission control mechanism, which is
*different* from a connection pool, although the two overlap.  A
connection pool solves 4 problems when it's working:

a) limiting the number of database server processes
b) limiting the number of active concurrent queries
c) reducing response times for allocating a new connection
d) allowing management of connection routes to the database
(redirection, failover, etc.)

What you were proposing is only (b).  While (b) alone is better than
nothing, it only solves some kinds of problems.  Database backend
processes are *not* free, and in general when I see users with "too many
connections" failures they are not because of too many concurrent
queries, but rather because of too many idle connections (I've seen up
to 1800 on a server).  Simply adding (b) for crappy applications would
make the problem worse, not better, because of the large number of
pending queries which the developer would fail to deal with, or monitor.

So while adding (b) to core alone would be very useful for some users,
ironically it's generally for the more advanced users which are not the
ones we're trying to help on this thread.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

In response to

Responses

pgsql-performance by date

Next:From: Samuel GendlerDate: 2010-07-10 03:29:59
Subject: Re: Pooling in Core WAS: Need help in performance tuning.
Previous:From: Mark KirkwoodDate: 2010-07-10 01:01:44
Subject: Re: Need help in performance tuning.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group