Re: Any better plan for this query?..

The idea is good, but *only* pooling will be not enough. I mean if all
what pooler is doing is only keeping no more than N backends working -
it'll be not enough. You never know what exactly your query will do -
if you choose your N value to be sure to not overload CPU and then
some of your queries start to read from disk - you waste your idle CPU
time because it was still possible to run other queries requiring CPU
time rather I/O, etc...

I wrote some ideas about an "ideal" solution here (just omit the word
"mysql" - as it's a theory it's valable for any db engine):
http://dimitrik.free.fr/db_STRESS_MySQL_540_and_others_Apr2009.html#note_5442

Rgds,
-Dimitri

On 5/13/09, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Glenn Maynard <glennfmaynard(at)gmail(dot)com> wrote:
>> I'm sorry, but I'm confused. Everyone keeps talking about
>> connection pooling, but Dimitri has said repeatedly that each client
>> makes a single connection and then keeps it open until the end of
>> the test, not that it makes a single connection per SQL query.
>> Connection startup costs shouldn't be an issue. Am I missing
>> something here?
>
> Quite aside from the overhead of spawning new processes, if you have
> more active connections than you have resources for them to go after,
> you just increase context switching and resource contention, both of
> which have some cost, without any offsetting gains. That would tend
> to explain why performance tapers off after a certain point. A
> connection pool which queues requests prevents this degradation.
>
> It would be interesting, with each of the CPU counts, to profile
> PostgreSQL at the peak of each curve to see where the time goes when
> it is operating with an optimal poolsize. Tapering after that point
> is rather uninteresting, and profiles would be less useful beyond that
> point, as the noise from the context switching and resource contention
> would make it harder to spot issues that really matter..
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>