FairCom ISAM for C

Deferred Indexing

Deferred Index Maintenance brings new speed to index maintenance operations.

c-tree’s ISAM add, delete, and update operations dutifully perform key insert and deletes on every index associated with a data file: a record add inserts a key value for each associated index; a record delete deletes a key value for each associated index; and a record update deletes the old key and inserts the new key for each associated index (for those keys whose values changed). However, each additional index operation can impose measurable performance loss, especially when numerous indexes are involved. If these immediate key operations could be held off for a period of time, applications can gain substantial performance in many circumstances.

Two application usages are considered strong candidates for deferred index processing.

• Deferred build of a permanent index added to an existing table. This typically requires waiting for an index to be loaded with all key values before returning control to the application. If this process could be sent to a background task, and only once the index build was complete does it become available. This index will be acting as "eventually consistent" until the end of the creation and initial population with existing data and data added during this initial period of time. After all keys are loaded it behaves either in synchronous or deferred mode depending on its permanent assigned deferred attributes.
• A particularly interesting application usage is an application deployed with a core set of primary indexes and allows additional end user alternate indexes to be created. With so many indexes, core application performance can be adversely impacted. If the additional index maintenance overhead could be deferred, core application performance remains less impacted while using its primary indexes.

FairCom addressed these challenges with new deferred maintenance modes. By delaying select index operations, applications can quickly create and/or update files with large numbers of indexes very quickly.

A key background support for this functionality is the ability to create an index file with the data file open in shared mode.

Permanent Deferred Index Mode

First addressing the second described scenario. A deferred indexing attribute can be specified on new index creation and enables ISAM record add, delete, and update operations to delay key insert/delete operations for that index file. OR in the KTYP_DFRIDX bit into the ikeytyp field of that index’s IIDX structure that you pass to the CREIFIL() function.

ISAM operations now avoid additional overhead of directly updating these deferred indexes. With deferred indexing enabled, a background thread performs key insert and delete operations on the deferred index files asynchronously. This mode becomes a permanent attribute of the index. That is, it always remains in this deferred mode.

Background Load of a Regular or Deferred Index

A permanent (as compared to a temporary) index can be created as a regular or permanent deferred index with the data file open in shared mode by setting the dxtdsiz field of the IFIL parameter passed to PRMIIDX() to one of the following modes:

1. ctNO_IDX_BUILD

   If using ctNO_IDX_BUILD, the index can be loaded later by either:

   1. Calling RBLIIDX() with the data file open in exclusive mode (which is already supported), or
   2. Calling ctDeferredIndexControl() with opcode of DFKCTLqueueload to queue the index load to the background index load thread.
2. ctQUEUE_IDX_BUILD

   With ctQUEUE_IDX_BUILD defined, the index load is immediately queued to a background index load thread after the index is created.

Note that when a regular index (non-deferred) is created with the data file open in shared mode, all connections that already have the associated data file open will internally open the new index and their ISAM record add/delete/update operations will affect this new index. This means that if an error occurs when updating the new index (for example if the add or update attempts to add a key that already exists in that index), the operation fails with that error. By contrast, updates to a deferred index that is created with the data file open in shared mode are queued to a background thread.

Background Thread Processing

For files under full transaction control (ctTRNLOG), deferred operations are written to transaction logs. A background thread reads the operations directly from the transaction log, calling and optional callback function, and optionally applies the operations to any deferred indices.

For atomicity only files (ctPREIMG) and non-transaction-controlled files, deferred operations are written to an in-memory queue. A background thread reads operations from the queue, calling an optional callback function, and optionally applies the operations to any deferred indices.

Note: Deferred Index features are not available for older compilers: Visual Studio VS2005 and older.

Transaction Log Limit for Deferred Indexing

Note: This is a Compatibility Change for FairCom DB V11.5 and later.

The deferred index processing thread for TRNLOG files reads transaction logs and informs the server of their minimum transaction log requirements.

FairCom DB Server now limits by default the number of active transaction logs that are kept for deferred index processing to 50. This default was chosen to avoid potentially running out of critical drive space.

The following configuration option was introduced for controlling this limit:

• MAX_DFRIDX_LOGS <max_logs> - Maximum number of logs to be held specifically for the deferred indexing thread.

This configuration setting does not impact your FairCom DB Server's ability to retain any necessary logs required for Automatic Recovery.

For more information about this setting (and the related setting for Replication Agent logs), see the following in the FairCom DB Server Administrators Guide:

• Transaction log limit for replication and deferred indexing
• MAX_DFRIDX_LOGS
• MAX_REPL_LOGS

Monitoring and Controlling Deferred Indexing

A Deferred Indexing API for monitoring and controlling deferred indexing is available.

A complete Deferred Indexing utility, dfkctl, is also available with many options ready to use.

For more information, see the ctDeferredIndexControl() function.

In This Section Queuing an Index Load Counting the Number of Deferred Operations Selected Deferred Index features extended to non-deferred indexes