We are having a large app in our Qlik Sense environment (around 95-96 gb app size) which contains billions of records. While opening app, the visualizations are loading too slow. There is nothing to optimize as such in the app as it fetched records directly from database sql query without any group by or major data modelling.
Also the frontend does not contain any major set analysis expression, just simple sum() expressions.
I tried using preload functionality but the issue I am facing here, is after preload, when I try to open the app, the engine service fails and I am presented with "Engine not found" error. Even after disabling the preload and trying to manually open the app for cache warming, the memory and cpu usage spikes to 100% and the engine service fails.
Server configurations he have:
Nodes : 2 engine nodes which are used by users to open the Apps (Note : when a user opens an app, he is redirected to one of the engine nodes) . 2 Scheduler nodes which manages reload
Ram : 768 GB on each of the 4 nodes
CPU : 12 core (can provide additional details if required)
Any suggestions/ways to atleast make the UI faster and responsive apart from scaling the infra ??
Could mashups be of any help here ? Any suggestions regarding the same are welcome !!
I doubt that mashups would be helpful because the bottleneck seems to be the available resources and/or the environment-configurations and/or the applied UI.
I suggest to monitor the resource-consumption closely. Various log-files from Qlik + OS + VM could provide deep insights. A simpler starting point could be just to look on the task-manager/resource-manager with a clock + paper/pencil to get a few rough information how many RAM is really used, how fast is the RAM filled, how much CPU is used (in total as well as for the single cores - respectively are there longer times when only a single core is working), ...
Beside this repeat the UI check with a single table-chart - but now taking only one or twice native fields from the facts and a measure like count(AnyFactField). No further stuff unless a few list-boxes. If it responses now significantly faster as with the previous table it would hint to an appropriate optimizing.
Hi @Aditya_Chitale I was thinking about optimizing the app -- data model and expressions. In some cases with this many rows it pays to look into optimizing the hardware as well as architecture options such as ODAG.
For third party tools, my QSDA Pro tool can identify some bottlenecks and optimization recommendations. Start with taking QSDA Pro's recommendation for removing unused data and then look at any performance flags.
Following Rob's and Marcus's comments, I'd say that an application of this size absolutely needs to be designed and built with performance in mind. Unlike with smaller datasets, here performance cannot be an after-thought. The data model has to be built specifically for optimized performance, and the sheet objects and expressions need to be built and tailored for the best performance. Some of the heaviest features may need to be avoided, because they simply cannot fly with such a large data set.
My Performance Tuning session at the Masters Summit for Qlik is an all-time favorite among our students. There, I describe the best practices and the most common performance pitfalls in data models and in layout objects.
I provide performance tuning as a service to many customers with similar issues. It's a very small and extremely cost-effective exercise that includes a detailed analysis of the app, the data model, and the UI objects, with specific recommendations for improvements. Send me a direct message if you'd like to discuss it further.
