SimbaEngine X SDK 10.1.11
Developing Drivers for Data Stores Without SQL
SQL Engine Memory Management
The SQL Engine component of the SimbaEngine X SDK allows applications to execute SQL commands on data stores that are not SQL-capable. Performing these operations often requires the SQL Engine to cache the data in memory; for example, to execute an ORDER BY command, the SQL engine must retrieve all the requested data, allocate memory to perform the sort, then return the result. Many SQL commands utilize a significant amount of memory when executed against large data sets.
You can design your custom driver for optimum behavior and performance during memory-intensive operations by configuring how the SQL Engine uses memory and disk resources. For example, you may decide that drivers deployed in the cloud should not use on-disk memory. You can also design your custom driver for maximum speed given a single memory-intensive operation, or you can choose to share memory resources between multiple operations.
Tip:
If you see a Memory Management error or the performance is affected, you can try increasing the memory manager limit. Add or edit the MemoryManagerMemoryLimit key in your simba.ini file or registry setting, and increase the limit.
Configuring Memory Properties
You can use DSI properties to configure the SQL Engine memory strategy. The DSIDriver class defines default values for the memory properties in DSIDriver.h. It also reads in values for these properties from the registry on Windows or from .ini files on Linux and Unix. Properties set in the registry or .ini files will override the properties set programmatically. If your driver extends the DSIDriver class, you can use this functionality to set the memory properties.
Note:
The MemoryManager object is a singleton class and sets the memory strategy for all connections and statements of one driver instance. You cannot set a memory strategy per connection. MemoryManager is instantiated the first time the SQL Engine is required. If you decide to specify memory configuration properties on the connection string, be aware that the first connection may not preceded the instantiation of the MemoryManager object.
The memory properties are set in the Simba Setting Reader location. For example, this location for the QuickStart driver is:
- In the Windows registry, HKEY_LOCAL_MACHINE\SOFTWARE\Simba\Quickstart\Driver (for a 64-bit driver on a 64-bit machine, or a 32-bit driver on a 32-bit machine)
- On Linux or Unix,
simba.quickstart.ini
Memory Properties
Use the properties in this section to configure your driver's memory strategy. Each DSI property has a corresponding key in the registry or .ini file.
For example, you can set the default value of DSI_MEM_MANAGER_STRATEGY property in your MyDSIDriver.h file. If the corresponding registry keyHKEY_LOCAL_MACHINE\SOFTWARE\Simba\Quickstart\Driver\MemoryManagerStrategy is set, that value will override the default value.
DSI_MEM_MANAGER_STRATEGY
| Type | Description | Key Name |
|---|---|---|
|
UInt16 |
Specifies the memory strategy. Defines whether the SQL Engine can swap memory to disk, and whether to maximize the performance of fewer operations or to support more concurrent operations. Allowed Values: 1, 2, or 3. See SQL Engine Memory Management. |
MemoryManagerStrategy |
DSI_MEM_MANAGER_MEMORY_LIMIT
| Type | Description | Key Name |
|---|---|---|
|
UIntNative |
Specifies the total amount of memory, in megabytes, that the SQL Engine can use when executing commands. The default value depends on the operating system’s bitness. The value is 1GB on 32-bit machines and 2GB on 64-bit machines. Note: Memory management is only performed for operations that consume memory. The SQL Engine will not allocate more memory (RAM) for these operations than is specified by this limit. However, the internal variables and data members of these memory-controlled algorithms are not included in this limit. |
MemoryManagerMemoryLimit |
DSI_MEM_MANAGER_THRESHOLD_PERCENT
| Type | Description | Key Name |
|---|---|---|
|
UInt16 |
Specifies the maximum percentage of the memory limit, specified by DSI_MEM_MANAGER_MEMORY_LIMIT, that can be used by existing operations. Allowed Values: An integer between 1 and 100. The default value is 80. |
MemoryManagerThresholdPercent |
DSI_MEM_MANAGER_SWAP_DISK_LIMIT
| Type | Description | Key Name |
|---|---|---|
|
UIntNative |
Specifies the maximum size of all the swap files on disk, in megabytes. The default value is no limit. |
MemoryManagerSwapDiskLimit |
SwapFilePath
| Type | Description | Key Name |
|---|---|---|
|
String |
Specifies the full path to the directory where the swap files are located. By default, this is set to the default temporary directory of the operating system. You can get and set this property using Allowed Value: Any valid directory path. Note:
|
SwapFilePath |
For information on how to configure these properties in a driver, see SQL Engine Memory Management.
SQL Engine Memory Strategy
In general, when the SQL Engine receives a command, it will make a request to allocate memory (RAM) to complete the operation. As the operation requires more memory, the SQL Engine will continue to request that more memory be allocated. At some point in the operation, the SQL Engine may decide to stop allocating more RAM and start swapping to disk, so that it can reserve more memory for new operations. The memory strategy, specified by DSI_MEM_MANAGER_STRATEGY, specifies the tradeoff between allocating more RAM for the current operation and reserving more RAM for new operations. It also specifies whether or not to swap memory to disk.
Note:
- Because the SQL Engine allocates memory before executing a query, it can return a memory error immediately if the requested memory is larger than the memory limit. However, if the requested memory is within the memory limits but currently not available, due to other consumers consuming memory (e.g. in a multi-threaded scenario) the memory manager makes the requester wait until enough memory becomes available.
- Memory swapping increases the amount of available memory and enables commands on very large data sets; however, writing to disk also negatively affects performance.
The SQL Engine supports three memory configuration strategies.
Memory Manager Strategy 1
When this memory strategy is used, the SQL Engine does not swap memory. When executing commands, memory is allocated up to the limit specified by DSI_MEM_MANAGER_MEMORY_LIMIT. If this amount of memory is insufficient, the command will be terminated with an out of memory error.
This strategy is useful if the driver should not write to disk, for example in cloud deployments.
Memory Manager Strategy 2
When this memory strategy is used, the SQL Engine prevents any single operation from using all the available memory. If a single operation uses a significant percentage of the memory limit and then requests more memory, the SQL Engine may swap to disk rather than allocate more memory. This ensures that enough memory is available for new commands to be processed with good performance. The SQL Engine's internal logic, which takes into account the value of DSI_MEM_MANAGER_THRESHOLD_PERCENT, determines the percentage of the memory limit that a single operation can use.
Use this strategy to ensure that a driver can always handle multiple commands with good performance.
Memory Manager Strategy 3
When this memory strategy is used, the SQL Engine allows a single command to access all the available memory, up to the limit specified by DSI_MEM_MANAGER_THRESHOLD_PERCENT. This strategy ensure maximum performance of a single command, while allowing subsequent commands to be processed with slower performance.
The SQL Engine reserves a percentage of the memory limit, specified by DSI_MEM_MANAGER_THRESHOLD_PERCENT, for new operations. Each new operation received after the DSI_MEM_MANAGER_THRESHOLD_PERCENT is reached is allocated only the minimum amount of RAM, and uses disk swapping.
Use this strategy to ensure that a driver can execute a single command with maximum performance.
Example:
Assume the following:
DSI_MEM_MANAGER_STRATEGY = 3
DSI_MEM_MANAGER_THRESHOLD_PERCENT = 80 /* percent */
DSI_MEM_MANAGER_MEMORY_LIMIT = 8 /* megabytes */
Suppose the SQL Engine is executing a single query against a very large data store. Once the memory allocated for this query reaches 6.4 megabytes, the SQL Engine uses memory swapping to complete the query. While executing the first query, the SQL Engine receives more queries. For each of the subsequent queries, SQL Engine allocates only the minimum amount of memory required and then uses memory swapping. All queries complete. The performance of the first query is maximized while the performance of the subsequent queries is affected.
