I running out of "freeable memory" on my Neptune cluster when running this query multiple times:
g.V().
has('User', 'uuid', 'c4746c15-4f8e-42a4-97d3-8e2d25de2dc0').
repeat(__.both('belongsTo').hasLabel('Group')).
until(__.has('Group', 'kind', 'site').or().loops().is((int) 2)).
filter(
__.out('belongsTo').
has('Group', 'uuid', '2da18428-538b-4b6a-b908-9f0edf7af422')).
in('belongsTo').
hasLabel('Person').
dedup().
project('person', 'siteName', 'siteId', 'encounters').
by(__.valueMap()).
by(__.out('belongsTo').values('tag')).
by(__.out('belongsTo').values('uuid')).
by(__.in('belongsTo').hasLabel('Encounter').valueMap().fold())
I would assume that the repeat part could trigger a memory issue so I put a limit over there, but that doesn't seem to prevent the memory issue from happening.
Any ideas?
UPDATED the Neptune profile information from the request:
*******************************************************
Neptune Gremlin Profile
*******************************************************
Query String
==================
g.V().has('User','uuid','c4746c15-4f8e-42a4-97d3-8e2d25de2dc0').repeat(__.both('belongsTo').hasLabel('Group')).until(__.has('Group','kind','site').or().loops().is(2)).filter(__.out('belongsTo').has('Group','uuid','2da18428-538b-4b6a-b908-9f0edf7af422')).in('belongsTo').hasLabel('Person').dedup().project('person','siteName','siteId','encounters').by(__.valueMap()).by(__.out('belongsTo').values('tag')).by(__.out('belongsTo').values('uuid')).by(__.in('belongsTo').hasLabel('Encounter').valueMap().fold())
Original Traversal
==================
[GraphStep(vertex,
[]), HasStep([~label.eq(User), uuid.eq(c4746c15-4f8e-42a4-97d3-8e2d25de2dc0)
]), RepeatStep([VertexStep(BOTH,
[belongsTo
],vertex), HasStep([~label.eq(Group)
]), RepeatEndStep
],until([HasStep([~label.eq(Group), kind.eq(site)
]), OrStep, LoopsStep, IsStep(eq(2))
]),emit(false)), TraversalFilterStep([VertexStep(OUT,
[belongsTo
],vertex), HasStep([~label.eq(Group), uuid.eq(2da18428-538b-4b6a-b908-9f0edf7af422)
])
]), VertexStep(IN,
[belongsTo
],vertex), HasStep([~label.eq(Person)
]), DedupGlobalStep(null,null), ProjectStep([person, siteName, siteId, encounters
],
[
[PropertyMapStep(value)
],
[VertexStep(OUT,
[belongsTo
],vertex), PropertiesStep([tag
],value)
],
[VertexStep(OUT,
[belongsTo
],vertex), PropertiesStep([uuid
],value)
],
[VertexStep(IN,
[belongsTo
],vertex), HasStep([~label.eq(Encounter)
]), PropertyMapStep(value), FoldStep
]
])
]
Optimized Traversal
===================
Neptune steps: [
NeptuneGraphQueryStep(Vertex) {
JoinGroupNode {
PatternNode[(?1, <uuid>,
"c4746c15-4f8e-42a4-97d3-8e2d25de2dc0", <~>) . project ?1 .
],
{estimatedCardinality=1, expectedTotalOutput=1, indexTime=0, joinTime=0, numSearches=1, actualTotalOutput=1
}
PatternNode[(?1, <~label>, ?2=<User>, <~>) . project ask .
],
{estimatedCardinality=382, expectedTotalInput=1, expectedTotalOutput=1, indexTime=0, joinTime=0, numSearches=1, actualTotalOutput=1
}
RepeatNode {
Repeat {
JoinGroupNode {
UnionNode {
PatternNode[(?3, ?6=<belongsTo>, ?4, ?7) . project ?3,?4 . IsEdgeIdFilter(?7) .
],
{cacheJoin=true, estimatedCardinality=37640, indexTime=0, joinTime=3, numSearches=2
}
PatternNode[(?4, ?6=<belongsTo>, ?3, ?7) . project ?3,?4 . IsEdgeIdFilter(?7) .
],
{cacheJoin=true, estimatedCardinality=37640, indexTime=0, joinTime=1, numSearches=2
}
},
{estimatedCardinality=75280
}
PatternNode[(?4, <~label>, ?5=<Group>, <~>) . project ask .
],
{hashJoin=true, estimatedCardinality=100, indexTime=0, joinTime=0
}
}
}
Until {
JoinGroupNode[project ask
] {
PatternNode[(?4, <~label>, ?8=<Group>, <~>) .
],
{hashJoin=true, estimatedCardinality=100, indexTime=1, joinTime=0
}
PatternNode[(?4, <kind>,
"site", <~>) . project ask .
],
{hashJoin=true, estimatedCardinality=87, indexTime=0, joinTime=0
}
}
}
LoopsCondition {
UnionNode[project ask
] {
LoopsFilter(?3,eq(2))
}
}
}, inputVar=?1, outputVar=?9, leftVar=?3, rightVar=?4, annotations={emitFirst=false, untilFirst=false, repeatMode=BFS, expectedTotalOutput=84, actualTotalOutput=84
}
JoinGroupNode[project ask
] {
PatternNode[(?9, ?12=<belongsTo>, ?10, ?13) . project ?9,?10 . IsEdgeIdFilter(?13) .
],
{estimatedCardinality=37640, expectedTotalInput=84, joinRatio=0.5, expectedTotalOutput=42, indexTime=18, joinTime=5, numSearches=84, actualTotalOutput=84
}
PatternNode[(?10, <~label>, ?11=<Group>, <~>) . project ask .
],
{estimatedCardinality=100, expectedTotalInput=42, indexTime=0, hashJoin=true, joinRatio=1.0, expectedTotalOutput=42, joinTime=1, actualTotalOutput=84
}
PatternNode[(?10, <uuid>,
"2da18428-538b-4b6a-b908-9f0edf7af422", <~>) . project ask .
],
{estimatedCardinality=1, expectedTotalInput=42, indexTime=0, hashJoin=true, joinTime=0
}
},
{expectedTotalOutput=82, actualTotalOutput=82
}
PatternNode[(?14, ?16=<belongsTo>, ?9, ?17) . project ?9,?14 . IsEdgeIdFilter(?17) .
],
{estimatedCardinality=37640, expectedTotalInput=82, expectedTotalOutput=974, indexTime=0, joinTime=3, numSearches=41, actualTotalOutput=974
}
PatternNode[(?14, <~label>, ?15=<Person>, <~>) . project ask .
],
{estimatedCardinality=399, expectedTotalInput=974, indexTime=0, hashJoin=true, joinTime=2
}
}, finishers=[dedup(?14)
],
{path=[Vertex(?1):GraphStep, Repeat[̶V̶e̶r̶t̶e̶x̶(̶?̶3̶)̶:̶G̶r̶a̶p̶h̶S̶t̶e̶p̶, Vertex(?4):VertexStep, ̶V̶e̶r̶t̶e̶x̶(̶?̶9̶)̶:̶V̶e̶r̶t̶e̶x̶S̶t̶e̶p̶
], Vertex(?14):VertexStep
], joinStats=true, optimizationTime=7, maxVarId=38, executionTime=305
}
},
NeptuneProjectStep {
NeptunePropertyMapStep {
JoinGroupNode {
PatternNode[(?14, ?18, ?19, <~>) . CompareFilter(?18 != <~label>) .
]
},
{initialValues={?1=null, ?3=null, ?4=null, ?9=null, ?14=null
}
}
},
NeptuneGraphQueryStep(PropertyValue) {
JoinGroupNode {
PatternNode[(?14, ?22=<belongsTo>, ?20, ?23) . project ?14,?20 . IsEdgeIdFilter(?23) .
],
{estimatedCardinality=37640, expectedTotalOutput=37640, indexTime=2, joinTime=39, numSearches=225, actualTotalOutput=225
}
PatternNode[(?20, ?24=<tag>, ?25, <~>) . project ?20,?25 .
],
{estimatedCardinality=100, expectedTotalInput=37640, indexTime=0, hashJoin=true, joinRatio=1.0, expectedTotalOutput=37640, joinTime=3, actualTotalOutput=225
}
},
{executionTime=93378, initialValues={?1=null, ?9=null, ?3=null, ?4=null, ?14=null
}, path=[Vertex(?1):GraphStep, Repeat[̶V̶e̶r̶t̶e̶x̶(̶?̶3̶)̶:̶G̶r̶a̶p̶h̶S̶t̶e̶p̶, Vertex(?4):VertexStep, ̶V̶e̶r̶t̶e̶x̶(̶?̶9̶)̶:̶V̶e̶r̶t̶e̶x̶S̶t̶e̶p̶
], Vertex(?14):VertexStep, Vertex(?20):VertexStep, PropertyValue(?25):PropertiesStep
], actualTotalOutput=225, optimizationTime=7, maxVarId=38, joinStats=true, expectedTotalOutput=37640, joinRatio=1.0
}
},
NeptuneGraphQueryStep(PropertyValue) {
JoinGroupNode {
PatternNode[(?14, ?28=<belongsTo>, ?26, ?29) . project ?14,?26 . IsEdgeIdFilter(?29) .
],
{estimatedCardinality=37640, expectedTotalOutput=37640, indexTime=0, joinTime=9, numSearches=225, actualTotalOutput=225
}
PatternNode[(?26, ?30=<uuid>, ?31, <~>) . project ?26,?31 .
],
{estimatedCardinality=59952, expectedTotalInput=37640, joinRatio=0.5, expectedTotalOutput=18820, indexTime=1, joinTime=33, numSearches=225, actualTotalOutput=225
}
},
{executionTime=92852, initialValues={?1=null, ?9=null, ?3=null, ?4=null, ?14=null
}, path=[Vertex(?1):GraphStep, Repeat[̶V̶e̶r̶t̶e̶x̶(̶?̶3̶)̶:̶G̶r̶a̶p̶h̶S̶t̶e̶p̶, Vertex(?4):VertexStep, ̶V̶e̶r̶t̶e̶x̶(̶?̶9̶)̶:̶V̶e̶r̶t̶e̶x̶S̶t̶e̶p̶
], Vertex(?14):VertexStep, Vertex(?26):VertexStep, PropertyValue(?31):PropertiesStep
], actualTotalOutput=225, optimizationTime=7, maxVarId=38, joinStats=true, expectedTotalOutput=18820, joinRatio=1.0
}
},
NeptuneGraphQueryStep(Vertex) {
JoinGroupNode {
PatternNode[(?32, ?34=<belongsTo>, ?14, ?35) . project ?14,?32 . IsEdgeIdFilter(?35) .
],
{estimatedCardinality=37640, expectedTotalOutput=37640, indexTime=0, joinTime=8, numSearches=225, actualTotalOutput=675
}
PatternNode[(?32, <~label>, ?33=<Encounter>, <~>) . project ask .
],
{estimatedCardinality=1184, expectedTotalInput=37640, indexTime=1, hashJoin=true, joinRatio=1.0, expectedTotalOutput=37640, joinTime=1, actualTotalOutput=675
}
},
{executionTime=62115, initialValues={?1=null, ?9=null, ?3=null, ?4=null, ?14=null
}, path=[Vertex(?1):GraphStep, Repeat[̶V̶e̶r̶t̶e̶x̶(̶?̶3̶)̶:̶G̶r̶a̶p̶h̶S̶t̶e̶p̶, Vertex(?4):VertexStep, ̶V̶e̶r̶t̶e̶x̶(̶?̶9̶)̶:̶V̶e̶r̶t̶e̶x̶S̶t̶e̶p̶
], Vertex(?14):VertexStep, Vertex(?32):VertexStep
], actualTotalOutput=675, optimizationTime=7, maxVarId=38, joinStats=true, expectedTotalOutput=37640, joinRatio=1.0
}
},
NeptunePropertyMapStep {
JoinGroupNode {
PatternNode[(?32, ?36, ?37, <~>) . CompareFilter(?36 != <~label>) .
]
},
{initialValues={?32=null, ?1=null, ?3=null, ?4=null, ?9=null, ?14=null
}
}
}
},
NeptuneTraverserConverterStep
]
Physical Pipeline
=================
NeptuneGraphQueryStep
|-- StartOp
|-- JoinGroupOp@1c433fca
|-- DynamicJoinOp@4cd84dd5(PipelineJoinOp(PatternNode[(?1, <uuid>,
"c4746c15-4f8e-42a4-97d3-8e2d25de2dc0", <~>) . project ?1 .
],
{estimatedCardinality=1, expectedTotalOutput=1
}))
|-- SpoolerOp(1000, @4cd84dd5, null)
|-- DynamicJoinOp@6a2d91c0(PipelineJoinOp(PatternNode[(?1, <~label>, ?2=<User>, <~>) . project ask .
],
{estimatedCardinality=382, expectedTotalInput=1, expectedTotalOutput=1
}))
|-- SpoolerOp(1000, @6a2d91c0, @4cd84dd5)
|-- RepeatOp
|-- <upstream input> (Iteration 0) [visited=1, output=0 (until=0, emit=0), next=1
]
|-- BindingSetQueue (Iteration 1) [visited=2, output=0 (until=0, emit=0), next=2
]
|-- JoinGroupOp@469e9999
|-- UnionOp@432653ef
|-- MultiplexerOp
|-- BindingSetQueue
|-- DynamicJoinOp@4d1e18bb(PipelineJoinOp(PatternNode[(?4, ?6=<belongsTo>, ?3, ?7) . project ?3,?4 . IsEdgeIdFilter(?7) .
],
{cacheJoin=true, estimatedCardinality=37640
}))
|-- BindingSetQueue
|-- DynamicJoinOp@4bbb2349(PipelineJoinOp(PatternNode[(?3, ?6=<belongsTo>, ?4, ?7) . project ?3,?4 . IsEdgeIdFilter(?7) .
],
{cacheJoin=true, estimatedCardinality=37640
}))
|-- DynamicJoinOp@79db29f6(HashJoinOp(PatternNode[(?4, <~label>, ?5=<Group>, <~>) . project ask .
],
{hashJoin=true, estimatedCardinality=100, indexTime=0
}))
|-- BindingSetQueue (Iteration 2) [visited=84, output=84 (until=84, emit=0), next=0
]
|-- JoinGroupOp@469e9999
|-- UnionOp@432653ef
|-- MultiplexerOp
|-- BindingSetQueue
|-- DynamicJoinOp@4d1e18bb(PipelineJoinOp(PatternNode[(?4, ?6=<belongsTo>, ?3, ?7) . project ?3,?4 . IsEdgeIdFilter(?7) .
],
{cacheJoin=true, estimatedCardinality=37640
}))
|-- BindingSetQueue
|-- DynamicJoinOp@4bbb2349(PipelineJoinOp(PatternNode[(?3, ?6=<belongsTo>, ?4, ?7) . project ?3,?4 . IsEdgeIdFilter(?7) .
],
{cacheJoin=true, estimatedCardinality=37640
}))
|-- DynamicJoinOp@79db29f6(HashJoinOp(PatternNode[(?4, <~label>, ?5=<Group>, <~>) . project ask .
],
{hashJoin=true, estimatedCardinality=100, indexTime=0
}))
|-- SpoolerOp(1000, @893b409, @6a2d91c0)
|-- JoinGroupOp@35f5be60
|-- AskOp
|-- BindingSetQueue
|-- DynamicJoinOp@27c672fc(PipelineJoinOp(PatternNode[(?9, ?12=<belongsTo>, ?10, ?13) . project ?9,?10 . IsEdgeIdFilter(?13) .
],
{estimatedCardinality=37640, expectedTotalInput=84, joinRatio=0.5, expectedTotalOutput=42
}))
|-- SpoolerOp(1000, @27c672fc, @893b409)
|-- DynamicJoinOp@7f26b6b(HashJoinOp(PatternNode[(?10, <~label>, ?11=<Group>, <~>) . project ask .
],
{estimatedCardinality=100, expectedTotalInput=42, indexTime=0, hashJoin=true, joinRatio=1.0, expectedTotalOutput=42
}))
|-- SpoolerOp(1000, @7f26b6b, @27c672fc)
|-- DynamicJoinOp@12d4a460(HashJoinOp(PatternNode[(?10, <uuid>,
"2da18428-538b-4b6a-b908-9f0edf7af422", <~>) . project ask .
],
{estimatedCardinality=1, expectedTotalInput=42, indexTime=0, hashJoin=true
}))
|-- SpoolerOp(1000, @35f5be60, isJoinRatioOp=true)
|-- DynamicJoinOp@a4a1ee4(PipelineJoinOp(PatternNode[(?14, ?16=<belongsTo>, ?9, ?17) . project ?9,?14 . IsEdgeIdFilter(?17) .
],
{estimatedCardinality=37640, expectedTotalInput=82, expectedTotalOutput=974
}))
|-- SpoolerOp(1000, @a4a1ee4, @35f5be60)
|-- DynamicJoinOp@1e8fccc3(HashJoinOp(PatternNode[(?14, <~label>, ?15=<Person>, <~>) . project ask .
],
{estimatedCardinality=399, expectedTotalInput=974, indexTime=0, hashJoin=true
}))
|-- FilterOp
NeptuneProjectStep
|-- BindingSetQueue
|-- JoinGroupOp@3a55f0ca
|-- DynamicJoinOp@31340f0b(PipelineJoinOp(PatternNode[(?14, ?18, ?19, <~>) . CompareFilter(?18 != <~label>) .
]))
|-- BindingSetQueue
|-- JoinGroupOp@ab2554a
|-- DynamicJoinOp@3df217d8(PipelineJoinOp(PatternNode[(?14, ?22=<belongsTo>, ?20, ?23) . project ?14,?20 . IsEdgeIdFilter(?23) .
],
{estimatedCardinality=37640, expectedTotalOutput=37640
}))
|-- SpoolerOp(1000, @3df217d8, null)
|-- DynamicJoinOp@679593bc(HashJoinOp(PatternNode[(?20, ?24=<tag>, ?25, <~>) . project ?20,?25 .
],
{estimatedCardinality=100, expectedTotalInput=37640, indexTime=0, hashJoin=true, joinRatio=1.0, expectedTotalOutput=37640
}))
|-- SpoolerOp(1000, @679593bc, @3df217d8)
|-- SpoolerOp(1000, @ab2554a, @679593bc)
|-- BindingSetQueue
|-- JoinGroupOp@71cb259
|-- DynamicJoinOp@1f1cbff5(PipelineJoinOp(PatternNode[(?14, ?28=<belongsTo>, ?26, ?29) . project ?14,?26 . IsEdgeIdFilter(?29) .
],
{estimatedCardinality=37640, expectedTotalOutput=37640
}))
|-- SpoolerOp(1000, @1f1cbff5, null)
|-- DynamicJoinOp@32fd3719(PipelineJoinOp(PatternNode[(?26, ?30=<uuid>, ?31, <~>) . project ?26,?31 .
],
{estimatedCardinality=59952, expectedTotalInput=37640, joinRatio=0.5, expectedTotalOutput=18820
}))
|-- SpoolerOp(1000, @32fd3719, @1f1cbff5)
|-- SpoolerOp(1000, @71cb259, @32fd3719)
|-- BindingSetQueue
|-- JoinGroupOp@79be4a89
|-- DynamicJoinOp@1b52b19d(PipelineJoinOp(PatternNode[(?32, ?34=<belongsTo>, ?14, ?35) . project ?14,?32 . IsEdgeIdFilter(?35) .
],
{estimatedCardinality=37640, expectedTotalOutput=37640
}))
|-- SpoolerOp(1000, @1b52b19d, null)
|-- DynamicJoinOp@61cb4615(HashJoinOp(PatternNode[(?32, <~label>, ?33=<Encounter>, <~>) . project ask .
],
{estimatedCardinality=1184, expectedTotalInput=37640, indexTime=1, hashJoin=true, joinRatio=1.0, expectedTotalOutput=37640
}))
|-- SpoolerOp(1000, @61cb4615, @1b52b19d)
|-- SpoolerOp(1000, @79be4a89, @61cb4615)
|-- BindingSetQueue
|-- JoinGroupOp@3f648ee9
|-- DynamicJoinOp@4a48cfe(PipelineJoinOp(PatternNode[(?32, ?36, ?37, <~>) . CompareFilter(?36 != <~label>) .
]))
Runtime (ms)
============
Query Execution: 306.521
Serialization: 28.401
Traversal Metrics
=================
Step Count Traversers Time (ms) % Dur
-------------------------------------------------------------------------------------------------------------
NeptuneGraphQueryStep(Vertex) 225 225 42.506 14.04
NeptuneProjectStep([
[NeptunePropertyMapStep
],
[... 225 225 236.267 78.06
NeptuneTraverserConverterStep 225 225 23.888 7.89
>TOTAL - - 302.662 -
Repeat Metrics
==============
Iteration Visited Output Until Emit Next
------------------------------------------------------
0 1 0 0 0 1
1 2 0 0 0 2
2 84 84 84 0 0
------------------------------------------------------
87 84 84 0 3
Predicates
==========
# of predicates: 265
Results
=======
Count: 225
Response serializer: application/vnd.gremlin-v2.0+json
Response size (bytes): 449308
Index Operations
================
Query execution:
# of statement index ops: 1938
# of unique statement index ops: 1710
Duplication ratio: 1.13
# of terms materialized: 0
Serialization:
# of statement index ops: 1938
# of unique statement index ops: 1710
Duplication ratio: 1.13
# of terms materialized: 0