Memory settings for big databases

[manonthegithub]

Hello!
I am trying to run very big database (several billions of nodes) on neo4j. I have created indexes and memrec shows:

• Lucene indexes: 0k
• Data volume and native indexes: 504g

I've got only 90GB of memory in my machine. So the question is: is it critical for indexes to fully fit into memory? Is it fine if I set about 50G for page cache?

[stefan_armbrust]

In recent versions of Neo4j I'd probably start of with 8G for java heap (depending on your query's memory footprint), leave up some GB for the OS and dedicate 75G for pagecache.

[manonthegithub]

Thank you for the fast reply!

[manonthegithub]

Queries run very slow, and suspect there is not enough memory. How can I determine that there is enough pagecache on my machine?

PS I use community edition.

[andrew_bowman]

For slow queries, we suggest using EXPLAIN and PROFILE to look at what's going on in the query plan and ensuring you have the right indexes or constraints in place to support quick lookup of starting nodes.

You can also open a new question here on the Cypher section (including the expanded PROFILE plan) for any help streamlining them.

[manonthegithub]

@andrew.bowman I have used PROFILE and it shows that it uses indexes, so I think the problem is not in the query. I suspect either too small page cache (I have set 54G) or not enough memory in the heap (I have set 18G). The queries are resource consuming, but I think I have powerful enough server (24 cores and 90G mem).

[andrew_bowman]

Can you provide the full query? We might see a way to troubleshoot based on that.

[manonthegithub]

here is the query:

match
(r:Resource)-[:ns1__P31]-(:Resource{uri:"http://www.wikidata.org/entity/Q5"}),
(s)-[:ns8__P2048]-(r)-[:ns1__P21]-(g),
(s)-[:ns7__P2048]-(v),
(v)-[:ns0__quantityNormalized]-(q),
(q)-[:ns0__quantityUnit]-(:Resource{uri:"http://www.wikidata.org/entity/Q11573"}),
(r)-[:ns1__P106]-(o:Resource)
where q.ns0__quantityAmount < 3
with g.rdfs__label as gender, o.rdfs__label as occupation, avg(q.ns0__quantityAmount) as height, count(*) as cnt
where cnt > 100
return gender, occupation, height, cnt
order by height desc

It runs for about 600 secs for the first time and for about 30 for seconds further.
Using explicit labels for the variables should not really affect the speed, because all the nodes are of the same label 'Resource'

[manonthegithub]

a simpler query

match
(o:Resource)-[:ns10__P2044]->(h:Resource)
where h.ns0__quantityAmount > 8000
match (r:Resource)-[:ns1__P31]->(:Resource{uri:"http://www.wikidata.org/entity/Q8502"}),
(r:Resource)-[:ns8__P2044]->(o:Resource)
return r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
order by height desc

runs for 78 secs for the first time.

Note that this is a test server and there is no load on it.

[andrew_bowman]

For that last query, can you expand all elements of the query plan (double-down arrow in the lower right corner of the result frame) and then add the expanded plan? That can better help associate steps of the query plan with certain parts of your query.

[manonthegithub]

[andrew_bowman]

So the filter at the end, applying h:Resource (and likely the `ns0__quantityAmount < 8000) resulted in the biggest drop in rows, from 313.4k to 16.

Out of curiosity, what happens if you use this query instead? Can you PROFILE this and give us the plan and timing?

MATCH (o:Resource)-[:ns10__P2044]->(h:Resource)
where h.ns0__quantityAmount > 8000
RETURN count(*)

I'm just curious what rows look like if we start here.

Also, do you have an index on :Resource(ns0__quantityAmount)? And if not, would it make sense to have one here, or do you not lookup by this property often enough to justify it?

[manonthegithub]

andrew.bowman:

MATCH (o:Resource)-[:ns10__P2044]->(h:Resource) where h.ns0__quantityAmount > 8000 RETURN count(*)

As you can see from the profiling I have an index for quantity amount. You can also see that there are only 69 entities from 3 billion which satisfy the criteria.
First run is about 60 seconds, following are about 2 secs.

Note. I use neo4j 3.5.5

[manonthegithub]

Maybe an important remark.
all the nodes have label Resource, but they have different sets of properties, for example quantityAmount exists only in 1 302 670 nodes out of 3 billion. So I expect from indexes to work really fast, as they should not contain information about nodes which do not have corresponding properties.

[andrew_bowman]

Correct, if a :Resource node does not have an indexed property, it won't be present in that index, so that will work well for selectivity.

Okay, this looks promising, let's see if we can adjust the query to allow the planner to start here.

We're going to add a join hint on the resource with the uri, which should ensure we can expand to this node, rather than using that node as the starting point and expanding from it.

Try this out, verify that it starts with a NodeIndexSeekByRange, and add the profile plan and timing. I think this should work better.

MATCH (o:Resource)-[:ns10__P2044]->(h:Resource)
WHERE h.ns0__quantityAmount > 8000
MATCH (o)<-[:ns8__P2044]-(r:Resource)-[:ns1__P31]->(end:Resource{uri:"http://www.wikidata.org/entity/Q8502"})
USING JOIN ON end
RETURN r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
ORDER BY height desc

Keep in mind also that nodes can be multi-labeled. You mentioned that certain :Resource nodes have different sets of properties, that suggests that these nodes can serve different roles in certain contexts. You might want to consider if it makes sense to add appropriate labels to these nodes so you can address them in these different contexts, which should also serve to narrow down the selectivity and potentially speed up your index lookups and label filtering.

[manonthegithub]

I am not sure if it worked. The query plan seems the same as earlier. I've got runing time of 5 sec for this query, but I think it is because it was already in cache.

I also got this warning from the engine:

The database was unable to plan a hinted join.
The hinted join was not planned. This could happen because no generated plan contained the join key, please try using a different join key or restructure your query. (hinted join key identifier is: end)

I do not really understand what is meant by no generated plan contained the join key.

Adding more labels should definitely help, but in many cases it will not make the search narrower (as in this query).
So I try to understand why is it slow for not so large indexes. For example the query with count we made before even with not so optimal execution plan should be performed in hundreds if not dozens of milliseconds but not 60 sec (in my subjective feeling). That is why I started to suspect some problems with insufficient memory.

[andrew_bowman]

Odd that it's having trouble with that join hint. Let's give this one a try instead, and if it doesn't work see if it will accept it with both hints:

MATCH (o:Resource)-[:ns10__P2044]->(h:Resource)
USING index h:Resource(ns0__quantityAmount)
WHERE h.ns0__quantityAmount > 8000
MATCH (o)<-[:ns8__P2044]-(r:Resource)-[:ns1__P31]->(end:Resource{uri:"http://www.wikidata.org/entity/Q8502"})
//USING JOIN ON end
RETURN r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
ORDER BY height desc

[manonthegithub]

That worked. First time running time is about 2 sec.
Both hints have the same time and execution plan.

So overall, do you think it is ok with memory and I should just adjust queries?

Do you think we can improve over 2 sec (not changing the db structure/adding multilabels), maybe using some special configuration property?

[andrew_bowman]

Fixing up the queries as you just did is the main thing. Remember that heap memory is like query workspace memory, so by streamlining the query and minimizing the db work, that means less activity in the heap, thus lower heap pressure.

As for the pagecache, that's going to be mostly for the native index cache and graph traversal. Now given with the sheer number of nodes, and the indexes on nodes of those labels, it's going to be challenging to be able to cover all of that with the pagecache, but in general I think the more selective you can make your indexes the better, and if you can figure out narrower labels to add in addition to :Resource, and add appropriate indexes, it should work out better for your lookups. And by narrowing down to a smaller set of rows as early as possible in the query, that should mean less graph data needs to be queried from the pagecache, which hopefully should mean less need for paging through the cache.

As always, if you can get more memory to your server, the better, but also remember that if he majority of your queries are hitting common areas of your graph, that should be good pagecache usage and fewer cache misses.

As for other query improvement ideas, you could try this at the beginning and see how it runs:

MATCH (h:Resource)
WHERE (h)<-[:ns10__P2044]-()
WITH h, true as ignored
...

After that section you can add add your WHERE clause back in, but we won't need the index hint as we're not performing an index lookup. What we're trying to figure out here is if looking up the number of :Resource nodes with an incoming relationship of this type is more efficient than the index lookup. It may not be, as you will have to touch all :Resource nodes to filter to your results, but a degree lookup per node is very cheap as well, so give it a try and see how selective this is comparatively.

[manonthegithub]

Ok. Great! Thank you so much for so awesome advice and support! You do a great job!

[manonthegithub]

Hello Andrew.
I have run the same optimised query today and it was running for 155 secs for the first time then 2nd and 3rd were for 5 -10 secs.

MATCH (o:Resource)-[:ns10__P2044]->(h:Resource)
USING index h:Resource(ns0__quantityAmount)
WHERE h.ns0__quantityAmount > 8000
MATCH (o)<-[:ns8__P2044]-(r:Resource)-[:ns1__P31]->(end:Resource{uri:"http://www.wikidata.org/entity/Q8502"})
//USING JOIN ON end
RETURN r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
ORDER BY height desc

It seems that last time it was cached and even our optimised query runs really slow.
I have connected to the jvm with jmx and was able to see that there is a lot of free space in the heap and processor activity is really low (1-2%). Overall server seems not loaded except for io, obviously there are a lot of reads.
Maybe the file sizes will be helpful:

8,0K    ./neostore
4,4M    ./neostore.counts.db.a
4,4M    ./neostore.counts.db.b
4,0K    ./neostore.id
3,0G    ./neostore.labelscanstore.db
8,0K    ./neostore.labeltokenstore.db
4,0K    ./neostore.labeltokenstore.db.id
32K     ./neostore.labeltokenstore.db.names
4,0K    ./neostore.labeltokenstore.db.names.id
14G     ./neostore.nodestore.db
4,0K    ./neostore.nodestore.db.id
56K     ./neostore.nodestore.db.labels
4,0K    ./neostore.nodestore.db.labels.id
55G     ./neostore.propertystore.db
8,0K    ./neostore.propertystore.db.arrays
4,0K    ./neostore.propertystore.db.arrays.id
4,0K    ./neostore.propertystore.db.id
136K    ./neostore.propertystore.db.index
4,0K    ./neostore.propertystore.db.index.id
576K    ./neostore.propertystore.db.index.keys
4,0K    ./neostore.propertystore.db.index.keys.id
137G    ./neostore.propertystore.db.strings
4,0K    ./neostore.propertystore.db.strings.id
3,8G    ./neostore.relationshipgroupstore.db
4,0K    ./neostore.relationshipgroupstore.db.id
98G     ./neostore.relationshipstore.db
4,0K    ./neostore.relationshipstore.db.id
64K     ./neostore.relationshiptypestore.db
4,0K    ./neostore.relationshiptypestore.db.id
456K    ./neostore.relationshiptypestore.db.names
4,0K    ./neostore.relationshiptypestore.db.names.id
128K    ./neostore.schemastore.db
4,0K    ./neostore.schemastore.db.id
255M    ./neostore.transaction.db.2575
110M    ./neostore.transaction.db.2576

Most of the index files are less than 200MB (95%), but some are a few gigs.
Maybe the fact that I run neo4j in the official docker container can also help.

Do you have any ideas why it can be so slow?

[andrew_bowman]

Can you PROFILE the query again, and expand all elements of the plan?

[manonthegithub]

Even the profile ran for 69 sec

[manonthegithub]

Hello Andrew,

do you have any ideas why the query with the execution plan above is so slow?

[andrew_bowman]

If you have query logging turned on you can observe things like cache hits vs misses. That info is actually available to you from PROFILE, but it's not shown on the browser profile plan. You can hit the Code result view button and look for PageCacheHits and PageCacheMisses, see what that says.

[manonthegithub]

It has no information about cache, probably because I use community edition.

[manonthegithub]

I've tried to enable query logging with dbms.logs.query.enabled=true, but it doesn't log anything

[andrew_bowman]

Okay, to get the most out of a PROFILE, try running it in cypher-shell. The textual representation is tough to read, but it provides more info than the browser PROFILE.

[manonthegithub]

I get:

+-----------------------------------------------------------------------------------------+| Plan      | Statement   | Version      | Planner | Runtime       | Time | DbHits | Rows |+-----------------------------------------------------------------------------------------+| "PROFILE" | "READ_ONLY" | "CYPHER 3.5" | "COST"  | "INTERPRETED" | 3174 | 909717 | 16   |+-----------------------------------------------------------------------------------------+

+-----------------------+----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| Operator              | Estimated Rows | Rows   | DB Hits | Cache H/M | Identifiers                                                                      | Ordered by                 | Other                                                                                              |
+-----------------------+----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +ProduceResults       |              0 |     16 |       0 |       0/0 | r.rdfs__label, r.ns1__P625, anon[143], height, anon[19], end, h, r, anon[171], o | height DESC                |                                                                                                    |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Projection           |              0 |     16 |      32 |       0/0 | r.rdfs__label, r.ns1__P625, anon[143], height, anon[19], end, h, r, anon[171], o | height DESC                | {r.rdfs__label : r.rdfs__label, height : cached[h.ns0__quantityAmount], r.ns1__P625 : r.ns1__P625} |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Filter               |              0 |     16 |      94 |       0/0 | anon[143], anon[19], cached[h.ns0__quantityAmount], end, h, r, anon[171], o      | h.ns0__quantityAmount DESC | end:Resource                                                                                       |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Expand(All)          |              0 |     78 |     147 |       0/0 | anon[143], anon[19], cached[h.ns0__quantityAmount], end, h, r, anon[171], o      | h.ns0__quantityAmount DESC | (r)-[anon[171]:ns1__P31]->(end)                                                                    |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Filter               |              0 |     69 |      69 |       0/0 | anon[143], anon[19], cached[h.ns0__quantityAmount], h, r, o                      | h.ns0__quantityAmount DESC | r:Resource                                                                                         |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Expand(All)          |              0 |     69 |     138 |       0/0 | anon[143], anon[19], cached[h.ns0__quantityAmount], h, r, o                      | h.ns0__quantityAmount DESC | (o)<-[anon[143]:ns8__P2044]-(r)                                                                    |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Filter               |             66 |     69 |      69 |       0/0 | anon[19], cached[h.ns0__quantityAmount], h, o                                    | h.ns0__quantityAmount DESC | o:Resource                                                                                         |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +Expand(All)          |             66 |     69 |  454618 |       0/0 | anon[19], cached[h.ns0__quantityAmount], h, o                                    | h.ns0__quantityAmount DESC | (h)<-[anon[19]:ns10__P2044]-(o)                                                                    |
| |                     +----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+
| +NodeIndexSeekByRange |          39080 | 454549 |  454550 |       0/0 | cached[h.ns0__quantityAmount], h                                                 | h.ns0__quantityAmount DESC | :Resource(ns0__quantityAmount) > {  AUTOINT0}                                                      |
+-----------------------+----------------+--------+---------+-----------+----------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------+

I get 0/0 for cache hit/misses

[andrew_bowman]

Hmm...with your current model this is probably the best you can get, as the real selectivity is that second (from the bottom...cypher-shell query plans are read from the bottom-up) Expand(All) step going from 454549 rows to 16. The relationships off of those nodes act as the filter.

Out of curiosity what does this return?

MATCH ()-[r:ns10__P2044]->()
RETURN count(r)

[manonthegithub]

The count is 1 661 172 , not so many. It is really strange that it takes about 200 seconds. Index seek should be very fast, the bottleneck must be in Expand.

Do you think using enterprise version will improve performance? What gain can we get from it?

[andrew_bowman]

I'd sooner look at the memory settings, specifically your heap and pagecache settings in your neo4j.conf, as well as the total memory on your machine. If you don't have enough memory available then you're going to have a lot of cache misses when doing that expand.

Here's another query to try, though I'm doubting that it will improve on the other:

MATCH (h:Resource)
USING index h:Resource(ns0__quantityAmount)
WHERE h.ns0__quantityAmount > 8000 AND ()-[:ns10__P2044]->(h)
MATCH (o:Resource)-[:ns10__P2044]->(h)
MATCH (o)<-[:ns8__P2044]-(r:Resource)-[:ns1__P31]->(end:Resource{uri:"http://www.wikidata.org/entity/Q8502"})
//USING JOIN ON end
RETURN r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
ORDER BY height desc

[manonthegithub]

Sorry @andrew.bowman for the late response. The memory settings should be fine.
I have installation with 18G of heap and 54G of page cache.
I have monitored heap with visualvm and can say it has a lot of free space. The process's memory on the machine is about 75G, so all the settings are set right.

I have noticed that neostore.relationshipstore.db is 98G (I have sent the size of all the db files in one of the previous messages). I may suspect that Expand works with this file. Is it right? Should we have page cache comparable to its size?

[andrew_bowman]

It's not correlated in that way. When you expand from a node, it's only looking at the relationships connected on that node, so with the rows going from 454549 to 69, we can infer there are only 69 total relationships of that type from amongst that set of nodes. The number and types and directions of those relationships are on the nodes themselves, so it won't actually hit the relationship store until it has to expand the relationships found, so it will only be hitting the relationship store for those 69 relationships. The filtering based on the presence of the relationship types is happening on the node record itself during expansion. If anything you could look at the node store.

We can try out a test to see if the index lookup and filtering is the bottleneck (I suspect it is from the db hits we're seeing).

Try getting node ids from this:

MATCH (h:Resource)
USING index h:Resource(ns0__quantityAmount)
WHERE h.ns0__quantityAmount > 8000 AND ()-[:ns10__P2044]->(h)
RETURN collect(id(h)) as ids

And then try the query starting from the nodes with these ids. That timing will be without the node lookups and filtering. If that's fast, but your other query is slow, then the index lookups and filtering are the bottleneck.

MATCH (end:Resource{uri:"http://www.wikidata.org/entity/Q8502"})
UNWIND $ids as id
MATCH (h)
WHERE id(h) = id
MATCH (o:Resource)-[:ns10__P2044]->(h)
MATCH (o)<-[:ns8__P2044]-(r:Resource)
WHERE (r)-[:ns1__P31]->(end)
RETURN r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
ORDER BY height desc

We'll want to take a look at the expanded plan for this one.

[manonthegithub]

Sorry, I had troubles with my neo4j instance.

Here is the explain plan for the second query:

It takes about 9 seconds running it for the first time and about 50 ms afterwards.
The first query ran for about 4 mins.

[andrew_bowman]

That makes sense. The initial index lookup and filtering is the bottleneck.

If there's anything you can do in your modeling to improve this, such as by adding a special label on nodes that fit this criteria (or at least those with an incoming :ns10__P2044 relationship), that may help.

[manonthegithub]

I don't think I can add a labels for all the criteria I have. I will think more of how can I improve performance.

Do you have an idea why is the second query with a few hundreds of db hits takes about 9 seconds (I think it is quite a lot)?

[manonthegithub]

I have investigated a bit more in depth.
I have run the query:

MATCH (o:Resource)-[:ns10__P2044]->(h:Resource)
USING index h:Resource(ns0__quantityAmount)
WHERE h.ns0__quantityAmount > 8000
MATCH (o)<-[:ns8__P2044]-(r:Resource)-[:ns1__P31]->(end:Resource{uri:"http://www.wikidata.org/entity/Q8502"})
//USING JOIN ON end
RETURN r.rdfs__label, h.ns0__quantityAmount as height, r.ns1__P625
ORDER BY height desc

and profiled neo4j with visialvm and figured out that most of the time (~ 48 out of ~49 sec) is spent on loading the relationships of the nodes (indeed it misses cache).

After watching the talk https://www.slideshare.net/thobe/an-overview-of-neo4j-internals (it is a bit old, but as I have seen from a short glance on the sources, everything regarding the structure of the files on disk should still be the same) it seems to me that:

• as you said before labeling should help to narrow down the amount of data used in query processing
• non-cached search seems to be fast only if you can filter out most of the data based on indexes
• fast random access disks should significantly improve non-cached queries

Please correct me if I am wrong.

[manonthegithub]

Hello @andrew.bowman! As I have got from presentation (https://www.slideshare.net/thobe/an-overview-of-neo4j-internals, slide 14), you had a limit of number of regions of cache for each file. Does neo4j 3.X still have this limit? Maybe I can somehow adjust it?

[ScOut3R]

@manonthegithub do you have server monitoring in place? If yes please take a note of your I/O throughput and operations values, disk latency and CPU iowait metrics while you execute any of your queries when they are not cached.

Then you can start think of how to put more memory in that machine of yours.

[manonthegithub]

There are two points.
The first is that it is definitely a lot of disk reads and io-waits when you do non-cached queries. And it is normal, you can’t avoid it. And also throughput itself does not really matter, what matters is fast disk seek and low latency, because the data is read not sequentially, but at arbitrary places.
The second point is that neo4j team suggests to warm-up your neo4j instance with the query which reads all the nodes and relationships to put some data into cache. So after warm-up it should be much faster and at this point amount of memory matters. Normally, you should start using the instance only after warm-up.