Tuning databases for the Event Manager

If the IBM® Business Process Manager Event Manager has poor performance, you can tune the databases by updating optimizer statistics, reorganizing the tables and indexes, providing your database system with a better index, or, on Oracle databases, reducing the contention.

About this task

The Event Manager is one of IBM Business Process Manager core components. It handles the communications between business process definitions (BPD) and services, schedules events, and runs undercover agents (UCA). The Event Manager consists of multiple Java threads and database tables. All events are recorded and tracked in the LSW_EM_TASK and LSW_EM_TASK_KEYWORDS tables. For high workloads, database tuning is essential for the Event Manager to work properly.

Because all events are recorded and tracked in the LSW_EM_TASK and LSW_EM_TASK_KEYWORDS tables, the Event Manager can become a bottleneck for performance, depending on your process models and workload. For heavy workloads, periodically tune the databases to help improve performance. The following symptoms indicate that database tuning is overdue.

SQL execution plans that use full table scans
High CPU use
Fragmented tables and indexes
Significant contention on records or data pages

Procedure

To prevent events from affecting how the Event Manager performs, consider completing the following tuning tasks in this order until performance improves.

Update the database optimizer statistics for the Event Manager tables.
Plan the update carefully because the workload on the Event Manager changes frequently. That is, the number of records in the database tables is volatile. The statistics must reflect heavy or peak workload for an SQL execution plan to make sense for the whole spectrum of workloads. It is unnecessary to update the optimizer statistics when the workload is close to null.

If this action does not solve the performance issues, you might have specific high-load scenarios. Try the next steps in order.
Periodically reorganize the Event Manager tables and indexes. This action typically solves the most common performance issues.
Because the workload is volatile and because new records are in ascending order (task_id, scheduled_time, discriminator columns), periodically reorganize the Event Manager tables and more importantly, reorganize the indexes. To decide whether reorganizing is necessary, you can use the information that is returned by the database catalog tables or by a command that checks reorganization, such as the reorgchk DB2 command.

If this action does not solve the performance issues, you might have specific high-load scenarios. Try the next steps in order.
Tune the indexes by reviewing the SQL execution plans for queries on Event Manager tables.
If the execution plan shows a full table scan or numerous read requests and if the number of records is significant, consider providing an extra index that is more suitable than the default IBM BPM indexes. These indexes work well for most use cases but some database systems might prefer a different index. Use your database vendor's advisor tool to determine whether a more suitable index is available for your database system.
On Oracle, reduce the contention. Increase the INITRANS values for tables and indexes.
Increase the INITRANS values for tables and indexes. Specifically on Oracle, you might observe contention that is related to blocks, for example interested translation list (ITL) wait events. Such events are reported in an Automatic Workload Repository (AWR) report. In this case, increase the INITRANS values for tables and indexes that are listed in the AWR report.