Data sources have 10 connections by default. For applications with many clients, this number is often too small. For lightly used data sources, you can lower the size to free up memory and network connections that would be wasted by rarely used database connections. To tune the pool size, monitor the data source statistics as described in Chapter 11, “Runtime Monitoring,” in the EAServer System Administration Guide. Tune the pool size by setting the properties listed in Table 6-1.
Property |
Description |
|---|---|
Initial Pool Size (initialPoolSize) |
The initial number of pooled connections, allocated at server start-up. If not set, the default is 0. |
Minimum Pool Size (minPoolSize) |
The minimum number of connections in the pool. When open connections are idle, the pool is pruned to this size. The default is zero. If no minimum size is specified, EAServer opens connections as-needed to fill the pool up to the maximum size. |
Maximum Pool Size |
The maximum number of connections allocated to the pool for this data source. If the maximum is exceeded, and cannot be resolved by waiting; for example, if deadlock occurs, you may see “ResourceMonitorTimeoutException” errors in your log file, and some transactions may fail. A value of zero sets no limit to the connection pool size. |
Maximum Wait Time |
The maximum number of seconds to wait for a connection before the request is cancelled. |
Maximum Idle Time |
Specifies the number of seconds an idle connection remains in the pool before it is dropped. The default is 60 seconds. Idle connections are dropped until the minimum pool size is reached.
|
Maximum Cached Statements |
The maximum number of JDBC prepared statements that can be cached for each connection by the JDBC driver. The value of this property is JDBC-driver specific. |
Set the pool size so the majority of database connections are taken from the data source. You can tune the minimum pool size and idle time parameters to reduce the number of database connections that are held during off-peak hours. You can raise the maximum size if you see many failed connection requests or waits.
Figure 6-1 illustrates how these settings affect the growth of the data source pool size.
Figure 6-1: Data source pool growth patterns
When the server starts, it preallocates the specified inital number of connections, allowing faster response times to the initial client requests that require a database connection.
If all connections are in use simultaneously, but the pool size is below the maximum, the pool manager creates new connections to satisfy demand. When released, these connections are added to the pool, causing it to grow towards its maximum pool size.
During peak use, requests may arrive when the pool contains the maximum number of connections, but all connections are in use. When this happens, the requesting component waits. If the wait time exceeds the configured maximum (Maximum Wait Time), the request fails with exception com.sybase.djc.util.ResourceMonitorTimeoutException.
When the activity level drops, the pool manager removes idle connections if you have configured an idle connection timeout (Maximum Idle Time). The pool size drops back down to the minimum.
If the idle timeout is set to 0, connections are not
dropped and can remain in the pool until the server shuts down.