SQL Operations Guide

Memory Manager (MM) System for Temporary Storage

For queries involving sorting and/or joins, FairCom DB SQL may create temporary tables which exist in memory and/or on disk, depending on the amount of data in the temporary table. FairCom DB SQL provides options to optimize these queries with regard to physical memory space available on the server. Tuning these options can greatly enhance performance when dealing with large result sets generating extremely large temporary tables.

An internal memory storage system provides a mechanism for FairCom DB SQL to store data in memory instead of on disk. By using this internal storage system for volatile data such as temporary tables and dynamic indexes, the FairCom DB SQL improves the performance of many queries, such as joins. Depending on the amount of memory available on a system, certain queries may create temporary tables too large to be stored in memory. In these cases, the internal storage system swaps blocks of data to a disk file as necessary. The following variables allow implementations to control the characteristics of how this internal storage system uses memory to create temporary tables.

• SETENV TPE_MM_CACHESIZE: Specifies the size, in kilobytes, of the memory cache used for temporary tables. The default value is 1,000 KB (1MB) of memory. The internal storage system uses this cache for storing temporary tables when sorting and creating dynamic indexes during processing. Increasing TPE_MM_CACHESIZE improves performance by reducing the need to write to the on-disk swap file.

  The TPE_MM_CACHESIZE setting determines how much memory the SQL engine uses for its temporary tables for each SQL client. Increasing this setting increases the amount of temporary table data the FairCom DB SQL stores in memory instead of writing this data to the disk-based swap file. However, note that this memory is allocated for each SQL client, so you should consider how many clients will connect to FairCom DB SQL at any given time and make sure total cache memory doesn't exceed available physical memory on the system.

  Up to 4 TB total memory is available in this subsystem.

• SETENV TPE_MM_SWAPSIZE: Specifies the maximum size, in kilobytes, of the swap file the internal storage system uses when it writes to disk from the main memory cache. The default is 500,000 KB (500 MB). The sorting of data larger than this size results in the following FairCom DB SQL error

  (-16001):MM No data block

  To resolve this error, the TPE_MM_SWAPSIZE limit needs to be increased.

  Note: These swap files can be found during an active connection with a filename beginning with ‘M’ followed by a string of random alphanumerics. These files can sometimes be left around after an abnormal server shutdown. They can be safely deleted in such a case.

  This keyword can be used to resolve error (-16025):MM sorting error.

Increasing the FairCom DB cache configurations DAT_MEMORY (data cache size) and IDX_MEMORY (index cache size) settings can significantly reduce the number of bytes read from disk while executing a query.

Note: The MM subsystem was replaced with a new architecture beginning with V10.3. See SQL_OPTION USE_MM.