Neo4j query performance with memory tuning

1
votes

I have a graph (User-[Likes]->Item) with millions nodes and billions nodes (roughly 50G in disk) built on a powerful machine with 256G RAM and 40 cores. Currently, I'm computing the allshortestpath() between two items.

To improve the cypher query performance, I set dbms.pagecache.memory=100g and wrapper.java.additional=-Xmx32g, with the hope that the whole neo4j can be loaded into meomory. However, when I execute the shortestpath query, the CPU usage is 1625% while MEMORY usage is only 5.7%, and I didn't see performance improvements on the cypher query. Am I missing something in the setting? Or can I setup something to run the query faster? I have read the Performance Tuning guide in the developer manual but didn't find solution.

EDIT1: The cypher query is to count the number of unique users that like both two items. The full pattern would be (Brand)-[:Has]->(Item)<-[:LIKES]-(User)-[:LIKES]->(Item)<-[:HAS]-(Brand)

profile 
MATCH p = allShortestPaths((p1:Brand {FID:'001'})-[*..4]-(p2:Brand {FID:'002'})) 
with [r in RELS(p)|type(r)] as relationshipPath, 
[n in nodes(p)|id(n)][2] as user, p1, p2  
return p1.FID, p2.FID, count(distinct user);

EDIT2: Below is a sampler query plan. It now seems that I'm not using shortestsPath efficiently (380,556,69 db hits). I use shortestsPath to get the common user node between start/end nodes, and then use count(distinct) to get the unique user. Is it possible to tell cypher to eliminate paths which contain the node that have been visited before?

enter image description here

1
performanceneo4jcyphergraph-databases
Can you add the Cypher query, please? And the query plan (add PROFILE to the beginning of the query)? – William Lyon
Hey William, Please see the update. Any suggestions? Thanks. – Idealist
Which version do you use? – Michael Hunger
If you upgrade to 3.0 you could use some of the APOC procedure to paralellize the query and use all your cores. – Michael Hunger
My version is 2.3.4. I will upgrade to 3.0 and try it again. – Idealist

1 Answers

0
votes

Can you try to run this instead:

MATCH (p1:Brand {FID:'001'}),(p2:Brand {FID:'002'})
MATCH (u:User)
WHERE (p1)-[:Has]->()<-[:LIKES]-(u) AND 
      (p2)-[:Has]->()<-[:LIKES]-(u)
RETURN p1,p2,count(u);

This starts at the user and checks against both brands, the explain plan looks much better

+----------------------+----------------+------------------------------------------+---------------------------+
| Operator             | Estimated Rows | Variables                                | Other                     |
+----------------------+----------------+------------------------------------------+---------------------------+
| +ProduceResults      |              0 | count(u), p1, p2                         | p1, p2, count(u)          |
| |                    +----------------+------------------------------------------+---------------------------+
| +EagerAggregation    |              0 | count(u) -- p1, p2                       | p1, p2                    |
| |                    +----------------+------------------------------------------+---------------------------+
| +SemiApply           |              0 | p2 -- p1, u                              |                           |
| |\                   +----------------+------------------------------------------+---------------------------+
| | +Expand(Into)      |              0 | anon[78] -- anon[87], anon[89], p1, u    | (p1)-[:Has]->()           |
| | |                  +----------------+------------------------------------------+---------------------------+
| | +Expand(All)       |              0 | anon[87], anon[89] -- p1, u              | (u)-[:LIKES]->()          |
| | |                  +----------------+------------------------------------------+---------------------------+
| | +Argument          |              1 | p1, u                                    |                           |
| |                    +----------------+------------------------------------------+---------------------------+
| +SemiApply           |              0 | p1 -- p2, u                              |                           |
| |\                   +----------------+------------------------------------------+---------------------------+
| | +Expand(Into)      |              0 | anon[119] -- anon[128], anon[130], p2, u | (p2)-[:Has]->()           |
| | |                  +----------------+------------------------------------------+---------------------------+
| | +Expand(All)       |              0 | anon[128], anon[130] -- p2, u            | (u)-[:LIKES]->()          |
| | |                  +----------------+------------------------------------------+---------------------------+
| | +Argument          |              1 | p2, u                                    |                           |
| |                    +----------------+------------------------------------------+---------------------------+
| +CartesianProduct    |              0 | u -- p1, p2                              |                           |
| |\                   +----------------+------------------------------------------+---------------------------+
| | +CartesianProduct  |              0 | p2 -- p1                                 |                           |
| | |\                 +----------------+------------------------------------------+---------------------------+
| | | +Filter          |              0 | p1                                       | p1.FID == {  AUTOSTRING0} |
| | | |                +----------------+------------------------------------------+---------------------------+
| | | +NodeByLabelScan |              0 | p1                                       | :Brand                    |
| | |                  +----------------+------------------------------------------+---------------------------+
| | +Filter            |              0 | p2                                       | p2.FID == {  AUTOSTRING1} |
| | |                  +----------------+------------------------------------------+---------------------------+
| | +NodeByLabelScan   |              0 | p2                                       | :Brand                    |
| |                    +----------------+------------------------------------------+---------------------------+
| +NodeByLabelScan     |              0 | u                                        | :User                     |
+----------------------+----------------+------------------------------------------+---------------------------+