Connection pooling

What is the purpose of connection pooling?

Establishing a database connection is typically processor-intensive. The use of connection pools allows us to maintain a set of open connections for use by database requests. These connections can then be allocated to short duration SQL requests and then immediately returned to the pool for re-use.

How does connection pooling work?

The Sterling Configurator Visual Modeler maintains these logical pools of connections:

• Pool of message-based connections. This pool is simply a HashMap that mates a message version to an appropriate Message-based DataService. One DataService instance is shared by all requests requiring that message version.
• Pool of database connections. This pool is used for all database requests. When a data bean persist() or restore() method is invoked, we retrieve a connection from the pool; process the operation; then return the connection to the pool for reuse.

  In past releases the Sterling Configurator Visual Modeler supported sharing a database connection across multiple concurrent requests. Not all databases are capable of supporting this functionality. In addition, performance testing has shown that an expensive SQL request can drastically impact the performance of all requests sharing the same connection. Based on these issues, the Sterling Configurator Visual Modeler has eliminated support for sharing of Query connections.

Why are there separate query and update connection cools?

The use of separate pools for Query and Update Connections allows the Sterling Configurator Visual Modeler to optimize connections for read-only access.

How do I validate connections prior to reuse?

The following properties control connection timeout and validation:

• The ConnectTimeout element provides a timeout setting for connections in the pools. The value is the number of minutes for a connection to timeout. For example, if you set this value to “1”, then if a connection has been unused for more than one minute, it is validated before being used. A setting of 0 means that connections do not timeout.
• The ReconnectOnTimeout element controls what is done when a connection timeout.
  • A setting of “true” indicates that when a connection times out it will automatically reconnect the next time it is retrieved from the pool.
  • A setting of “false” indicates that a connection timeout will result in the connection being validated prior to reuse. If the validation fails, then a reconnect will occur.

How can I limit the number of connections used?

Each DataSource specified in the DataServices.xml configuration file supports a MaxConnections property. This specifies an absolute upper limit on the number of connections that will be used. A setting of “-1” indicates there is no limit. The DataServices.xml file is contained in the WEB_INF\lib\cmgt-dataservices.jar file.

How can I free up connections when demand drops?

Each DataSource specified in the DataServices.xml configuration file also supports a MaxPoolSize property. This provides a soft limit on the number of connections that will be pooled. A setting of “-1” indicates there is no limit. The pool size is not an absolute limit, but as connections are released the pool will gradually move back down to its maximum size.

The Sterling Configurator Visual Modeler does allow the number of connections to grow beyond the maximum pool size, but when the number of free connections exceeds a preset limit we will begin releasing connections until the number of connections eventually drops back to the maximum pool size. We do this gradually to avoid excessive connection requests when pool is at the boundary.

What happens when connection limits are reached?

If a connection is requested from the pool, but no free connections are available, then we would normally create a new connection. If the connection limit is reached, then we will instead wait for a connection to be returned to the pool.

Why are the connection limits on the data source?

Providing connection limits for each data source provides greater flexibility in allocating connection resources. For example, this allows you to limit the number of connections to a back-end ERP system, while providing higher limit when accessing the primary database server.