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?
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.