Optimizing Analytic Services Caches Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Optimizing Analytic Services Caches


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.

Understanding Analytic Services Caches

Analytic Services uses five memory caches to coordinate memory usage:


Table 71: Analytic Services Caches  

Cache
Description

Index cache

The index cache is a buffer in memory that holds index pages. How many index pages are in memory at one time depends upon the amount of memory allocated to the cache.

Data file cache

The data file cache is a buffer in memory that holds compressed data files (.pag files). Analytic Services allocates memory to the data file cache during data load, calculation, and retrieval operations, as needed. The data file cache is used only when direct I/O is in effect.

Data cache

The data cache is a buffer in memory that holds uncompressed data blocks. Analytic Services allocates memory to the data cache during data load, calculation, and retrieval operations, as needed.

Calculator cache

The calculator cache is a buffer in memory that Analytic Services uses to create and track data blocks during calculation operations.

Dynamic calculator cache

The dynamic calculator cache is a buffer in memory that Analytic Services uses to store all of the blocks needed for a calculation of a Dynamic Calc member in a dense dimension (for example, for a query).



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:

Deciding Whether to Use Cache Memory Locking

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

Administration Services

Enabling Cache Memory Locking

Essbase Administration Services Online Help

MaxL

alter database enable cache_pinning

Technical Reference

ESSCMD

SETDBSTATEITEM 26

Technical Reference



Sizing Caches

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.

Sizing the Index Cache

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.


Table 72: Index Cache Size Settings

Minimum Value
Default Value
Recommended Value

1024 KB (1048576 bytes)

Buffered I/O: 1024 KB (1048576 bytes)

Direct I/O: 10240 KB (10485760 bytes)

Combined size of all essn.ind files, if possible; as large as possible otherwise. Do not set this cache size higher than the total index size, as no performance improvement results. To determine the total index size, see Index Files.



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.

Changing the Index Cache Size

To set the size of the index cache, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Cache Sizes

Essbase Administration Services Online Help

MaxL

alter database set index_cache_size

Technical Reference

ESSCMD

SETDBSTATEITEM 12

SETDBSTATE

Technical Reference



Sizing the Data File Cache

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.


Table 73: Data File Cache Size Settings

Minimum Value
Default Value
Recommended Value

Direct I/O: 10240 KB (10485760 bytes)

Direct I/O: 32768 KB (33554432 bytes)

Combined size of all essn.pag files, if possible; otherwise as large as possible.

This cache setting not used if Analytic Services is set to use buffered I/O.



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.

Changing the Data File Cache Size

To set the size of the data file cache, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Cache Sizes

Essbase Administration Services Online Help

MaxL

alter database set data_file_cache_size

Technical Reference

ESSCMD

SETDBSTATEITEM 27

Technical Reference



Sizing the Data Cache

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.


Table 74: Data Cache Size Settings

Minimum Value
Default Value
Recommended Value

3072 KB (3145728 bytes)

3072 KB (3145728 bytes)

0.125 * the value of data file cache size. Increase value if any of these conditions exist:

  • Many concurrent users are accessing different data blocks.

  • Calculation scripts contain functions on sparse ranges, and the functions require all members of a range to be in memory (for example, when using @RANK and @RANGE)

  • For data load, the number of threads specified by the DLTHREADSWRITE setting is very high and the expanded block size is large.


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.

Changing the Data Cache Size

To set the size of the data cache, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Cache Sizes

Essbase Administration Services Online Help

MaxL

alter database set data_cache_size

Technical Reference

ESSCMD

SETDBSTATEITEM 5

SETDBSTATE

Technical Reference



Sizing the Calculator Cache

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:

Understanding the Calculator Cache Bitmap

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:


Table 75: Options for calculator cache

Option
Method
Performance Rating

1

Single anchoring dimension, multiple bitmaps

1

2

Single anchoring dimension, single bitmap

2

3

Multiple anchoring dimensions, single bitmap

3



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.

Calculating the Calculator Cache Size

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.

Calculator cache

=

Bitmap size in bytes * Number of bitmaps

Bitmap size in bytes

=

Max ((member combinations on the bitmap dimensions/8), 4)

Number of bitmaps

=

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

S1

20

Not applicable

S2

20

Not applicable

S3

50

Not applicable

S4

50

Not applicable

S5

200

3



Use this example information for these sample calculations:

Option 1: Single Anchoring Dimension, Multiple Bitmaps

For this example calculation, assume the following facts about a database (from Table 75):

Now perform this calculation:

Bitmap size in bytes

=

=

=

(S1 * S2 * S3 * S4) / 8

(20 * 20 * 50 * 50) / 8

125,000 bytes

Number of bitmaps

=

 

=

=

Maximum number of dependent parents in the
anchoring dimension

+

2 constant bitmaps

3 + 2

5

Calculator cache

=

=

=

Bitmap size * Number of bitmaps

125,000 * 5

625,000 bytes



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.

Option 2: Single Anchoring Dimension, Single Bitmap

For this example calculation, assume the following facts about a database (from Table 75):

Now perform this calculation:

Bitmap size in bytes

=

=

=

(S1 * S2 * S3 * S4) / 8

(20 * 20 * 50 * 50) / 8

125,000 bytes

Number of bitmaps

=

=

Single bitmap

1

Calculator cache

=

=

=

Bitmap size * Number of bitmaps

125,000 * 1

125,000 bytes



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.

Option 3: Multiple Anchoring Dimensions, Single Bitmap

For this example calculation, assume the following facts about a database (from Table 75):

Now perform this calculation:

Bitmap size in bytes

=

=

=

(S1 * S2 * S3) / 8

(20 * 20 * 50) / 8

2,500 bytes

Number of bitmaps

=

=

Single bitmap

1

Calculator cache

=

=

=

Bitmap size * Number of bitmaps

2,500 * 1

2,500 bytes



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.

Choosing a Calculator Cache Size for a Database

The following table shows which calculator cache option Analytic Services uses, depending on the calculator cache size specified:


Minimum Size Specified
Option Selected

625,000 bytes

Option 1 (provides optimal performance)

125,000 bytes

Option 2

2,500 bytes

Option 3



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.

Sizing the Calculator Cache to Calculate the Database for the First Time

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.

Changing the Calculator Cache with Calculation Scripts

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.

Sizing Dynamic Calculator Caches

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.

Reviewing Dynamic Calculator Cache Usage

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).

Changing the Dynamic Calculator Cache Size

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.


Table 76: essbase.cfg Settings for Dynamic Calculator Caches  

DYNCALCCACHEMAXSIZE

Description

This setting specifies the maximum size Essbase can allocate to each dynamic calculator cache on the server.

Recommended Setting

Recommended setting value = C * S * U.

  • C is the value of the appropriate CALCLOCKBLOCK setting in the essbase.cfg file. (The SET LOCKBLOCK command specifies which CALCLOCKBLOCK setting to use.)

  • S is the size of the largest expanded block across all databases on the machine. To calculate the expanded block size, multiply the number of members (including Dynamic Calc members) in each dense dimension together, and then multiply the result by the size of each member cell, 8 bytes.

    For example, for the dense dimensions in Sample Basic, 12 (Year) * 8 (Measures) * 3 (Scenario) * 8 bytes = 2304 bytes.

  • U is the maximum number of expected concurrent users on the database that has the largest number of concurrent users.

    Assigning the value 0 (zero) to DYNCALCACHEMAXSIZE tells Essbase not to use dynamic calculator caches.

    By default, the maximum size for this value is 20 MB (20,971,520 bytes).

DYNCALCCACHEWAITFORBLK

Description

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.

Recommended Setting

Recommended setting value = FALSE (default value).

Before setting to TRUE, try these alternatives:

  • Add physical memory to the server machine

  • Increase the value of DYNCALCCACHEMAXSIZE, test, and repeat until you verify that you cannot use any more memory for the dynamic calculator cache.
DYNCALCCACHEBLKTIMEOUT

Description

If Analytic Services is to wait for available space in the dynamic calculator cache, this setting defines how long it waits.

Recommended Setting

Recommended setting value = WT / B.

  • WT is the maximum tolerable wait time for a query; for example, 5 seconds.

  • B is the total number of logical blocks accessed in the largest query.

    To determine the value of B, check the messages in the application log for the largest number of Dyn.Calc.Cache "Big Block Allocs" for a query, as shown in Figure 192.

DYNCALCCACHEBLKRELEASE

Description

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

Recommended setting value = FALSE (default value).

Set to TRUE only if you are experiencing severe memory shortage problems.

DYNCALCCACHECOMPRBLKBUFSIZE

Description

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

Recommended setting value = (C * S) / 2.

  • C is the value of the current CALCLOCKBLOCK setting in the essbase.cfg file. The SET LOCKBLOCK command specifies which CALCLOCKBLOCK configuration setting is current.

  • S is the size of the largest expanded block across all databases on the machine. Calculate S as described for the DYNCALCCACHEMAXSIZE setting.


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.

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 Analytic Services performs. It is difficult to predict optimal cache sizes without testing. You may need to adjust 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 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.

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 migrating from a previous version of Analytic Services, see the Essbase Analytic Services Installation Guide for relevant migration information.

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 the Essbase Administration Services Online Help.

Checking Performance

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.

Running Test Calculations

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.



Hyperion Solutions Corporation link