After you determine how much physical memory the operating system and other applications require, calculate how much of the remaining memory is required by Sybase IQ.
For UNIX systems, databases using file systems rather than raw partitions may require another 30% of the remaining memory to handle file buffering by the operating system. On Windows, file system caching should be disabled by setting OS_FILE_CACHE_BUFFERING = ‘OFF’ (the default for new databases).
For multiuser queries of a database, Sybase IQ needs about 10MB per “active” user. Active users are defined as users who simultaneously access or query the database. For example, 30 users may be connected to Sybase IQ, but only 10 or so may be actively using a database at any one time.
Processing threads require a small amount of memory. The more Sybase IQ processing threads you use, the more memory needed. The -iqmt server switch controls the number of threads for Sybase IQ. The -iqtss and -gss server switches control the amount of stack memory allocated for each thread. The total memory allocated for IQ stacks is roughly equal to: (-gn * (-gss + -iqtss)) + (-iqmt * -iqtss ).
If you have a large number of users, the memory needed for processing threads increases. The -gn switch controls the number of tasks (both user and system requests) that the database server can execute concurrently. The -gss switch controls—in part—the stack size for server execution threads that execute these tasks. IQ calculates the stack size of these worker threads using the following formula: (-gss + -iqtss).
The total number of threads (-iqmt plus -gn) must not exceed the number allowed for your platform.
All commands and transactions use some memory. The following operations are the most significant memory users in addition to those discussed previously:
Backup. The amount of virtual memory used for backup is a function of the IQ PAGE SIZE specified when the database was created. It is approximately 2 * number of CPUs * 20 * (IQ PAGE SIZE/16). On some platforms you may be able to improve backup performance by adjusting BLOCK FACTOR in the BACKUP command, but increasing BLOCK FACTOR also increases the amount of memory used.
Database validation and repair. When you check an entire database, the sp_iqcheckdb procedure opens all Sybase IQ tables, their respective fields, and indexes before initiating any processing. Depending on the number of Sybase IQ tables and the cumulative number of columns and indexes in those tables, sp_iqcheckdb may require very little or a large amount of virtual memory. To limit the amount of memory needed, use the sp_iqcheckdb options to check or repair a single index or table.
Dropping leaked blocks. The drop leaks operation also needs to open all Sybase IQ tables, files, and indexes, so it uses as much virtual memory as sp_iqcheckdb uses when checking an entire database. It uses the Sybase IQ temp buffer cache to keep track of blocks used.