Server and component transaction settings  Chapter 7: Cluster Tuning

Chapter 6: Database Access Tuning

Connection cache settings

Connection caches increase performance by allowing reuse of database connections, eliminating the overhead of repeatedly creating and destroying connections to the same database. For general information on connection caches, see:

The following sections describe how to tune connection cache settings for the best performance.

Tuning the cache size

Connection caches have 10 connections by default. For applications with many clients, this number is often too small. For lightly used caches, you can lower the size to free up memory and network connections that would be wasted by rarely used database connections. To tune the cache size, monitor the cache statistics as described in Chapter 11, “Runtime Monitoring,” in the EAServer System Administration Guide. Tune the cache size by setting the properties listed in Table 6-1.

Table 6-1: Properties to configure connection cache size



Minimum Connection Pool Size

The minimum number of pooled connections, allocated at server start-up. If not set, the default is 0.

With jagtool, you can set as

Maximum Connections

The absolute maximum number of connections that can be created from the cache. Requests for excess connections either block or fail. A value of 0 indicates that there is no limit.

With jagtool, you can set as

Maximum Connection Pool Sizes

The maximum number of connections that can be cached. If connections are allocated beyond this number, the cache manager drops the excess connections when they are released.

With jagtool, you can set as

Wait for Connections

When the maximum connections limit is reached, specifies whether requests for excess connections fail immediately or wait until a connection is released. If this setting is enabled, the request waits.

With jagtool, you can set as

Pooled Connection Idle Timeout

Specifies the number of seconds an idle connection remains in the pool before it is dropped. The default is 300 seconds (5 minutes). Idle connections are dropped until the minimum pool size is reached.

NoteIf the minimum connection pool size is set to 0, connections are not dropped.

To disable the monitoring of idle connections, set to a negative value. For more information, see “Disabling the cache size monitor thread”.

With jagtool, you can set as

Pooled Connection Refresh Rate

The refresh rate for the cache, that is, how often the cache manager checks for excess connections that have been idle longer than the idle timeout period. The default is 600 seconds (10 minutes).

To disable the monitoring of idle connections, set to a negative value. For more information, see “Disabling the cache size monitor thread”.

With jagtool, you can set as

Set the pool size so the majority of database connections are taken from the cache. You can tune the minimum pool size and refresh rate parameters to reduce the number of database connections that are held during off-peak hours. You can raise the maximum size if you see many failed connection requests or waits.

Figure 6-1 illustrates how these settings affect the growth of the connection cache.

Figure 6-1: Connection cache growth patterns

When the server starts, it preallocates the minimum number of connections, allowing faster response times to the initial client requests that require a database connection.

If all connections are in use simultaneously, the cache manager creates new connections. When released, these connections are added to the cache, causing it to grow towards its maximum pool size.

During peak use, additional connections may be required beyond the maximum pool size, up to the absolute maximum. When these excess connections are released, they are closed rather than placed in the cache. Setting an absolute maximum prevents your application from overwhelming the database with too many connections or exceeding database license limits. You can set the Wait for Connections setting to determine what happens when your application asks for connections in excess of the absolute maximum size. You can also set the absolute maximum to 0 to indicate no limit.

NoteWhen many connections are created in excess of the maximum pool size, you may see a drop in performance. You may also see a drop in performance if excess, unpooled connections are rapidly created and destroyed to service short transactions. To avoid these scenarios, raise the maximum pool size.

When the activity level drops, the cache manager removes idle connections if you have configured a refresh rate and idle connection limit. If there is no activity, the cache size drops back down to the minimum.

Monitoring cache activity

You can monitor the cache activity in EAServer Manager to determine how effective the cache settings are. See “Monitoring connection caches and managed connection factories” in Chapter 11, “Runtime Monitoring,” in the EAServer System Administration Guide for more information.

Disabling the cache size monitor thread

The cache manager runs a thread to monitor the number of connections and close those that are in excess of the minimum size when they have been idle longer than the idle timeout period. If you do not want the cache size reduced below the maximum pool size, you can set the Idle Timeout and Refresh Rate to negative values. When you use these settings, the cache monitor thread terminates when the maximum pool size is reached.

Tuning caches used by EJB CMP entity beans

For EJB CMP entity beans, EAServer provides wrapper drivers that improve performance by using statement batches and stored procedures. For more information, see “Using CMP JDBC wrapper drivers”.

Remove unused connection caches

Remove unused connection caches or set the Minimum Pool Size setting to 0. EAServer allocates the minimum pool size for each cache, and unused connections waste memory and network resources.

Sanity checking

If sanity checking is enabled, the cache manager runs a stock query to verify that connections are ready for use before placing them back in the cache. Sanity checking prevents errors that occur when components release a connection that is not ready for use by another component. For example, there may be pending results on the connection, causing an error when the next component to use the connection tries to send a command.

If you have debugged the results handling in your application, you can improve performance by disabling sanity checking on the Caching tab in the EAServer Manager Cache Properties dialog box or by using jagtool to set the property to false.

SQL tracing

You can enable tracing of the commands issued through each connection in a cache on the SQL Tracing tab in the EAServer Manager Connection Properties dialog box. For details, see “SQL tracing properties” in the EAServer System Administration Guide.

SQL tracing can help you debug performance issues that are caused by poor database response times. However, the file I/O does increase the response time of EAServer, so disable SQL tracing unless you are debugging database performance issues.

Using the caching APIs

In Java/CORBA, C, C++, and ActiveX components, you must use the EAServer APIs to obtain a cache reference and retrieve connections from the cache. For best performance, use by-name lookup to obtain cache handles and pass the cache handle when obtaining and releasing connections. Doing so avoids internal table searches in the cache manager. For information on using these APIs, see Chapter 26, “Using Connection Management,” in the EAServer Programmer’s Guide.

Dynamic prepare on jConnect caches

Ensure that connection caches that utilize a com.sybase driver class are defined with the DYNAMIC_PREPARE property set to FALSE for optimal performance. In EAServer 4.1.1 and later, this property is set to FALSE by default. However, it was set to TRUE by default in some earlier versions. In EAServer 4.1.1 and later, a warning is printed in the log file if a connection cache has this property set to TRUE.

Database and driver specific settings

See the documentation for your database and the connectivity driver or library for performance tuning recommendations. For example, if you are using Sybase jConnect for JDBC, the Programmer’s Reference includes a chapter on performance and tuning. This document is available in the jConnect documentation on the Sybase Web site. If using Sybase Adaptive Server Enterprise, see the Performance and Tuning Guide, available in the Adaptive Server Enterprise Documentation on the Sybase Web site.

Unless you are actively debugging problems, ensure that the trace and debug settings are disabled for your connectivity driver or library.

Copyright © 2005. Sybase Inc. All rights reserved. Chapter 7: Cluster Tuning