I am not sure of your use case, but this may help you or someone else so thought I’d share.
Assuming your sql is tuned and you have the correct/appropriate indexes, if your issue is that too many objects get loaded into memory and crash the jvm, then attached is a file that depicts a “chunking” method I was forced into to get around the fact that OLAP queries crashing the grid. Select count(*) queries by date range, or some other chunking criteria on the driving table, until you get a count that you feel is safe. Count(*) queries don’t seem to cause OOMs as I suspect GridGain has some optimization to just increment a counter and discard each record such that you don’t get OOM. In my case, where as I wanted to run one query in the Ignite Web Console and generate 1 file, I was forced to write and run 8 chunked queries and provide 8 files (could have found a way to merge the files I suppose). The idea is to run your count queries to get count of number of objects you feel is safe on your driving table, then use that chunk criteria on your full query so that it does not overwhelm the java heap and cause an OOM. From: Stephen Darlington <stephen.darling...@gridgain.com> Sent: Thursday, May 27, 2021 3:26 AM To: user <user@ignite.apache.org> Subject: Re: Ignite nodes crash after firing heavy SQL query CAUTION: This email originated outside 24 Hour Fitness. Do not click links or open attachments unless you recognize the sender and know the content is safe. Those options are not available in Ignite. There are solutions built on top of Ignite that support SQL quotas. On 27 May 2021, at 06:17, Kamlesh Joshi <kamlesh.jo...@ril.com<mailto:kamlesh.jo...@ril.com>> wrote: Hi Craig, Yes, query is returning many records. But, we can not control who executes which type of command. However, cluster should be stable enough to handle such queries. Somewhere I have seen below few properties for the same, are those not available in Ignite open source? 1. sqlQueryMemoryQuota 2. sqlOffloadingEnabled 3. sqlGlobalMemoryQuota Thanks and Regards, Kamlesh Joshi From: Craig Gresbrink <cgresbr...@24hourfit.com<mailto:cgresbr...@24hourfit.com>> Sent: 26 May 2021 21:17 To: user@ignite.apache.org<mailto:user@ignite.apache.org> Subject: [External]RE: Ignite nodes crash after firing heavy SQL query The e-mail below is from an external source. Please do not open attachments or click links from an unknown or suspicious origin. Can you provide the query and the explain plan of the query (via Ignite Web Console)? First step I would suggest is to seek to tune the query, maybe it needs an index, or the driving table needs to be changed, or maybe the query uses indexes correctly but many rows are returned from the driving table. From: Kamlesh Joshi <kamlesh.jo...@ril.com<mailto:kamlesh.jo...@ril.com>> Sent: Wednesday, May 26, 2021 5:54 AM To: user@ignite.apache.org<mailto:user@ignite.apache.org> Subject: Ignite nodes crash after firing heavy SQL query CAUTION: This email originated outside 24 Hour Fitness. Do not click links or open attachments unless you recognize the sender and know the content is safe. Hi Igniters, We want to use Ignite (v2.7.6) mostly via SQL for operational activities (adding extra indexes to enable querying). While doing POC, observed that if any heavy query is fired, few nodes from the clusters are going down due to ‘OOM Error’. 1. Is there any way available to kill such queries ? 2. Any way to set SQL query timeout at cluster level? 3. Any parameters that we should be tweaking to avoid this? Thanks and Regards, Kamlesh Joshi
ZMCP-2020-04-07-CCPC-93-ExtendAnyActivePtSubscriptionsBy75Days-270521-1214-62.pdf
Description: ZMCP-2020-04-07-CCPC-93-ExtendAnyActivePtSubscriptionsBy75Days-270521-1214-62.pdf
