We have encountered a longstanding issue related to Cadence and Cassandra. When making a request to retrieve a list of closed workflows (either "closed" or "completed") via Cadence-web or cli cadence-fronted, one or two nodes from the Cassandra cluster go down. This seems to indicate that they cannot handle such a request and become inoperative.
Consequently, the entire functionality of the Cadence + Cassandra integration halts, preventing the creation of new workflows and processing of previously established ones.
Has anyone experienced a similar issue in the past and found a solution? What can we try to understand and rectify the cause of this problem?
Versions in use: cadence - 0.11.0, cadence-web - 3.11, cassandra - 3.11.5, Cassandra-cluster - 5 nodes, replica factor = 3
Approximately 13 million closed workflows are recorded over a retention period of 5 days.
A similar search for a smaller amount of data (workflow) does not lead to such problems
I'm pretty sure that your Cassandra nodes get an out-of-memory exception.
The solution is to not use Cassandra for indexing the large number of workflows as secondary indexes are really broken. That's why both Cadence and Temporal provide ElasticSearch integration.