Large App Refresh Crashing Qlik

[wlabarca99]

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!

[pablolabbe]

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

[wlabarca99]

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

[Levi_Turner]

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.