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

Re: Need help in performance tuning.

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: 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: Need help in performance tuning.
Date: 2010-07-09 17:03:51
Message-ID: alpine.DEB.2.00.1007091753350.2534@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 9 Jul 2010, Kevin Grittner wrote:
>> Interesting idea. As far as I can see, you are suggesting solving
>> the too many connections problem by allowing lots of connections,
>> but only allowing a certain number to do anything at a time?
>
> Right.

I think in some situations, this arrangement would be an advantage. 
However, I do not think it will suit the majority of situations, and could 
reduce the performance when the user doesn't need the functionality, 
either because they have a pool already, or they don't have many 
connections.

No, I don't have any numbers.

>> 1. Pool can be on a separate machine or machines, spreading load.
>
> Sure, but how would you do that with a built-in implementation?

That's my point exactly. If you have an external pool solution, you can 
put it somewhere else - maybe on multiple somewhere elses.

>> 3. A large amount of the overhead is sometimes connection setup,
>>    which this would not solve. A pool has cheap setup.
>
> This would probably be most useful where the client held a
> connection for a long time, not for the "login for each database
> transaction" approach.  I'm curious how often you think application
> software uses that approach.

What you say is true. I don't know how often that is, but it seems to be 
those times that people come crying to the mailing list.

>> 4. This could cause Postgres backends to be holding onto large
>>    amounts of memory while being prevented from doing anything,
>>    which is a bad use of resources.
>
> Isn't this point 2 again?

Kind of. Yes. Point 2 was simple overhead. This point was that the backend 
may have done a load of query-related allocation, and then been stopped.

>> 7. That lock would have a metric *($!-load of contention.
>
> Here I doubt you.  It would be held for such short periods that I
> suspect that collisions would be relatively infrequent compared to
> some of the other locks we use.  As noted in the email, it may
> actually normally be an "increment and test" within an existing
> locked block.

Fair enough. It may be much less of a problem than I had previously 
thought.

Matthew

-- 
 Change is inevitable, except from vending machines.

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-07-09 17:52:18
Subject: Re: Need help in performance tuning.
Previous:From: Jorge MonteroDate: 2010-07-09 16:44:12
Subject: Re: Need help in performance tuning.

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