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.
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.
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.
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:
PLUGIN_NAME as Name,
PLUGIN_VERSION as Version,
PLUGIN_STATUS as Status
WHERE PLUGIN_TYPE='STORAGE ENGINE';
You can partition by range (periodical) or list (for tenants).