# Deleting 20k nodes with about 50 rels each takes 3 days

**URL:** <https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733>\
**Category:** Cypher\
**Tags:** performance, cypher, delete\
**Created:** [November 10, 2020, 3:54am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733 "2020-11-10T03:54:31Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 3:54am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/1 "2020-11-10T03:54:31Z")

</div>

Hi there,

It is taking my server about 3 days to delete just 20,000 nodes in my graph, and they have at most 50 relationships each.

I am using Neo4j Python driver 1.7.6 to do this operation on an Ubuntu 14.04 LTS server with 32GB RAM running Neo4j Enterprise 3.5.19.

Here's my query:

```auto
MATCH (:AWSAccount{id: '1234'})-[:RESOURCE]->(:AWSVpc)<-[:MEMBER_OF_AWS_VPC]-(:EC2Subnet)<-[:PART_OF_SUBNET]-(n:NetworkInterface) 
WHERE n.lastupdated <> {UPDATE_TAG} 
WITH n LIMIT {LIMIT_SIZE}
DETACH DELETE (n)
RETURN COUNT(*) as TotalCompleted

```

`UPDATE_TAG` is a timestamp like `1604973243`, and `LIMIT_SIZE` is set to `100`. My code deletes nodes that don't have a given timestamp value, and as per [this guide](https://neo4j.com/developer/kb/large-delete-transaction-best-practices-in-neo4j/), I run the above query to delete in small batches until `TotalCompleted` returns 0. My problem is that this process takes **days**. Any ideas on why this is and how I can fix this? The actual Python code that calls this query is [here](https://github.com/lyft/cartography/blob/043e3ba65037a59767768f33d9e3f0c73e9a2453/cartography/graph/statement.py#L76-L85), and although it could clearly be written much more safely, I don't see this slow behavior on any of my other dozens of deletion jobs.

### Repro

1. Before running my delete job, I have 21,209 nodes with a timestamp of `1604836804`:  

2. I run the delete job in my Python script as described above and check the Neo4j web interface after a couple minutes while it continues:  

3. The count has been stuck here at 4300 for the past hour and I am very confused. My system memory usage stays around 31% usage out of a total 32GB, CPU hovers around 30%. When I manually run the delete query in the web interface, it works:  

Here's my PROFILE:

 ![plan (1)](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/7/726f8b84cfd546b179c73d70b64099f1621fe2c0.png)

I have read these links searching for an answer but have not found one yet:

- [Deleting nodes taking forever](https://community.neo4j.com/t/deleting-nodes-taking-forever/15918)
- [What is the most efficient way to Delete 20 Million Nodes from a database with over 7.5 Billion Nodes and 15 Billion relationships?](https://community.neo4j.com/t/what-is-the-most-efficient-way-to-delete-20-million-nodes-from-a-database-with-over-7-5-billion-nodes-and-15-billion-relationships/4151)

Neo4j.log and query.log don't show anything. I'd like to avoid APOC if possible because I don't want to force users of my software library to need to install anything other than a fairly vanilla Neo4j setup. Any ideas? Thanks very much in advance.

---

<div class="post-metadata">

**Author:** ![benjamin.squire](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/benjamin.squire/32/1496_2.png) [@benjamin.squire](https://community.neo4j.com/u/benjamin.squire)\
**Post date:** [November 10, 2020, 4:45am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/2 "2020-11-10T04:45:20Z")

</div>

What is your heap and max heap and page cache in Neo4j.conf?

Is there anything in particular about those 4300? They all have at most 50 rels?

You could delete the relationships individually, this might look like:

```auto

MATCH (:AWSAccount{id: '1234'})-[:RESOURCE]->(:AWSVpc)<-[:MEMBER_OF_AWS_VPC]-(:EC2Subnet)<-[r:PART_OF_SUBNET]-(n:NetworkInterface) 
WHERE n.lastupdated <> {UPDATE_TAG} 
WITH n,r {LIMIT_SIZE}
DELETE r SET n:DeleteMe
RETURN COUNT(*) as TotalCompleted

```

Then run something like

```auto
match (n:DeleteMe) Delete n limit {LIMIT_SIZE} RETURN 
COUNT(*) as TotalCompleted

```

---

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 4:53am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/3 "2020-11-10T04:53:59Z")

</div>

> What is your heap and max heap and page cache in Neo4j.conf?

My neo4j.conf has heap size:

```auto
dbms.memory.heap.max_size=5g

```

and cache size in the file says...

```auto
# The default page cache memory assumes the machine is dedicated to running
# Neo4j, and is heuristically set to 50% of RAM minus the max Java heap size.
#dbms.memory.pagecache.size=10g

```

Admittedly I've never tuned these values. Any recommendations?

> Is there anything in particular about those 4300? They all have at most 50 rels?

Nothing in particular, they have about 12 fields each and they all have at most 50 rels.

---

<div class="post-metadata">

**Author:** ![benjamin.squire](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/benjamin.squire/32/1496_2.png) [@benjamin.squire](https://community.neo4j.com/u/benjamin.squire)\
**Post date:** [November 10, 2020, 4:58am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/4 "2020-11-10T04:58:56Z")

</div>

I would change that to explicit settings each of maybe 10g heap and 10g page cache. There are two settings for heap, change them both.

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [November 10, 2020, 8:40am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/5 "2020-11-10T08:40:38Z")

</div>

Hello @alexchantavy 🙂

You can use APOC:

```auto
CALL apoc.periodic.iterate('MATCH (n) RETURN n', 'DETACH DELETE n', {batchSize: 1000})

```

Regards,  
Cobra

---

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 8:53am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/6 "2020-11-10T08:53:03Z")

</div>

I upped the heap values to 10g but accidentally forgot to set pagecache - still hanged for half an hour with 8000 nodes left. I'm doing another run with pagecache set correctly and will report back.

---

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 8:55am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/7 "2020-11-10T08:55:16Z")

</div>

> You can use APOC

Hi, as said in my post, I'd like to try to avoid apoc if possible. I'll give it a try if nothing else works though.

---

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 4:16pm UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/8 "2020-11-10T16:16:19Z")

</div>

I re-ran it with the new cache and heap settings and it's still stuck after 8 hours. Before I rewrite some code and give APOC a try, any other ideas?

---

<div class="post-metadata">

**Author:** ![clem](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/clem/32/4364_2.png) [@clem](https://community.neo4j.com/u/clem)\
**Post date:** [November 10, 2020, 4:43pm UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/9 "2020-11-10T16:43:05Z")

</div>

Do you have indices on `AWSAccount.id` and `NetworkInterface.lastupdated`? Without indexes, a linear search would need to be done which would be painfully slow (depending on many nodes there are in the DB.)

Is there a reason why `AWSAccount.id` are strings instead of numbers? Numbers would be faster.

NOTE: indexes are not a magic bullet. Don't index everything! Only index things that you are searching on (e.g. with conditions) or where there aren't many different values (e.g. booleans).

See: [https://neo4j.com/docs/cypher-manual/current/administration/indexes-for-search-performance/](https://neo4j.com/docs/cypher-manual/current/administration/indexes-for-search-performance/)

---

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 5:05pm UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/10 "2020-11-10T17:05:14Z")

</div>

> Do you have indices on `AWSAccount.id` and `NetworkInterface.lastupdated` ?

I have indexes on `AWSAccount.id` but not on `{any label}.lastupdated`. It's still odd how none of my other labels have this problem, but thanks for the idea: I'll do some testing with an index on lastupdated for just the NetworkInterface.

> Is there a reason why AWSAccount.id are strings instead of numbers? Numbers would be faster.

Not really other than "someone else decided this a long time ago and now that's how it is" 🙂.

---

<div class="post-metadata">

**Author:** ![clem](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/clem/32/4364_2.png) [@clem](https://community.neo4j.com/u/clem)\
**Post date:** [November 10, 2020, 5:12pm UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/11 "2020-11-10T17:12:45Z")

</div>

I think you can change them all to integers. (Not too hard.).

You might experiment with a clone of a DB using ints for id's instead of strings. Obviously, comparing two integers can be done in a few instructions, where as a string comparison is a loop. (To make a hash out of string, you still need a loop.)

I don't know what sorts of Java profiling tools can be used on Neo4J but maybe those can be used to find out where the bottlenecks.

---

<div class="post-metadata">

**Author:** ![alexchantavy](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/alexchantavy/32/12695_2.png) [@alexchantavy](https://community.neo4j.com/u/alexchantavy)\
**Post date:** [November 10, 2020, 6:01pm UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/12 "2020-11-10T18:01:55Z")

</div>

No luck, it still hangs short of finishing the last 4000 nodes. I looked closer and it appears 5 of them have 50 rels, and the rest have fewer than 5 rels each. All of them have about 12 properties each.

I'm going to go stare at code some more 😕

---

<div class="post-metadata">

**Author:** ![benjamin.squire](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/benjamin.squire/32/1496_2.png) [@benjamin.squire](https://community.neo4j.com/u/benjamin.squire)\
**Post date:** [November 11, 2020, 6:49pm UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/13 "2020-11-11T18:49:18Z")

</div>

At this point I don't think APOC will help you as the issue is fundamentally due to the node in particular you are deleting, not the speed. You should try and maybe delete a few of them manually one at a time.

Did you try deleting the rels independently of the Nodes?

---

<div class="post-metadata">

**Author:** ![clem](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/clem/32/4364_2.png) [@clem](https://community.neo4j.com/u/clem)\
**Post date:** [November 14, 2020, 4:48am UTC](https://community.neo4j.com/t/deleting-20k-nodes-with-about-50-rels-each-takes-3-days/28733/14 "2020-11-14T04:48:32Z")

</div>

Have you tried PROFILE? I haven't tried it but it looks like it could help:

> **[Query tuning - Cypher Manual](https://neo4j.com/docs/cypher-manual/current/query-tuning/#how-do-i-profile-a-query)**
>
> Query tuning for the Cypher query language.