Why is Neo4j slower(totally dead) with many nodes and relationships in lower specification of pc/notebook while MySQL is not?

[Rio Eduardo]

I'm testing my thesis which is about transforming from relational database to graph database. After transforming from relational database to graph database, I will test their own performance according to query response time and throughput. In relational database, I use MySQL while in graph database I use Neo4j for testing. I will have 3 Million more nodes and 6 Million more relationships. But when I just added 60000 nodes, my Neo4j is already dead. When I tried to return all 60000 nodes, it returned unknown. I did the same to MySQL, I added 60000 records but it could return all 60000 records. It's weird because it's against the papers I read that told me graph database is faster than relational database So Why is Neo4j slower(totally dead) in lower specification of pc/notebook while MySQL is not? And What specification of pc/notebook do I should use to give the best performance during testing with millions of nodes and relationships?

Thank you.

[Kenny Bastani]

Please provide your Neo4j version and details about how you are interacting with the database. Are you using the Neo4j 2.0 browser to return back all 60,000 nodes?

Typically this amount of data, when loaded into memory in the browser, will cause for a timeout, which results in an unknown exception. I recommend using the Neo4j shell for querying this amount of data.

Thanks,

Kenny

[Javad Karabi]

make sure you are setting up your indexes.

this was something that i did not do at first, but once i realized how important it was, my queries were incredibly fast.

also, profile your queries by prepending "profile " to the query, and try to decrease _db_hits.

if you can provide the output of "profile ...", that would be awesome.

[Michael Hunger]

Why would you want to return all nodes in the first place?

If you really want to do that, use the transactional http endpoint and curl that streams the response:

I tested it with a db of 100k nodes, it takes 0.9 seconds to transfer them (1.5MB) over the wire

time curl -o result.json -d'{"statements":[{"statement":"match (n) return id(n)"}]}' -H accept:application/json -H content-type:application/json http://localhost:7474/db/data/transaction/commit

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

100 1552k    0 1552k  100    55  1708k     60 --:--:-- --:--:-- --:--:-- 1707k

real 0m0.915s

user 0m0.153s

sys 0m0.409s

wuqour:neo4j-enterprise-2.0.1 mh$ ls -lh result.json 

-rw-r--r--  1 mh  staff   1,5M 24 Mär 23:24 result.json

If you transfer all their properties by using "return n", it takes 1.4 seconds and results in 4.1MB transferred.

If you just want to know how many nodes are in your db. use something like this instead:

match (n) return count(*);
+----------+
| count(*) |
+----------+
| 100052   |
+----------+
1 row
186 ms

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rio Eduardo]

My Neo4j Version is 2.0.1 and yes I'm using Neo4j 2.0.1 browser to return back all 60,000 nodes. So I should use Neo4j Shell to return many nodes?

[Michael Hunger]

Please read what I wrote. And then please answer/ask again.

--

[Rio Eduardo]

yes I want to return all nodes to testing neo4j. And I just tested it in higher specification of pc and it said "Resultset too large (over 1000 rows)". And I just tested it again in Neo4j Shell and it said "68351 rows and 1580 ms" and when I open http://localhost:7474/db/data/transaction/commit, it shows me blank page. Please help me how to run this statement => time curl -o result.json -d'{"statements":[{"statement":"match (n) return id(n)"}]}' -H accept:application/json -H content-type:application/json.

Thank you.

[Rio Eduardo]

I successfully added 1 Million nodes in my Neo4j database and now the size of the database is 547 MB. And I just read the system requirement for Neo4j at http://docs.neo4j.org/chunked/stable/deployment-requirements.html. Can't Neo4j handle 1 Million nodes in lower specification of pc?

My specification of PC:
CPU: Intel Core i7
Memory: 4GB DDR3
HardDisk: 300GB SATA
Operation System: Windows 7
Neo4j: 2.0.1

Please help me.

Thanks.

[Michael Hunger]

Yes totally, should be no problem

Sent from mobile device

--

[Rio Eduardo]

you mean Neo4j can't handle 1 Million nodes in lower specification of pc?

[Koen Kleingeld]

it is more the structure your query than the amount of nodes

--

Groeten / Best Regards,

Koen

[Michael Hunger]

Neo can easily handle 1M nodes on this kind of PC.

[Michael Hunger]

If you want to test a graph database, it doesn't make sense to return all data. What you want to do are graph queries.

Otherwise you can just use any KV-store or filesystem if it is just about returning all the data stored in the database.

Curl is a command line tool for executing http requests on unix systems. It also exists for windows.

http://localhost:7474/db/data/transaction/commit is an http API url, not something you point your browser to

Cheers

Michael

[Rio Eduardo]

Ok, I should explain about testing of my thesis more detail. I use graph database because of my case. My case is social network. Social network model is identical to connected data that is similar to graph database model. That's why I use graph database instead of others NoSQL's methods. So in testing, I will test both relational database and graph database. And I will test them both directly in database and through the social network. I build social network with PHP(Neo4jPHP). And I'm sorry I just realized that I made mistake, I meant I wanted to return all nodes that have label "User" => MATCH(U:User) RETURN U. And why do I still want to return all nodes that have label "User"? It's because my first page of social network I want to display all users that already registered to my social network. And other activities like post something, give comment, like post, like comment, add as friend and others feature or activity I do with graph queries like you did say => What you want to do are graph queries.

And once again it's my mistake or maybe it's misuderstanding. Actually I should say I would have 1M Users in my social network not 1M nodes. Why do I choose 1M Users in my social network? It's because of Partner and Vukotic's experiment:

In their book Neo4j in Action, Partner and Vukotic perform an experiment using a relational store and Neo4j. The comparison shows that the graph database is substantially quicker for connected data than a relational store. Partner and Vukotic’s experiment seeks to find friends-of-friends in a social network, to a maximum depth of five. Given any two persons chosen at random, is there a path that connects them that is at most five relationships long? For a social network containing 1,000,000 people, each with approximately 50 friends, the results strongly suggest that graph databases are the best choice for connected data. And graph database can still work 150 times faster than relational database at third degree and 1000 times faster at fourth degree. So I have my hypothesis is like this:

My social network containing 1,000,000 people that uses graph database can work up to 150 times faster than my social network containing 1,000,000 people that uses relational database at third degree and up to 1000 times faster at fourth degree. And graph database can do seeks to find friends-of-friends up to fifth degree.

That's actually I'm doing with my thesis right now.

Please help. Please give me suggestions.

Thanks.

[Rio Eduardo]

I just knew about memory allocation and just read Server Performance Tuning of Neo4j. neo4j.properties:
# Default values for the low-level graph engine

#neostore.nodestore.db.mapped_memory=25M
#neostore.relationshipstore.db.mapped_memory=50M
#neostore.propertystore.db.mapped_memory=90M
#neostore.propertystore.db.strings.mapped_memory=130M
#neostore.propertystore.db.arrays.mapped_memory=130M

Should I change this to get high performance? If yes, please suggest me.

And I just knew about Neo4j Licenses, they are Community, Personal, Startups, Business and Enterprise. And at Neo4j website all features are explained. So which Neo4j should I use for my case that has millions nodes and relationships?

Please answer. I need your help so much.

Thanks.

On Tuesday, March 25, 2014 12:03:58 AM UTC+7, Rio Eduardo wrote:

[Lundin]

Rio, any version will do. They can all handle million nodes on common hardware, no magic at all. When hundred of millions of billions then we might need to look into specfication more in detail. But in that case with that kind of data there are other bottlencks for a social network or any web appp that needs to be taken care of as well.

you said:

 Given any two persons chosen at random, is there a path that connects them that is at most five relationships long? For a social network containing 1,000,000 people, each with approximately 50 friends, the results strongly suggest that graph databases are the best choice for connected data. And graph database can still work 150 times faster than relational database at third degree and 1000 times faster at fourth degre

I fail to see how this is connected to your attempt to list 1M users in one go at the first page. You would want to seek if there is a relationship and return that path between users. You need two start nodes and seek a path by traveser the relationsip rather than scan tables and that would be the comparison.

Otherwise if you really need to present that many "things" just paging the result with SKIP,LIMIT. I has never made sense to present 1M of anything at a time for a user. Again, that wouldn't really serve your experiment much good to prove graph theory.

What is the result of MATCH(U:User) RETURN count(U); ?

Also when you do your test make sure to add the warm/cold cache effect (better/worse performance)

[Rio Eduardo]

Thank you so much for the reply Lundin. I really apreciate it. Okay, yesterday I just tested my experiment again. And the result was not what I imagined and expected before. Okay, before I tested 1M users, I reduced the number of users into 1000 users and tested it not in my social network but directly in database only(Neo4j Shell) to find out that it was not caused by the performance of pc. But the result of returning 1000 users was 200ms and 1 row and the result of returning friends at depth of two was 85000ms and 2500 rows and are 200ms and 85000ms fast to you? and what does ms stand for? is it milliseconds or microseconds?

the query I use for returning 1000 users is

MATCH (U:User) RETURN COUNT(U);

and the query I use for returning friends at depth of two is

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)
WHERE U.user_id=1 AND FFU.user_id<>U.user_id AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.username

Please note that I tested with default configuration of Neo4j and created users with 1000 random nodes and created friends relationships with 50000 random relationships(1 user has 50 friends). Each relationship has a label Friend and no properties on it. Each node has a label User, 4 properties: user_id, username, password and profile_picture. Each property has a value of 1-60 characters. average of characters of user_id=1-1000 characters, all usernames have 10 characters randomly, all passwords have 60 characters because I MD5 it, and profile_picture has 1-60 characters.

And about your statement "Otherwise if you really need to present that many "things" just paging the result with SKIP,LIMIT. I has never made sense to present 1M of anything at a time for a user.", I already did according to your statement above but it is still the same, Neo4j returns result slower.

And I'm wondering if Neo4j already applied one of graph algorithms(shortest path, djikstra, A*, etc) in its system or not.

Thank you.

[Lundin]

ms, it is milliseconds.

What is the corresponding result for a SQL db ?

MATCH (n:User)-[:Friend*3]-(FoFoF) return FoFoF;

Albeit a valid search is it something useful ? I would think finding a specific persons FoFoF in either end, as a starting point or end point, would be a very realistic scenario. Adding an Index on User:name and query for a User with name:Rio try to find his FoFoF.

Yes, neo4j has been kind and exposed various function, like shortestpath in cypher

http://docs.neo4j.org/refcard/2.0/

Also look at some gist examples

https://github.com/neo4j-contrib/graphgist/wiki

[Michael Hunger]

Rio,

was this your first run of both statements? If so, please run them for a second time.
And did you create an index or constraint for :User(user_id) ?

MATCH (U:User) RETURN COUNT(U);

I would also change:

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)
WHERE U.user_id=1 AND FFU.user_id<>U.user_id AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.username

to

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)
WHERE U.user_id=1 

WITH distinct U, FFU

WHERE FFU<>U AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.username

I quickly created a dataset on my machine:

cypher 2.0 foreach (i in range(1,1000) | create (:User {id:i}));

create constraint on (u:User) assert u.id is unique;  

match (u1:User),(u2:User) with u1,u2 where rand() < 0.1 create (u1)-[:Friend]->(u2);

Relationships created: 99974

778 ms

match (u:User) return count(*);

+----------+
| count(*) |
+----------+
| 1000     |
+----------+
1 row
4 ms

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)

WHERE U.id=1 

WITH distinct U, FFU

WHERE FFU<>U AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.id;

...

910 rows

101 ms

but even your query takes only

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)

WHERE U.id=1 AND FFU.id<>U.id AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.id;

...

8188 rows

578 ms

[Rio Eduardo]

Thank you for the reply Ludnin.

And I already tried MATCH (n:User)-[:Friend*3]-(FoFoF) return FoFoF; and the result is still the same that is about 85000ms.

I just realized that was my mistake. I always thought that the new feature Labels already applied Index or Constraint so I had never created Index or Constraint when I was using cypher.

And after I created the constraint I got what I expected, see my reply for Michael's reply below.

and thanks for the references.

[Rio Eduardo]

Thank you for the reply Michael. Yes, and I already tried it again for a second time.

I just realized that was my mistake. I always thought that the new feature Labels already applied Index or Constraint so I had never created Index or Constraint when I was using cypher.

And after I created constraint for :User(user_id), I got the result I expected:

match (u:User) return count(*);

+----------+

| count(*) |

+----------+

| 1000     |

+----------+

1 row

7 ms

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)

WHERE U.user_id=1 

WITH distinct U, FFU

WHERE FFU<>U AND NOT (U)-[:Friend]->(FFU)

RETURN FFU.user_id;

...

879 rows

187 ms

after I got my cypher faster than before, I have a question again,

why is the execution time between me and you different?

yours

+----------+

| count(*) |

+----------+

| 1000     |

+----------+

1 row

4 ms

...

910 rows

101 ms

mine

+----------+

| count(*) |

+----------+

| 1000     |

+----------+

1 row

7 ms

...

879 rows

187 ms

is it because the size of the property and the number of the property that belongs to node is different?

And what is different between Index and Constraint?

Should I create two of them?

If I already created Index, Should I create Constraint again?

Or if I already created Constraint, Should I create Index again?

Thank you.

[Michael Hunger]

Probably a faster CPU on my machine?

constraint also guarantees uniqueness and creates an index automatically but adds more cost on insertion

index is optimizing lookups

Depends on your needs, one property might be unique so you want a constraint

other properties you might want to search by, so you add an index.

[Rio Eduardo]

Thank you so much for your help and your explaination Michael. It really helps.

[Rio Eduardo]

Please help me again Michael.

You ever said:

I would also change:

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)
WHERE U.user_id=1 AND FFU.user_id<>U.user_id AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.username

to

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)
WHERE U.user_id=1 

WITH distinct U, FFU

WHERE FFU<>U AND NOT (U)-[:Friend]->(FFU)
RETURN FFU.username

Query above is to find friends of friends at depth of two. And I would like to find friends of friends  at depth of three, when I use model of your query, it returns result longer than mine and the result is much more than mine. Ok so here is model of your query at depth of three:

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)-[FFF:Friend]->(FFFU:User)

WHERE U.user_id=1

WITH DISTINCT U, FU, FFU, FFFU

WHERE FFU<>U AND FFFU<>FU AND NOT (U)-[:Friend]->(FFFU)

RETURN FFFU.username;

...

118858 rows

20090 ms

Mine:

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)-[FFF:Friend]->(FFFU:User)

WHERE U.user_id=1 AND FFU<>U AND FFFU<>FU AND NOT (U)-[:Friend]->(FFFU)

RETURN DISTINCT FFFU.username;

...

950 rows

18133 ms

Please help me, Why is model of your query longer than mine and return much more results than mine?

Thank you.

On Friday, March 28, 2014 8:30:20 PM UTC+7, Michael Hunger wrote:

[Michael Hunger]

Split it up in one more intermediate step, the intermediate steps are there to get the cardinality down, so it doesn't have to match billions of paths, only millions or 100k

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)-[FFF:Friend]->(FFFU:User)

WHERE U.user_id=1

WITH DISTINCT U, FU, FFU

WHERE FFU<>U 

WITH DISTINCT U, FFU

MATCH (FFU:User)-[FFF:Friend]->(FFFU:User)

WHERE NOT (U)-[:Friend]->(FFFU)

RETURN distinct FFFU.username;

[Rio Eduardo]

Thank you so much Michael for the help. It really helps.

[Rio Eduardo]

Hello again Michael.

I just want to make sure that my query is correct to find friends of friends at depth of four and five. Please help me by checking my query.

Query at depth of four:

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)

WHERE U.user_id=1

WITH DISTINCT U, FU, FFU

WHERE FFU<>U 

WITH DISTINCT U, FU, FFU

MATCH (FFU:User)-[FFF:Friend]->(FFFU:User)

WHERE FFFU<>FU

WITH DISTINCT U, FFU, FFFU

MATCH (FFFU:User)-[FFFF:Friend]->(FFFFU:User)

WHERE FFFFU<>FFU AND FFFFU<>U AND NOT (U)-[:Friend]->(FFFFU)

RETURN DISTINCT FFFFU.username;

Query at depth of five:

MATCH (U:User)-[F:Friend]->(FU:User)-[FF:Friend]->(FFU:User)

WHERE U.user_id=1

WITH DISTINCT U, FU, FFU

WHERE FFU<>U 

WITH DISTINCT U, FU, FFU

MATCH (FFU:User)-[FFF:Friend]->(FFFU:User)

WHERE FFFU<>FU

WITH DISTINCT U, FFU, FFFU

MATCH (FFFU:User)-[FFFF:Friend]->(FFFFU:User)

WHERE FFFFU<>FFU

WITH DISTINCT U, FFFU, FFFFU

MATCH (FFFFU:User)-[FFFFF:Friend]->(FFFFFU:User)

WHERE FFFFFU<>FFFU AND FFFFFU<>U AND NOT (U)-[:Friend]->(FFFFFU)

RETURN DISTINCT FFFFFU.username;

I need your help so much.

Thank you.

[Michael Hunger]

Just use a dataset that you can reason about and check if they work correctly.

Hard for me to be the consistency checker on your queries :)

In general if you really want to do these deep traversals you might be better off (in terms of performance) using the traversal-API with an appropriate uniqueness constraint, like node-path.

[Lundin]

Michael,

Wow i just looked into the Traversal API docs on neo4j. Will this kind of functionallity like the evaluater ever be exposed to Cypher commands and intepreted ? I program Gol(lang) and Nodejs but this framework combined with Neo4j is a good reason for start using Java ;)

[Rio Eduardo]

Hi Michael,
you mean don't use dataset that doesn't make sense?

Please help me just by checking my last two queries. I'm testing my thesis until depth of five only.

and is there no others way to speed up the traversal in cypher? maybe shortestPath? is the only way using traversal-API?

Please help me, I have to graduate this year.
Thank you.

[Rio Eduardo]

Hi Michael,

you said "In general if you really want to do these deep traversals you might be better off (in terms of performance) using the traversal-API with an appropriate uniqueness constraint, like node-path". Please give me any references so I can learn it. or Does it mean you suggest me to use Gremlin?

Thank you.

[Michael Hunger]

You should be able to test your queries yourself, with a small dataset to look at their results and reason about them.

And performance wise, if they are fast enough it's good too.

[Michael Hunger]

For the traversal framework check out: http://docs.neo4j.org/chunked/milestone/tutorial-traversal.html

[Rio Eduardo]

Thank you.

[Michael Hunger]

Probably something like this but not sure, to many F's

MATCH (U:User)-[F:Friend]->(FU:User)-[:Friend]->(FFU:User)

WHERE U.user_id=1

WITH DISTINCT U, FU, FFU

WHERE FFU<>U 

MATCH (FFU:User)-[:Friend]->(FFFU:User)

WITH DISTINCT U, FFU, FFFU

WHERE FFFU<>FU

MATCH (FFFU:User)-[:Friend]->(FFFFU:User)

WITH DISTINCT U, FFU, FFFU, FFFFU

WHERE FFFFU<>FFU AND FFFFU<>U AND NOT (U)-[:Friend]->(FFFFU)

RETURN DISTINCT FFFFU.username;

you might also try:

MATCH (U:User)-[F:Friend]->(FU:User)-[:Friend]->(FFU:User)

WHERE U.user_id=1

WHERE FFU<>U 

WITH DISTINCT U, FU, FFU

MATCH (FFU:User)-[:Friend]->(FFFU:User)

WHERE FFFU<>FU

WITH DISTINCT U, FFU, FFFU

MATCH (FFFU:User)-[:Friend]->(FFFFU:User)

WHERE FFFFU<>FFU AND FFFFU<>U AND NOT (U)-[:Friend]->(FFFFU)

RETURN DISTINCT FFFFU.username;

[Rio Eduardo]

Thank you for the checking out my query Michael. yeah i think so and i already tried all possibility and it's getting longer if I put all possible condition to the query. I should find others way.

[Rio Eduardo]

Oh yeah Michael I'm new in traversal api, after I read the doc about traversal api, should I use it with java? I mean is there others way to use traversal api, ex: I can run the syntax traversal api in neo4j shell or http? if there is, please provide me a reference how to use traversal api not through java.

Thank you.

[Michael Hunger]

It is also supported via the rest api

You find it in the docs

Sent from mobile device

[Rio Eduardo]

Thank you so much Michael. it's obviously in the docs.