Flowable forms - act_fo_form_instance slow query

Flowable Engine
1

Hey there,
We are using flowable 6.5.0 for modeling apps (processes + forms).
We have around 10 flowable processes currently running in production. Each of those 10 have atleast 10 user-tasks with forms attached. With more to come in near future.
Every day around 5-10k new process instances are created. and so around ~50k new form instances are created in act_fo_form_instance every day.
Currently we are having problem with form instance select query. It is taking too much time as compared to other queries. As a comparison: act_fo_form_instance select query is taking ~600ms avg time to return result while task select takes ~50 ms avg.
Currently our act_fo_form_instance table has 1M+ rows. and so I checked and found out that it doesnt have any index on form_definition_id and task_instance_id columns. Form engine uses these two columns to find the relevant form for a task. We use taskService.getTaskFormModel(taskId) to get the relevant form for the task.
I have attached two images below. First one shows the avg time graph for form instance select. Second one shows the breakdown for our api while it does multiple operations. As you can see form-instance select takes most of the time.

image
image1010×524 33.2 KB
image
image975×587 46.8 KB

Should we create a new index in act_fo_form_instances table manually?
or, should we delete excess data from act_fo_form_instance table? if so, is there any way to do it via cron jobs like history cleaning which was introduced in flowable 6.5.0

Thanks!

2

Yes, that would be the best way forward.

We’ll look at whether one can be added by default.

No, for form instances there’s no such delete query available.

3

@joram anything required from our side? or will you guys add it in the db scripts?

4

No, nothing needed. We’ll evaluate it and add it (but an index by taskId sounds reasonable).

5

Hey @joram, sorry for bugging you.

We are reluctant on adding this index manually on our db as this index would be duplicated when it will be added in flowable db scripts.
This is highly important for as our data has been growing continuously. Now the query is almost taking 1 second to execute.
Your help will be highly appreciated :slight_smile:

So, I wanted to know if we can expect this index to be present in next open source release (v6.6)?

6

Hey @urwah1996,

We added 3 indices to the ACT_FORM_INSTANCE table:

  • TASK_ID_
  • PROC_INST_ID_
  • SCOPE_ID_ and SCOPE_TYPE_

This was done in this commit.

An index on the form_definition_id was not needed since that is almost always accompanied with one of the others and you would anyways have a lot of entries for a particular task id.

Cheers,
Filip

1 Like
7

Hey @filiphr thanks alot!

any chance of these updates being made available on any minor release of v6.6?
or we will have to wait for 6.7 release?

8

It will depend on the kind of changes that will go in. Currently, the master branch is on 6.6.1-SNAPSHOT.