Data Source Connection Pool Sizing
- by Steve Felts
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman","serif";}
One of the most time-consuming procedures of a database
application is establishing a connection. The connection pooling of the data
source can be used to minimize this overhead. That argues for using the
data source instead of accessing the database driver directly.
Configuring the size of the pool in the data source is
somewhere between an art and science – this article will try to move it closer
to science.
From the beginning, WLS data source has had an initial
capacity and a maximum capacity configuration values. When the system
starts up and when it shrinks, initial capacity is used. The pool can
grow to maximum capacity. Customers found that they might want to set the
initial capacity to 0 (more on that later) but didn’t want the pool to shrink
to 0. In WLS 10.3.6, we added minimum capacity to specify the lower limit
to which a pool will shrink. If minimum capacity is not set, it defaults
to the initial capacity for upward compatibility. We also did some
work on the shrinking in release 10.3.4 to reduce thrashing; the algorithm that
used to shrink to the maximum of the currently used connections or the initial
capacity (basically the unused connections were all released) was changed to
shrink by half of the unused connections.
The simple approach to sizing the pool is to set the
initial/minimum capacity to the maximum capacity. Doing this creates all
connections at startup, avoiding creating connections on demand and the pool is
stable. However, there are a number of reasons not to take this simple approach.
When WLS is booted, the deployment of the data source
includes synchronously creating the connections. The more connections
that are configured in initial capacity, the longer the boot time for WLS
(there have been several projects for parallel boot in WLS but none that are
available). Related to creating a lot of connections at boot time is the
problem of logon storms (the database gets too much work at one time).
WLS has a solution for that by setting the login delay seconds on
the pool but that also increases the boot time.
There are a number of cases where it is desirable to set the
initial capacity to 0. By doing that, the overhead of creating
connections is deferred out of the boot and the database doesn’t need to be
available. An application may not want WLS to automatically connect to
the database until it is actually needed, such as for some code/warm failover
configurations.
There are a number of cases where minimum capacity should be
less than maximum capacity. Connections are generally expensive to keep
around. They cause state to be kept on both the client and the server,
and the state on the backend may be heavy (for example, a process).
Depending on the vendor, connection usage may cost money. If work load is
not constant, then database connections can be freed up by shrinking the pool
when connections are not in use. When using Active GridLink, connections
can be created as needed according to runtime load balancing (RLB) percentages
instead of by connection load balancing (CLB) during data source deployment.
Shrinking is an effective technique for clearing the pool
when connections are not in use. In addition to the obvious reason that
there times where the workload is lighter, there are some configurations
where the database and/or firewall conspire to make long-unused or too-old
connections no longer viable. There are also some data source features
where the connection has state and cannot be used again unless the state
matches the request. Examples of this are identity based pooling where
the connection has a particular owner and XA affinity where the connection is
associated with a particular RAC node. At this point, WLS does not
re-purpose (discard/replace) connections and shrinking is a way to get rid of
the unused existing connection and get a new one with the correct state when
needed.
So far, the discussion has focused on the relationship of
initial, minimum, and maximum capacity. Computing the maximum size
requires some knowledge about the application and the current number of
simultaneously active users, web sessions, batch programs, or whatever access
patterns are common. The applications should be written to only reserve
and close connections as needed but multiple statements, if needed, should be
done in one reservation (don’t get/close more often than necessary). This means that the size of the pool is likely to be significantly smaller then the number of users.
If
possible, you can pick a size and see how it performs under simulated or real
load. There is a high-water mark statistic (ActiveConnectionsHighCount)
that tracks the maximum connections concurrently used. In general, you
want the size to be big enough so that you never run out of connections but no
bigger. It will need to deal with spikes in usage, which is where
shrinking after the spike is important. Of course, the database capacity
also has a big influence on the decision since it’s important not to overload
the database machine. Planning also needs to happen if you are running in
a Multi-Data Source or Active GridLink configuration and expect that the remaining
nodes will take over the connections when one of the nodes in the cluster goes
down. For XA affinity, additional headroom is also recommended.
In summary, setting initial and maximum capacity to be the
same may be simple but there are many other factors that may be important in
making the decision about sizing.
