After determining how much physical memory the operating system and other applications use, you can calculate how much of the remaining memory Sybase IQ requires to do its tasks. The factors that affect this overhead are described in the following sections.
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 more information, see the Installation and Configuration Guide for your platform.
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.
Sybase IQ also requires a portion of memory separate from the buffer caches to perform load operations, synchronization, and deletions. This memory is used for buffering I/O for flat files. Sybase IQ uses memory to buffer a read from disk. The size of this read equals the BLOCK FACTOR multiplied by the size of the input record. BLOCK FACTOR is an option of the LOAD TABLE command. With the default value of 10,000, an input row of data of 200 bytes results in 2MB total that Sybase IQ uses for buffering I/O. Memory requirements for a load are determined by the number and width of columns, not the number of rows.
This memory is required only when loading from flat files, using INSERT..LOCATION, or INSERT..SELECT. A relatively small amount of memory is needed for deletions and updates.
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 server switch controls the amount of stack memory allocated for each thread. The total memory allocated for IQ stacks is the product of -iqmt and iqtss values.
If you have a large number of users, the memory needed for catalog store processing threads also increases, although it is still relatively small. The -gn switch controls catalog store processing threads.
The total number of threads (-iqmt plus -gn) must not exceed the number allowed for your platform. For details, see Chapter 1, “Running the Database Server,” in Utility Guide.
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. See“Increasing memory used during backup” in Chapter 12, “Data Backup, Recovery, and Archiving,” in System Administration Guide: Volume 1.
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.
