Perforamance optimisation

Hi,

We have several pretty simple bpmns. To test the system performance we loaded the database to have about 1 m, workflows created.

In the ACT_HI_VARINST table we have 19 486 694 records.
When we create a workflow now takes about 7-8 s. This is a big degradation from less than a second wit empty db.

On the ACT_HI_VARINST table we added and index on TASK_ID_. This improved the performance from 14 s to 7-8 s.

Looking at the insert times I noticed that several INSERT HistoricVariableInstanceEntity takes longer than the rest of the insert queries.

Which other indexes have you added and proved useful?

Thanks,
Sorin

Hi Sorin,

Im interested in what you have the history level set to? ie full, audit or activity?

For optimal performance, you can trade off history level, ie none would give you very good perormance, but you have to give up history…Full gives you the entire history, but you give up performance…

You can also tune the DB layer depending on your DB technology. One option is to only maintain a moving window of say n months in the history tables. Hence use a process to purge history older than n.

Some DB technology allows you to partition the tables. Hence if you partition on timestamps, new entries may go into smaller partitions. Note this is very DB technology and DBA skill dependent…

regards

Rob

2 Likes

Hi @sorin.maier,

thank you for the feedback, would you be interested in providing a pull request based on your findings?

Cheers,
Askar

Hi Rob,

we are using Oracle as DB.
The history level is set to FULL. We are relying on history information logged for reporting and audit purposes.

This are normal weekly volumes for our most demanding clients. I will talk with our DBAs regarding DB partitioning.

Regards,
Sorin

Hi Askar,

what do you mean by “providing a pull request”?

Regards,
Sorin

@sorin.maier can you use a secondary DB or a Log system to capture and process the logs rather than processing them into your runtime db ?

https://docs.camunda.org/manual/7.5/user-guide/process-engine/history/#provide-a-custom-history-backend

Hi @sorin.maier,
did you do the partitioning and if yes, does it help?

I was also implementing Custom History Backend but it doesn’t help too much (it reduces the amount of data generated every morning). Currently, my workflow is very slow and it needs 1h to process about 10K instances. I think the database size has an effect for slow processing.

Current database state:

  • ACT_GE_BYTEARRAY = 21.89GB
  • ACT_HI_ACTINST = 25.08GB
  • ACT_HI_DETAIL = 12.06GB
  • ACT_HI_VARINST = 16.92GB
  • ACT_HI_PROCINST = 1.32GB

Total database size: 113.1 GB

VM details:

  • 8 CPU
  • 16 GB RAM
  • 300 GB HDD

MySQL details:

  • MySQL 5.7.24
  • query_cache_type = 1
  • query_cache_size = 80M
  • query_cache_limit = 256K
  • join_buffer_size = 8M
  • innodb_buffer_pool_size = 10G
  • innodb_log_buffer_size = 256M
  • innodb_log_file_size = 2047M
  • innodb_read_io_threads = 8
  • innodb_write_io_threads = 8
  • innodb_flush_method=O_DIRECT

@hedza06 have you looked at using zeebe?

Hi @StephenOTT,
zeebe is not an option because I don’t have time to learn about it and implement something like that.

Also, I don’t have resources.

I will start with reducing data by archiving it to another database and start to make partitions. My question was will partitions make some progress in process engine.

Take a look at the picture Pool configs so you can adjust how many jobs are part of the pool and can be worked on at once. Also take a look at using a cluster set so you can have more engines working on your tasks. That’s the easiest way to increase your performance.

Many engines to use the same database?

Yes. Basically many nodes in your cluster. All talking to the same db. Also make sure to set “deploymentaware=false” so they will all execute jobs rather than just the node/engine that deployed.

Check out: https://m.youtube.com/watch?v=Nx4I8lNMUs0

Few things to improve performance:

  • Database partition (table level)
    
  • Reduce History level stored in the database
    
  • Secondary datasource for history data
    
  • Running multiple nodes in clustered mode against shared database
    
  • External task patterns for long running jobs
    
  • Isolation level as READ COMMITTED
    
  • Asynchronous Operations

MySQL won’t scale better when compared to PostgreSQL interms of multiple join query. For example, if you query task instance with process variables you’ll end up with atleast 3 inner join and exists function for each variables.

I have tested with 90k process instance, MySQL took most time and PostgreSQL is comparatively better.

Which partition type do you suggest?

For Postgres, it should be Declarative Partitioning and it’s an easier way to set up partitions.

Partitioning can drastically improve performance on a table when done right, but if done wrong or when not needed, it can make performance worse, even unusable.

If the table is determined to be “too big”, it’s time to look at the access patterns. Either by knowing the applications that access the database, or by monitoring logs and generating query reports with something like pgBadger, we can see how a table is accessed, and depending on how it’s accessed, we can have options for a good partitioning strategy.

@aravindhrs I’m using MySQL 5.7.24, not Postgres. All of the above mentioned tables has more then 1M rows. Now I’m investigating does range partitioning will be the best choice for boosting performance and somehow reduce the space on disk.

@hedza06 MySQL supports Horizontal partitioning which divides a table into multiple tables.

Various partitioning criteria are:

  • Range: This partitioning saves rows in a partition based on the value of a column falling within a given range. Range is great because you have groups of known IDs in each table, and it helps when querying across partition IDs.

  • List: This partitioning saves rows in the partition which matches the value of a column against a given list of values.

  • Hash: This partitioning saves rows based on a non-negative integer value returned by a user-defined function. Partitioning by hash “load balances” the table, and allows you to write to partitions more concurrently. This makes range queries on the partition key a bad idea.

  • Key: This partitioning saves rows based on the value returned by an internal MySQL hashing function.

To check if your engine supports partitioning:

SHOW PLUGINS

SELECT
    PLUGIN_NAME as Name,
    PLUGIN_VERSION as Version,
    PLUGIN_STATUS as Status
    FROM INFORMATION_SCHEMA.PLUGINS
    WHERE PLUGIN_TYPE='STORAGE ENGINE';

You can partition by range (periodical) or list (for tenants).