After determining how much overhead memory Sybase IQ needs, you must decide how to split what is left between your main IQ and temp buffer caches. The dashed line dividing the two areas in Figure 4-1 indicates that this split may change from one database to another based on several factors.
The general rule of thumb for IQ, unlike most other databases, is a split of about 40% for the main buffer cache and 60% for temp buffer cache. However, this rule of thumb is only a beginning. While some operations, such as queries with large sort-merge joins or inserts involving HG indexes, may require a larger temp buffer cache than main, other applications can have entirely different needs. IQ supports memory allocation ratios from 30/70 to 70/30, main to temp.
These guidelines assume you have one active database
on your system at a time (that is, any Sybase IQ users are accessing only
one database). If you have more than one active database, you need
to further split the remaining memory among the databases you expect
to use.
Sybase strongly recommends that you start with the general guidelines presented here and watch the performance of Sybase IQ by using its monitor tool (described in “Monitoring the buffer caches” on page 562) and any specific tools described in the Sybase IQ Installation and Configuration Guide for your platform.
The total memory used for IQ main and temporary buffer caches, plus IQ memory overhead, and memory used for the operating system and other applications, must not exceed the physical memory on your system.
If you set buffer cache sizes higher than your system will accommodate, Sybase IQ cannot open the database. Specify the server startup options -iqmc (main cache size) and -iqtc (temp cache size) to open the database and reset the cache sizes from their default values, which are only 16MB for the main cache and 12MB for the temporary cache.
On some UNIX platforms, you may need to set other server
switches to make more memory available for buffer caches. See “Platform-specific memory options” for more
information.
Sybase IQ buffer cache sizes may differ from one database to the next based on how you use it. For maximum performance, you need to change the settings between inserting, querying the database, and mixed use. In a mixed-use environment, however, it is not always feasible to require all users to exit the database so that you can reset buffer cache options. In those cases, you may need to favor either load or query performance. When possible, define the cache sizes before doing any work in the database.
The buffer cache and memory overhead guidelines also may differ between platforms. See your Sybase IQ Installation and Configuration Guide for any other issues.
