Saturday, January 3, 2009

Fine Tuning Hyperion Essbase Cache Settings

Fine Tuning Cache Settings

After using a database at your site with typical data, user access, and standard environment (including server machines, network, etc.), check to see how Essbase performs. It is difficult to predict optimal cache sizes without testing. You may need to adjust your cache settings.
Understanding Cache Settings

The sizes of the index cache and the data file cache (when direct I/O is used) are the most critical Essbase cache settings. In general, the larger these caches, the less swapping activity occurs; however, it does not always help performance to set cache sizes larger and larger. Read this entire section to understand cache size considerations.
Index Cache

The advantages of a large index cache start to level off after a certain point. Whenever the index cache size equals or exceeds the index size (including all index files on all volumes), performance does not improve. However, to account for future growth of the index, you can set the index cache size larger than the current index size. Because the index cache is filled with index pages, for optimum use of storage, set the size of the index cache to be a multiple of the size of the index page (8 KB). See Index Files for an example of estimating index size.
Data File Cache

If possible, set the data file cache to equal the size of the stored data, which is the combined size of all ess*.pag files. Otherwise, the data file cache should be as large as possible. If you want to account for future growth of stored data, you can set the data file cache size larger than the current size of stored data.
Note:

The data file cache is used only if you are using direct I/O.
Data Cache

The data cache should be about 0.125 times the data file cache. However, certain calculations require a larger data cache size. If many concurrent users are accessing different data blocks, this cache should be larger.

In general, if you have to choose between allocating memory to the data file cache or allocating it to the data cache, choose the data file cache if you are using direct I/O. If you are upgrading from a previous version of Essbase, see the Hyperion Essbase - System 9 Installation Guide.
Checking Cache Hit Ratios

Every cache has a “hit ratio.” The hit ratio indicates the percentage of time that a requested piece of information is available in the cache. You can check the hit ratio of the index cache, the data cache, and the data file cache to determine whether you need to increase the cache size.

* To check cache hit ratios, see “Checking Cache Hit Ratios” in Essbase Administration Services Online Help.

*

The cache hit ratio indicates the percentage of time that a requested piece of information is already in the cache. A higher hit ratio indicates that the data is in the cache more often. This improves performance because the requested data does not have to be retrieved from disk for the next process. A hit ratio of 1.0 indicates that every time data is requested, it is found in the cache. This is the maximum performance possible from a cache setting.
*

The Hit Ratio on Index Cache setting indicates the Essbase Kernel success rate in locating index information in the index cache without having to retrieve another index page from disk.
*

The Hit Ratio on Data File Cache setting indicates the Essbase Kernel success rate in locating data file pages in the data file cache without having to retrieve the data file from disk.
*

The Hit Ratio on Data Cache setting indicates the Essbase success rate in locating data blocks in the data cache without having to retrieve the block from the data file cache.
*

Check memory allocation. Add smaller amounts of memory at a time , if needed, because a smaller increment may have the same benefit as a large one. Large, incremental allocations of memory usually result in very little gain in the hit ratio.

3 comments:

amarnath said...

Good Post.I appreciate you taking time to post this information.
It's mostly theoritical (no offense).
I could add few inputs to your post.
It's not always required to have a high Cache all the time. An optimal setting of cache would help. Now how do I can set my cache to optimal without running out of memory?
Tips for setting Index Cache
For your database your Index cache might be 1GB, so does that means you need to set your Index cache to 1GB.
I say "NO". It all depends on your Hit ratio.If you can get a Hit Ratio of around 99% for 150MB of your Index Cache. That is god enough.

Tips for setting my Data cache
Typically I have my database size around 350GB. So how much should be my Data Cache.
2 ways.
1) 0.125 of your total data cache.
2) with the above setting test and check your Hit Ratio. If you are able to achieve around 25%-35%, then your data cache is good enough to perform calculations in a very less time.

Now the bigger Question.
Is my Outline Order important?
Ofcourse it is. Your optimization and tuning should start from here.
I guess you know the order of dimensions.

Hope this information helps.
If you need any more information related to Essbase feel free to conatact me at amarnath.kothapalli@gmail.com

Have fun with essbase

Anuj Tripathi said...

Thats the nice stuff of information!.....

Thanks Chinmay for your efforts on putting this piece in here

Anuj Tripathi said...

Thanks to Amar too :-) ..for adding more to it.