Table of Contents
- Brazos Portal database tuning
- Brazos Portal Polling (REST API version)
- Environment Cleanup/Maintenance
Even though Brazos Portal does not require any special kind of tuning once installed in BPM environment sometimes the need for tuning might have different drivers.
Some may consider tuning as part of their regular routine when installing new applications in BPM environment. Sometimes issues may arise in the Production environment due to higher workload, so, having optimal settings for the Production database might be crucial. In some cases you might want to tune some of the parameters of Brazos Portal itself, so, REST API calls or database calls consume less resources.
This article covers a number of tuning considerations for Brazos Portal.
Brazos Portal REST API version does a lot of REST API calls and in turn BPM REST calls are hitting BPM database. Depending on the number of concurrent users in your environment this might potentially result in pretty heavy and frequent queries that are executed against the corresponding tables (lsw_bpd_instance, lsw_task) in BPM Process Server database. Attached you may find an example of one of such queries.
Database indexes are tricky. Typically you do not want to add extra indexes unless your DBA tells you that it will dramatically improve execution of particular SQL queries that are slowing down your database server. Back in IBM days I wrote this article related to database indexes in IBM BPM and last I checked it's still "blessed" by IBM Support. Make sure you read it first before reading further material in this article.
When do I need to apply additional indexes?
There are few things you may want to monitor in Brazos Portal database triggers version that would help you determine if you need to look into adding new indexes for Brazos Portal and/or BPM database(s).
- If your end users observe noticeable delay after completing the tasks until the task is removed from Brazos Portal
- Monitoring the STAGED_EVENT table by checking if Brazos Portal can keep up with processing of records in that table. If stays empty if everything is processed. So, if you say lots of records there and the count is not going down in a timely fashion then you may want to run database queries analysis tool that would suggest particular indexes to be applied
- In the logs, you have a lot of entries like this one:
[xxxx-xx-xx 09:52:48,251] [WARN ] [com.bp3.portal.events.TaskEventDispatcher$1]: Dispatching events took 20000 millis which is longer than the dispatch interval of 1000, if this continues, end users will see a delay getting new task, updates to existing tasks, and task removal
Common indexes for Brazos Portal REST API and Database triggers versions
Here are few indexes that we have received from customer's dba that helped them to keep up with the load in EVENT_LOG and TASK tables. These are just example indexes that might or might not work for other BPM environments where you run with Brazos Portal. So, before applying them run through database analysis tool that will indicate what kind of improvement such an index might give.
create unique index IDXCUSTOM1 on BRAZOS_PORTAL.EVENT_LOG (EVENT_ID) Include (TASK_ID, USER_ID) Allow Reverse Scans;
create unique index IDXCUSTOM2 on BRAZOS_PORTAL.EVENT_LOG (EVENT_ID) Include (TASK_ID) Allow Reverse Scans;
create unique index IDXCUSTOM3 on BRAZOS_PORTAL.TASK (USER_ID, PROVIDER_TASK_ID) Include (TASK_ID) Allow Reverse Scans;
create unique index IDXCUSTOM4 ON BPMDB_SCHEMA.LSW_BPD_INSTANCE (BPD_INSTANCE_ID ASC) INCLUDE (EXECUTION_STATUS ) COLLECT SAMPLED DETAILED STATISTICS COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS;
NOTE: IDXCUSTOM4 is applied on BPM database, so, replace with the correct schema in your environment.
Indexes for Brazos Portal Database triggers version
Here are few indexes that we have received from customer's dba that helped them to optimize work of BPM database when using Brazos Portal with db triggers. These are just example indexes that might or might not work for other BPM environments where you run with Brazos Portal db triggers version. So, before applying them run through database analysis tool that will indicate what kind of improvement such an index might give.
NOTE: All of the below indexes are applied on BPM database, so, replace with the correct schema in your environment.
create index IDXCUSTOM1 on BPMDB_SCHEMA.LSW_TASK (GROUP_ID, CLOSE_DATETIME, DUE_DATE, PRIORITY_ID, RCVD_DATETIME, STATUS, ACTIVITY_NAME, SUBJECT, TASK_ID, BPD_INSTANCE_ID) Allow Reverse Scans
create index IDXCUSTOM2 on BPMDB_SCHEMA.LSW_BPD_INSTANCE (CACHED_BPD_VERSION_ID, EXECUTION_STATUS, INSTANCE_NAME, BPD_INSTANCE_ID) Allow Reverse Scans;
create unique index IDXCUSTOM3 on BPMDB_SCHEMA.LSW_USR_GRP_XREF (GROUP_ID) Include (DISPLAY_NAME) Allow Reverse Scans;
Rule of thumb here is to keep BPM database performance stable and the most effective way of doing that is to have a regular purge and index reorg/rebuild process in place.
Here is an example of such a purge process -
- BPM instances/tasks cleanup is executed every Saturday in the evening (~6-7 PM) - there is a number of ways how you can cleanup instances/tasks in BPM depending on your BPM version (wsadmin, stored procedure, REST API)
- Brazos Portal event_log table 'DELETE' events cleanup is executed every Saturday in a couple of hours after BPM instances/tasks cleanup (~8-9 PM) - see cleanup article reference below in this section
- Statistics update with full scan is executed daily early in the morning (~5 AM)
- Indexes optimization is executed daily in 1 hour after statistics update (~6 AM) with the following logic - reorg if 5% > fragmentation < 30% and rebuild if fragmentation > 30%
Generally there is already a maintenance plan in place for DB server instances that performs statistics updates and indexes optimization (reorg/rebuild) on a regular basis. However you might want to perform it more frequently, like daily, in order to have a good execution plans for REST API related queries and to keep fragmentation for brazos_portal.task, brazos_portal.event_log tables indexes on a good level. Also, make sure to update statistics and perform indexes optimization (reorg/rebuild) after BPM instances or/and Brazos Portal tables cleanup procedures.
When using REST APIs there are several parameters that can be tuned with the help of BP3 Support.
Considerations to polling tuning -
1) If you observe an increased delay in updating/refreshing tasks in Brazos Portal (REST API) and end users reporting that it takes time for the completed task to disappear.
2) You observe a noticeable increase in utilization of your database server.
Polling parameters that can be tuned in REST API version of Brazos Portal are:
- Number of REST API threads that run in Parallel. By default the Brazos Portal polls on 4 concurrent threads.
- Frequency of REST API polling.
By default the Brazos Portal polls the REST API every:
- 2 second per logged in user for active tasks
- 30 seconds per logged in user for closed tasks in active instances
These parameters are defined in the Brazos Portal database. Please open a support ticket with BP3 Labs support if you consider changing any of these, so, we can help you to perform proper updates to the tables.
It's important to keep your database server performance stable. The more data you have in table the slower the queries would be executing over time. So, it's important to establish a cleanup process for your BPM environment. This section talks about cleanup in BPM database (general guideline) and Brazos Portal database.
We won't be going into deep details of how BPM database can be cleaned up on regular basis simply because there are many different ways of doing that and at the same time there is a number of limitations depending on which version of BPM you're currently running on.
It's worth to note that there are two main types of cleanup in BPM database:
1) Runtime data cleanup. In other words, cleanup of BPD instances and all related data.
2) Application data cleanup. In other words, cleanup of application snapshots.
For #1 you have a number of tools for doing that depending on your version of BPM. Here are some links to comprehensive guides which tools you can use in which version of BPM:
For #2 see official documentation for BPMDeleteSnapshot and BPMSnapshotCleanup commands but more importantly be aware of a number of defects related to this command in IBM BPM. Always review the data from this technote before proceeding with deletion of snapshots. If you don't have all the required fixes installed then do not perform such a cleanup in your environment.
For the EVENT_LOG table cleanup please refer to the following article.
In addition to what's described in the referred article you may tune "Task Limits" parameter in Brazos Portal Admin page to manage the limits on completed tasks available to end users. This helps to maintain EVENT_LOG and TASK tables.
- To do that goto: http://host:port/brazos-portal/admin.html
- Open "Task Limits" tab and specify period of time in days after which completed tasks will no longer be available (default value is 30).