About Caching

There is one buffer pool cache per XDB Server, and all applications connecting to the XDB Server share this global memory. The overall size of the buffer cache has a large impact on database performance. If the cache is large enough to keep the required data in memory, less disk activity occurs and performance increases. If the cache pool is not large enough, the overall performance of the XDB Server can decrease, with the XDB Server becoming I/O-bound, as physical disk activity is required to service application requests.

The buffer pool cache effectiveness increases with multiple users, when a large amount of repeated access to the same data and index pages occur, and when one primary location is accessed.

The cache manager stores pages (4K each) of database object data. (Therefore, the number of pages is the cache size divided by four.) These database objects consist of table, index, and dictionary pages for all accessed locations. The dictionary pages originate from the dictionary files that reside in every location. As an application requests data from one of these object types, pages containing the data are transferred from the disk into the buffer pool.

Pages are not written to disk until one of the following occurs:

• The XDB Server is shut down.
• A checkpoint occurs.
• Another page needs to be read into a full buffer pool.

Consider the following when determining how much RAM to allocate to cache:

• A large cache usually speeds up online transaction processing (OLTP). However, if the checkpoint frequency is too infrequent, there can be detectable processing delays each time the checkpoint causes data to be written to the location tables. If checkpoint delays are a problem, either lower the cache amount or increase the checkpoint frequency. For more information about the CHECKPOINT command and the Checkpoint Frequency parameter, see the SQL Reference and the chapter Backup and Recovery chapter in this guide.
• Decision support systems usually benefit from a larger cache, but the amount of cache does not necessarily have to be as large as the combined size of the database tables and indexes. For example, in a database where much of the data is historical (and rarely accessed), the amount of cache would only have to be large enough to contain the currently active data -- such as the current month or quarter.
• On systems where sort operations are heavy, you might find that an increase in Sort Space Size gives a better performance gain than increasing or decreasing the cache. Once the appropriate Sort Space Size is defined, you should find it easier to determine the amount of cache needed.

Use the Cache Statistics screen of the Monitor Utility to help determine the best amount of cache for your system. When your XDB Server has reached a steady state (work is well in progress), check the percentage shown for Pages in Use. If Pages in Use is running at or near 100% and the Number of Tosses is high, then most of your allocated cache is being used and you might want to try increasing it. If Pages in Use and Tosses are running low, you have more cache allocated than you need.