Saturday, October 23, 2010

JDBC Connection Pool Performance Tuning

ConnectionPools Enhance Performance

Establishing a JDBC connection with a DBMS can be very slow. If the application requires database connections that are repeatedly opened and closed, this can become a significant performance issue. WebLogic connection pools offer an efficient solution to this problem.

When WebLogic Server starts, connections from the connection pools are opened and are available to all clients. When a client closes a connection from a connection pool, the connection is returned to the pool and becomes available for other clients; the connection itself is not closed. There is little cost to opening and closing pool connections.

Prepared Statement Cache

The prepared statement cache keeps compiled SQL statements in memory, thus avoiding a round-trip to the database when the same statement is used later.

Set LRU from the drop down box from
Servicesà JDBC à Data Source à Connection Pool à Statement Cache Type

 Tuning JDBC ConnectionPool Initial Capacity

During development, it is convenient to set the value of the InitialCapacity attribute to a low number. This helps the server start up faster.
In production systems, consider setting InitialCapacity equal to the MaxCapacity so that all database connections are acquired during server start-up.

If InitialCapacity is less than MaxCapacity, the server then needs to create additional database connections when its load is increased. When the server is under load, all resources should be working to complete requests as fast as possible, rather than creating new database connections.

Tuning JDBC Connection Pool Maximum Size

The MaxCapacity attribute of the JDBCConnectionPool element allows to set the maximum number of physical database connections that a connection pool can contain. Different JDBC drivers and database servers might limit the number of possible physical connections.

In production, it is advisable that the number of connections in the pool equal the number of concurrent client sessions that require JDBC connections. The pool capacity is independent of the number of execute threads in the server. There may be many more ongoing user sessions than there are execute threads.

Tune the chunk size

A chunk is a unit of memory that the WebLogic Server network layer, both on the client and server side, uses to read data from and write data to sockets. To reduce memory allocation costs, a server instance maintains a pool of these chunks. For applications that handle large amounts of data per request, increasing the value on both the client and server sides can boost performance. The default chunk size is about 4K. Use the following properties to tune the chunk size and the chunk pool size: weblogic.Chunksize—Sets the size of a chunk (in bytes).

The primary situation in which this may need to be increased is if request sizes are large. It should be set to values that are multiples of the network's maximum transfer unit (MTU), after subtracting from the value any Ethernet or TCP header sizes. Set this parameter to the same value on the client and server.—Sets the maximum size of the chunk pool. The default value is 2048. The value may need to be increased if the server starts to allocate and discard chunks in steady state. To determine if the value needs to be increased, monitor the CPU profile or use a memory/ heap profiler for call stacks invoking the constructor

weblogic.PartitionSize—Sets the number of pool partitions used (default is 4). The chunk pool can be a source of significant lock contention as each request to access to the pool must be synchronized. Partitioning the thread pool spreads the potential for contention over more than one partition.

Tuning Connection Backlog Buffering

The AcceptBacklog attribute of the Server element in the config.xml file allows to set the number of connection requests the server will accept before refusing additional requests. 

The AcceptBacklog attribute specifies how many TCP connections can be buffered in a wait queue. This fixed size queue is populated with requests for connections that the TCP stack has received, but the application has not accepted yet. The default value is 50 and the maximum value is operating system dependant.

Select EnvironmentàServerà Configurationà Tuning from the Administration Console to enter a value for the Accept Backlog attribute.

During operations, if many connections are dropped or refused at the client, and there are no other error messages on the server, the AcceptBacklog attribute might be set too low.

No comments:

Post a Comment