Bookshelf Home | Contents | Index | Search | PDF

Performance Tuning Guide > Tuning the Siebel Application Object Manager for Performance > Best Practices for AOM Tuning >

Configuring Shared Database Connection Pooling

---

Shared database connections are used by most AOM operations. A connection will be shared by more than one user session once the number of sessions within the multithreaded process exceeds the maximum number of shared connections allowed per process, as explained below.

Configuring Parameters for Shared Connection Pooling

This section describes how to enable or disable connection pooling and multiplexing using the parameters MaxSharedDbConns and MinSharedDbConns. These parameters are included in named subsystems of type InfraDatasources.

• To enable connection pooling and multiplexing, set MaxSharedDbConns and MinSharedDbConns to positive values.
  • MaxSharedDbConns controls the maximum number of shared database connections for each multithreaded process.
  • MinSharedDbConns controls the minimum number of shared database connections the AOM tries to keep available for each multithreaded process.

    The setting of MinSharedDbConns must be equal to or less than the setting of MaxSharedDbConns. Depending on your AOM usage patterns, you may set these to the same value or set MinSharedDbConns to a lower value—if you determine this to be helpful in conserving database connection resources.

• To disable connection pooling and multiplexing, set MaxSharedDbConns and MinSharedDbConns to -1 (this is the default value).
• MaxSharedDbConns and MinSharedDbConns are defined per AOM component, on an enterprise basis. The database connections these parameters control are not shared across multithreaded processes. The actual number of database connections for each multithreaded process is determined by the ratio MaxTasks/MaxSharedDbConns.

To configure parameters for shared database connections

1. Determine the appropriate number of user tasks that should share a database connection.
2. Start with a 10:1 ratio for MaxTasks/MaxSharedDbConns: in other words, 10 user tasks will share the same database connection.
3. Adjust the ratio based on think time. The 10:1 ratio assumes a 30-second think time. For a 15-second think time, reduce the ratio proportionally, to 10/(30/15) = 5:1.
4. Adjust the ratio lower if there are known long-running queries in your deployment.

NOTE:  A long-running query on a shared database connection may block other users sharing that database connection. Therefore, it is critical to optimize database access in your environment when using database connection pooling. If long-running queries cannot be avoided, monitor the overall database response time and reduce the ratio MaxTasks/MaxSharedDbConns accordingly, until a satisfactory response time is achieved.

Example Configuration for Shared Connection Pooling

Assume, for example, the following parameter settings:

MaxTasks = 500

MaxMTServers = 5

MinMTServers = 5

MaxSharedDbConns = 50

MinSharedDbConns = 50

With these settings, the AOM component can support a maximum of 500 threads. Those 500 threads would be spread over five multithreaded processes, each having 100 threads. Each multithreaded process would have a maximum of 10 shared database connections, each of which would serve up to 10 threads.

NOTE:  Adjust the ratio based on think time. For example, if think time is 15 seconds, then the ratio would be 10/(30/15)=5:1, and MaxSharedDbConns = 500/5=100.

How Shared Connections Are Assigned

When the AOM starts up, the shared connection pool is empty. When a user logs into the AOM, the shared connection pool is checked to see if a connection is available.

Shared database connections may be assigned to a new user session in any of the following ways:

• If a database connection is available in the pool that is not being used by another session, assign the connection to the new session. The connection is not removed from the pool.
• If the number of connections in the pool is less than MaxSharedDbConns, create a new connection, place it into the pool, and assign it to the new session.
• Select the current connection in the pool that is shared by the fewest sessions (has the lowest usage count), and assign it to the new session.

Once a shared connection is assigned to the new session, all database operations (read, write, update, and delete) for the session go through the connection.

When the session terminates, the usage count for the database connection is decremented. If the usage count has reached 0 (no sessions use this connection) and there are at least MinSharedDbConns connections already in the pool, the connection is removed from the pool and closed. Otherwise, it is left in the pool so the minimum number of shared connections is maintained.

When an AOM multithreaded process shuts down, any remaining connections in the pool are closed.

Database Connections and Database Authentication

When your deployment uses database authentication, a database connection is created for each login, for authentication purposes. Afterwards, this connection is released to the shared connection pool, if it has less than MaxSharedDbConns. Otherwise, if the pool is full, the connection is closed (terminated).

Therefore, even when the pool is full and connections are available, new connections are still created temporarily for each new session login. These connections must be accounted for in determining the allocation of database connections.

Database Connections and Long-Running Queries

When multiple user sessions are assigned to a shared database connection, all database operations from these users go through this shared connection. A database connection can process only one database operation at any particular moment.

If a long-running query is run which takes, for example, three seconds, then, for this duration, database requests from other users sharing the same database connection would be queued until the query operation completes.

For this reason, when you use database connection pooling, it is critical to minimize the number of long-running queries.

Scenario for Assigning Shared Connections

Assuming, for example, the parameter settings described in Example Configuration for Shared Connection Pooling, shared database connections will be handled as in the following scenario:

• If 10 users log in, in sequence, each user is assigned a new database connection on the same multithreaded process. (Each multithreaded process will have at most 10 shared database connections.)
• User 21-100, continuing to log in (in sequence), would reuse these connections.
• Users 51-100, logging in (in sequence again), would reuse Connections 1-50. (Each database connection will have at most a usage count of 10. Each multithreaded process would have at most a thread count of 100.)
• Once assigned a database connection, a user session is tied to that database connection for the duration of the session. This mapping is maintained until the user logs out or the session times out.
• So, assuming 100 users have logged in, in sequential order, then Connection 1 is then used by Users 1, 11, 21, 31, 41, 51, 61, 71, 81, and 91.
• When a user logs out or session timed out, the usage count for Connection 1 decrements by 1. (Connections with lower usage counts will be assigned to new user sessions, as needed.)
• Once the usage count for a database connection reaches 0, it is closed if the number of database connections is greater than MinSharedDbConns. If it is equal to or less than MinSharedDbConns, then it is not closed.

For details, see How Shared Connections Are Assigned.

Bookshelf Home | Contents | Index | Search | PDF