I've been given the unenviable task of support a QlikView environment that I didn't build and with very little background in QlikView.
After only a few weeks the users are complaining of performance problems.
I've had a look at the Perfomance Metrics and when (Memory) Committed Bytes In Use (%) gets over 90% we start to see the Page File being increasing used, which is an obvious problem.
I found this rather good document: DS-Technical-Brief-QlikView-Server-Memory-Management-and-CPU-Utilization-EN.pdf that discusses the Working Set and Paging etc.
Our Working Set Min is 90% and Max 97%
Our QVS Servers are a clusterd pair with 192GB of RAM
These servers are pretty large already so there's a limit to how much more memory I can add, plus I don't want to simply throw hardware at the problem without understanding it.
My question is if I reduce the Working Set Min (and Max) will QVS start managing it's use of memory (clearing cache etc as described in the above pdf) and prevent Paging, or as I fear will the servers simply start Paging at a lower overall Memory Usage?
Just to throw in my two cents. It's important to understand that Qlikview Server will never deliver a good user experience if the QVS task is paging at all. It all has to live in RAM to give good performance. So as you've stated you need to either increase available RAM or decrease the RAM demand of the apps.
Confirm a couple of things --
1. Are there apps other than QV running on this box?
2. Are reloads being done on this box or another server?
To answer your original question. If you reduce the WS Max, you will begin paging sooner. If you reduce the WS Min, you should not begin paging sooner. However, you will begin cache trimming sooner, which may be a good thing but probably won't resolve your overall problem.
If you analyze the QV documents, you may find that one or two are contributing to the bulk of the RAM usage and those would be the best tuning candidates.
Make sure you have done below recommended settings.
This are the recommended settings by QlikTech.
• Hyper threading - Disabled
• NUMA=Disable, Node interleaving=True or SUMA
• Energy Saving settings to be turned OFF
Hyperthreading is CPU. In essence so in Energy Saving so are not relevant as CPU is not a bottleneck.
I'll check NUMA but I'm not sure how it's relevant to my issue either and doesn't answer my question.
May be this wont be your answer, but this i told you as a preliminary check.
Anyways the performance issue may occur due to many a reason, like Improper Data Modeling, improper use of the expression at chart level.
To make sure that everything is ok at chart level you can check the memory statistics of an application.
For this you can open the application -> settings -> Document properties -> General Tab -> Click on Memory Statistics.
Save the file.
Open the new application and use this .mem file for reloading data.
Once you reload data from this file you will see the object id and their avg calculation time.
This will help you to identify if you have any issue in chart level.
As you have read in the PDF, the low working set means that QlikView will use up to that memory without asking to the OS. The high means that it will start swapping, but the Events log in the QlikView logs folder should log that there is some documents using more virtual memory than expected.
How is the pagefile size managed: is it a manual value or is left to the OS? Because it may be conflicting with the 5+ GB that you have left for the OS and the rest of applications in the high limit being at 97%. Note that the working set is a Windows OS feature (http://msdn.microsoft.com/en-us/library/windows/desktop/cc441804(v=vs.85).aspx), and it controls QVS.exe, but not the rest of QlikView services that may be running in the same computer, along with antivirus software, backups, etc.
Check the Windows Event Viewer and the QlikView Events log (use the System Monitor to help you that: http://community.qlik.com/docs/DOC-4307) to see when this happens and when the OS starts paging.
Hope that helps.
You stated "Our Working Set Min is 90% and Max 97%"
That is way to high. You are not leaving mcuh memory for the OS. I would set it to 70 90.
I'm trying to determine why those values were chosen by the build team, but I read in the technical document:
"For servers with large RAM (i.e. 256 GB of RAM) these settings can be changed to allocate a couple of gigabytes of RAM for the operating system and allow the remaining RAM to be used by the QlikView Server."
I'd consider 192GB to be large so does the 70 90 rule still apply? The recomendations I've seen in the documentation all sound a little vague to me.
I have never changed these values. These are the same values that Microsoft recommends for SQL server on all their setup.
My background is in Performance Management not QlikView so I'll take those recomendations back to the team that built the Documents.
However I am confident I've identified the source of the Performance Issues as being due to the high use of the Paging File. What I'm trying to determine is what to do about that - as I see it I have two options.
1) Add more memory
2) Use the existing memory more efficiently.
Before I start digging in to the efficiency of the Documents I think I need to ascertain whether the QlikView settings are optimal.
Thanx for clarifying that. I guess that rather clearing the cache QVS is Paging to disk; when the QCS process reaches 176000MB is when Paging occurs which is 90% of 192GB.
The Paging File is 16384MB, and the peak usage I've seen on this has been 40%. That's equivalent to about 4% of the RAM that QVS using. Is that a significant amount or pretty typical when the Working Set for QVS reaches it's Min value?
I summise that if I reduce the Working Set Min / Max values it won't necessarily prevent Paging from occuring it will just Page sooner (in relation to total Memory Usage).
btw I've had a look in the "Events" log and all it's telling me is the same couple of errors occuring a couple of dozen times a day:
2013-07-15 10:39:19 2013-07-15 18:12:12 2 500 Warning Document Load: The document \\HBEU.ADROOT.HSBC\DFSROOT\GB002\COREP_MI_QLIKVIEW\QLIKVIEW FILES\Fermat\FermatQlikViewApp\Variance Report R7a.qvw failed to load because of no file access .
2013-07-15 10:39:19 2013-07-15 19:48:17 2 500 Warning Session Recovery Failed: The document QlikView Files/Fermat/FermatQlikViewApp/June13 Month End Report R7a.qvw failed to apply $LASTKNOWNSTATE
...nothing about Memory. There's a couple of fields called "Bytes Sent" and "Bytes Received" in the Sessions logs but I'm not sure what to make of them.