ElevateDB uses caching and buffering algorithms internally to ensure that data is cached for as long as possible and is accessible in the fastest possible manner when needed to perform an operation. ElevateDB offers several different types of buffering, each having a specific purpose for optimizing performance: global file I/O buffering, per-session table buffering, and per-session SQL statement and function/procedure caching.

Global File I/O Buffering

---

Global file I/O buffering is used to cache as much of the configuration, log, database catalog, and table files as possible in order to maximize I/O throughput. This is accomplished by utilizing heuristics and settings that are specific to ElevateDB, allowing for more control over the caching than what is available when leaving the file caching to the operating system.


Global file I/O buffering can be enabled in ElevateDB at the engine level and specific file I/O buffering settings can be also specified at the engine level for any configuration, log, database catalog, and table files that are accessed by ElevateDB. Please see your product-specific manual for more information on enabling and configuring file I/O buffering in code for ElevateDB, and the Starting and Configuring the ElevateDB Server topic for more information on enabling and configuring file I/O buffering in the ElevateDB Server.


After global file I/O buffering has been enabled, buffering settings must also be specified so that they provide the optimal caching for your specific ElevateDB installation. You can also adjust these settings at a later time so that they stay current with any system configuration changes, such as adding more physical memory, or with any major changes to the underlying file sizes. Each buffering setting consists of the following properties:



In addition to the file mask buffering settings, there is an additional flush check interval setting that specifies how often, in seconds, ElevateDB will scan the buffer pools for each file in order to write any dirty buffers that are past their flush age to the file. The default value is 60 seconds.

As mentioned above, the file I/O buffering is susceptible to causing data loss if the process running ElevateDB is terminated unexpectedly. You can minimize the possibility of this issue for selected files by:

• specifying a low (30 seconds) flush check interval and



• specifying a low flush age (30 seconds) for all applicable files.

You can view the file I/O buffer settings, as well as current statistics for the active file I/O buffer pools, by querying the following table:

FileIOStatistics Table

File Block Buffer Replacement Policy

Any file block buffer maintained within the global file I/O buffer pool is replaced using an LRU, or least-recently-used, algorithm. For example, if the buffer pool is full when reading a file block, ElevateDB will discard the least-recently-used file block in order to make room for the new file block. The "age" of a given buffered file block is determined by the access patterns at the time. Every time a file block buffer is accessed, it is moved so it is the first file block buffer in the LRU list of file block buffers. This would make it the "youngest" buffer present in the LRU list of file block buffers, and all other file block buffers would be moved down the LRU list. As a particular file block buffer moves down the LRU list, it becomes "older" and is more likely to be removed and discarded from the LRU list of file block buffers.

Optimized Writes with File I/O Buffering

When ElevateDB writes file block buffers to a file, the file blocks are ordered according to their offset and ElevateDB attempts to write contiguous file blocks in the fewest number of write operations as possible. This reduces the number of I/O calls and can greatly improve write throughput, especially on hard disk drives that benefit from fewer drive seeks.

File I/O Buffering and OS Buffering

In addition to the file I/O buffering in ElevateDB, additional buffering may be provided by the operating system. When ElevateDB writes data using operating system calls, there is no guarantee that the data will be immediately written to disk. On the contrary, it may be several seconds or minutes until the operating system lazily flushes the data to disk. This has implications in terms of data corruption if the computer is improperly shut down after updates have taken place in ElevateDB. You can minimize the possibility of this issue for selected files by:

• specifying a low (30 seconds) flush check interval,



• specifying a low flush age (30 seconds) for all applicable files,



• specifying that all applicable files follow all flush checks with a disk flush call to the operating system if any file block buffers were written to the file.

Per-Session Table Buffering

---

At a level above the global file I/O buffering, if enabled, is the per-session table buffering. The per-session table buffering buffers rows, index pages, BLOB blocks, and published update blocks for each open table. There are separate buffer pools for each class of buffer - rows, index pages, BLOB blocks, and published update blocks. If global file I/O buffering is enabled in ElevateDB, any data that isn't available in the per-session table buffers will require a read operation to the file block buffer pool for the applicable file. If global file I/O buffering is not enabled in ElevateDB, any data that isn't available in the per-session table buffers will require a read operation to the operating system.

The amount of memory used for the per-session table buffers is typically very small and only used for improving the locality of access for rows, index pages, and BLOB/published update blocks that are being currently accessed/updated. In most cases the default memory settings for the per-session table buffers will suffice. If necessary, ElevateDB will increase the amount of memory that is being used for the table buffers for a particularly table.

The only exception to this is when an application wants to use direct access to a shared configuration and database(s) located on a file server. In such a case, one can modify the per-session table buffers so that they are larger than the default values. These modifications can be performed when the table is created via the CREATE TABLE statement, or after the table is created via the ALTER TABLE statement. The applicable clauses are as follows:

MAX ROW BUFFER SIZE <MaxRowBufferSize>
MAX INDEX BUFFER SIZE <MaxIndexBufferSize>
MAX BLOB BUFFER SIZE <MaxBLOBBufferSize>

The default amount of memory used for each is detailed below:


You can view the per-session table buffer settings, as well as current statistics for the active table buffers, by querying the following tables:


Table Buffer Replacement Policy

Any table buffer maintained within the per-session table buffer pool is replaced using an LRU, or least-recently-used, algorithm. Each class of table buffer maintains its own buffer pool. Subsequently, each buffer pool has its own LRU list. For example, if the table row buffer pool is full when reading a row, ElevateDB will discard the least-recently-used row in order to make room for the new row. The "age" of a given buffered row is determined by the access patterns at the time. Every time a row buffer is accessed, it is moved so it is the first row buffer in the LRU list of row buffers. This would make it the "youngest" buffer present in the LRU list of row buffers, and all other row buffers would be moved down the LRU list. As a particular row buffer moves down the LRU list, it becomes "older" and is more likely to be removed and discarded from the LRU list of row buffers.

Read-Ahead Buffering with Table Buffering

ElevateDB performs intelligent read-ahead when reading rows and BLOB/published update blocks:

• For read-ahead on rows, this intelligence is gathered from information in the active index for a given table when accessing a table using a specific ordering, or using raw row information for non-ordered access, and allows ElevateDB to determine how rows physically align with one another on disk.



• For read-ahead on BLOB/published update blocks, this intelligence is gathered from information in the row about the size of the BLOB, or from information about the size of the published updates.

Performing read-ahead in this manner can reduce the number of read calls that ElevateDB has to make to the global file I/O buffering or the operating system and can significantly speed up sequential read operations such as those found in SQL queries and other bulk operations.

Optimized Writes with Table Buffering

When ElevateDB writes table buffers, the table buffers are ordered according to their offset and ElevateDB attempts to write contiguous table buffers in the fewest number of write operations as possible.

Table Buffering and OS Buffering

The effect of operating system buffering on per-session table buffering depends upon whether the global file I/O buffering is enabled or not. If the global file I/O buffering is not enabled, then writes using the per-session table buffers go directly to the operating system. There are session-level settings in ElevateDB that will allow you to specify that such writes are followed by a disk flush call to the operating system. In addition, there are transaction commit options to do the same, as well as specific methods/function calls for explicitly performing disk flush calls. Please see your product-specific manual for more information on enabling session-level table buffer disk flushing or explicitly performing disk flush calls.

Per-Session SQL Statement and Function/Procedure Caching

---

At a level above both the per-session table buffering and and the global file I/O buffering is the SQL statement and function/procedure caching. A session can be configured to cache a specified maximum of SQL statements, as well a specified maximum of functions/procedures, per open database in the session.


This level of caching is used to eliminate costly prepare/unprepare cycles with SQL statements and functions/procedures without requiring the developer to explicitly keep statements and functions/procedures prepared. In a lot of cases, such as within scripts, triggers, and other forms of SQL/PSM routines in ElevateDB, it is impossible for a developer to manage the prepared state of various SQL statements and functions/procedures being used.

The SQL statement and function/procedure caching works as follows:

• Each cached SQL statement is managed using a checksum of the SQL statement, and each function/procedure is managed using a checksum of the function/procedure name. Additionally, once an object has been added to the cache, it stays present in the cache until is is ejected due to the LRU replacement policy (see below for the replacement policy details) or explicitly freed from the cache. Each cached object contains an in-use flag that is used to track whether the object can be used or whether a new object must be created. This allows the cache to work in the face of recursive triggers and other functions/procedures, and prevents the cache from incurring an inordinate amount of overhead due to constant modifications to the internal list of objects in the cache.



• When an SQL statement or function/procedure is prepared, ElevateDB checks the cache for the open database in which the SQL statement or function/procedure is being prepared. If the same SQL statement or function/procedure is already present in the cache, then ElevateDB will use the cached object instead of creating a new object. If the SQL statement or function/procedure cannot be found in the cache, then a new object is created and added to the cache. If the maximum number of cached objects has been exceeded, then the oldest (see below for the replacement policy details) cached object is ejected from the cache and freed before the new object is added to the cache.



• When an SQL statement or function/procedure is un-prepared, ElevateDB checks to see if the object was previously cached. If it was, then ElevateDB simply marks the cached object as available for re-use in the cache. If it wasn't, then the object is simply un-prepared as normal, releasing all memory and resources associated with the object.

Possible Cached SQL Statement and Function/Procedure Conflicts

Within a given session, ElevateDB automatically manages freeing cached SQL statements and functions/procedures whenever the session performs an operation that may conflict with any of the cached SQL statements and functions/procedures. This resolves situations where the same session may try to perform operations that may conflict, but does not address issues with cached SQL statements and functions/procedures that may conflict with operations being attempted by other sessions. In order to handle such situations, ElevateDB provides session-level calls that can be used to manually free any cached SQL statements and/or functions/procedures within the session. There are separate calls for both SQL statements and functions/procedures, and the calls allow you to free objects within a specific open database, or for all open databases within the session. Please see your product-specific manual for more information on performing these operations.

Cached SQL Statement and Function/Procedure Replacement Policy

Any cached SQL statement or function/procedure is maintained within a separate pool for each open database in a session. Each SQL statement or function/procedure is replaced using a LRU, or least-recently-used, algorithm.