topic Re: Large App Refresh Crashing Qlik in App Development

Large App Refresh Crashing Qlik

wlabarca99 — Sat, 16 Nov 2024 21:53:40 GMT

All,

I am currently running into an issue in Qlik Sense where the services no longer work and users are getting a 404 error. We are running Qlik Sense on an EC2 instance through AWS (t3.xlarge) and it seems that the cause could be one of our large apps that running into memory limits when trying to refresh.

The app's file size is 210.89 MB and it's connecting to data through Amazon Redshift. The error script reads:

2018-11-14 14:26:03 Error: Connector reply error: SQL##f - SqlState: HY000, ErrorCode: 30, ErrorMsg: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState HY000] out of memory for query result

I am looking for any answers as to why we are encountering the following problems...

1) Why are some Qlik Sense services crashing, resulting in a 404 error?

2) Is there a more efficient way to load an app to avoid the "out of memory for query result" error code?

Thanks!

Re: Large App Refresh Crashing Qlik

pablolabbe — Wed, 14 Nov 2018 16:06:35 GMT

I think you have 2 different issues and I think they are not related.

The first one is the 404 error. I have some questions to help in troubleshooting:

Did you check if services are started in the windows service console ?
Did you checked the Qlik services Log files ?
How much memory have the server ?
How many users do you have accessing the app ?

The second one is the SQL error. It´s related to the data load process and the error is issued by Amazon Redshift, so Redshift is running out of memory, not qlik sense. More on this you can find here: https://forums.aws.amazon.com/thread.jspa?threadID=124525

Re: Large App Refresh Crashing Qlik

wlabarca99 — Wed, 14 Nov 2018 16:30:20 GMT

For the 404 Error:

I usually RDP into the EC2 and Stop/Start the services through two scripts but that only works approx. 30% of the time. Usually I will Stop/Start the EC2 in order to resolve the issue.
The EC2 instance has 15GB of RAM and 50GB of storage
I have not checked the log files
We typically have 5-15 users at any time

Re: Large App Refresh Crashing Qlik

Levi_Turner — Wed, 14 Nov 2018 22:03:03 GMT

I'd strongly encourage monitoring the RAM usage on the server during the refresh to see which processes are using the most memory / whether the server is getting exhausted since 15GB for a server isn't large at all.

Given that it's Redshift, which typically houses larger datasets, these settings on the DSN are relevant:

By default, the data pull will grab the entire result and store it into memory in the QVConnect process which is Qlik's interface with a DSN. Changing this to Single Row Mode will prove out whether this is the cause. Overall you'd expect a longer reload time using the Single Row Mode so once it's confirmed, switching to a Use Declare/Fetch and an appropriate value which doesn't saturate the server is ideal.

Hope that helps.