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 4-1 indicates that this split may change from one database to another based on several factors.
Unlike most other databases, the general guideline for Sybase IQ is a split of 40% for the main buffer cache and 60% for temp buffer cache. This guideline, 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.
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 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.
In almost every case, the default temporary buffer cache size of 8MB is too low. For optimal performance, allocate as much memory as possible to the IQ main and temporary buffer caches. For example, if you have 4GB of physical memory on your machine available to Sybase IQ, you can split that amount between the main and temporary shared buffer caches.
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 use. 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.
The buffer cache and memory overhead guidelines also may differ between platforms. See your Installation and Configuration Guide for any other issues.
