Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
This chapter describes the memory caches that Analytic Services uses and provides recommendations for cache-related settings.
This chapter includes the following sections:
The caches described in this chapter are not relevant to aggregate storage databases. For information about the aggregate storage cache, see Managing the Aggregate Storage Cache.
Analytic Services uses five memory caches to coordinate memory usage:
Analytic Services provides default size settings for each cache. You can adjust the size of any of these five caches as needed for each database. Appropriate cache size is affected by many factors, including database size, block size, index size, and available memory on the server. Cache size settings can effect database and general server performance significantly.
Use these topics for information and instructions about sizing caches for performance:
Before setting cache sizes, you need to enable cache memory locking or leave cache memory locking disabled (the default).
The setting for cache memory locking controls whether the memory used for the index cache, data file cache, and data cache is locked into physical memory, giving the Analytic Services kernel priority use of system RAM.
To use cache memory locking, you must be using direct I/O (buffered I/O is the default I/O access mode), and direct I/O requires a larger index cache size than buffered I/O. For more information, see "Migrating and Upgrading Databases" in the Essbase Analytic Services Installation Guide, and Managing Database Settings.
Locking improves performance for an Analytic Services database because the system memory manager does not need to swap the memory used by the caches when swapping the memory used by Analytic Server. By default, cache memory locking is turned off.
Enabling cache memory locking gives the Analytic Services Kernel priority use of system RAM. If you enable cache memory locking, leave at least one-third of the system RAM available for non-Analytic Services Kernel use. If you do not want to give the Analytic Services Kernel priority usage of system RAM, do not enable cache memory locking.
If you are running Analytic Services on Solaris, run the Bourne shell script, root.sh. before starting Analytic Services and enabling cache memory locking. This script sets the server to run in Superuser mode so that it can lock memory. For information about running the root.sh
script, see the Essbase Analytic Services Installation Guide.
To enable cache memory locking, use either of the following methods:
Tool |
Topic |
Location |
---|---|---|
The settings that you should use for each of the caches that you can configure depend on data distribution and the dense/sparse configuration of the database.
If memory resources are restricted, you can optimize performance by adjusting the cache settings relative to the memory available on the machine which contains your database.
The needs for each site and even for a particular database can vary. Depending on the complexity and type of each operation, Analytic Services allocates as much memory for the data file cache and the data cache as needed. Use the recommended values in this section to estimate enough memory for optimal performance.
If you are using Analytic Services for the first time, cache sizes are automatically set to the default values discussed in the following sections. If you are migrating from Analytic Services Release 5.x, the data file cache is set to the default value and the other cache settings from that version are retained when you migrate. See the Essbase Analytic Services Installation Guide for migration information.
Note: Changes made to cache sizes take effect the next time you start the database.
Use these topics to find and understand recommendations for each cache size:
Note: The size of index pages is fixed at 8 K. This is to reduce input-output overhead, as well as to simplify database migration.
The index is stored in index files on disk. When a database is active, the most recently accessed index pages are held in the index cache, which is a memory area that is allocated for index pages. How much of the index can be held in memory at one time depends upon the amount of memory you allocate to the index cache.
When a data block is requested, Analytic Services looks at the index pages in the index cache to find the block location on disk. If the block location is not found in index pages in the index cache, the index page containing the block location is pulled into the index cache from disk. If the index cache is full, the least recently used index page in the cache is dropped to make room for the index page containing the location of the data block.
The effectiveness of the index cache size depends on the nature of the calculation you are performing. For example, if you were reloading and recalculating an entire database (such as a database that is refreshed each month), a high index cache size is not helpful because Analytic Services is creating new blocks rather than searching the index cache for existing blocks during calculation.
Table 72 shows default and recommended settings for the index cache.
Minimum Value |
Default Value |
Recommended Value |
---|---|---|
Combined size of all |
For information about changing the I/O access mode for a database, or about changing the default for all newly created databases, see Understanding Buffered I/O and Direct I/O.
In general, if you are using direct I/O, make the index cache as large as system resources allow, up to 2 GB. If you are using buffered I/O, make the index cache as small as possible.
For information and instructions on testing and fine tuning cache settings, see Fine Tuning Cache Settings.
To set the size of the index cache, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
The data file cache holds data files (.pag
files) in memory, if you are using direct I/O. If you are not using direct I/O, the data file cache is not used. How much of the data within data files can fit into memory at one time depends on the amount of memory you allocate to the data file cache.
In general, if you have to choose whether to allocate memory to the data cache or to the data file cache, choose the data file cache if you are using direct I/O.
Table 73 shows default and recommended settings for the data file cache.
In general, if you are using direct I/O, make the data file cache as large as system resources allow, up to 2 GB. If you are using buffered I/O, the data file cache is not used.
For information and instructions on testing and fine tuning cache settings, see Fine Tuning Cache Settings.
To set the size of the data file cache, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
Data blocks reside on physical disk and in memory. The data cache is the memory area that is allocated to hold uncompressed data blocks. The number of blocks that can be held in the data cache at one time depends on the amount of memory you allocate to the data cache.
When a block is requested, Analytic Services searches the data cache for the block. If Analytic Services finds the block in the cache, it is accessed immediately. If the block is not found in the cache, Analytic Services searches the index for the appropriate block number and then uses the index entry of the block to retrieve it from the appropriate data file on disk. Retrieving a requested block from the data cache is faster, and therefore improves performance.
In general, if you have to choose whether to allocate memory to the data cache or to the data file cache, choose the data file cache if you are using direct I/O.
This table shows default and recommended settings for the data cache.
Make the data cache as small as possible whether you are using buffered I/O or direct I/O.
For information and instructions on testing and fine tuning cache settings, see Fine Tuning Cache Settings.
To set the size of the data cache, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
Analytic Services can create a bitmap, whose size is controlled by the size of the calculator cache, to record and track data blocks during a calculation. Determining which blocks exist using the bitmap is faster than accessing the disk to obtain the information, particularly if calculating a database for the first time or calculating a database when the data is very sparse.
Analytic Services uses the calculator cache bitmap if the database has at least two sparse dimensions, and either of these conditions are also met:
The best size for the calculator cache depends on the number and density of the sparse dimensions in your outline. Use these topics to understand the calculator cache bitmap, size the calculator cache, and change the size of the calculator cache (and therefore the largest possible size for the bitmap), if required:
For the calculator cache, Analytic Services separates sparse dimensions in the database into two groups:
Analytic Services starts with the first sparse dimension in the database outline and fits as many sparse dimensions as possible into the bitmap. The dimensions that fit are the bitmap dimensions. Analytic Services stops the process when it cannot fit another complete sparse dimension into the bitmap. Because the calculator cache controls the size of the bitmap, the number of sparse dimensions that can fit in the bitmap depends on the size of the calculator cache (and the number and size of the sparse dimensions).
The remaining sparse dimensions are the anchoring dimensions. For anchoring dimensions, Analytic Services cannot use the bitmap to determine whether or not blocks exist.
To see which dimensions are anchoring dimensions and which are bitmap dimensions, use the SET MSG DETAIL calculation command to display bitmap information in the application log.
Carefully order the sparse dimensions in your outline so that as many dimensions as possible can be placed into the bitmap. Start with the dimension that contains the fewest members, and continue until the dimension with the most members is last. This order allows more dimensions to fit into the bitmap and results in improved calculation performance.
Note: The order of sparse dimensions in the outline also affects query performance. To optimize the outline for query performance, see Optimizing Query Performance.
Analytic Services uses a single bitmap if there is more than one anchoring dimension or if the calculator cache is not large enough to support multiple bitmaps, and uses two or more bitmaps if there is a single anchoring dimension.
A single bitmap has these properties:
Multiple bitmaps have these properties:
Analytic Services chooses one of three options for the calculation:
Option |
Method |
Performance Rating |
---|---|---|
Analytic Services chooses the optimal performance method for a database calculation, based on the size of the calculator cache. If the calculator cache size is too small for any of the above options, Analytic Services does not use a calculator cache. Calculation performance may be significantly impaired.
Enabling parallel calculation may change which calculator cache option is used. See Calculator Cache for details.
Caution: If you are calculating the database for the first time, the size of the calculator cache is particularly significant for calculation performance. If possible, ensure that the calculator cache is large enough for Analytic Services to use the optimal calculator cache option.
The optimum size of the calculator cache depends on the amount of memory the system has available. It also depends on the nature and configuration of the database.
Using the following formula, you can calculate the calculator cache size required for Analytic Services to choose each of the three options in Table 75.
Maximum number of dependent parents in the anchoring dimension + 2 constant bitmaps |
Note: The minimum bitmap size is 4 bytes. If (member combinations on the bitmap dimensions/8) is less than 4 bytes, Analytic Services uses a bitmap size of 4 bytes.
Consider an example database with five sparse dimensions (S1 to S5):
Sparse Dimension |
Number of Members |
Dependent Parents |
---|---|---|
Use this example information for these sample calculations:
For this example calculation, assume the following facts about a database (from Table 75):
Maximum number of dependent parents in the | ||
In order for Analytic Services to use multiple bitmaps for this database with a single anchoring dimension, the calculator cache needs to be 625,000 bytes.
For this example calculation, assume the following facts about a database (from Table 75):
In order for Analytic Services to use a single bitmap for this database with a single anchoring dimension, the calculator cache needs to be 125,000 bytes.
For this example calculation, assume the following facts about a database (from Table 75):
In order for Analytic Services to use a single bitmap for this database with multiple anchoring dimensions, the calculator cache needs to be 2,500 bytes.
The following table shows which calculator cache option Analytic Services uses, depending on the calculator cache size specified:
Minimum Size Specified |
Option Selected |
If you specify a calculator cache size of less than 2,500 bytes, Analytic Services does not use a calculator cache during the calculation. Calculation performance may be significantly impaired.
You can check which calculator cache option Analytic Services is able to use on a database by using the SET MSG SUMMARY command in a calculation script. Run the following calculation script on the empty database:
SET MSG SUMMARY; CALC ALL;
Analytic Services displays the calculator cache setting in the ESSCMD window or in the application log. For a discussion of why you use SET MSG SUMMARY, see SET MSG SUMMARY and SET MSG DETAIL.
The maximum calculator cache size that you can specify is 200,000,000 bytes. The default is 200,000 bytes. The calculator cache size that you choose depends on how much memory is available and the configuration of the database.
Note: The sizes of the calculator, index, data file, and data caches usually have a greater effect on performance if the database calculation is based more on aggregations and less on formula calculations.
If you are calculating the database for the first time, the size of the calculator cache is particularly significant. If possible, ensure that the calculator cache is large enough for Analytic Services to use the optimal calculator cache option. For discussion and examples of how to determine cache size, see Calculating the Calculator Cache Size.
You can use the default calculator cache size, or you can set the size of the calculator cache within a calculation script. If you set the size from a calculation script, the setting is used only for the duration of the calculation script. For details, review information about the calculation script SET CACHE command and the CALCCACHE configuration setting in the Technical Reference.
Essbase uses a separate dynamic calculator cache for each open database. The DYNCALCCACHEMAXSIZE setting in the essbase.cfg
file, specifies the maximum size of each dynamic calculator cache on the server. By default, the maximum size is 20 MB. Essbase allocates area in a dynamic calculator cache for data blocks until the maximum memory area specified by the DYNCALCACHEMAXSIZE setting is allocated.
For detailed information about DYNCALCACHEMAXSIZE and other dynamic calculator cache settings, see Changing the Dynamic Calculator Cache Size.
For each database, Analytic Services writes two messages to the application log for each data retrieval:
[Thu Oct 17 11:37:17 2002]Local/Sample///Info(1007125) The number of Dynamic Calc Non-Store Members = [7 6 0 0 2 ] [Thu Oct 17 11:37:17 2002]Local/Sample///Info(1007126) The number of Dynamic Calc Store Members = [0 0 0 0 0 ]
The first message describes the total amount of time required for the retrieval. If a dynamic calculator cache is used, the second message displays the number of blocks calculated within the data calculator cache (DCC = n) and the number of blocks calculated in general memory (non-DCC = n).
Five configuration file settings are relevant to dynamic calculator caches. The optimum values for these dynamic calculator cache settings depend on the amount of memory on the server machine, the configuration of all databases on the server machine, and the nature of user queries.
Table 76 describes each setting and includes recommendations on how to determine values for your system. To match your site's unique requirements, you may need to test and adjust the settings.
DYNCALCCACHEMAXSIZE |
|
This setting specifies the maximum size Essbase can allocate to each dynamic calculator cache on the server. |
|
Recommended setting value = C * S * U.
|
|
DYNCALCCACHEWAITFORBLK |
|
If Analytic Services uses all of the area allocated for a dynamic calculator cache, this setting tells Analytic Services whether to wait until space becomes available in the cache or to immediately write and calculate the blocks in memory outside the dynamic calculator cache. If the dynamic calculator cache is too small, it is possible for more than one thread to be in queue, each thread waiting to calculate its data blocks. |
|
DYNCALCCACHEBLKTIMEOUT |
|
If Analytic Services is to wait for available space in the dynamic calculator cache, this setting defines how long it waits. |
|
Recommended setting value = WT / B.
|
|
DYNCALCCACHEBLKRELEASE |
|
If Analytic Services has waited the specified time and space is still not available in the dynamic calculator cache, this setting tells Analytic Services whether to write and calculate the blocks immediately outside the dynamic calculator cache or to create space in the dynamic calculator cache by swapping out blocks and temporarily compressing the swapped blocks in a dynamic calculator cache compressed-block buffer. |
|
Recommended setting value = FALSE (default value). Set to TRUE only if you are experiencing severe memory shortage problems. |
|
DYNCALCCACHECOMPRBLKBUFSIZE |
|
If Analytic Services has waited the specified wait time and the DYNCALCCACHEBLKRELEASE setting is TRUE, this setting is the size of the dynamic calculator cache compressed-block buffer. |
|
Recommended setting value = (C * S) / 2.
|
Note: After changing any parameter in the essbase.cfg
file, you must stop and restart Analytic Server to use the new values.
For detailed information about specific dynamic calculator cache settings, see the Technical Reference.
After using a database at your site with typical data, user access, and standard environment (including server machines, network, etc.), check to see how Analytic Services performs. It is difficult to predict optimal cache sizes without testing. You may need to adjust cache settings.
The sizes of the index cache and the data file cache (when direct I/O is used) are the most critical Analytic Services 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.
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.
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.
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 migrating from a previous version of Analytic Services, see the Essbase Analytic Services Installation Guide for relevant migration information.
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 the Essbase Administration Services Online Help.
You can check cache statistics for a database by using the GETPERFSTATS
command in ESSCMD.
Monitoring Performance, provides detailed information about ways to check performance.
Because calculations are the most processor-intensive operations on a Analytic Services database, you should run test calculations and examine how various cache sizes affect memory use on Analytic Server.
![]() |