Re: Any better plan for this query?..

On MySQL there is no changes if I set the number of sessions in the
config file to 400 or to 2000 - for 2000 it'll just allocate more
memory.

After latest fix with default_statistics_target=5, version 8.3.7 is
running as fast as 8.4, even 8.4 is little little bit slower.

I understand your position with a pooler, but I also want you think
about idea that 128 cores system will become a commodity server very
soon, and to use these cores on their full power you'll need a
database engine capable to run 256 users without pooler, because a
pooler will not help you here anymore..

Rgds,
-Dimitri

On 5/12/09, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, May 12, 2009 at 11:22 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
>> Robert, what I'm testing now is 256 users max. The workload is growing
>> progressively from 1, 2, 4, 8 ... to 256 users. Of course the Max
>> throughput is reached on the number of users equal to 2 * number of
>> cores, but what's important for me here - database should continue to
>> keep the workload! - response time regressing, but the troughput
>> should remain near the same.
>>
>> So, do I really need a pooler to keep 256 users working?? - I don't
>> think so, but please, correct me.
>
> Not an expert on this, but there has been a lot of discussion of the
> importance of connection pooling in this space. Is MySQL still faster
> if you lower max_connections to a value that is closer to the number
> of users, like 400 rather than 2000?
>
>> BTW, I did not look to put PostgreSQL in bad conditions - the test is
>> the test, and as I said 2 years ago PostgreSQL outperformed MySQL on
>> the same test case, and there was nothing done within MySQL code to
>> improve it explicitly for db_STRESS.. And I'm staying pretty honest
>> when I'm testing something.
>
> Yeah but it's not really clear what that something is. I believe you
> said upthread that PG 8.4 beta 1 is faster than PG 8.3.7, but PG 8.4
> beta 1 is slower than MySQL 5.4 whereas PG 8.3.7 was faster than some
> older version of MySQL. So PG got faster and MySQL got faster, but
> they sped things up more than we did. If our performance were getting
> WORSE, I'd be worried about that, but the fact that they were able to
> make more improvement on this particular case than we were doesn't
> excite me very much. Sure, I'd love it if PG were even faster than it
> is, and if you have a suggested patch please send it in... or if you
> want to profile it and send the results that would be great too. But
> I guess my point is that the case of a very large number of
> simultaneous users with pauses-for-thought between queries has already
> been looked at in the very recent past in a way that's very similar to
> what you are doing (and by someone who works at the same company you
> do, no less!) so I'm not quite sure why we're rehashing the issue.
>
> ...Robert
>