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.he...@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: > /* "productCache".IGNITEPRODUCT_MANUFACTURERCODE_IDX */ > 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
