Sybase 12.4.2 manual Adaptive Server IQ main and temp buffer caches, 425

CHAPTER 12 Managing System Resources

Adaptive Server IQ main and temp buffer caches

After determining how much overhead memory Adaptive Server IQ needs, you must decide how to split what’s left between your main IQ and temp buffer caches. The dashed line dividing the two areas in Figure 12-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.

Note These guidelines assume you have one active database on your system at a time (that is, any Adaptive Server IQ users are accessing only one database). If you have more than one, you need to further split the remaining memory among the databases you expect to use.

It is highly recommended that you start with the general guidelines presented here and watch the performance of Adaptive Server IQ by using its monitor tool (described in “Monitoring the buffer caches” on page 467) and any specific tools described in the Adaptive Server IQ Installation and Configuration Guide for your platform.

Buffer caches and physical memory

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, Adaptive Server 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 defaults. The default sizes are 8MB for the main cache and 4MB for the temporary cache.

Note 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” on page 434 for more information.

425