High performance WebLogicapplication and system influenced by many factors. One of important factor is good and properly JDBC data sources setting in WebLogicServer domain. Bellow check list can be considered while tuning a JDBC data source:
Setting WebLogic Connection Pool Size
Maximum Capacity
Maximum capacity is number of allocated session in database side for WebLogic Server divide by number of WebLogic server. Example, DBA allocated 500 session for WebLogic server which has 5 server, so this config value is 500/5 = 100
Initial Capacity
Initial capacity mean number of initial session created when data source started. Recommended value for Initial Capacity is equal to Maximum Capacity size database connection pools. In this case WebLogicnever increase the number of connections when it needed by application. Negative impact for this case is increase DB load although only few session use by WebLogic, but if impact in database side is not significant its work well.
Capacity Increment
There is no rule about how many session added when need more connection from application to database, but need to make sure that database can be handle this increment session login. This value should be less than 10 to increase speed of session increment.
Store the Prepared Statements in the Statement Cache
Every statement from an application or EJB, there is a processing cost at application server, database server and network communication between both. This cost can be minimizing using Statement Cacheapproach by cache statements used in application. In the next time WebLogic will be reuses the statement stored in the cache. Using cache will be improve application and system performance by reduce CPU usage in application server side and database server side.
Statement Cache Type
Two algorithm options available for maintaining the prepared statements stored in the statement cache
LRU (Least Recently Used)
LRU is default option for Statement Cache Type. In this algorithm, WebLogic Server caches statements used on the connection until the statement cache size is reached. When an application calls prepare Statement, WebLogic Server checks to see if the statement is stored in the statement cache. If cache is available (currently not being used), WebLogic Server returns the cached statement, else (statement is not in the cache, and the cache is full), WebLogic Server determines which existing statement in the cache was the least recently used and replaces that statement in the cache with the new statement.
Fixed
In Fixed algorithm, WebLogic Server caches statements used on the connection until the statement cache size is reached. When other statements are used, they are not cached, even though the statement is frequently used. Weakness of this algorithm is WebLogic will cache infrequently used statements.
In most of WebLogic implementation LRU algorithm is preferred because caches only save frequently used statements, so goal of Statement caching is reach.
Statement Cache Size
Statement Cache Size shows the total number of statements to cache for each connection in each instance of the data source. WebLogic Server can reuse statements in the cache without reloading the statements, which can increase server performance. Each connection in the connection pool has its own cache of statements.
Setting the statement cache size = 0 mean that disable statement caching. For example, if a data source with 10 connections deployed on 5servers, and setting Statement Cache Size = 10, Database maximum will be open 500 cursors (10 Conn x 2 server x size 10) on database server for the cached statements.
WebLogic statement caches related to database cursor. The statement may open a cursor in the database. If cache contains too many statements, database connection may reach maximum limit of open cursors for. Avoiding this case can be done in two approaches; increase maximum open cursors allowed for a connection or reduce Statement Cache Size setting.
The conclusion for ideal number of Statement Cache Size is depends on bellow factor:
- Number of servers which data source deployed
- Maximum connection for data source
- Maximum cursor open allowed on database side
Connection Testing Options for a Data Source
The purpose of connection testing is to make sure that the database connections in a data source in a health status healthy. Connection testing should execute periodically in a specific interval time.
There are two methods to testing a data source connection in WebLogic Server:
- Manual testing
Testing by manually execute connection testing. Usually do while trouble-shoot a data source for a problem. To do manual testing, go to Tab Monitoring, and then tab Testing. Choose data source which want to test and click “Test Data Source”. If there is no problem will be shown a notification Test of SUGI-DB on server MedRecServer was successful.
- Automatic testing
Configure options on the data source so that WebLogic Server will be periodically test to makes sure that database connections remain healthy.
Test Connections on Reserve
This option will be enables WebLogic Server to test a connection before giving it to a client application. Test connection done by WebLogic using SQL statement based on value of Test Table Name. This connection test adds a small delay in serving the client’s request for a connection from the pool, but ensures that the client receives a reliable connection.
Test Frequency
Test Frequency is time interval in seconds WebLogic Server tests unused connections. Connections that fail the test are closed, re-establish a valid physical connection to database, and test the new connection before returning it to the pool. If the test fails again, the connection is closed. If value set to 0, periodic testing is disabled.
Test Table Name
Test table name is config for SQL Statement which used when testing physical database connections. This value is required when Test Frequency value more than 0 and Test Reserved Connections is enable. The SQL code used to test a connection is depends on database provider. Best practice for this value is fastest SQL statement to the database. Example in ORACLE database, SQL SELECT 1 FROM DUAL is super-fast SQL statement which can be used in this option. Test Table Name must begin with string “SQL “, and then followed by SQL statement.
Maintain Connection to Data Source
Seconds to Trust an Idle Pool Connection
This option can minimize the impact of testing reserved connections. The appropriate value depends on system environment. Too low setting will be poor because during heavy traffic connection testing is give more loads to database, but if value is set too high will be reduce effectiveness of connection testing itself. Default value for this config is 10.
Shrink Frequency
The number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand. Shrinking means that WebLogic drop some connections from the data source when a peak usage period has ended, freeing up WebLogic Server and DBMS resources. When set to 0, shrinking is disabled.
Init SQL
Init SQL statement will be execute while WebLogic initialize newly created physical database connections in a data source. Default value is blank, but for specific purposes, this value can be set based on Database Provider Script. For example in Oracle, you can set Init SQL with alter session. Init SQL must begin with string “SQL “, and then followed by SQL statement.
Connection Creation Retry Frequency
This value represent interval in seconds between attempts to establish connections to the database. If the database is unavailable when the data source is created, WebLogic Server will attempt to create connections in the pool again after the number of seconds you specify, and will continue to attempt to create the connections until it succeeds. When set to
0, connection retry is disabled. Recommended to set this value to a desired seconds. The exact value need suggest from Database Administrator because related to database environment behavior.Login Delay
Login delay means interval in seconds before creating each physical database connection. Recommended to set the value with 0 (no delay), but if database cannot handle multiple connection requests it means that need delay during creating database connection.
Inactive Connection Timeout
Inactive connection in WebLogic Server will be releases and back into the connection pool if this value set more than 0. Recommendation for this config is specific value in seconds, so if found leaked connections that were not correctly closed by the application will be handle via this feature.
Maximum Waiting for Connection
The maximum number of connection requests that can concurrently block threads while waiting to reserve a connection from the data source’s connection pool. Leave this setting with default value (Maximum value).
Connection Reserve Timeout
When an application requests a connection from a data source, if all connections in the data source are in use and if the data source has expanded to its maximum capacity, the application will get a Connection Unavailable SQL Exception. Configure the Connection Reserve Timeout value (in seconds) will be affected that connection requests will wait for a connection to become available. After the Connection Reserve Timeout has expired, if no connection becomes available, the request will fail and the application will get a PoolLimitSQLException exception. To set connection request will timeout immediately, set value = -1. To set a connection request will wait until connection available, set value = 0. Fair value is 10 seconds by default.
Statement Timeout
Statement currently being executed will time out if consume execution time more than this setting. Set timeout via this setting will impacted to every statement in this data source. It recommended that setting timeout in application adapter depends on every statement. But also recommended to set this value also with upper limit of timeout define by business process to anticipate application adapter with no timeout.
Ignore In-Use Connections
This option enables the data source to be shutdown even if connections obtained from the pool are still in use. Enable or disable this option depends on policy in the system, but default value is enable.
Pinned-To-Thread
PinnedToThread is an option that can improve performance by enabling execute threads to keep a pooled database connection even after the application closes the logical connection. Default value is disable. This option can be enable and will be useful if implemented with correct calculation between WebLogicresource and database resource because related to reserved thread.
Remove Infected Connections Enabled
Default and safe value is enable which means the physical connection is not returned to the connection pool after the application closes the logical connection. Instead, the physical connection is closed and recreated.
