Sets the number of partitions to be used for the main and temporary buffer caches.
0, 1, 2, 4, 8, 16, 32, 64
0: Sybase IQ computes the number of partitions automatically as number_of_cpus/8, rounded to the nearest power of 2, up to a maximum of 64.
1: 1 partition only; this value disables partitioning.
2 – 64: Number of partitions; must be a power of 2.
Can be set for the PUBLIC group only. Takes effect for the current database the next time you start the database server.
Partitioning the buffer cache can sometimes improve performance on systems with multiple CPUs by reducing lock contention. Normally you should rely on the value that Sybase IQ calculates automatically, which is based on the number of CPUs on your system. However, if you find that load or query performance in a multi-CPU configuration is slower than expected, you might be able to improve it by setting a different value for CACHE_PARTITIONS. See System Administration Guide: Volume 1 > Transactions and Versioning > Tools for Managing Locks > Tools for Investigating Lock Contention.
Both the number of CPUs and the platform can influence the ideal number of partitions. Experiment with different values to determine the best setting for your configuration.
The value you set for CACHE_PARTITIONS applies to both the main and temp buffer caches. The absolute maximum number of partitions is 64, for each buffer cache.
The -iqpartition server option sets the partition limit at the server level. If -iqpartition is specified at server start-up, it always overrides the CACHE_PARTITIONS setting. See the Utility Guide.
The number of partitions does not affect other buffer cache settings. It also does not affect statistics collected by the IQ monitor; statistics for all partitions are rolled up and reported as a single value.
In a system with 100 CPUs, if you do not set CACHE_PARTITIONS, Sybase IQ automatically sets the number of partitions to 16:
100 cpus/8 = 12, rounded to 16.
With this setting, there are 16 partitions for the main cache and 16 partitions for the temp cache.
In the same system with 100 CPUs, to explicitly set the number of partitions to 8, specify:
SET OPTION "PUBLIC".CACHE_PARTITIONS=8