Give Your Database a Performance Tune Up with These Advanced Caching Tips
The c-treeACE database engine provides high performance data and index caches to maintain frequently requested data in memory for fast access. This a key advantage of using server technology over the multiuser standalone model. The server process can maintain your data in a single pool available to all client threads for faster throughput. This can bring the performance of some file access to in-memory levels.
c-treeACE maintains two separate cache areas; one for data files and one for index files. Maintaining information in cache avoids excessive disk I/O for multiple accesses to this data. Your data and index files are read into memory from disk as needed. c-treeACE reads the information in chunks, or pages, to obtain the most efficient use of the underlying disk I/O subsystems. This page size parameter can be changed with the PAGE_SIZE setting in your server configuration to best match that of your computing environment. A page is a single node of an index structure, where each node consists of multiple keys to your data.
Data Cache
|
The data cache contains all the pages of data from all open data files by all users. For variable length records, only the first and last pages of a data record are retained in cache if the data record extends beyond two pages: the middle portion of the record is read directly from disk when needed. While one might think this defeats the value of cache, however, in practice, it is a very efficient I/O operation as the reads are aligned and are for multiples of cache page size. This approach avoids large data records from pushing out other valuable cached data. Also, most update operations only affect the record header (or fixed length portion) or extend the end of record (variable portion), thus the first and last pages are the most commonly updated pages required of a record and maintaining only these in cache proves to be very efficient.
|
Index Cache
|
c-treeACE requires that an index node fit into a single index cache page, frequently called the buffer space. This is also determined by the PAGE_SIZE configuration option. The server can access index files that were created with an index node size less than or equal to the server’s PAGE_SIZE setting, but attempting to open an index whose index node size exceeds the server’s PAGE_SIZE fails with error KSIZ_ERR (40, index node size too large).
Note that to take advantage of a change in the PAGE_SIZE setting, you will need to rebuild any indexes with existing settings. Once an index is created with a particular node size (PAGE_SIZE) that attribute is persistent until the index is rebuilt.
|
In both types of cache, a Least Recently Used (LRU) algorithm is utilized to determine when to flush a page from cache to disk. For optimized performance, different approaches to these caching strategies should be considered. c-treeACE offers many options for fine tuning cache control. Always be aware that caching of data can directly impact recoverability in case of system failure, for example a power outage. With transaction processing enabled files this is not an issue as changes to the files are secured in the transaction log files and will be recovered on the next startup. However, files not under transaction control will most certainly lose data that was remaining in cache at the time of failure.
Monitoring Cache
c-treeACE provides both global and file level cache statistics through the SNAPSHOT statistics. If you are concerned that your cache is not being utilized in the most optimal manner, you should examine these statistics. Most important is the cache hit rate. You want this value to be as high as possible to ensure you are maximizing your performance. With cache hit rates better than 95%, you can be assured you are obtaining the best possible performance with your data and index files.
The c-treeACE Performance Monitor tool provides real time cache information for both the data and index caches.
Use the command line ctstat statistics monitoring utility to capture output statistics in handy comma separated value (csv) format.
Advanced Cache Control
The memory allocated to the data and index caches is set in the c-treeACE configuration file, ctsrvr.cfg, using the DAT_MEMORY and IDX_MEMORY options. For example, to allocate 1 GB data cache and 1 GB index cache, specify these settings in ctsrvr.cfg:
DAT_MEMORY 1 GB
IDX_MEMORY 1 GB
The server allocates this memory at startup and partitions it into cache pages. The server’s PAGE_SIZE setting determines the size of the data and index cache pages.
The c-treeACE Server’s data and index cache configuration options support specifying a scaling factor used when interpreting cache memory sizes. The supported scaling factors are:
- KB: interpret the specified value as a number of kilobytes.
- MB: interpret the specified value as a number of megabytes.
- GB: interpret the specified value as a number of gigabytes.
You are always free to specify the exact numerical value as well. The following web page lists those keywords that accept the scaling factors and their limits:
Scaling Factors for Configuration Keyword Values
Per File Cache Options
In some cases it may be advantageous to turn off data file caching, especially for files with very long variable length records. Configuration entries of the form:
NO_CACHE <data file name>
result in caching for the specified data files to be disabled. Note that <data file name> may specify a wildcard pattern. You can specify multiple entries for more than one file.
Cache memory can be dedicated to specific data files using the following server configuration option:
SPECIAL_CACHE_FILE <data file name>#<bytes dedicated>
This option is useful for files that you wish to always remain in memory. For example, when occasionally reading other large files, this avoids required files being pushed out of cache.
For even more control, you can also specify percentage of total cache to be reserved for the specially cached files.
SPECIAL_CACHE_PERCENT <percentage>
This option specifies the overall data cache space that may be dedicated to individual files.
Priming Cache
The c-treeACE database engine optionally maintains a list of data files and the number of bytes of data cache to be primed at file open. When priming cache, c-treeACE reads the specified number of bytes for the given data file into data cache when physically opening the data file.
Data files are added to the priming list with configuration entries of the form:
PRIME_CACHE <data file name>#<bytes primed>
For example, the following keyword instructs the Server to read the first 100,000 bytes of data records from customer.dat into the data cache at file open:
PRIME_CACHE customer.dat#100000
A dedicated thread performs cache priming, permitting the file open call to return without waiting for the priming to be completed.
Use PRIME_CACHE with the SPECIAL_CACHE_FILE keyword to load dedicated cache pages at file open.
To prime index files, use configuration entries of the form:
PRIME_INDEX <index file name>#<bytes primed>[#<member no>]
If the optional <member no> is omitted, all index members are primed. If an index member number is specified, only that member is primed.
For example, the following keyword instructs the Server to read the first 100,000 bytes of index nodes in customer.idx into the index buffer space at file open:
PRIME_INDEX customer.idx#100000
The nodes are read first for the host index, and then for each member until the entire index is primed or the specified number of bytes has been primed.
The following example restricts the priming to the first member (the index after the host index):
PRIME_INDEX customer.idx#100000#1
The <data file name> or <index file name> can be a wild card specification using a ‘?’ for a single character and a ‘*’ for zero or more characters.
c-treeACE also supports priming the data cache in forward AND reverse order by index.
PRIME_CACHE_BY_KEY <data_file_name>#<data_record_bytes_to_prime>#<index_number>
For example
PRIME_CACHE_BY_KEY mark.dat#-1#100000000
Primes up to 100,000,000 bytes from mark.dat reading by the first index in reverse key order.
Control of Cache Flushing
By default, c-treeACE creates two idle flush threads at startup. One thread flushes transaction file buffers, and the other flushes non-transaction file buffers. The threads wake up periodically and check if the server is idle. If so, the flush is begun. If subsequent server activity causes the server to no longer be idle, then the flushes are terminated. Low priority background threads, such as the delete node thread, do not affect the server’s idle state. c-tree clients and c-treeACE SQL clients and checkpoints do cause the idle status to be modified.
The configuration file can modify the characteristics of these idle flush threads:
IDLE_TRANFLUSH <idle check-interval for tran files>
IDLE_NONTRANFLUSH <idle check-interval for nontran files>
The idle check interval values are specified in seconds. Setting the interval to zero or a negative value disables the thread. The defaults for these intervals are each set at 15 seconds.
New Feature in V9 - Scanner Cache
The data cache and index buffer pages are managed using a least-recently-used (LRU) scheme such that the page that has not been touched the longest will be the page selected for re-assignment when a page is required to satisfy a current request. For indices, this simple strategy works very well since traversing an index tree frequently requires access to the same, important high level nodes (such as the root node) and/or to the “current” leaf node.
For data files, the LRU scheme is not always effective. In particular, when data file access or updates involve many different records with little or no revisiting of records once they have been processed, the LRU scheme can result in many cache pages to be assigned to recently accessed records. But, at least from the user’s perspective, there is little chance of revisiting these pages.
Consider simply traversing a large set of records using NXTREC()/NXTVREC(): it would be possible to exhaust the cache with the contents of these records; but if no access beyond the primary access is required, these cache pages may be wasted. Even when we consider adding a large number of records, especially to the end of a file, this presents the same problem.
Using the advanced c-treeACE header mode, ctSCANCACHEhdr, allows an alternative caching strategy. The scanner cache strategy uses all the standard cache routines except when requiring a cache page (that is, c-treeACE has not found the data block it needs already in cache), it uses one of two dedicated cache pages.
Example
The following call will enable the scanner mode of the file, specified by the file number datno, to use the scan cache strategy:
PUTHDR(datno, YES, ctSCANCACHEhdr);
The scanner only goes to the LRU list if it has not yet dedicated both cache pages. Once dedicated, these pages may be referenced by other users, but they are not placed back on the LRU until either the file is closed or a scanner mode is toggled off.
Note: The strategy only applies to the calling user’s access to datno; other users control their data cache strategies individually.
The significance of this strategy is that a large set of data record operations can be performed with only two cache pages with little or no decrease in performance compared to no restrictions on cache usage. Moreover, such restricted use may help other aspects of system performance since the bulk of data cache can be used for other files and/or other users. A large traversal of the data file will not replace other cache pages.
Two dedicated cache pages were chosen since two pages are always sufficient to hold the beginning and end of a record image which is typically all that is stored in the cache. A short record can always fit in one or two cache pages, and a long record has its interior (i.e., that portion which does not fit within the two pages) read or written directly (regardless of scanner mode unless MPAGE_CACHE has been enabled), bypassing the cache.
Particular Cases
SPECIAL_CACHE_PERCENT
The previous dedicated cache strategy uses the keyword SPECIAL_CACHE_PERCENT to specify the maximum percentage of the data cache that can be dedicated. This percentage defaults to 50%. If the number of dedicated cache pages is already at this limit, then the scan cache approach will not take effect until the total number of dedicated cache pages falls below the limit.
SPECIAL_CACHE_FILE
The SPECIAL_CACHE_FILE list of files with dedicated cache does not affect the scan cache strategy: a user can call PUTHDR() for any data file whether or not it is on the SPECIAL_CACHE_FILE list. The SNAPSHOT statistics for special cache pages include dedicated scan cache pages.
Other References
c-treeACE V9 Update Guide
c-treeACE Support Guide
c-treeACE Administrator’s Guide
Boost the Performance of Your c-tree Server
|
