Neo4j Performance during writes

[jalesar2011]

My graph contains 2.5 Milion nodes. During writes, read query time is reaching till 4 sec.
Current graph size is 58G.
RAM = 24G.
cypher query : (s:Node(id : x) ) - [r:RELATION] -> (d:Node) return r,d;
where x has unique index.

Is there any optimization which can improve performance.

[michael_hunger]

Can you share the PROFILE output for your query?
What does your config for heap/page-cache look like?

How does the graph size distribute across nodes, relationships, properties?

[jalesar2011]

Hi

heap size = 7G
Page cache = 10G
The graph contains 2.5M nodes and per node, there are two properties. One node will have min 100 relations and max 300 relations. every relation contains 2 properties.

[michael_hunger]

How do you write the data? How many cores does your machine have? What kind of disk?

Can you check if during that time, it's CPU or I/O bound?

[michael_hunger]

Could be that it's I/O bound due to disk paging during writes that causes read pages to be faulted.

[jalesar2011]

Hi Michale,

I am using bulk write with cypher queries ( with UNWIND {props}).

Machine core : 12
Disk type : hdd

I enabled query logging, logged queries which took more than 500ms

2018-11-23 07:50:52.811+0000 INFO  1518 ms: (planning: 0, cpu: 1, waiting: 0) - 102640 B - 3 page hits, 0 page faults - bolt-session    bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752  neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['DSRECOMMENDATIONS'], styleIds: [2031960]} - {}
2018-11-23 07:50:52.964+0000 INFO  2329 ms: (planning: 0, cpu: 1, waiting: 0) - 180312 B - 3 page hits, 0 page faults - bolt-session    bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752 neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['CROSSSELL'], styleIds: [2250052]} - {}
2018-11-23 07:50:52.979+0000 INFO  2768 ms: (planning: 0, cpu: 2, waiting: 0) - 182648 B - 3 page hits, 0 page faults - bolt-session    bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752 neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['CROSSSELL'], styleIds: [7205812]} - {}
2018-11-23 07:50:53.056+0000 INFO  1148 ms: (planning: 0, cpu: 1, waiting: 0) - 102352 B - 3 page hits, 0 page faults - bolt-session    bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752   neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['DSRECOMMENDATIONS'], styleIds: [924520]} - {}
2018-11-23 07:50:53.072+0000 INFO  4244 ms: (planning: 0, cpu: 1, waiting: 0) - 157576 B - 3 page hits, 0 page faults - bolt-session    bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752  neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['CROSSSELL'], styleIds: [2441072]} - {}
2018-11-23 07:50:53.129+0000 INFO  2340 ms: (planning: 0, cpu: 2, waiting: 0) - 181840 B - 3 page hits, 0 page faults - bolt-session    bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752  neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['CROSSSELL'], styleIds: [6838216]} - {}
2018-11-23 07:50:53.139+0000 INFO  1169 ms: (planning: 0, cpu: 0, waiting: 0) - 74632 B - 3 page hits, 0 page faults - bolt-session     bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752  neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['DSRECOMMENDATIONS'], styleIds: [7436181]} - {}
2018-11-23 07:50:54.063+0000 INFO  570 ms: (planning: 0, cpu: 1, waiting: 0) - 99960 B - 3 page hits, 0 page faults - bolt-session      bolt    neo4j   neo4j-java/1.6.0-db4c435686e7fbc8c630c11e4220c0082cb76752   neo4j - match (s:StyleNode) -[r:RECOMMEND] -> (dest:StyleNode) where r.recommendationType in {recommendationTypes} and s.styleId in {styleIds} return r.recommendationType as recommendationType, s.styleId as srcStyle, dest.styleId as destStyle, r.articleType as articleType order by r.rank - {recommendationTypes: ['DSRECOMMENDATIONS'], styleIds: [1988592]} - {}```

Page faults are 0 in almost all queries, but still they are taking more time.


Thanks

[michael_hunger]

I guess the HDD is the problem.

While it's synching for a commit, the HDD reads will suffer.
There might be also some internal write locks that might only be released after a commit finished.
So if you read the same nodes that you're currently writing to that might be affected.