Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

BES - Performance, Scalability, and Capacity Planning of JDBC Connection Pool

BES - Performance, Scalability, and Capacity Planning of JDBC Connection Pool

Frequently Asked Questions

  1. Is there a correlation between the number of Jdbc connections, and scalability of BES paritition?
  2. What are the common quality of service goals/examples to keep in mind (when doing capacity planning for JDBC pool)?
  3. How can I do simple experiments to determine the number of JDBC connections needed for my application to scale?
  4. How to turn on EJBTimers?
  5. What are the examples of JDBC related EJBTimers and what are they called?
  6. What algorithm is used in the Jdbc pool for re-using the pooled connections?
  7. What is the effect of load-blancing, and clustering on the number of JDBC connections?
  8. Is there a co-relation between the VBJ thread pool paramters, and the number of JDBC connections?
  9. What type of properties are relevant to number of JDBC connections?
  10. Why use maxPoolSize, if database limits connections?
  11. What to keep in mind if database has license for unlimited concurrency?
  12. Any standard (commercial) benchmark to predict or guide BES scalability?
  13. Any formula to guess a good number for JDBC connections?
  14. What to tune in the database itself?
  15. What is the recommended value of maxPreparedStatementCacheSize property of the pool?
  16. What is the recommended value of maxPoolSize property?
  17. What to know about initialization and warm-up behavior of BES JDBC connection pool?
  18. When to use readOnly property of BES datasource?
  19. How the pool behavior changes with transaction characteristics?
  20. How to turn-off BES JDBC connection timeouts (for e.g in benchmark setups)?
  21. What kind of an Enterprise Java Bean (EJB) application/deployment can be a JDBC connection hog?
  22. What can lead to "out of cursors" (or similar) error and how to avoid it?
  23. Is there any performance impact of setting reuseStatement=false?
  24. How to estimate number of cursors required?
  25. What else is done in BES JDBC connection pool (in addition to connection caching)?
  26. What is the effect of using BES ITS (out-of-process TM) on JDBC connection pool?
  27. If the underlying JDBC driver provides connection pooling, should the application still use JDBC connection pooling of BES?

1. Is there a correlation between the number of JDBC connections, and scalability of BES paritition?

There sure is, and incorrect configuration can lead to significant performance degradation. However, when tuning BES for the optimal number of JDBC connections, there are various related issues, and considerations that should be taken into account. The FAQs below, try to touch upon various aspects to keep in mind.

Back

2. What are the common quality of service goals/examples to keep in mind (when doing capacity planning for JDBC pool)?

It is important to understand the quality-of-service goals. For example, in some cases, it may be perfectly valid  for the transaction to take upto (say) 2 seconds, and in others, even a milli-second response may be too slow.In some applications  it will be reasonable to have very good average transaction response times. In others, you may need to guarantee that the worst transaction response time should not exceed a particular threshold.

For example, SPECjAppServer200x (please read documents at www.SPEC.org for more details) is a benchmark which simulates an enterprise resource planning (ERP) application in a manufacturing plant. There are software components that interact with the users to take manufacturing orders, and there are other components that simulate manufacturing of widgets, needed to fullfil orders. Also, there are components that (simulate) interaction with external businesses (B2B). In pretty much each of the scenarios, data access is involved. The order entry application has a restriction that 90% of the transactions should not exceed 2 seconds, while the transactions that are part of the manufacturing process need to be finished within 5 seconds. So, in this distributed application, care must be taken to meet the response-time goals for each of the components, and each component may need different tuning.

Back

3. How can I do simple experiments to determine the number of JDBC connections needed for my application to scale?

EJBTimers can be used to see how many connections are opened. The timers can be used on the relevant hardware using :

  • J2EE Application that is actually deployed in the real environment.
  • BES Performance Example (<bes-install>/examples/ejb/perf) which is made to simulate the real application, and user behavior. Please see the perf example README for more details about the example.

Back

4. How to turn on EJBTimers?

In BES Management Console:

  1. Right click your partition (e.g. standard, if that is the partition where you deployed your J2EE application)
  2. Choose "Performance Tuning"
  3. Uncheck 'Enable statistics gathering for the EJB container'
  4. Continue to click Next (3 times) until you get to the screen where you see "Advanced Configuration" button.
  5. Press "Advanced Configuration" (This will open a partition_server.config file in a text window)
  6. At the bottom of the text window add the following:

vmprop -DEJBTimers

After the above changes, and the partition restart, the event_log of your partition will contain EJBTimers information (example is shown below).

Note: The above steps are used to enable the textual output of EJBTimers. The BES Management Console shows graphical representation of EJBTimers by default. However, the Console (currently) shows selective timers.

Back

5. What are  the examples of JDBC related EJBTimers and what are they called?

The following is cut and paste from Borland Enterprise Server's partition log (Please see above FAQ on how to turn on EJBTimers):

====================
Jdbc Pool Statistics
====================
Time                   Wed May 15 12:57:48 PDT 2002
--------------------
Jdbc2 Pool (Direct)    serial://datasources/OracleCPDS
Free                   1
Discarded              19
Total in memory        20
Total in use           1
========================
  
Action         Total (ms)     Count          T/C (ms)        Percent
------         ----------     -----          --------       -------
Dispatch_POA   1119135        233848         4.78            7.99%
Dispatch_Home  269927         232741         1.15            1.92%
Dispatch_RHome 21             540            0.03            0.0%
Dispatch_Cmpt  31403          232201         0.13            0.22%
Dispatch_Bean  9961938        232241         42.89           71.15%
BeginTx        163208         232201         0.7             1.16%
CommitTx       2200260        232201         9.47            15.71%
LoadClass      158            25             6.32            0.0%
PrepareStmt    1771           240            7.37            0.01%
ORB_Activate   246            44             5.59            0.0%
ORB_Deactivate 1              20             0.05            0.0%
Jdbc2_GetCon   229153         232201         0.98            1.63%
Jdbc2_NewCon   5291           20             264.55          0.03%
Jdbc2_RegRes   18243          232201         0.07            0.13%
------         ----------     -----          --------       -------
Total          14000755
  
====================
Jdbc Pool Statistics
====================
Time                   Wed May 15 12:58:48 PDT 2002
--------------------
Jdbc2 Pool (Direct)    serial://datasources/OracleCPDS
Discarded              20
Total in memory        20
Total in use           0
========================

Description of JDBC related EJBTimers:

Jdbc2_GetCon: Time spent in the Jdbc2 datasource to get a pooled connection.

Jdbc2_NewCon: Time spent in the Jdbc2 datasource to get a new connection (startup cost only).

Jdbc2_RegRes: Time spent in the Jdbc2 datasource to register a transaction resource in transaction service.

Brief Analysis:

In the above EJBTimers snapshot:

  • Jdbc Pool Statistics show the total Jdbc connections in memory, and also the current state of various connections in Jdbc pool.The information is printed (by default) every 5 seconds. Note that unused connections are discarded after idleTimeout seconds.
  • As shown by Jdbc2_NewCon, there are 20 connections opened, also 1.63% or 0.98 ms)of the CPU was spent in obtaining the Jdbc connections.

Please see documentation for more complete description of the timers. Also, please note that pool statistics are only printed for Jdbc2 datasources.

Back

6. What algorithm is used in the JDBC pool for  re-using the pooled connections?

It is useful to know how the BES JDBC pool internally works. The JDBC connection pool that is provided by Borland Enterprise Server reuses connections if it can find them in the pool and opens new ones only if the pool is empty and maxPoolSize hasn't reached. Two usage models of the pool:

  • Non-transactional use: This means the application (example an EJB) is not associated with a transaction when it requests a connection. In this case, the connection is put back into the pool as soon as the application calls java.sql.Connection.close().
  • Transactional use: This means the application (thread) is associated with an active transaction when it requests a JDBC connection. In this case, the JDBC connection returns to be pool for re-use when the transaction ends.

Back

7. What is the effect of load-blancing, and clustering on the number of JDBC connections?

JDBC pools are created per partition (ie JVM)  process. If you have multiple partition processes running, it is important to note that each partition will open a separate set of connections. So, the total number of JDBC connections would be the summation of connections in each of the pools in different partitions.

In general, it is best to deploy your tightly coupled EJBs as part of a single J2EE application. This will guarantee good performance, better resource utilization, and also can avoid a two-phase commit protocol where it may not be needed. Please note that, the JDBC connections are opened only when they are needed; hence if an application is replicated for fail-over, the connections would be created when that replica is actually used.

Back

8. Is there a co-relation between the VBJ thread pool paramters, and the number of JDBC connections?

Usually maximum size of the VBJ thread pool can give a fairly good idea about the number of JDBC connections  that a BES partition would need. This is true in most of the situations, except for a case discussed in the FAQ below - What kind of an Enterprise Java Bean (EJB) application/deployment can be a JDBC connection hog?

Back

9. What type of properties are relevant to number of JDBC connections?

idleTimeout, maxPoolSize and reuseStatements

Back

10. Why use maxPoolSize, if database limits connections?

Some databases cause the transaction to hang when it tries to open a connection on a database that has reached its max connection limit. It is recommended that you use maxPoolSize property of BES JDBC pool which would limit the connection pool size in the AppServer layer. The pool also provides a way to tune the amount of time that a thread should spend waiting. In applications where it may be perfectly valid to rollback few transactions. In those applications one can use BES's maxPoolSize property with a waitTimeout. Please see BES docs for detail.

Back

11. What to keep in mind if database has license for unlimited concurrency?

Even in cases where database doesn't have any concurrency limit, you may want to limit the application concurrency to a reasonable level for the JVM to behave in a performant manner. Usually we have seen  that concurrency of 20-30 per VM performs the best, and 50 or more threads tend to degrade JVM's performance.

Back

12. Any standard (commercial) benchmark to predict or guide BES scalability?

  • SPECjAppServer200x benchmarks (previously called ECperf) can be used to predict the behavior of a given hardware. If your application happens to have same quality of service requirements as SPECjAppServer2001 then you can also learn by looking at our submission available at the website: http://www.spec.org/jAppServer2001/results/res2002q4/jAppServer2001-20021113-00009.html
  • CSIRO Report has a very OLTP oriented approach of simulating multiple users. Please contact Borland technical support for more information.

Back

13. Any formula to guess a good number for JDBC connections?

Every application has different requirements, and also the CPU power/quality of the relevant machine matters a lot. For a single partition on a 2 CPU machine we found that limiting the VBJ thread pool size to 10, and JDBC maxPoolSize to 15 per partition will serve your OLTP (short transactions) needs (as in CSIRO, and in SPECjAppServer2001). Please note that (as mentioned above) your app will not attain 15 connections; it may do so, only if it has a pattern where intra-VM bean calls have RequiresNew transaction attributes.

Back

14. What to tune in the database itself?

Usually for high-end applications, the database installation with defaults values, may not be sufficient, and you may need to tune few things. As an example, if your application needs to create lots of java.sql.PreparedStatement objects then you may need to bump up the maximum number of cursors. In case of Oracle you will need to edit init.ora file of your database. Also, for applications that involve lot of new data insertion you may need to increase the table space, and logs etc.

Please also keep in mind that if the database machine is shared then load on the database machine will slow all the relevant applications down. This means, the connections will spend more time doing JDBC work, and hence will become available in the shared pool little later.

Usually RDBMS vendors  provide documentation for performance tuning and scalability of databases. Also, in case of Oracle the website http://technet.oracle.com has very useful information.

Back

15. What is the recommended value of maxPreparedStatementCacheSize property of the pool?

This property controls the cache size (ie number PreparedStatement BES will re-use). Usually the default (of 40) is good enough. However, since the pool is JVM-wide, and can be shared by many deployed applications, the settings may need to be changed if your partition hosts many apps that access the same database. This property is relevant only when reuseStatements property is set to True.

Back

16. What is the recommended value of maxPoolSize property?

You can set it to the number that best matches the number of licensed database connections possible. Please note that if your database is shared, other applications may use some of the connections, and you should take that into account . Also, if your VBJ thread pool setting is less than the maxPoolSize setting, and your application/deployment doesn't usually lead to many suspended transactions (as discussed above) then you can expect the number of connections to be in the vicinity of max VBJ thread pool size.

Back

17. What to know about initialization and warm-up behavior of BES JDBC connection pool?

BES JDBC pool lazily creates JDBC connections. In other words, new connections are only created when application needs them. In a long running server application this cost amortizes overtime,. Typically benchmarks have a way to warm-up/rampup your server. So, it is important to do that with the same stress/concurrency level  as expected in  the steadyState (ie measured interval). This is specially true in SPECjAppServer200x (ECperfTM)  benchmarks. As BES doesn't provide initSize(), the very first request to get JDBC connection will be expensive, as it involves two (one-time) RPCs:

  • A JNDI lookup (of the vendor datasource) which may involve an RPC if naming service is not running within your partition. This is per datasource lookup.
  • An RPC to the database to create a new connection. This is for each new connection created.

Back

18. When to use readOnly property of BES datasource?

For read-only applications, it is recommended to use ReadOnly datasources of BES. These datasources don't register transaction resources with the transaction service, and hence result in performance gain. They also avoid issuing commit() RPC calls to the database.

Back

19. How the pool behavior changes with transaction characteristics?

As mentioned, as soon as the transaction finishes the JDBC connection(s) that were participating in the transaction are released to the pool, for re-use. Please note that the following situations may affect the number of Jdbc connections:

  • Long (batch) transactions: These transactions may be doing lot of computation in the business (middle) tier or are very database intensive. In either case, they will hold on to the JDBC resources for long time, and if there are many such concurrent transactions active, then they will lead to opening more connections then short transactions.
  • If an application is designed in a manner that client initiates a transaction, and the transaction remains active during client/user's think time then the JDBC connection will only be released when client finishes the transaction. If possible, applications should be designed to avoid this pattern.

Back

20. How to turn-off BES JDBC connection timeouts (for e.g in benchmark setups)?

BES JDBC pool supports certain timeouts that may not be required/useful in a controlled environment of typical benchmarks. Example of such periodic activities are:

  • idleTimeout of unused connections
  • busyTimeout of connections participating in non-responsive transactions.
  • refresh/validation of JDBC connections that are in the pool.

The way to turn them off, is to add the following VM property in your partition's config file. In your console:

  1. Right click your partition
  2. Choose Configure
  3. Choose JDK Tab
  4. Choose Advanced Configuration (will open a text window)
  5. At the bottom of the partition_server.config file that is opened in a text window, please add the following:

vmprop -DITS_timeout=0

Back

21. What kind of an Enterprise Java Bean (EJB) application/deployment can be a JDBC connection hog?

A J2EE application (deployment) that can lead to many suspended transactions can cause many more JDBC connections than the concurrency in the JVM. Consider the following situation, where intra-VM calls are leading to suspension, and start of new transactions in the same thread. Square bracketed values are the transaction attributes:

Example:

J2EE Client --- calls ->  [TxRequired]Session-A ---calls --> [TxRequired] Entity-A ---calls --> [TxRequiresNew] Session-B ---calls --> [TxRequired] Entity-B

In the above example, the JDBC connection that is tied with a transaction (spanning Session-A, Entity-A) will only be released and hence available to other transactions when this transaction ends. If many threads have the same call pattern (as above) then even a smaller concurrency can lead to large number of JDBC connections. This type of deployment should be avoided, if possible, or maxPoolSize property (discussed in an FAQ below) should be used to bound the number of connections. Care must be taken to avoid a deadlock. The waitTimeout property of BES JDBC connection pool should be used to enable timed waits.

Back

22. What can lead to "out of cursors" (or similar) error and how to avoid it?

  • Proper JDBC Code: Make sure all the JDBC resources that were factory'ed from the JDBC connection,  are properly closed (even in the presence of exceptions). This usually means calling close() method on JDBC resources like:  java.sql.ResultSet, java.sql.Statement, and java.sql.Connection.
  • Database configuration: Make sure each connection has reasonable cursor setting  to handle your needs.

Back

23. Is there any performance impact of setting reuseStatement=false?

This feature (which is ON by default) hints BES JDBC pool  to reuse java.sql.PreparedStatement objects for the queries that are re-executed many times. Since preparing new queries is expensive, this provides significant performance advantage. Depending upon the overall application design, not using this feature, can lead to about 5-10% degradation (or even more for data-centric applications).

Back

24. How to estimate number of cursors required?

Most of the JDBC applications can run in less than 50 cursors (for e.g ECperf could). It may be hard to exactly know the cursor value, as it depends on the number of applications that a BES partition is hosting, the behavior of each application and also on shareability of your RDBMS. If an application is not coded properly, for e.g if you continue to open PreparedStatement in a loop without closing them your app can easily exhaust cursors.

Approaches to find out good cursor limit and diagnosis:

  • Run your container with -DEJBTimers, and it shows how many PreparedStatements are being in use, if you don't see a constant count in steady state something is wrong (most likely in the application). An example below shows a timer snapshot from a ejb example (perf):

Action          Total (ms)      Count           T/C (ms)      Percent

------          ----------      -----           --------      -------

Dispatch_POA    70115           75419           0.92           1.59%

Dispatch_Home   287839          75406           3.81           6.56%

Dispatch_RHome  32419           75406           0.42           0.73%

Dispatch_Cmpt   236574          150786          1.56           5.39%

Dispatch_Bean   637855          289212          2.2            14.53%

EntityHome      457884          75403           6.07           10.43%

Activate_SB     2012            108             18.62          0.04%

Passivate_SB    3597            108             33.3           0.08%

BeginTx         725157          150980          4.8            16.52%

CommitTx        614187          150971          4.06           14.0%

Synchronization 316287          301945          1.04           7.2%

LoadClass       225             228             0.98           0.0%

CMP_Init        1067            1               1067.0         0.02%

CMP             64366           96443           0.66           1.46%

CMP_Update      28              1               28.0           0.0%

CMP_Query       594921          21044           28.27          13.56%

PrepareStmt     239             20              11.95          0.0% --> Here PrepareStmt timer which shows the total number of statements created, stayed constant at 20

CMP_GetConn     12248           21047           0.58           0.27%

ORB_Activate    330             61              5.4            0.0%

ORB_Deactivate  63              108             0.58           0.0%

Jdbc1_GetCon    67383           21047           3.2            1.53%

Jdbc1_NewCon    15710           10              1571.0         0.35%

Jdbc1_RegRes    4623            21047           0.21           0.1%

TxCurSvr        241921          75419           3.2            5.51%

------          ----------      -----           --------      -------

Total           4387050

Note that if you really need to construct different query strings in your application in each use, you may want to see if you really need to use Statement or PreparedStatement.

  • If the above is not enough, use RDBMS monitoring tools to see your database is running at it max

Notes:

The current BES default is 40, and (Oracle's is 50). To increase:

  • For your database, please see the docs. For Oracle usually this is done using init.ora file of the database.
  • For EJB Container, the system property: -DmaxPreparedStatementCacheSize=1000. This will increase the limit, but you still need to make sure that your database has a capacity to handle these many cursors (Statements).

Back

25. What else is done in BES JDBC connection pool (in addition to connection caching)?

BES JDBC pool provides seamless transaction integration between application server, and the underlying database. Basically, it is the responsibility of BES JDBC pool to make sure that database transactions are driven (demarcated) by the transaction manager that is part of the AppServer. This means, BES connection pool, under the covers makes the JDBC connection that is being dispensed from the connection pool, a transaction resource, and makes the connection available for re-use when the transaction ends.

Back

26. What is the effect of using BES ITS (out-of-process TM) on JDBC connection pool?

As mentioned in earlier FAQs, when an application calls javax.sql.DataSource.getConnect(), one of the behind-the-scene steps is to register a CORBA transaction resource with the AppServer TM. The cost of this registration (as discussed in the EJBTimers FAQ section above) can be approximated by observing the following timers:

  • dbc2_RegRes: Time spent in the Jdbc1 or Jdbc2 datasource to register a transaction resource in transaction service.
  • BeginTx: Time spent in the container to start a new transaction (doesn't involve/include) JDBC.
  • CommitTx: Time spent in commiting a transaction. The cost includes communication with TM, and also JDBC.

(Part of the Data related:)

Action         Total (ms)     Count          T/C (ms)       Percent
------         ----------     -----          --------       -------
               ........// other timers removed for brevity
                 
BeginTx        163208         232201         0.7             1.16%
CommitTx       2200260        232201         9.47            15.71%
Jdbc2_RegRes   18243          232201         0.07            0.13%

For an out-of-process TM, this cost will become higher as each of the above steps will result in an RPC to an external transaction service (TM). In fact, you can use these EJBTimers to make sure you haven't mistakenly configured an out-of-process TM (which will result in very costly values for T/C, and CPU Percent) when you didn't need it.

Back

27. If the underlying JDBC driver provides connection pooling, should the application still use JDBC connection pooling of BES?

For the transactional use case, if one were to use JDBC-connection-pooling of the underlying driver (and not configuring BES pool at all), the transaction glue code (to integrate AppServer transactions with database transactions) will need to be written by the user. This integration is reasonably involved, and becomes trickier with distributed (XA) transactions.

 Back

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Version history
Revision #:
1 of 1
Last update:
‎2014-08-19 10:58
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.