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.
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:
If using ctNO_IDX_BUILD, the index can be loaded later by either:
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:
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:
Monitoring and Controlling Deferred Indexing
A Deferred Indexing API for monitoring and controlling deferred indexing is available.
For more information, see the ctDeferredIndexControl() function.