1. 개요

성능이나 장애에 영향을 주는 WebLogic DB 연결 관련 설정이다.

2. 설정

2-1. Seconds to Trust an Idle Pool Connection

The number of seconds within the use of a pooled connection that WebLogic Server trusts that the connection is still viable and will skip connection testing.

If an application requests a connection within the time specified since the connection was tested or successfully used and returned to the connection pool, WebLogic Server skips the connection test before delivering it to an application (if TestConnectionsOnReserve is enabled).

WebLogic Server also skips the automatic refresh connection test if the connection was successfully used and returned to the connection pool within the time specified (if TestFrequencySeconds is specified).

This attribute is a tuning feature that can improve application performance by minimizing the delay caused by database connection testing, especially during heavy traffic. However, it can reduce the effectiveness of connection testing, especially if the value is set too high. The appropriate value depends on your environment and the likelihood that a connection will become defunct.

2-2. Test Frequency

The number of seconds between database connection tests. After every TestFrequencySeconds interval, unused database connections are tested using TestTableName. Connections that do not pass the test will be closed and reopened to re-establish a valid physical database connection. If the test fails again, the connection is closed.

If TestTableName is not set, the test will not be performed.

If set to 0 (the default), connections are not tested.

MBean: weblogic.management.configuration.JDBCConnectionPoolMBean

Attribute: TestFrequencySeconds

2-3. Connection Creation Retry Frequency

The number of seconds between attempts to create database connections when the connection pool is created. If you do not set this value, connection pool creation fails if the database is unavailable. If set and if the database is unavailable when the connection pool 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 (the default), this feature is disabled.

MBean: weblogic.management.configuration.JDBCConnectionPoolMBean

Attribute: ConnectionCreationRetryFrequencySeconds

2-4. oracle.net.CONNECT_TIMEOUT

2-5. Statement Cache Size

The Statement Cache Size attribute determines the total number of prepared and callable statements to cache for each connection in each instance of the data source. By caching statements, you can increase your system performance. However, you must consider how your DBMS handles open prepared and callable statements. In many cases, the DBMS will maintain a cursor for each open statement. This applies to prepared and callable statements in the statement cache. If you cache too many statements, you may exceed the limit of open cursors on your database server.

For example, if you have a data source with 10 connections deployed on 2 servers, if you set the Statement Cache Size to 10 (the default), you may open 200 (10 x 2 x 10) cursors on your database server for the cached statements.

내용을 잘 보면 each connection 당 캐시되는 수이다. WebLogic Global의 캐시 제한 숫자가 아니다.

또 주의할 점이 있는데,

  • T4CPreparedStatement의 기본 ResultSet를 베이스로 한 버퍼 크기는 <컬럼 총합 X PreparedStatement의 기본 패치 크기> 이다.
  • 만약 총 컬럼 사이즈 1000바이트에 패치 사이즈 50이라고 하면 이 값은 50,000바이트, 약 50K 가량이 된다.
  • 그래서 각 Connection당 총 100개의 PreparedStatement가 캐싱되어 있다고 하면, 각 Connection 당 약 5,000K, 즉 5MB 가량 점유하고 있는 셈이다.
  • 이것은 각 Connection 당이므로 Connection이 30개 있다고 하면 30 X 5MB = 총 150MB 가량이 된다.

이와 유사하게 JEUS에도 설정이 있다.

Stmt Caching Size

JDBC 드라이버는 애플리케이션에서 PreparedStatement 를 요청할 때마다 파라미터로 넘어온 SQL 문장을 파싱하게 된다. 이 파싱 작업이 성능에 영향을 줄 수 있기 때문에 이를 피하기 위해서 JEUS 내부적으로 PreparedStatement 를 캐시하는 기능을 제공한다. 이 설정은 캐싱할 PreparedStatement 의 개수를 지정한다.

그런데 간혹 이 설정이 -1로 되어 있는 경우가 있다. 이는 캐싱 대상 PreparedStatement의 수가 무한대라는 것이다. 힙 메모리를 점유하여 OOM을 유발할 수 있으므로 반드시 적당한 수로 제한해야 한다.