Re: Any better plan for this query?..

Folks, it's completely crazy, but here is what I found:

- if HISTORY table is analyzed with target 1000 my SELECT response
time is jumping to 3ms, and the max throughput is limited to 6.000 TPS
(it's what happenned to 8.3.7)

-if HISTORY table is analyzed with target 5 - my SELECT response time
is decreasing to 1.2ms (!) and then my max TPS level is ~12.000 !
and CPU is used up to 95% even by 8.3.7 :-) and 8.4 performed better
just because I left its analyze target to default 100 value.

Anyone may explain me why analyze target may have so huge negative
secondary effect?..

Next point: SCALABILITY ISSUE

Now both 8.3.7 and 8.4 have similar performance levels, but 8.3.7 is
always slightly better comparing to 8.4, but well. The problem I have:
- on 8 cores: ~5.000 TPS / 5.500 MAX
- on 16 cores: ~10.000 TPS / 11.000 MAX
- on 32 cores: ~10.500 TPS / 11.500 MAX

What else may limit concurrent SELECTs here?..

Yes, forget, MySQL is reaching 17.500 TPS here.

Rgds,
-Dimitri

On 5/7/09, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Thu, 2009-05-07 at 20:36 +0200, Dimitri wrote:
>
>> I've simply restarted a full test with hashjoin OFF. Until 32
>> concurrent users things are going well. Then since 32 users response
>> time is jumping to 20ms, with 64 users it's higher again, and with 256
>> users reaching 700ms, so TPS is dropping from 5.000 to ~200..
>>
>> With hashjoin ON it's not happening, and I'm reaching at least 11.000
>> TPS on fully busy 32 cores.
>
> Much better to stick to the defaults.
>
> Sounds like a problem worth investigating further, but not pro bono.
>
>> About scalability issue - there is one on 8.3.7, because on 32 cores
>> with such kind of load it's using only 50% CPU and not outpassing
>> 6.000 TPS, while 8.4 uses 90% CPU and reaching 11.000 TPS..
>
> Yeh, small changes make a big difference. Thanks for the info.
>
> How does MySQL perform?
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>