On Neo4j Aura (GCP) I'm getting this error:
Neo.TransientError.General.MemoryPoolOutOfMemoryError The allocation of an extra 2.0 MiB would use more than the limit 18.7 GiB. Currently using 18.7 GiB. dbms.memory.transaction.total.max threshold reached
The instance configuration is:
Neo4j version 5 Memory 64GB Storage 128GB CPU 12 Region Oregon, USA (us-west1)
This thing is costing a fortune so it's disappointing i don't get to reference all the memory with my query. This is my query:
CALL apoc.periodic.iterate("MATCH (st:ConsumerID)-[r:`ConsumerID->ProspectID` | `ProspectID->ConsumerID`]-(n1)-[r2]-(n2)-[r3]-(n3)-[r4]-(n4)-[r5]-(n5)-[:`ConsumerID->ProspectID` | `ProspectID->ConsumerID`]-(fn:ConsumerID)
WHERE id(st) <> id(fn) AND st.SourceSystemCreatedDate >= fn.SourceSystemCreatedDate and NOT n2:ExternalCreditReportID
RETURN r2",
"DELETE r2",{batchSize:1, parallel:false, params:{}})
YIELD batches, total RETURN batches, total;
Even reducing batchsize 1, or single/multithread doesn't make a difference, I have also tried these indexes:
CREATE INDEX ConsumerID_id_SourceSystemCreatedDate FOR (n:ConsumerID) ON (n.id,n.SourceSystemCreatedDate);
CREATE INDEX ConsumerID_SourceSystemCreatedDate FOR (n:ConsumerID) ON (n.SourceSystemCreatedDate);
Any ideas would be greatly appreciated.
Best practices for any version after 4.0 is not to RETURN node or relationship or path variables from the outer driving query, but pass their id() instead, and reMATCH by id() in the batch query.
https://neo4j.com/developer/kb/a-significant-change-in-apoc-periodic-iterate-in-apoc-4-0/
This is to work around some behavior where the driving transaction would hold references to the child transactions, which essentially disables the batch processing behavior that you want.