Re: Need help in performance tuning.

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.