Camunda - Database growing quickly issue

user_4078505 0
Viewed 1996

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?

Current size of my table is:

  • 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

P.S. I've started to modify processes to use small(simple) variables instead of "big" variables - serialized objects that are too big, to reduce size of ACT_HI_VARINST.

Thanks in advance.

spring-boot camunda mysql java
2 Answers

You will have to look into the History Time To Live (TTL) feature that was introduced in the past year. You can define a time interval after which old instances will be removed. If you still need the data for reporting, consider exporting it to a suitable storage, maybe camunda optimize/elastic is a solution here. So in short: define TTL and write exporters that store important data somewhere else before it's deleted.

Keep in mind that storage management for a (camunda) process application is no trivial matter, depending on the nature of your processes (straight through vs. long running) and your auditing requirements you will need a large schema.

user_2904250

Perhaps you should start thinking about creating partitions on existing tables.

Due to not having enough information about mentioned tables ,it would be difficult to provide code snippet, but perhaps below mentioned SQL snippet would assist you:

ALTER TABLE data_table 
PARTITION BY RANGE( UNIX_TIMESTAMP(last_updated) ) ( 
PARTITION p_first VALUES LESS THAN (UNIX_TIMESTAMP('2000-01-01 00:00:00')), 
PARTITION p200001 VALUES LESS THAN (UNIX_TIMESTAMP('2005-01-01 00:00:00')), 
PARTITION p200501 VALUES LESS THAN (UNIX_TIMESTAMP('2010-01-01 00:00:00')), 
PARTITION p201001 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p_future VALUES LESS THAN (MAXVALUE) );

Once you have these partitions created, you should be able to drop old partitions.

user_11147180
Related