BPM Database Metrics and Monitoring

Follow

Introduction:

Monitoring your BPM system to determine if you are "adequately sized" is a tricky subject.  There are two main evaluation points:

This article deals with the later of the two.  There are a number of other items with BPM Advanced you could monitor (for example WPS Failed events, Process Service Cleanup of BPC, etc), but these are topics beyond the scope of this article which focuses on trending data for BPM STD (not the Advanced feature set).

Problem:

On a smaller (think daily) scale, the performance of a system can vary widely due to OS issues (updates, antivirus, etc), but the health of BPM really lays in the database layer, but daily health of the system is better reported through a monitoring of the CPU/RAM/etc.

We want trending data for the BPD instances and tasks and to do this, we run queries on the database, to see how many BPM instances and active tasks there are, we can see how much the system is being used over time (in relation to new BPM instances and tasks/services).  The more instances and tasks, the more the system is being used.  Over time this can cause a performance impact as the queries BPM uses to query the database will take longer to return results (as the number of results increases).
 
Simple enough right?  We are looking at the number of BPM instances and tasks and taking measurements periodically (time periods will vary depending on your needs).

Methodology:

There are a couple SQL queries that will give you results, one for BPD instance data and one for task data. First, realize that each BPM instance and each task can have a state.  These states are what we are looking at.  Some are Active, Complete, Failed, etc.

Task status data:
 
The first query is to give you the execution status (the state) of the tasks in BPM.  Remember, each task has a state independent of the BPD instance state and these task states are listed by the following query:

select inst.EXECUTION_STATUS, count(*) as total from
LSW_BPD_INSTANCE inst, LSW_TASK task
where task.BPD_INSTANCE_ID = inst.BPD_INSTANCE_ID
group by inst.EXECUTION_STATUS

The results are a table of:
status (a numeric value) – number of tasks with that status
 
This gives us the data, we just need to have a correlation from numeric value to task status.  This is given below:

  • 1 -- Total Active Tasks
  • 2 -- Total Complete Tasks
  • 3 -- Total Failed Tasks
  • 4 -- Total Terminated Tasks
  • 5 -- Total “Did not start” Tasks
  • 6 -- Total Suspended Tasks

BPD Instance status data:

Likewise, the second query will give you the same data but just for BPD instances:

select inst.EXECUTION_STATUS, count(*) as total from
LSW_BPD_INSTANCE inst
group by inst.EXECUTION_STATUS

And the associated states:

  • 1 -- Total Active BPD instances
  • 2 -- Total Complete BPD instances
  • 3 -- Total Failed BPD instances
  • 4 -- Total Terminated BPD instances
  • 5 -- Total "Did not start" BPD instances
  • 6 -- Total Suspended BPD instances

 
Over Time:
What does that data mean over time.  Well, ideally:

  • we want to see the number of completed tasks/BPDinstances increase
  • the number of failed instances kept low
  • the number of Active instances remain fairly constant

If the number of Active instances increases over time, then we should consider adding resources to the servers because the trending data is showing we are starting more and more instances without them closing in our time window.  If the number of Active instances remains constant (again, we are talking about trending over time), then we are adequately resourced for the BPM applications we have.

Alternative data acquisition methods:

We have discussed how to query the database directly, but there are other ways to get data from the BPM system:

Both of the above methods will work to get data, however this relies on the BPM system being operational whereas the SQL query method directly interacts with the database bypassing any BPM operations.  The choice is up to the implementer.

Conclusions:

While there is no single answer to if you are adequately sized, this should help answer whether you are sized appropriately for the volume of instances/tasks your BPM instance is running.

Have more questions? Submit a request

Comments

Powered by Zendesk