MPX_MAX_CONNECTION_POOL_SIZE Option

1 to 1000

10

Option can be set at the database (PUBLIC) level only.

Requires the SET ANY SYSTEM OPTION system privilege to set this option. Setting takes effect immediately and persists across server restarts.

INC connections are inter-server connections between secondary nodes and the coordinator node. An INC connection is associated with each user connection on a secondary server doing a DDL or read-write operation. The connection is active until that command commits or rolls back; it then returns to the pool. If these transactions are short lived, then the default setting of MPX_MAX_CONNECTION_POOL_SIZE suffices for many user connections running DDL or RW operations. If many concurrent connections run DDL or read-write operations, or the transactions take a long time, increase the value of MPX_MAX_CONNECTION_POOL_SIZE. For example, increase the value when many user connections do concurrent loads without committing.

Exceeding MPX_MAX_CONNECTION_POOL_SIZE returns SQL Anywhere Error -1004000: The number of connections in the connection pool have exceeded the upper limit.

To estimate the pool size required, consider the setting of the -gm server option. The -gm setting indicates how many users can connect to the secondary server; the INC connections are not included, but will add to this number. Use application requirements to assess how many read-write or DDL operations are likely to occur per user, and increase the pool size accordingly.

Each connection (INC or user) carries a memory overhead depending on -gn setting and number of cores. The burden of memory and thread contention may affect SAP IQ server response times.