![CHAINED option [TSQL]](arrow-right.gif){kind=icon}
Sets the number of partitions to be used for the main and temporary buffer caches.
0, 1, 2, 4, 8, 16, 32, 64:
Value |
Description |
|---|---|
0 |
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.
0 (IQ computes the number of partitions automatically).
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 may be able to improve it by setting a different value for CACHE_PARTITIONS.
Both the number of CPUs and the platform can influence the ideal number of partitions. You may need to 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 startup, it always overrides the CACHE_PARTITIONS setting.
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, IQ automatically sets the number of partitions to 16 as follows:
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
-iqpartition in the section “Server command-line options” on page 7 in Chapter 1, “Running the Database Server” of the Sybase IQ Utility Guide.
“Managing lock contention” in Chapter 10, “Transactions and Versioning” of the Sybase IQ System Administration Guide
