In this article I discuss dependence of procedure cache on SAP ASE parameters max online engines and engine local cache percent. It is a lesson learned from a situation where inappropriate configuration lead to really bad performance due to spinlock contention in the ASE server.
It is assumed you do have a basic working knowledege of SAP ASE as a DBA.
Despite its name SAP ASE's procedure cache is not only used for procedure execution but it is used as a general purpose memory pool used for all kind of memory requirements in SAP ASE. Proper sizing and configuration of the procedure cache is important for performance of the SAP ASE server.
ASE's procedure cache is divided into a so called engine local (ELC) and a global part. The ELC part is used only by a particular engine and thus does not require expensive memory lock operations to serialise access to it. This saves CPU cycles.
The global part of the procedure cache is used by all engines and thus requires to be accessed under spinlock protection.
The amount of ELC vs global cache is determined at startup of the SAP ASE server by the parameters.
- engine local cache percent , default is 50 percent
- max online engines
- procedure cache size
At startup each engine gets a chunk of
ELC cache per engine =
(((procedure cache size)*(engine local cache percent))/(max online engines))
This is fixed at startup of the ASE server. If 'max online engines' is large and 'procedure cache size' is comparatively small, then each ELC will be tiny. That will lead to excessive use of the global part of procedure cache and may lead to spinlock contention issues on the
rproccache_spin spinlock..
Another effect of having set
max online engines too high is waste of memory. If
max online engines is set to a high value but you actually configure much fewer engines, then this leads to unused memory in the procedure cache. ASE puts aside ELC memory for each engine that might be brought online (as specified by parameter
max online engines). This memory is reserved whether or not it is used.
To avoid this pitfall:
- don't set 'max online engines' much higher than what you plan to use.
- ensure procedure cache is large enough at ASE startup
- review the setting of 'engine local cache percent'
The right value for the size of ELC per engine depends a bit on system load - but I suggest to start with a value in the range of 100MB - 400MB per engine. On larger systems, when memory is given, configure it to a higher value. Make sure though, that you do not starve the global part of procedure cache by setting engine local cache percent to a very high value. I don't recommend to set it higher than 70 percent.
Example:
Configuration parameter
max online engines is set to 48. Actual number of threads in thread pool syb_default_pool is 16. Configuration parameter
engine local cache percent is set to the default, 50 percent. Configuration parameter
procedure cache size is set to 10485760 (2k memory pages) i.e. 20480MB.
ASE puts aside 20480M * 0.5 = 10240M for engine local cache. ELC per engine is 10240M / 48 , roughly 213M, which is a good starting point. However, some 6827M of the procedure cache is reserved for ELC but unused. In that case we would reduce the value of 'max online engines'. If 'max online engines' is reduced to 24, then ELC per engine is roughly 427M. The configuration still allows increasing the number of engines 1.5 fold in case of extraordinary demand for CPU resources. Reserved but unused memory in procedure cache would be reduced to 3413M. Global part of procedure cache remains at 50% (10240MB). If memory was at a premium in the system one could even reduce the value of engine local cache percent which reduces unused memory in procedure cache.
Side topic
Configuration parameter
enable large chunk elc enables engines to allocate memory in larger chunks in the ELC.
It reduces overhead of memory allocation and deallocation in the ELC to some degree.
I recommend to set it to 1 (which is the default).
SAP ASE parameter
enable large chunk elc replaces ASE trace flag 758 which was used in 15.7 to enable the feature.
ASE parameter
large allocation auto tune determines whether ASE uses a feature that attempts to automatically fine tune memory allocations in the procedure cache. It is set to 1 (enabled) by default. It replaces traceflag 753 which was used in ASE 15.7 to turn off the feature.
I recommend to set it to 1 (which is the default). However, the feature may result in a higher demand for memory in the the global part of the procedure cache. If you find
rproccache_spin to be hot despite a properly sized procedure cache and proper distribution of procedure cache memory between engine local and global pool, consider disabling the feature. Also, consider disabling the feature if the system was upgraded from ASE 15.7 and you did set trace flag 753 while system was on 15.7.
Conclusion
It is a ok to set SAP ASE parameter
max online engines a bit higher than what the number of engines is during normal operations. It gives you some headroom in case extraordinary load requires more CPU resources for ASE. However, you should not set it to too high due to the detrimental effects it has on the size of ELC per engine.
