Product Documentation

Database Administrator's Guide

Previous Topic

Next Topic

SET_CACHE_OPTIONS

Memory caching of data is one of the most important subsystems of a database server, including FairCom DB. Caching allows the database process to optimally serve clients with data directly from memory when possible. Separate independent memory caches are used for data and indexes (buffers). Proper cache sizing is a critical performance tuning task of the database administrator. Too little memory, and the database engine becomes bottlenecked with persisted storage I/O. Too much, and not enough memory is made available to the operating system and other applications running on the host machine.

Cache resizing is a powerful feature for performance tuning with increasing database loads. Adding additional client connections, thus increasing user concurrency, frequently benefits from larger caches. Another situation is a very large ad-hoc database load or query where temporarily having a larger memory cache improves overall performance. Changing a cache size requires restarting the FairCom DB server process which is not an easy task in the 24/7 enterprise environment.

FairCom DB "hard" allocates memory for caches at server startup directly from the OS memory heap. Once allocated, the memory is permanently associated with the database for the life of the process. Allocated memory is chunked into page size blocks within the memory cache subsystem, historically making it difficult to resize as needed. An additional complication is how to handle existing data residing in cache. Losing that "hot" data will likely result in performance loss for connected applications until a runtime steady state is again achieved over time.

FairCom has had increasing requests for ability to change cache sizes at runtime and this release introduces dynamic cache resizing. Now, you can fine tune data and index caches as performance demands based on database connections and load avoiding costly database down time.

dynamic_cache_resize_flat_wide

How to Resize Your Current Cache

The server administrator utility, ctadmn, supports a new configuration option set_cache_options as a parameter. This is followed with a null-terminated JSON string with desired options. Supported cache options are as follows:

  • "timeout": <timeoutInSeconds> where <timeoutInSeconds> is the maximum number of seconds to wait for active transactions to complete when quiescing the server in order to change the cache sizes. The default timeout is 1 second.
  • "dat_memory": "<new_data_cache_size>" where <new_data_cache_size> is an integer cache size followed by optional suffix such as mb for megabytes or gb for gigabytes. For example, "dat_memory": "100 mb". If not specified, the data cache size is not changed.
  • "idx_memory": "<new_index_cache_size>" where <new_index_cache_size> is an integer cache size followed by optional suffix such as mb for megabytes or gb for gigabytes. For example, "idx_memory": "200 mb". If not specified, the index cache size is not changed.
  • "keep_cached_data": "<yes_or_no>" where <yes_or_no> is either yes, which means the currently-cached data is kept in the data cache, or no, which means the currently-cached data is removed from the data cache. The default is yes.
  • "keep_cached_nodes": "<yes_or_no>" where <yes_or_no> is either yes, which means the currently-cached nodes are kept in the index cache, or no, which means the currently-cached nodes are removed from the index cache. The default is yes.

Administrator Utility Usage

From ctadmn, choose option 10 "Change the specified configuration option"


Change Server Settings:

1. Configure function monitor

2. Configure checkpoint monitor

3. Configure memory monitor

4. Configure request time monitor

5. Change dynamic dump sleep time

6. Change dynamic dump sleep interval

7. Enable or disable a status log mask option

8. Change advanced encryption master password

9. Change a DIAGNOSTICS option

10. Change the specified configuration option

11. Change Logon Block Settings

Enter your choice (1-11), or 'q' to return to previous menu>> 10

Enter the configuration option and its value >> set_cache_options {"dat_memory": "200 mb"}

Successfully changed the configuration option.

Press RETURN to continue...

Expected Status Log Messages

The resize request triggers an internally generated server quiesce state with the following common messages.


Mon Jun 13 13:47:51 2022 - User# 00030 ctQUIET: attempt quiet transaction state with action: 1100a0x timeout: 1 sec

Mon Jun 13 13:47:51 2022 - User# 00030 ctQUIET: blocking call with action: 641a2x

Mon Jun 13 13:47:54 2022 - User# 00030 Transaction aborted at ctQTaborttran for user# 33: 817

Mon Jun 13 13:47:54 2022 - User# 00030 Transaction aborted at ctQTaborttran for user# 40: 817

Mon Jun 13 13:47:54 2022 - User# 00030 Transaction aborted at ctQTaborttran for user# 47: 817

Mon Jun 13 13:47:54 2022 - User# 00030 Transaction aborted at ctQTaborttran for user# 52: 817

Mon Jun 13 13:47:54 2022 - User# 00030 Transaction aborted at ctQTaborttran for user# 58: 817

Mon Jun 13 13:47:54 2022 - User# 00030 Transaction aborted at ctQTaborttran for user# 65: 817

Mon Jun 13 13:47:54 2022 - User# 00030 ctQUIET: end of blocking call

Mon Jun 13 13:47:54 2022 - User# 00030 Using 3 index node LRU lists with 3962 buffers per list

Mon Jun 13 13:47:54 2022 - User# 00030 ctQUIET: unblocking call with action: 809e00x

Mon Jun 13 13:47:55 2022 - User# 00030 ctQUIET: end of unblocking call

Examples

  1. Set data cache size to 100 MB and keep existing data in cache (since "keep_cached_data" defaults to "yes"). No change to index cache. In this example, if data cache is already 100 MB, no action is taken:

    set_cache_options {"dat_memory": "100 mb"}

  2. Set data cache size to 100 MB and remove all data from data cache:

    set_cache_options {"dat_memory": "100 mb", "keep_cached_data": "no"}

  3. Keep current data cache size and remove all data from data cache:

    set_cache_options {"keep_cached_data": "no"}

  4. Set index cache size to 200 MB and keep existing nodes in cache:

    set_cache_options {"idx_memory": "200 mb"}

  5. Set index cache size to 200 MB and remove all nodes from index cache:

    set_cache_options {"idx_memory": "200 mb", "keep_cached_nodes": "no"}

  6. Keep current index cache size and remove all nodes from index cache:

    set_cache_options {"keep_cached_nodes": "no"}

This example shows actions on both data and index cache:

set_cache_options {"dat_memory": "500 mb", "idx_memory": "400 mb", "keep_cached_nodes": "no", "timeout": 5}

Direct SDK API

An application that is logged in as the ADMIN user can change data and/or index caches by calling the ctSETCFG() API function with option of setcfgCONFIG_OPTION and value set to:

set_cache_options {<cache_options>}

where <cache_options> is the null-terminated string in JSON format.

This example showing how to change the cache sizes in C or C++ code by calling the ctSETCFG() API function:

NINT rc;

rc = ctSETCFG(setcfgCONFIG_OPTION, "set_cache_options {\"dat_memory\": \"100 mb\"}");

Diagnostic Logs

The configuration option DIAGNOSTICS RESIZE_CACHE, which can be specified in ctsrvr.cfg and changed at runtime, causes cache resize diagnostic messages to be logged to CTSTATUS.FCS.

TOCIndex