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: SQL query is slow
Date Tue, 29 Aug 2017 15:05:53 GMT
It is possible returned dataset is too large and cause high network
pressure that results in large query execution time.

There is no recommendation for grid nodes count.
Simple SQL queries can work slower on large grid as most of time is spent
in inter-node communication.
Heavy SQL queries may show better results on larger grid as every node will
have smaller dataset.

You can try to look at page memory statistics [1] to get estimate numbers.

Really, there is an issue with large OFFSET as Ignite can't just skip
entries and have to fetch all of them from nodes.
OFFSET makes no sense without ORDER as Ignite fetch rows from other nodes
in async way and row order should be preserved between such queries.
OFFSET applies on query initiator node (reduce side) after results merged
as there is no way to understand on map side what rows should be skiped.

Looks like underlying H2 tries to use index scan, but I don't think index
can help in case of functional condition.
You can try to make Ignite to have inline values in index or use separate
field with smaller type that can be inlined. By default, index inlining is
enabled for 10 byte length values.
See IGNITE_MAX_INDEX_PAYLOAD_SIZE_DEFAULT system property docs and [2].

[1] https://apacheignite.readme.io/v2.1/docs/memory-metrics
[2] https://issues.apache.org/jira/browse/IGNITE-6060

On Tue, Aug 29, 2017 at 3:59 PM, mhetea <mihaela.hetea@gmail.com> wrote:

> Thank you for your response.
> I used query parallelizm and the time reduced to ~2.3s, which is still too
> much.
> Regarding 1. is there any documentation about configuration parameters
> (recommended number of nodes, how much data should be stored on each node).
> We currently have 2 nodes with 32GB RAM each. Every 1 million records from
> our cache occupy about 1GB (is there a way to see how much memory a cache
> actually occupies? we look now at the Allocated next memory segment log
> info)
> For 3. it seems that the index is hit  from the execution plan:
> No?
> We have this issue also when we use a large OFFSET (we execute this kind of
> query because we want paginated results)
> Also, this cache will be updated frequently so we expect it to grow in
> size.
> Thank you!
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/SQL-query-is-slow-tp16475p16487.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Best regards,
Andrey V. Mashenkov

View raw message