Improve clean-up for process instances with many activity instances

[toco-cam]

User Story (Required on creation)

see https://github.com/camunda/product-hub/issues/1521

Functional Requirements (Required before implementation)

see https://github.com/camunda/product-hub/issues/1521

Technical Requirements (Required before implementation)

• Improve the batch operation for setting a removal time for the history data of one or more process instances.
  1. In the update statements, also consider newRemovalTime != REMOVAL_TIME_ (i.e., only update rows where removal time differs from the current removal time).
  2. To avoid transaction timeouts, limit the amount of the to-be updated rows (e.g., by default to 500, streamlined with the history cleanup mechanism).
  3. When the update statement returns a row affected count == row limit, the job is rescheduled for being executed again.
• This special behavior is activated intentionally via a new batch operation flag. It does not replace the default behavior since it might degrade performance when selecting many process instances handled per execution job due to its more complex WHERE condition where each process instance is not related to a huge amount of child entities.

Limitations of Scope

• Setting removal times is only adjusted for the batch operation(s). The procedure after a process end event stays as is.

Hints

• By setting the removal time of history elements related to a running process instance, the history cleanup mechanism can already remove history data in the background. This would allow the running instance to finish normally after the cleanup is done since a huge part of its history has already been removed.
• The batch operation for setting a removal time to process instances currently only distributes the load by splitting up jobs by process instances or into groups of process instances. This leads to transaction timeouts since a single process instance's history data tree might be huge.
• The batch operation for deleting running process instances completes the process instances synchronously and thus also tries to set the removal time to all related history elements. It suffers from the same behavior as the "regular" completion of the process instance.

Links

• https://jira.camunda.com/browse/SUPPORT-14840
• Support cases: https://jira.camunda.com/browse/SUPPORT-14840?focusedCommentId=259738&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-259738

Breakdown

Backend

• Add a new flag to update table rows in chunks in the set removal time builder. (De-)serialize the flag in the SetRemovalTimeJsonConverter as well. Also, add an attribute that defines the chunk size with it.
• Determine the invocationsPerBatchJob through the batch job handler, e.g. return 1 in ProcessSetRemovalTimeJobHandler when the new flag is enabled. By default, return the defined engine config value. This ensures we only handle one process instance per job when the flag is enabled.
• Make the MessageEntity repeatable, so batch jobs are not always deleted after one execution but can be rescheduled. The postExecute method of the entity needs to be adjusted so it considers an attribute to decide whether it should be deleted or not, e.g. the repeat attribute or a new one.
• In ProcessSetRemovalTimeJobHandler, collect the update operations and hand them over (together with a reference to the current job) to a new TransactionListener on the state TransactionState.COMMITTED.
• Update the database mappings so they consider the chunk size when the flag is enabled and only update the removal time if it is NULL or different from the new one. Create an engine configuration flag that allows defining the chunk size. It has a default (and maximum) of 500. The chunk size can also be provided as input for the operation (e.g. via REST API).
• In the transaction listener, reschedule the job if there is at least one affected row count that equals the chunk size. Those tables might contain more entries to update. Optionally, pass on the tables that need updates to the next job run so only those tables receive update queries again. Otherwise, delete the job.
• If possible, to avoid orphan data in the history tables, update the historic process instance last after all other related data has been updated (e.g. in a separate, last job run).

REST API

• Add a new boolean attribute updateInChunks to the set removal time endpoint.
• Add a new integer attribute updateChunkSize to the set removal time endpoint.
• Pass on the new attributes to the set removal time builder.
• Document the new attributes in OpenAPI.

Dev2QA handover

• [ ] Does this ticket need a QA test and the testing goals are not clear from the description? Add a Dev2QA handover comment

---

### Pull Requests
- [ ] https://github.com/camunda/camunda-bpm-platform/pull/3687

[toco-cam]

@tasso94: Is it fair to summarize the solution ideas as: "Implementing the improved approach for setting removal time, we introduced with "Removal Time Based" for the clean-up job, now for batch"?

[tasso94]

Maybe something like this:

"Implementing an improved approach (avoiding database transaction timeouts) for setting a removal time via batch, inspired by the battle-proven approach we use for the removal time based history cleanup strategy".

Or:

"Avoiding database transaction timeouts when setting a removal time via batch, inspired by the battle-proven approach we use for the removal time based history cleanup strategy".

[tmetzke]

Initial investigations

To be investigated

✅When we cancel a running process instance (synchronously or asynchronously), the removal time is directly set to all related history elements as well (synchronously, either in the API call or in the job execution thread canceling the instance). This cannot be prevented currently. ✅When a process instance ends (nominally or canceled), existing removal times in related history elements are overridden if they already exist. We don't consider existing removal times when updating the removal time in related history elements upon process instance completion. ✅Can MySQL update queries with LIMIT also be run using an INNER JOIN instead of a sub-select? Does this have any performance impact?

Tables per batch job

Two general ideas: 1. One batch job 1. Keep one batch job, update all tables at once. 1. Reschedule until all rows of all tables are updated. 1. Parallel batch jobs 1. One job per table, updating only the defined table. 1. Reschedule each batch job until all rows of the respective table are updated. ### One batch job 1. If new flag enabled, start with all tables 1. Update all tables using the limit. 1. If updated rows == limit for at least one table, reschedule job for all tables. 1. Else no follow-up job. Batch job and batch are done. #### Pros * Very similar to what we do with history cleanup (removal time). We can probably reuse some code to get this running. * Easy to establish, not messing with current batch job creation (considering `invocationsPerBatchJob`, `batchJobsPerSeed`, and process instance ID lists/mappings). Still one batch job per process instance (by default, can be adjusted using `invocationsPerBatchJob`) that is simply rescheduled if necessary. * Only a bit of the existing update code needs to be adjusted (still updating all tables at once, only introducing a limit and collecting update results). #### Cons * Potentially slower than parallel jobs; one job updates rows in all tables at once and is rescheduled if necessary. * Potentially firing off update queries for tables that are already done multiple times, e.g. if only one table has a lot of data. ### One batch job, only required tables 1. If new flag enabled, start with all tables 1. Update all tables using the limit. 1. Collect tables with `updated rows == limit`. 1. If collected above is not empty, reschedule job for all collected tables. 1. Else no follow-up job. Batch job and batch are done. #### Pros * See above in "One batch job" * Only updates those tables that need an update. #### Cons * Potentially slower than parallel jobs; one job updates rows in all tables at once and is rescheduled if necessary. * A bit more complex since we need to adjust the batch configuration between job runs to keep track of the tables we update. ### Parallel jobs for all tables 1. If new flag enabled, create one job per table (from a hardwired list) in `#createJobEntities` or `#createJobs` of `BatchSetRemovalTimeJobHandler` (overriding from `AbstractBatchJobHandler`). 1. Inside those jobs, update all rows until limit. 1. If updated rows == limit, reschedule job for the table. 1. Else no follow-up job. Batch job is done. 1. When all table jobs are done, batch is done. #### Pros * Potentially faster than rescheduling one job for all tables and rows; tables are handled in parallel (depends on the parallelism of the job executor though). * Update queries only issued until necessary per table. If a table contains lots of rows to be updated, it is updated. Otherwise, the respective batch job is done. * Can be an improvement iteration of the "all tables at once" solution above. It would use the same mechanisms overall but split up the batch job and the table(s) to update per job. #### Cons * Could be tricky to establish the right amount of jobs (considering `invocationsPerBatchJob`, `batchJobsPerSeed`, and process instance ID lists/mappings). The `batchJobsPerSeed` determines how many batch jobs can be created per batch seed. Since we have to update 12 tables per process instance, we might have to consider this when calculating the number of jobs to create for a seed job (and potentially creating a new seed job). We also have to think about how `invocationsPerBatchJob` is handled (allowing multiple process instances per batch job and thus table or not). * Needs more new code to handle updates per table (collect the list of tables to update, keep track of which table to update).

Process instances per batch job

### Multiple PIs per batch job * If `invocationsPerBatchJob` >1, multiple PIs can be present in one batch job. * We can handle them inside the job or allow only 1 per job (basically ignoring `invocationsPerBatchJob`). * Handling multiple PIs per job is possible. It makes it more complex though since we need to adjust the batch configuration between job runs to keep track of the instance we handle. Without this, we wouldn't have to do this. ### Only one PI per batch job * Ask the batch job handler for the number of allowed `invocationsPerBatchJob`. Let the `ProcessSetRemovalTimeJobHandler` return `1` when the new flag is enabled. As a result, we'll always create one batch job per process instance. * Potentially, no need to store any updates in the BatchConfiguration anymore. Re-schedule the job until all table update queries return row counts below the limit. * Parallelizes on process instances since each instance is handled in a separate (recurring) batch job (this is the default behavior for all batch jobs but can be adjusted via `invocationsPerBatchJob` in the engine config).

Repeating batch jobs

* Usually, a batch job (`MessageEntity`) runs exactly once. After the run, it deletes itself. * Batch job configuration data is deleted after the batch job handler execution already. * Idea: * Keep config data until the job is deleted (already implemented with `onDelete` call in the job handler) - don't delete the data after the batch job handler execution already. * Reuse the `EverLivingJobEntity` (used in History Cleanup) as much as possible. * Create a transaction listener that inspects affected rows of the update queries (needs a reference to the DB operations and the job). * Delete the job from the transaction listener when all numbers below the limit (no re-run necessary anymore). * The transaction listener can adjust the job's batch configuration if necessary (e.g. the process instance id for the next run) by creating a new byte array entity and setting its ID in the job's configuration. * The batch job is repeated until all tables have been adjusted for all defined process instances (by default only one instance). * Considerations for engine update scenarios: * To support upgrade scenarios (batch and/or batch jobs created with old engine, picked up by new engine), the set removal time job handler only needs to reschedule the job if it's an `EverLivingJobEntity`. For example, the transaction listener only needs to be created for such cases. * To support rolling update scenarios (batch jobs created with new engine, picked up by old engine), reusing the `EverLivingJobEntity` becomes a problem since it's never deleted by the old set removal time batch job handler (it never creates the transaction listener that deletes the job). The following alternatives exist: * Create a new batch operation for "set removal time to process instances in chunks" that uses a new type. There would be no handler for such a job in the old engine in the rolling update scenario. * Result: `JobEntity#execute` will run into an NPE since no job handler can be found. * Create a new entity type "repeatable batch job" that is used in the updated "set removal time handler". This would not be known by the old engine in the rolling update scenario. * Result: This fails in loading the job from the database as the type is unknown. * Make the `MessageEntity` repeatable in the new engine. The old engine will still be able to execute the jobs as usual (they will only be repeated with the new flag which is not allowed to be used in rolling updates due to [our policy](https://docs.camunda.org/manual/develop/update/rolling-update/#usage-of-new-features)). * Suggestion: * Make `MessageEntity` repeatable.

Track tables to update

* Context: * We have a set of `DbOperation`s from executing update statements. * We pass on those operations to the transaction listener to track the affected rows. * Problem to solve: map between operations in listener and statements to execute in `HistoricProcessInstanceManager` and `HistoricDecisionInstanceManager`. * Idea: * Each `DbOperation` has a related `DbEntity` class. Those entity classes are unique per operation call we execute from the two manager classes. We map the entity class names to update operations we can invoke. * We need to keep the same order of operations we currently have since we explicitly use `updatePreserveOrder` calls. * We can work with a list of key-value pairs of entity class names and operation calls. * Alternatively, we can also introduce IF statements in the current methods to check if the next operation can be executed or not (introduces quite some code duplication, though). * In a batch job run, we go through the list of operation calls and only execute those whose entity class name is in the list of entities to update (an empty/null list also means execute since we will have to start with that and will never reschedule the job with an empty list again). * In the listener, we extract the names of the entity classes from the operations that have an affected row count == limit (and thus need to be executed again). We store a `Set` of those entity class names in the batch job configuration for the next run. If the list of entities to update is empty, we are done with the batch job.

Database updates with LIMITs

* General observations: * Some databases (e.g. PostgreSQL) use full table scans instead of index scans when updating with a LIMIT if that is considered cheaper due to a low-enough limit. For higher LIMIT values, they may also use index scans again. * The existing indexes (e.g. on ACT_HI_ACTINST) on process instance ID and root process instance ID were considered in the LIMITed update queries on my test subset of databases (Oracle, PostgreSQL, MySQL) and tables (ACT_HI_ACTINST, ACT_HI_DETAIL, ACT_GE_BYTEARRAY). * Creating new composite indexes on (ROOT_)PROC_INST_ID_ and REMOVAL_TIME_ doesn't seem plausible to me. The optimizers use an index scan on the process instance ID and then scan the result set for the first LIMIT rows that do not have a removal time or a wrong one. ### H2 ```sql UPDATE ACT_HI_ACTINST SET REMOVAL_TIME_ = '2023-08-...' WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') LIMIT 500 ``` ### DB2 ```sql UPDATE ACT_HI_ACTINST SET REMOVAL_TIME_ = '2023-08-...' WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') FETCH FIRST 500 ROWS ONLY ``` ### Oracle ```sql UPDATE ACT_HI_ACTINST SET REMOVAL_TIME_ = '2023-08-...' WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') AND ROWNUM <= 500 ``` ### PostgreSQL, CockroachDB * Requires a sub-select where a LIMIT can be used. ```sql UPDATE ACT_HI_ACTINST SET REMOVAL_TIME_ = '2023-08-...' WHERE ID_ IN ( SELECT ID_ FROM ACT_HI_ACTINST WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') LIMIT 500 ) ``` ### MySql, MariaDB * Requires sub-selects where a LIMIT can be used. * This can become inefficient when it uses sub-selects. It might be advisable to use an INNER JOIN. ```sql UPDATE ACT_HI_ACTINST SET REMOVAL_TIME_ = '2023-08-...' WHERE ID_ IN ( SELECT ID_ FROM ( SELECT ID_ FROM ACT_HI_ACTINST WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') LIMIT 500 ) tmp ) ``` The INNER JOIN variant would look like the following: ```sql UPDATE ACT_HI_ACTINST INNER JOIN ( SELECT ID_ FROM ACT_HI_ACTINST WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') LIMIT 500 ) tmp using (ID_) SET REMOVAL_TIME_ = '2023-08-...' ``` ### MS SqlServer ```sql UPDATE TOP (500) ACT_HI_ACTINST SET REMOVAL_TIME_ = '2023-08-...' WHERE ROOT_PROC_INST_ID_ = '5' AND (REMOVAL_TIME_ is null or REMOVAL_TIME_ != '2023-08-...') ```

Potential scopes and iterations

1. One batch job, one process instance per job - this is the basis, it is the smallest increment needed and reuses most of what's already there. 1. Depending on priority, either 1. Only update affected tables in subsequent runs * Avoid executing unnecessary table update queries in subsequent runs of the batch job. * Requires adjusting the configuration between job runs and keeping track of the tables to handle. Requires the database update code to keep a list of update "items" that can be skipped optionally. * Effort S 1. Allow multiple process instances per batch job * Don't ignore a potential custom `invocationsPerBatchJob` configuration. * Requires adjusting the configuration between job runs and keeping track of the instance to handle. * Effort S * _Note:_ Might not be necessary at all since we consider this to be for exceptional cases where one process instance got out of hand. 1. Parallel batch jobs * Minimizes the number of queries for getting the job done since tables aren't queried that are done already. * Requires overwriting the job creation routine for batch jobs which could be tricky and requires some consideration. * _Note:_ Might create more concerns than it solves issues given we implement option 1. * Effort M 1. Batch operation "Delete running process instances" can set removal times in batches as well * Creates a one-stop-shop for getting rid of unwanted process instances, even with large row numbers. * Needs investigation on how setting removal times can be skipped in the internal process end event and how to trigger the batched removal time setting afterward. * Effort M * _Note:_ Might not be necessary at all with the following workaround: 1. Remove the HTTL from the process (instance). 1. Set the removal time to `null` for the instance. 1. End the process either normally or cancel it. No removal time is set since no HTTL exists. The process should end without transaction issues. 1. Set removal time to process instance using the new batch operation flag to update in chunks.

Required changes

### One batch job, one PI #### Backend - Add a new flag to `SetRemovalTimeToHistoricProcessInstancesBuilder` and `SetRemovalTimeBatchConfiguration` to enable taking a row limit into account on updates; copy the flag from builder to config in `SetRemovalTimeToHistoricProcessInstancesCmd`; (de-)serialize the flag in the `SetRemovalTimeJsonConverter`. - Determine the `invocationsPerBatchJob` through the batch job handler, e.g. return `1` in `ProcessSetRemovalTimeJobHandler` when the new flag is enabled. By default, return the defined engine config value. - Create a `RepeatableBatchJobDeclaration` that uses the `EverLivingJobEntity` instead of the `MessageEntity`. - In `ProcessSetRemovalTimeJobHandler`, collect the update operations and hand them over (together with a reference to the current job) to a new `TransactionListener` on the state `TransactionState.COMMITTED`. - In the update operations and mappings, consider the batch limit when the flag is enabled and only update the removal time if it is NULL or different from the new one. - In the transaction listener, reschedule the job if there is at least one affected row count that equals the batch limit. That table might contain more entries to update. Otherwise, delete the job. #### REST API - Add a new attribute `updateInChunks` to the set removal time endpoint. - Pass on the new attribute to the set removal time builder. #### Frontend - Add a new checkbox option `Update in chunks` to the operation dialog of the `PROCESS_SET_REMOVAL_TIME` operation. - Pass on the new option to the endpoint as `updateInChunks`.