On Thu, Jan 15, 2009 at 1:26 PM, Kirk Strauser <kirk@strauser.com> wrote:
> On Jan 15, 2009, at 12:30 PM, Steve Crawford wrote:
>
>> But if your application is designed to work well with pooling, it can
>> provide dramatic performance benefits.
>
> I think that's the problem. As I mentioned at one point, a lot of our
> applications have connections open for hours at a time and fire off queries
> when the user does something. I'm coming to think that pooling wouldn't
> give much benefit to long-living processes like that.
As mentioned, this is exactly what pooling is good for. Imagine 200
users each accessing the database once every hour. If on average you
have two or three users actually hitting the database, then you could
handle that many users with only 10 or 20 database connections. The
apps each have a persistent connection up to the pool service, which
then routes the active sessions through a connection pool so the db
only thinks it has 20 or fewer users.
> On a related note, is max_connections=400 reasonably sized for a server with
> 8GB of RAM? Again, most of these are dormant at any given time. The
> database itself is currently hosted on a dual Xeon server with 3GB of RAM
> and other applications so I'm sure the new 8-core/8GB hardware is bound to
> do better at any rate.
That depends very much on how many of those are ever active, or could
become active. When things get ugly is when everyone at the company
needs to log in at once. Suddenly, the 390 connections out of the 400
that were idle go active, and the server grinds to a crawl under the
load. A connection pool would simple hold the connection in a wait
state until one of the 20 or so live connections to the db became
available, preventing server meltdown.