Sybase IQ command line option changes for large numbers of users

The following start_asiq switches affect operations with large numbers of users:

-gm #_connections_to_support

-iqgovern #_ ACTIVE_ queries_to_support

-gn #_Catalog_Store_front_end_threads

-c Catalog_Store_cache_size

-ch size

-cl size

-gm

This is the total number of connections the server will support. Statistically, some of these are expected to be connected and idle while others are connected and actively using the database.

-iqgovern

Although 1000 users can be connected to Sybase IQ, for best throughput you should ensure that far fewer users are allowed to query at once, so that each of them has sufficient resources to be productive. The -iqgovern value places a ceiling on the maximum number of queries to execute at once. If more users than the -iqgovern limit have submitted queries, new queries will be queued until one of the active queries is finished.

The optimal value for -iqgovern depends on the nature of your queries, number of CPUs, and size of the Sybase IQ buffer cache. The default value is 2*numCPU + 10. With a large number of connected users, you may find that setting this option to 2*numCPU + 4 provides better throughput.

-gn

The correct value for -gn depends on the value of -gm. The start_asiq utility calculates -gn and sets it appropriately. Setting -gn too low can prevent the server from operating correctly. Setting -gn above 480 is not recommended.

-c

The Catalog Store buffer cache is also the general memory pool for the Catalog Store. To specify in MB, use the form -c nM, for example, -c 64M. Sybase recommends these values:

Table 5-5: Catalog buffer cache settings

For this many users

On these platforms

Set -c to this minimum value or higher

up to 1000

64-bit only

64MB

up to 200

64-bit

48MB (start_asiq default for 64-bit); larger numbers of users may benefit from 64MB

up to 200

32-bit

32MB (start_asiq default for 32-bit)

In some cases the standard Catalog cache size may be too small, for example, to accommodate certain queries that need a lot of parsing. In these cases, you may find it helpful to set -cl and -ch. For example, on 32-bit platforms, try these settings

-cl 128M
-ch 256M

Do not use -c in the same configuration file or command line with -ch or -cl. For related information, see the -ch cache-size option.

WARNING!  To control Catalog Store cache size explicitly, you must do either of the following, but not both, in your configuration file (.cfg) or on the UNIX command line for server startup:

Specifying different combinations of the parameters above can produce unexpected results.

-iqmt

You do not need to set the -iqmt option. If -iqmt is set too low for the number of specified connections, the number of threads will be increased to handle the number of requested connections. That is, -gm overrides -iqmt. However, if the number of Sybase IQ threads is elevated by means of the -iqmt option then that factor needs to be used in setting limits, as described in “Setting operating system parameters for large numbers of users.”