We met a performance issue with filters and authorization, after around 1000 tasks in the response, the filter execution get significantly slower. Around 5000 tasks, it takes 30 seconds.
The root cause of the issue seems to be that MySQL is not able to efficiently use index inside JOIN ON IN statement and have to do full table scan.
SELECT Count( DISTINCT res.id_ )
FROM ACT_RU_TASK res
inner join ACT_RU_IDENTITYLINK i
ON i.task_id_ = res.id_
left join ACT_RE_PROCDEF procdef
ON res.proc_def_id_ = procdef.id_
left join ( SELECT a.*
FROM ACT_RU_AUTHORIZATION a
WHERE a.type_ < 2 AND
( a.user_id_ IN ( âdemoâ, ââ ) OR
a.group_id_ IN ( âcamunda-adminâ, âmanagementâ, âaccountingâ, âsalesâ ) ) AND
(( a.resource_type_ = 7 AND
a.perms_ & 2 = 2 OR
a.resource_type_ = 6 AND
a.perms_ & 64 = 64 )) ) auth
ON ( auth.resource_id_ IN ( res.id_, procdef.key_, 'â ) ) #<-- this is the problematic part
WHERE res.assignee_ IS NULL AND
res.assignee_ IS NULL AND
i.type_ = âcandidateâ AND
( i.group_id_ IN ( âaccountingâ, âcamunda-adminâ, âmanagementâ, âsalesâ ) ) AND
res.suspension_state_ = 1 AND
( ( res.case_execution_id_ IS NOT NULL ) OR
( auth.resource_id_ IS NOT NULL ) );
âtableâ: {
âtable_nameâ: âaâ,
âaccess_typeâ: âALLâ,
âpossible_keysâ: [
âACT_UNIQ_AUTH_USERâ,
âACT_UNIQ_AUTH_GROUPâ,
âACT_IDX_AUTH_GROUP_IDâ,
âACT_IDX_AUTH_RESOURCE_IDâ
],
ârows_examined_per_scanâ: 18421,
ârows_produced_per_joinâ: 3610516,
âfilteredâ: â100.00â,
ârange_checked_for_each_recordâ: âindex map: 0x1Eâ,
âcost_infoâ: {
âread_costâ: â426.85â,
âeval_costâ: â722103.22â,
âprefix_costâ: â729195.28â,
âdata_read_per_joinâ: â6Gâ
},
âused_columnsâ: [
âTYPE_â,
âGROUP_ID_â,
âUSER_ID_â,
âRESOURCE_TYPE_â,
âRESOURCE_ID_â,
âPERMS_â
]
}
It happens on 7.5.0 and 7.6.0 using MariaDB 10.1, MySQL 5.6, MySQL 5.7, PostgreSQL 9.4. On H2 it is significantly faster. Using DB2 or Oracle is not really an option for us.
Version 7.4.0 use different approach and even though the queries also get slower, it is not as significant, but the SQL is very huge (over 600 lines for just few users, groups) and it gets slower with larger number of groups.
Steps to reproduce:
1. Start with clean Camunda
2. Start 5000 processes by calling POST engine-rest/process-definition/invoice:2:${PROCESS_ID}/submit-form
a. Randomly generated values for variables
3. Execute filter Accounting GET camunda/api/engine/engine/default/filter/{$ID_ACCOUNTING_FILTER}/count
4. Execute similar query without authentication (use engine-rest) and add the criteria
POST engine-rest/filter/{$ID_ACCOUNTING_FILTER}/count
{âcandidateGroupsâ:[âaccountingâ],âprocessVariablesâ:[],âtaskVariablesâ:[],âcaseInstanceVariablesâ:[],âfirstResultâ:0,âmaxResultsâ:15,âsortingâ:[{âsortByâ:âcreatedâ,âsortOrderâ:âdescâ}],âactiveâ:true}
Result:
- Camunda 7.6.0 API with authentication - 30s
- Camunda 7.4.0 API with authentication - 1s
- Camunda (both versions) API without authentication - 100ms
From reading the documentation and the forum, it isnât really clear what is the recommendation and if there is some way how to force the DB to use the key? Or is there some other ideas how to handle larger number tasks in the filters?