Re: Any better plan for this query?..

On Mon, May 11, 2009 at 11:18 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
> 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.

why aren't you preparing the query? mysql uses simple rule based
planner and postgresql has a statistics based planner. Our planner
has all kinds of advantages in various scenarios, but this is
compensated by slightly longer planning time in some cases. OTOH, you
have prepared queries to compensate this. (mysql also has prepared
queries, but the syntax is awkward and there is much less benefit to
using them).

merlin

merlin