prepared statement cache size - Websphere

This is a discussion on prepared statement cache size - Websphere ; I've 3 applications running on 2 load balanced WAS 6.1 app servers each, using a common Data source . I've been trying to define a ballparked setting for Prep Stmt Cache Size but the SQL Server database crashes regularly due ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: prepared statement cache size

  1. prepared statement cache size

    I've 3 applications running on 2 load balanced WAS 6.1 app servers each, using a common Data source . I've been trying to define a ballparked setting for Prep Stmt Cache Size but the SQL Server database crashes regularly due to heavy load or I guess due to some incorrect setting. Following is my current config:
    Web Container Thread pool (for each app) : 10 - 25
    Connection Pool: Min-0, Max-40
    Prep Stmt Cache Size: Tried with 200,400,500

    Let me know your thoughts!

  2. Re: prepared statement cache size

    I have tried with so many settings, but the database crashes after sometime. My new settings are as below:
    Web Container Thread pool (for each app) : 1 - 10
    Connection Pool: Min-0, Max-50
    Prep Stmt Cache Size: 200

    Please let me know your inputs ASAP. This is urgent!

  3. Re: prepared statement cache size

    The prepared statement cache size isn't likely to be bringing down
    your RDBMS.
    It's more likely to be something to do with the connection pool size.
    The main cost of the prepared statement cache is in memory consumed on
    the application server, it usually reduces the load on the RDBMS.
    Take a look at the number of connections that your RDBMS is configured
    to accept, and set the number of connections allowed in the data
    source to be no more than this. Remember though, if you have a
    cluster accessing the database, each instance/cluster member has that
    many connections available to it. If your database allows 100
    connections, and you have two JVMs, set the maximum to just 50 for the
    data source connection pool.

    Hope that helps, good luck solving the problem.

    Pete Bennett
    www.dsps.net
    www.middleware-dsps.blogspot.com

    On Sep 16, 10:50*pm, beekay wrote:
    > I have tried with so many settings, but the database crashes after sometime. My new settings are as below:
    > Web Container Thread pool (for each app) : 1 - 10
    > Connection Pool: Min-0, Max-50
    > Prep Stmt Cache Size: 200
    >
    > Please let me know your inputs ASAP. This is urgent!



  4. Re: prepared statement cache size

    The prepared statement cache saves work on the client side in building duplicate SQL queries. It does not cache query results. It won't help if your database is overloaded or crashing for some other reason. You really need to first identify why the database server is crashing in order to develop an appropriate solution.

    If the problem is simply that load on the database is too high then you will likely be making things worse by increasing the maximum connection pool size as that will just increase the number of concurrent client connections to the database. A better approach is often to tune pool sizes downwards to the point where the database is running at capacity without being overloaded.

    It could also be that the database is overloaded because the queries being made against it are not optimal. Or it could be the database is not tuned optimally. Or it could be that the server simply does not have the capacity to fulfill the client requirements. Identifying the problem makes it much easier to find the right solution.

+ Reply to Thread