IBM Support

Performance issues due to database index in IBM Business Monitor and WebSphere Business Monitor

Troubleshooting


Problem

IBM Business Monitor and WebSphere Business Monitor supply a set of indexes. This set of indexes needs to be tuned based on the model, and the database.

Symptom

During the execution of a large number of inbound events, the query executed to find an existing monitoring context instance with a key matching the key from the inbound event, may take a long time, causing transaction rollbacks.

Cause

The index that is used is the one supplied by the IBM Business Monitor and the WebSphere Business Monitor products, which requires tuning based on the model, and the database.

Environment

All

Diagnosing The Problem

Transaction rollbacks caused by a query executed during the processing of inbound events.

Resolving The Problem

When a SELECT query executed during the processing of an inbound event takes a long time, and causes transaction rollbacks, this often means that an index on the table is missing, or is ineffective. An index is used to speed up the query, preventing an expensive full table scan. The index also allows quicker searching of rows based on the specific columns in the index. When a SELECT query takes a long time to complete, it causes a bottleneck in event processing, which cascades itself back to the consumption side. This is where the index, and in general, database tuning comes into play.

Indexes over time require reorganization, and database tuning should be performed regularly. The key here is that the performance of the query gets slower as the size of the table increases, the database administrator (DBA) needs to analyze why this is happening.

IBM Business Monitor and WebSphere Business Monitor provide certain indexes out of the box, but they cannot provide a "one size fits all" set for every data pattern. The provided indexes also need to be reorganized over time.

For a query that is running slowly, an "execution plan" can be generated, which shows in details what operations are being executed on the database as the query is executing. The DBA will be able to perform this task.

The IBM Business Monitor and the WebSphere Business Monitor products are reliant on database performance. The database statements and the indexes provided are designed with performance in mind, but ensuring good performance is also the responsibility of the DBA.

[{"Product":{"code":"SSSRR3","label":"WebSphere Business Monitor"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Performance","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF002","label":"AIX"},{"code":"PF027","label":"Solaris"}],"Version":"7.0.0.5;7.0.0.4;7.0.0.3;7.0.0.2;7.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SS7NQD","label":"IBM Business Monitor"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Performance","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"","label":"Linux zSeries"},{"code":"PF027","label":"Solaris"}],"Version":"7.5.1;7.5","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21606956