Optimizing the Cache Partitions

Changing the CACHE_PARTITIONS value may improve load or query performance in a multi-CPU configuration.

SAP Sybase IQ automatically calculates the number of cache partitions for the buffer cache according to the number of CPUs on your system. If load or query performance in a multi-CPU configuration is slower than expected, you may be able to improve it by changing the value of the CACHE_PARTITIONS database option.

As buffers approach the Least Recently Used (LRU) end of the cache, they pass a wash marker. SAP Sybase IQ writes the oldest pages—those past the wash marker—out to disk so that the cache space they occupy can be reused. A team of SAP Sybase IQ processing threads, called sweeper threads, sweeps (writes) out the oldest buffers.

When SAP Sybase IQ needs to read a page of data into the cache, it grabs the LRU buffer. If the buffer is still “dirty” (modified) it must first be written to disk. The Gdirty column in the monitor -cache report shows the number of times the LRU buffer was grabbed dirty and SAP Sybase IQ had to write it out before using it.

Usually SAP Sybase IQ is able to keep the Gdirty value at 0. If this value is greater than 0 for more than brief periods, you may need to adjust one of the database options that control the number of sweeper threads and the wash marker.