ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrey Mashenkov <andrey.mashen...@gmail.com>
Subject Re: Fetched result use too much time
Date Thu, 21 Sep 2017 13:28:40 GMT
Lucky,


1. Looks like it make no sense to set query parallelism level higher number
of available CPU on node.

2. Map query use index for field FASSIGCUID type of String and seems values
are 16 chars length strings (32 bytes)
By default, values with size < 10 bytes can be inlined in index, so Ignite
doesn't need to lookup a data page for value data.
You can try to increase it up to 32 via*
cacheConfiguration.setSqlIndexMaxInlineSize(32) *or JVM property
*-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=32*.

3. Ignite doesn't know whether your data is collocated by FDATABASEDID
(group by clause) or not collocated.
So, Ignite can't apply HAVING condition instantly on map phase and have to
load and merge all groups from all nodes before check for HAVING.
If it possible to collocate data on GROUP BY condition, you can hint Ignite
with setting query flag:   *sqlFieldsQuery.setCollocated(true).*
However, I'm not sure it will help much and H2 will be able to make any
optimization here.

4. Also, you can force Ignite to use different index. E.g. group index on
FDATABASEDID and FASSIGCUID and same fields in different order.

5. Sometimes, Ignite change join order and it can cause unexcpected
slowdown. You can try to change join order by changing tables positions in
query string.
To preserve Ignite join order optimization you may use a flag:
*sqlFieldsQuery.setEnforceJoinOrder(true).*


Hope, this will help you.

On Tue, Sep 19, 2017 at 5:07 AM, Lucky <wanxing987@163.com> wrote:

>
> Please see the attachment.
> I have set query parallelism  to 30. it took 42 seconds.
> But it is not enough.
> I excepted it took less than 3 seconds.
>
> then,I have 3 nodes.
>
> As for the 3589 number, we need to check the number of ID using in
> conditions. Only the number of times used is equal to the record in the in
> condition. That's the record we need. This is the business scenario
> required. I can't change this.
>
> Thanks for your suggestion.
> Lucky
>
>
> 2017年09月18日 21:55,Vladimir Ozerov<vozerov@gridgain.com>
> <vozerov@gridgain.com> :
>
> Hi Lucky,
>
> Could you please share you data model and node/cache configuration? I want
> to make sure that proper indexes are set. I will be able to advise
> something then. As I quick suggestion you may try to increase query
> parallelism on your "databaseDAssignCache". Please try setting it to the
> number of cores on your server nodes. Relevant property -
> CacheConfifuration.queryParallelism. Btw, how many nodes do you have?
>
> Also I am struggling to understand the number "3589". Why this number
> appears both as ">= 3589" condition and as a number of parameters inside
> "IN" clause?
>
> Vladimir.
>
>


-- 
Best regards,
Andrey V. Mashenkov

Mime
View raw message