After determining how much overhead memory Sybase IQ needs, you must decide how to split what is left between your main Sybase IQ and temp buffer caches. The dashed line dividing the two areas in Figure 5-1 indicates that this split may change from one database to another based on several factors.
Unlike most other databases, the general rule of thumb for Sybase IQ is a split of 40% for the main buffer cache and 60% for temp buffer cache. This rule of thumb, however, is only a start. While some operations, such as queries with large sort-merge joins or inserts involving HG indexes, may require a temp buffer cache larger than main, other applications might have different needs. Sybase 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”) and any specific tools described in the Sybase IQ Installation and Configuration Guide for your platform.
The total memory used for Sybase IQ main and temporary buffer caches, plus Sybase 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.
