Sybase IQ uses memory for a variety of purposes:
Buffers for data read from disk to resolve queries
Buffers for data read from disk when loading from flat files
Overhead for managing connections, transactions, buffers, and database objects
The options discussed below, as well as other options you can set once the server is running, determine how much memory is available for these purposes.
The default IQ buffer cache sizes of 16MB for the main cache and 8MB for the temporary cache are too low for any active database use. You need to set the buffer cache sizes for the IQ main and temporary stores in one of two ways:
To set buffer cache sizes server-wide for the current server session, specify the server startup options -iqmc (main cache size) and -iqtc (temp cache size). Recommended method.
To set cache sizes for a database, you can use the SET OPTION command to set the Main_Cache_Memory_MB and Temp_Cache_Memory_MB database options. This method only lets you set values less than 4GB.
In both cases, you must restart the server for the new cache sizes to take effect. If you set IQ buffer cache sizes higher than your system will accommodate, however, Sybase IQ cannot open the database.
The server options (-iqmc and -iqtc) override the database option settings. They also let you use as much memory as your system allows, the only limit being the amount of physical memory on the machine. For this reason, on 64-bit systems you should use -iqmc and -iqtc.
The cache sizes set by -iqmc and -iqtc apply to all databases started until the server is shut down. So for example, if you set both -iqmc and -iqtc to 500 (MB) and start one database at server startup and another database later on the same server, you need at least 2GB available for the two main and two temp caches.
Your license sets the absolute number of concurrent users. However, you must also set the -gm switch. This required switch lets you limit the number of concurrent user connections on a particular server.
The -gn switch sets the number of execution threads that will be used for the Catalog Store and connectivity while running with multiple users. It applies to all operating systems and servers.
On Windows, start_asiq calculates the value of this parameter and sets it using the following formula:
gn_value = gm_value + 5)
Specify a minimum of 25.
On UNIX platforms, see the Sybase IQ Installation and Configuration Guide for your platform for more information.
There may be times when you want to tune performance for a particular operation by limiting the number of user connections to fewer than your license allows. Alternatively, you may want to use the -iqgovern switch to control query use; see “Concurrent queries.”
The -iqgovern switch lets you specify the number of concurrent queries on a particular server. This is not the same as the number of connections, which is controlled by your license. By specifying the -iqgovern switch, you can help IQ optimize paging of buffer data out to disk, and avoid overcommitting memory. The default value of -iqgovern is (2 x the number of CPUs) + 10. You may need to experiment to find an ideal value. For sites with large numbers of active connections, try setting -iqgovern slightly lower.
The -iqwmem switch creates a pool of “wired” memory on certain UNIX platforms only. For details, see “Platform-specific memory options” in Sybase IQ Performance and Tuning Guide.
WARNING! Use this switch only if you have enough memory to dedicate some of it for this purpose. Otherwise, you can cause serious performance degradation.
Use the -iqmt switch to set the number of processing threads that Sybase IQ can use. Sybase IQ assigns varying numbers of kernel threads to each user connection, based on the type of processing being done by that process, the total number of threads available, and the setting of various options. Increasing the number of threads can improve performance.
Use the -c switch to set the amount of memory in the cache for the Catalog Store.
The start_asiq command, and the asiqdemo.cfg and default.cfg configuration files set the -c parameter to 48MB on 64-bit systems and 32MB on 32-bit systems. Sybase recommends using one of these methods.
If you start the server without using start_asiq, asiqdemo.cfg or default.cfg , the default initial cache size is computed based on the amount of physical memory, the operating system, and the size of the database files. The database server takes additional cache for the Catalog when the available cache is exhausted.
Any cache size less than 10000 is assumed to be in KB (1K =1024 bytes); any cache size 10000 or greater is assumed to be in bytes. You can also specify the cache size as nK or nM.
WARNING! To control Catalog Store cache size, you must do either of the following, but not both, in your configuration file (.cfg) or on the UNIX command line for server startup:
Set the -c parameter
Set specific upper and lower limits for the Catalog Store cache size using the -cl and -ch parameters
Do not specify other combinations of these parameters.
The cache size for the IQ Store does not rely on the
Catalog cache size. See “IQ buffer cache sizes”.
For more information on setting the Catalog cache size, see the -c, -ca, -ch, and -cl server options in “Starting the database server” in Sybase IQ Utility Guide.
