Skip to main content

Tuning JDBC Data Sources Connection Pool in WebLogic

Tuning JDBC Data Sources Connection Pool in WebLogic:
While we are Load testing Web services with our application deployed on WebLogic Server Version: 10.3.6.0 most of transactions started failing and when checked in the logs we found the below exception:
####<28.08.2015 17:32:54 MSK> <Info> <Common> <server.com> <clust1> <[ACTIVE] ExecuteThread: '50' for queue: 'weblogic.kernel.Default (self-tuning)'> <internal> <BEA1-17FBAA30098FE2C54C98> <> <1439904774589> <BEA-000627> <Reached maximum capacity of pool "AppDataSource", making "0" new resource instances instead of "1".>
####<28.08.2015 17:32:54 MSK> <Error> <server.com> <clust1> <[ACTIVE] ExecuteThread: '82' for queue: 'weblogic.kernel.Default (self-tuning)'> <guest> <> <> <1439904774589> <BEA-000000> <ERROR wf - WfSubscriberSysJMSBean.onMessage:
WF Engine detected error:
com.App.Exception: java.security.PrivilegedActionException: java.rmi.RemoteException: EJB Exception: ; nested exception is:
        <……Error Stack>
Caused by: com.app.framework.jdbc.DataAccessException: Error due to access to database:
<…. Error Stack>
Caused by: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool AppDataSource to allocate to applications, please increase the size of the pool and retry..
        at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:252)
        at weblogic.jdbc.jts.Driver.newConnection(Driver.java:938)
        at weblogic.jdbc.jts.Driver.createLocalConnection(Driver.java:336)
        at weblogic.jdbc.jts.Driver.connect(Driver.java:173)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:533)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:498)
       at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:491)
        at com.App.framework.jdbc.JDBCTemplates.execute(JDBCTemplates.java:130)
        ... 25 more

We have the below values set for JDBC data pool settings:
<initial-capacity>10</initial-capacity>
<max-capacity>35</max-capacity>
1   
As we see in the above screenshot all the 35 were occupied when test was running. So the exception is obvious.
We need to increase the JDBC Connection Pool.
So before increasing the number to some random number we re-calculated the required capacity based on required work manager threads.
In work managers we have types defined are as following :
Work Manager, Fair Share Request Class and Maximum Threads Constraint.
More information on work managers can be found at http://docs.oracle.com/cd/E17904_01/web.1111/e13701/self_tuned.htm#CNFGD112

For MaxThreadsConstraint the count is set to 25 as below. Also same is set for two more work managers

2

For XA DataSource:
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.
So the initial-capacity is set equal to max-capacity. Both are set to 80.
We have in total 21 work Managers configured. Out of 21, 3 are MaxThreadConstaints. So calculation is done as below:
(MaxThreadsConstraint (25) + widgetJobsMaxThreadsConstraint(16) + widgetCancelJobsMaxThreadsConstraint (4)) * 1.2 + 18 (Work Manager, Fair Share Request Class) = 72 connection to take a small margin 80.
Equality initial-capacity = max-capacity is also driven by the desire to reduce the cost base in create / delete / shrink connections in the pools.
 In WLS 10.3.6, minimum capacity is used 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.
So we have initial-capacity = max-capacity = min-capacity = 80
More details on data source connection pool sizing can be found at https://blogs.oracle.com/WebLogicServer/entry/data_source_connection_pool_sizing
So we have below settings:
3
For Non-XA data source the settings are as below:
Increased from 5 to 15
4
Also in all descriptors, data sources are necessary to register two additional parameters designed to protect the stability of the Productions in the future from a different kind of problem.
 The two parameters added are as below:
  <internal-properties>
    <property>
      <name>CountOfRefreshFailuresTillDisable</name>
      <value>0</value>
    </property>
    <property>
      <name>CountOfTestFailuresTillFlush</name>
      <value>0</value>
    </property>
  </internal-properties>

  1. CountOfRefreshFailuresTillDisable
Specifies the number of reconnect failures allowed before WebLogic Server disables a connection pool to minimize the delay in handling the connection request caused by a database failure. Zero means it is disabled. Default value is 2
  1. CountOfTestFailuresTillFlush
Specifies the number of test failures allowed before WebLogic Server closes all unused connections in a connection pool to minimize the delay caused by further database testing. Zero means it is disabled. Default value is 2
After running the test with these settings we no more have the “weblogic.jdbc.extensions.PoolLimitSQLException” in the logs

Comments

  1. good article

    But what is widgetJobsMaxThreadsConstraint and widgetcancelJobsMaxThreadsConstraint. how to calculate creation of work manager and at what basics you created 21 work managers

    ReplyDelete
  2. you're color scheme is illegible

    ReplyDelete

Post a Comment

Popular posts from this blog

Steps to Analyze AWR Report in Oracle

Steps to Analyze AWR Report in Oracle AWR -   Automatic   workload repository is a collection of persistent   system performance   statistics   owned by SYS. It resides in SYSAUX   tablespace . By default snapshot are generated once every 60 min and maintained for 7 days . Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in " dba_hist_snapshot " view.   If we have Database performance issue and not the Database machine, then AWR Report is the place to look at. AWR is not used for real-time performance monitoring like the v$ tables. It is used for historical analysis of performance. AWR complements, but doesnot replace real-time monitoring. Once AWR Report is generated in Oracle, the next task is to analyze it. By going through the AWR Report we can easily solve issues like slow database, high wait events, slow query and many more issues. Even though the report is lengthy, Analyzing or Reading relevant part of AWR Report can

Verifications and Error Handling in LoadRunner *Web_reg_find and Web_reg_save_param*

Verification's and Error Handling Verification points must be inserted into the scripts to verify the application being tested with the load. Verification function that are used in our application are:  Web_reg_find, web_reg_save_param These verification should be outside towards the end of the transaction. Mandatory verification points: ■          Login – whenever there is a login, verification points must be added to verify that the user logged in correctly. ■          Update Transactions – transactions that update the database must be verified that they are successful. ■          Each page should be verified using Web_reg_find function Using Web _ reg_find for verifications. Example: web_reg_find("Text= Main Package","SaveCount=Verify_Count1",LAST); Verification: if (strcmp(lr_eval_string("{Verify_Count1}"),"0")==0) { lr_end_transaction("AddMainPackage", LR_FAIL);  lr_error_message(&q

Vmstat Output explained

vmstat : vmstat is a tool in Unix/Linux which is used to Report virtual memory statistics. It shows how much virtual memory there is, how much is free and paging activity. Most important, you can observe page-ins and page-outs as they happen.   vmstat   reports   information about processes, memory, paging, block IO, traps, and cpu activity.   > vmstat procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----   r   b    swpd    free    buff   cache         si    so     bi     bo    in    cs us sy id wa st   0   0   21688 12797396 850716 15372668     0     0      3     35     0     0   1   0 99   0   0   0   0   21688 12789292 850716 15372668     0     0      0   1355   883   744   1   0 99   0   0   1   0   21688 12784648 850716 15372676     0     0      0     15 1934 1292   6   0 93   0   0   2   0   21688 12781356 850716 15372736     0     0      0     16 2222 2094   8   1 92   0   0   0   0   21688 12809992 850716 15376880     0