Emptying / Deleting the process engine history tables in database

[sascha.karch...@gmail.com]

Hi Folks,

We are running out camunda instance with history-level=full.
This is great for debugging problems etc.

But the datbase (especially the history tables) have become very large now.

We like to empty them.

So the question is:
Is it safe to empty the history Database tables (according to http://docs.camunda.org/latest/guides/user-guide/#process-engine-database-schema these are the tables prefixed with ACT_HI) by simple SQL "DELTE FROM ...." statements?
Or are there any things to consider?

We would be glad for help.

[thorben....@camunda.com]

Hi Sascha,

There are several ways of purging the history:

1. You can use the java API and methods like RepositoryService#deleteDeployment with the cascade option set to true if you do not need an entire deployment any more or HistoryService#deleteHistoricProcessInstance for single process instances. Of course, this is less efficient than using plain SQL but is public API and therefore tested.

2. You can use plain SQL as you suggest. You should be able to safely delete all historic process instances (and activity instances, task instances) etc. for all process instances that have actually ended (meaning that ACT_HI_PROCINST.END_TIME_ is not null). For non-ended instances, historic entities are updated with time stamps later on. After a quick look at the code, the engine should tolerate the case in which the historic process instance is gone for a running process instance. However, we do not guarantee this behavior and I think there are no tests for that. Another aspect to consider is that the table ACT_GE_BYTEARRAY is shared by both, history and runtime. It is used to persist process variables that are BLOBs. Accordingly, you cannot simply purge the entire table.

I hope this answers your question.

Best regards,
Thorben

[sascha.karch...@gmail.com]

Hi Thorben,

Thanks for the quick help.
We tried the "official way" with HistoryService#deleteHistoricProcessInstance.
This worked in one of our test installations.

But on the productive installation it somehow failed.

Here's the java class we've written:
http://pastebin.com/DUgjgcqb

It provides some REST mehtods to query historic information as well as deleting historic finished process instances.

In our productive system we weren't able to delete historic process instances because there was a lock problem with database table ACT_GE_BYTEARRAY.
Problem is that HistoryService#deleteHistoricProcessInstance somehow locks the table ACT_GE_BYTEARRAY and then subsequently isn't able to delete any records from this table. Like it's tricking itself. (server log is http://pastebin.com/BdzRQ8Ed)

We're using camunda 7.1.0 (prepackged with jboss downloaded from camunda page) with mysql and hibernate. Do you have any ideas?

[Daniel Meyer]

Hi,

Does it help to upgrade to 7.2? We changed the delete behavior for historic detail entities to make sure that locks on the ACT_GE_BYTEARRAY table are held for a shorter period of time. [1]

Cheers,

Daniel

[1] https://app.camunda.com/jira/browse/CAM-2912

[sascha.karch...@gmail.com]

Hi Daniel,

Thanks for the quick reply.
We will try it and inform you about the outcome.