ClaimTask call depends on history table?

Flowable Engine
1

Due to the heavy-volume/scale at which we use Flowable, our history tables have gotten quite large and I haven’t yet implemented any archiving.

I noticed that when you claim a task, Flowable queris the act_hi_actinst table. Why does it need to that? Shouldn’t it only depend on the runtime tables ideally? In our case, postgres decided to use the wrong index for some reason, it’s using the end_time index and it’s causing trouble with slow response times. And we are still on Flowable 6.3.0 at the moment.

Stack trace for reference below

Any thoughts? Thanks!

### Error querying database.  Cause: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command
### The error may exist in org/flowable/db/mapping/entity/HistoricActivityInstance.xml
### The error may involve org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityImpl.selectUnfinishedHistoricActivityInstanceExecutionIdAndActivityId-Inline
### The error occurred while setting parameters
### SQL: select * from ACT_HI_ACTINST RES     where EXECUTION_ID_ = ? and ACT_ID_ = ? and END_TIME_ is null
### Cause: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at org.flowable.engine.common.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:199)
    at org.flowable.engine.common.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:193)
    at org.flowable.engine.common.impl.db.DbSqlSession.selectList(DbSqlSession.java:155)
    at org.flowable.engine.common.impl.db.DbSqlSession.selectList(DbSqlSession.java:160)
    at org.flowable.engine.common.impl.db.DbSqlSession.selectList(DbSqlSession.java:140)
    at org.flowable.engine.common.impl.db.AbstractDataManager.getList(AbstractDataManager.java:149)
    at org.flowable.engine.common.impl.db.AbstractDataManager.getList(AbstractDataManager.java:142)
    at org.flowable.engine.impl.persistence.entity.data.impl.MybatisHistoricActivityInstanceDataManager.findUnfinishedHistoricActivityInstancesByExecutionAndActivityId(MybatisHistoricActivityInstanceDataManager.java:57)
    at org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityManagerImpl.findUnfinishedHistoricActivityInstancesByExecutionAndActivityId(HistoricActivityInstanceEntityManagerImpl.java:46)
    at org.flowable.engine.impl.history.AbstractHistoryManager.findActivityInstance(AbstractHistoryManager.java:292)
    at org.flowable.engine.impl.history.AbstractHistoryManager.findActivityInstance(AbstractHistoryManager.java:257)
    at org.flowable.engine.impl.history.DefaultHistoryManager.recordTaskInfoChange(DefaultHistoryManager.java:303)
    at org.flowable.engine.impl.history.DefaultHistoryTaskManager.recordTaskInfoChange(DefaultHistoryTaskManager.java:30)
    at org.flowable.task.service.impl.persistence.entity.TaskEntityManagerImpl.changeTaskAssignee(TaskEntityManagerImpl.java:64)
    at org.flowable.task.service.impl.TaskServiceImpl.changeTaskAssignee(TaskServiceImpl.java:67)
    at org.flowable.engine.impl.util.TaskHelper.changeTaskAssignee(TaskHelper.java:116)
    at org.flowable.engine.impl.cmd.ClaimTaskCmd.execute(ClaimTaskCmd.java:58)
    at org.flowable.engine.impl.cmd.ClaimTaskCmd.execute(ClaimTaskCmd.java:27)
    at org.flowable.engine.impl.cmd.NeedsActiveTaskCmd.execute(NeedsActiveTaskCmd.java:58)
    at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:51)
    at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:93)
    at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:72)
    at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:56)
    at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:25)
    at org.flowable.engine.common.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53)
    at org.flowable.engine.common.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:71)
2

I’m not entirely certain that this is your current problem, but by default the engine records history in the same transaction that it updates the process. This can cause some bottle necks and, as a result, Flowable introduced asynchronous history. You may get some perceived performance increase by turning it on. The History queries and inserts will still happen, of course, but they’ll happen when the user isn’t watching.

3

Yeah, I think it’s because our logging level is set to AUDIT and as part of the task assignee change history archiving it queries the history tables. I can’t to switch to async history logging at the moment unfortunately without some code changes on our end.

Our problem really is postgres suddenly is using an inefficient index despite the EXECUTION_ID and ACT_ID passed in the query, it choose the act_idx_hi_act_inst_end index because it exists. I have tried various postgres tricks like vacuum and reindex the table to no avail.

Do you happen to know what purpose the act_idx_hi_act_inst_end index serves? Dropping this in one option, as I don’t see any obvious code in flowable that relies on this index’s existence directly.

4

Database algorithms are very complex, most likely some threshold was reached what made it tip over to use a different approach for getting the data.

Async history indeed solves this, but as you mention, it comes with programmatic changes.

It looks like this index is used primarily for querying on the end time (which is a frequent choice for the historic activities). All indices in Flowable can be dropped if needed, no logic depends on them.

Lastly, the next version of Flowable will have API’s in the HistoryService to delete historical data using the query API (see for example flowable-engine/modules/flowable-engine/src/main/java/org/flowable/engine/HistoryService.java at main · flowable/flowable-engine · GitHub), and also using async jobs for this.