Database quickly growing issue

Camunda 7 Topics Discussion & Questions
1

Hi everyone,
I have a problem with my database. It’s growing so quickly. I’m using MySQL 5.7.24, Spring Boot and History Level Audit. Now my database is ~100GB.

Does anyone knows a technique or tip how to reduce the size of database? Can I delete byte arrays from ACT_GE_BYTEARRAY or move some history data to another database?

If I move some historic data to another database, how can I access it from history service?

Thanks in advance.

2

Hi Heril,

It is not recommended to delete byte arrays because they could be part of a running process and it will lead to problems.
One option to get rid of historic data which you do not need any more is the History cleanup feature.

Best regards,
Yana

3

@Yana, I can not delete historic data because I need them for reporting. Is there any way to use another database for querying historic process instances?

Table sizes:

  • ACT_HI_VARINST => 10.66 GB
  • ACT_HI_PROCINST => 0.46 GB
  • ACT_HI_TASKINST => 0.01 GB
  • ACT_HI_ACTINST => 15.92 GB
  • ACT_GE_BYTEARRAY => 29.49 GB
  • ACT_HI_DETAIL => 16.02 GB

Best regards,
Heril

4

Camunda provides you with quite some options all described here: History and Audit Event Log

1st: reduce the history detail level
2nd: implement your own history level (to only log what you really need)

In both cases, reading the data via HistoryService is retained. Any other option would most likely make you loose that option (e.g. via custom History Backend).

Anyway, didn’t you make some tests and calculations to predict the impact of the requirement that you have to follow for the database? Also, if you have such a requirement you should also have a time interval (e.g. 3 months, 1 year, 10 years, …) so that you actually can do your math for the database capacity and performance impact.

I’d also highly recommend to either use some auto-partitioning techniques or do manual / periodic partitioning of the history tables to maintain BPM performance over time. However, I cannot recommend any MySQL-specific stuff here because I am not that much into MySQL.

Generally speaking, the provided history levels from Camunda BPM are pretty generic and can give you a good start, but in most cases need to be thought of with your specific requirements for auditing in mind which requires changes to either fulfill the requirements or to optimize (on multiple levels: Processes, Camunda BPM configuration, database, (virtual) hardware?).

If you need to track “everything” also to retain data for future use-cases, you really should look into history event tracking pushing data into a different backend (which is a better fit for historic/time-series data). Depending on your requirements, this might be a better fit for you: JSON History Provider (usecase: ElasticSearch indexing)

5

Hi Team,

I am also facing the same issue. Over the period of a few weeks , the size grew up to a hundred GB. I initially deployed it with history level as “FULL” now I changed it to “AUDIT”. But how do I remove the un-necessary data in the history tables to reduce the size of the db. I checked all the history tables and found
REMOVAL_TIME_ is null for all the records, So history clean up wont work.

Is there a way to reduce the records at least up to the level of “AUDIT” for the existing data.

Regards,
Prasad
.

6

You should check out the best practice guide on how to deal with the histroy data:
https://camunda.com/best-practices/cleaning-up-historical-data/

7

@Niall , It doesn’t talk about existing data which is not configured with TTL. Also mentions

" Note that if you are not configuring any TTL, the corresponding process instances will never be cleaned up by the Camunda history cleanup feature".

8

It is possible to add a removal_time to existing data. Set Removal Time

9

Thanks @Bill_Powell :slightly_smiling_face:!!!

I have set the Removal time to an absolute value. Now

  • Is it safe if I delete those records having REMOVAL_TIME_ which is expired.

  • Does it affect any running processes if I delete records from history tables (tables starts with ACT_HI )

  • When I switched to history level “activity” , I could see that the tables ACT_HI_JOB_LOG,ACT_HI_DETAIL stopped getting populated. So removing all the records from these tables will put the existing data to “AUDIT” level which was at “FULL” earlier

Regards,
Prasad

10

Finally , I have set up the REMOVAL_TIME_ for a set of process instances and started cleaning. During the clean up process , it is getting stopped in between. When I checked the logs , I could see that there are deadlocks in the catalina.out logs.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 86) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Also some of the jobs are getting failed with the below exception

java.lang.NullPointerException
at org.camunda.bpm.engine.impl.persistence.entity.JobEntity.getJobHandlerConfiguration(JobEntity.java:478)
at org.camunda.bpm.engine.impl.persistence.entity.JobEntity.execute(JobEntity.java:136)
at org.camunda.bpm.engine.impl.cmd.ExecuteJobsCmd.execute(ExecuteJobsCmd.java:102)
at org.camunda.bpm.engine.impl.cmd.ExecuteJobsCmd.execute(ExecuteJobsCmd.java:39)
at

I am using Camunda 7.14 with the default configuration. Only thing I set is the window period for the cleanup .

Please help me

11

Hi All,

I have set REMOVAL_TIME for all the historical process instance. However after running the cleanup
DB size is not getting reduced. In the log file I could see a few exceptions. I have added closed to 20000 historical process instance to the batch for setting the REMOVAL_TIME_. Is this a high load for Camunda which in turn caused the below issues. Can someone please help me as this is very urgent since our DB is growing day by day.

  1.  Caused by: org.apache.ibatis.exceptions.PersistenceException:

    Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed

    The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Statistics.xml

    The error may involve org.camunda.bpm.engine.impl.persistence.entity.ProcessDefinitionStatisticsEntity.selectBatchStatistics-Inline

    The error occurred while setting parameters

    SQL: SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* from ( select RES.* , JOB.REMAINING_JOBS_COUNT_ , JOB.FAILED_JOBS_COUNT_ from camunda.ACT_RU_BATCH RES left outer join ( select JOB_DEF_ID_ , count(ID_) as REMAINING_JOBS_COUNT_ , sum(case when RETRIES_ = 0 then 1 else 0 end) as FAILED_JOBS_COUNT_ from camunda.ACT_RU_JOB JOB group by JOB_DEF_ID_ ) JOB on RES.BATCH_JOB_DEF_ID_ = JOB.JOB_DEF_ID_ …

    Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed

  2.      `25-Jul-2021 10:15:02.086 WARNING [Tomcat JDBC Pool Cleaner[1469821799:1627196512604]] org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been abandoned PooledConnection[ConnectionID:58]:java.lang.Exception25-Jul-2021 10:13:32.043 SEVERE [pool-2-thread-16531] org.camunda.commons.logging.BaseLogger.logError ENGINE-11003 Exception while firing event ROLLINGBACK: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.
 org.camunda.bpm.engine.ProcessEngineException: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.`

  3. Caused by: org.apache.ibatis.exceptions.PersistenceException:

    Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Statistics.xml

    The error may involve org.camunda.bpm.engine.impl.persistence.entity.ProcessDefinitionStatisticsEntity.selectBatchStatistics

    The error occurred while handling results

    SQL: SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* from ( select RES.* , JOB.REMAINING_JOBS_COUNT_ , JOB.FAILED_JOBS_COUNT_ from camunda.ACT_RU_BATCH RES left outer join ( select JOB_DEF_ID_ , …

    Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Regards,
Prasad

12

@prasadps you can check the demo on this link:

Maybe it will help you.

1 Like
13