Use this task to fine-tune the database.
Before you begin
The business process container and business processes must be running.
Why and when to perform this task
A common problem is that the database runs out of lock list space,
resulting in lock escalation, which severely impacts performance. Depending
on the structure of the business processes run, you might therefore need to
customize the settings of certain performance-related parameters in your database
management system.
Note: If you are not using DB2®, refer to your database management system
documentation for information about monitoring the performance of the database,
identifying and eliminating bottlenecks, and fine-tuning its performance.
The rest of this topic offers advice for DB2 users.
Steps for this task
- Tune the lock list space, to help ensure optimum
performance.
Check the
db2diag.log file for
your DB2 instance.
Look for entries like the following example:
2005-07-24-15.53.42.078000 Instance:DB2 Node:000
PID:2352(db2syscs.exe) TID:4360 Appid:*LOCAL.DB2.027785142343
data management sqldEscalateLocks Probe:4 Database:BPEDB
ADM5503E The escalation of "10" locks on table "DB2ADMIN.ACTIVITY_INSTANCE_B_T"
to lock intent "X" has failed. The SQLCODE is "-911".
This type of message indicates that the parallelism for
business process applications has improved to the point where the number of
available locks is now too small. Increase the LOCKLIST value to approximately
10 * p, where p is your estimate for
the maximum number of parallel JDBC connections that are required at any time.
For example, if you sized your Business Process Choreographer database,
BPEDB, with a value of p=50, enter the following command:db2 UPDATE DB CFG FOR BPEDB USING LOCKLIST 500
- If you used the DB2 configuration advisor, your database throughput is
probably pretty good. You can, however, further improve the performance, in
the following ways:
- Follow the best practices for database tuning that are described in the DB2 online
documentation, books, and articles.
- Use DB2 monitors,
and examine the db2diag.log file for more information about
bottlenecks within the database.
- Regularly run runstats on your database.
- Tune the following DB2 parameters:
- LOCKLIST
- See the description in step 1.
- AVG_APPLS
- It is better to set this parameter too high rather than too low. For example,
if there are a maximum of 20 connected applications, set AVG_APPLS to 50.
- LOGBUFSZ
- Increasing the size of the buffer for the DB2 log decreases how often
a full log buffer must be written to disk.
- LOG_FILSIZ
- Increasing the size of the log files reduces how often they are switched.
- Adjust database and database manager settings according to workload
requirements. After the configuration advisor has configured the
database, you can also tune the following settings:
- MINCOMMIT
- A value of 1 is strongly recommended. The DB2 Configuration Advisor may
suggest other values.
- NUM_IOSERVERS
- Must match the number of physical disks that the database resides on.
You should have at least as many IOSERVERs as you have disks. IOSERVERs do
not use many system resources, so it is better to set a value that is too
high rather than too low.
Result
Your long-running processes are running as fast as possible under
the current environment and loading conditions.