I want to offload the History stream to a completely separate MySQL server. I don’t really want to customize anything, I just need the History database itself to run on separate server from the runtime database. We are using Camunda 7.6.2-ee/WildFly 10.1.0 with a MySQL commercial 5.7.18 database.
Moreover, it would be interesting to know if such an external database would need a READ COMMITTED isolation level. If it did not require that isolation level, then the very “expensive” process of maintaining history could be done by a horizontally scaled cluster.
What I’m hoping we can do is something as simple as changing a configuration value that tells DbHistoryEventHandler to read-write to a different server than runtime database. We don’t want to change what/how the history gets recorded or how the Camunda web app interacts with it, we just want the history database I/O overhead to be on a different server.
currently, the Camunda BPM engine doesn’t allow to configure another database for history data. You can use a custom history backend by providing a custom history event handler but the history queries wouldn’t use the custom backend by default. So the history rest Api and the Camunda web apps wouldn’t have any history data.
Maybe you can replace the default history event handler by a DbHistoryEventHandler which uses another database, and redirect all history rest request to another process engine that access this history database. Or, you could replace the history manager for the queries to access the other database.
Perhaps the solution for you is to use a custom history plugin. That way you could effectively ‘fork’ the history event stream. Write a minimum subset of history events to the engine’s database instance to simulate history level at ACTIVITY. Write all events to an event buffer/queue for subsequent writing to an alternate database. Thus you can perform deep analytics or audit trails on the alternate database.
Of course this only works provided the buffer/queue you use is sufficient in terms of durability and faster to write to than the native database…
We’ve written a crude Java class to dump variables to an external service which will then asynchronously shove the entire output of execution.getVariables() into a JSON object and put it into a completely separate MySQL database. In the database, we use a table with indexed columns for certain key values and a JSON data type for the process variables. We’ll fire this at various points throughout the process. This means we turn Camunda history down to ‘activity’, which a crude measurement appears to write only 5% of the data as ‘full’.
We’re preparing a data set to test performance on the MySQL server and then we’ll have to stress test this in Camunda.
This essentially shifts some of the burden back to Camunda, some of it to an external service, and the rest to the other database. We’ve no idea if this will work and there are countless permutations of how to handle this. This is just the first.
The fact that we’re using these variables only for debugging, historical analysis, and audit requirements means we can use a non READ COMMITTED database cluster if we have to.
I’m also curios about storing the history data to another database. I am quite surprised this is not an option already.
What is needed to make the query part read from the other database. I understood from the comments and browsing the code that writing should be possible via a custom DbHistoryEventHandler but reading needs to be fixed.
From what I can tell HistoryServiceImpl implements a command pattern and delegates the query execution to an implementation of a manager, like HistoricProcessInstanceManager. This in turn uses a Session to get the DbEntityManager instance to make the actual queries.
Now, it should be possible to produce another DbEntityManager for the history related parts, one that uses another datasource. I’m not familiar enough with the code. It would be nice to get a confirmation about this.
Thanks,
Example for making a history database call -> delegate to the HistoricProcessInstanceManager.
public long executeCount(CommandContext commandContext) {
checkQueryOk();
ensureVariablesInitialized();
return commandContext
.getHistoricProcessInstanceManager()
.findHistoricProcessInstanceCountByQueryCriteria(this);
}
@SuppressWarnings({"unchecked"})
public <T> T getSession(Class<T> sessionClass) {
Session session = sessions.get(sessionClass);
if (session == null) {
SessionFactory sessionFactory = sessionFactories.get(sessionClass);
ensureNotNull("no session factory configured for " + sessionClass.getName(), "sessionFactory", sessionFactory);
session = sessionFactory.openSession();
sessions.put(sessionClass, session);
sessionList.add(0, session);
}
return (T) session;
}
public DbEntityManager getDbEntityManager() {
return getSession(DbEntityManager.class);
}