Every now and then, we get tickets complaining about severe performance problems in Axon Ivy processes, not too long after the initial Going Live of the respective application.
What we do in these cases is get an overview of the status of the Axon Ivy System Database of the slow application:
- get the number of cases and tasks in their tables, grouped by their state
- get the number of rows and size of these same tables
Out of these simple metrics, we usually find the reasons for the performance problem, namely
- a very low ratio of active process elements vs. total process elements
- a lot of deleted rows in the tables without them being physically removed.
The Root Causes
Let's have a closer look at the active/total ratio.
For cases, only states CREATED (0) and RUNNING (1) are active states, all others are terminal - no more changes after the case has been set into one of these states.
For tasks, the active states are CREATED (0), READY_FOR_JOIN (1), JOINING (2), DELAYED (3), SUSPENDED (4), RESUMED (5), UNASSIGNED (9), and FAILED (10). All others are, again, terminal.
Once an element enters a terminal state, it will not change again, so, it will take a slot in every table, view and index, but will not change. This is a looming problem, because database systems tend to automatically reorganize their indices that make queries quick if a certain percentage of data in the index has changed. If we have an ever-growing number of static entities, this percentage will trend hyperbolically towards zero. Once below the threshold of your Database Management System (DBMS), your DBMS will no longer help you to keep your queries quick.
For SQL Server, this threshold is at 20%. For other DBMSs, it may be different, but the effect will catch up with you.
Even worse is the second problem: deleted, but not removed data sets.
Often, we see 90% or more "dead wood" - space taken, data of no further use.
In other words, your database steadily grows in size.
There are several parts to the solution to the problems above:
- Maintain your Axon Ivy system database!
- Define and implement a Retention Policy
Maintaining an Axon Ivy System Database
Database Maintaince is a job usually executed by Database Administrators. If you have a DBA at hand, it is a good idea to let him take care of the maintenance.
For those who don't have the luxury of a DBA, this is largely a series of technical tasks that can be automated. It depends on the DBMS what you do. For PostgreSQL, the VACUUM command may help, for SQL Server, there is the excellent open-source solution of Ola Hallengren. For other DBMSes, a quick google of "Index Statistic Maintance" with the name of the DBMS will reveal solutions. Do not hesitate to peruse sites like StackOverflow and StackExchange.
Now that maintenance is in place, there is still one thing left to do to keep the size of your System Database in check: Since your application executes cases and their tasks, it adds more cases and tasks to the database with every process instance.
To get this growth under control, there is just one solution: Delete finished cases.
Since it is not yours to define what happens with the end user's data, you will have to define a Data Retention Policy for your application.
There are a few questions that help to set the base for such a policy:
Is the data of completed cases still needed, e.g.,
is the system database used as an archive for later lookups and retrievals?
This is not what we had in mind when conceiving Ivy, but is is both legitimate and possible.
are there regulatory requirements that force you to keep this process data available?
Or, are there clear indications that the data is no longer needed:
- is relevant process data stored into an archive, or passed over to an ERP or another application?
- do you need to delete the data to comply with GDPR?
If the data is not needed after a case has finished, then you are lucky, because you can now define the parameters for the management of the data of finished (which encompasses both completed and aborted) processes:
- how long do we have to retain this data (the threshold)?
- how often do we delete data older than the threshold defined above?
Once the questions are answered, you need to design and implement the data removal as part of your application. Whether you implement this in the database or as a separate process in your application is up to you.
We did not include process data deletion in Axon Ivy because there is no one-size-fits-them-all solution.
But, we made sure that you can do what is needed by adding triggers to the System Database which retain referential integrity if you delete cases or tasks.