You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by "avowkind (via GitHub)" <gi...@apache.org> on 2023/09/21 02:11:47 UTC
[GitHub] [age] avowkind opened a new issue, #1240: Detach Delete performance questions
avowkind opened a new issue, #1240:
URL: https://github.com/apache/age/issues/1240
I'm finding DETACH DELETE of a filtered list of nodes to be very slow in comparison with the equivalent match or create. I understand there's work to do but its taking 30 seconds to delete 1000 vertices.
Given a schema where we have Observations(100000s), Features (1000s) and Properties( 100s) and Datasets (10s)
That each observation has three outgoing edges ( example values )
```
(o:Observation) -[observedProperty]-> (p:Property { id: 'water_temperature_celsius'})
(o:Observation) -[featureOfInterest]-> (f:Feature { id: 'tank:A01'})
(o:Observation) -[in]-> (d:Dataset { id: 'site_temperatures'})
```
The Observation has properties such as date, result etc.
Creating and Matching observations performance is very good - I can upload 1000s of observations a second and retrieving a set of observations filtered on particular properties and belonging to a given dataset takes only a few seconds to get MB of results.
However, Deleting observations is really really slow in comparison.
For example to remove all the obs in a dataset.
```
MATCH (o:Observation)-[in]->(d:Dataset { id: 'site_temperatures'} )
DETACH DELETE o
RETURN o
```
Takes so long it times out. I have seen examples where the database process receives a kill 9 from the supervisor.
Adding LIMIT 1000 does complete but still takes several seconds.
Questions:
1 - am I doing this with the correct query?
2 - why might this take so long? - Although there are 1 vertex and 3 edges to remove per observation this is not much more work to find than the equivalent match.
I wonder whether:
- is this an indexing thing?
- Is this a lock / deadlock thing? - some suggestion that any other asynch requests to the db to get observations ( which is quite likely) results in some obs being 'in use'. blocking the delete. This might just be a side effect of it taking so long.
This screen shot from PGAdmin taken at 14:06 shows a detach delete of all observations that started at 13:26.
This is running on a MacBook Pro 2023. Apple M2 Max 64GB. MacOS Ventura. PostgreSQL 15.
I see the same problem on an Ubuntu Linux server.
My app is running in docker - but I can reproduce the issue by putting the same SQL request into PgAdmin.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Detach Delete performance questions [age]
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 closed issue #1240: Detach Delete performance questions
URL: https://github.com/apache/age/issues/1240
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Detach Delete performance questions [age]
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1769581779
@avowkind A patch for detach delete was merged into [master](https://github.com/apache/age/tree/master) recently. Could you let us know if that improves the performance issue?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] jimenasansav commented on issue #1240: Detach Delete performance questions
Posted by "jimenasansav (via GitHub)" <gi...@apache.org>.
jimenasansav commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1729917559
Hi @avowkind
Thank you very much for sharing this issue. Our team is already looking into it. In the meantime, please do not hesitate to reach out if you have any questions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Detach Delete performance questions [age]
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1756474525
Hi @avowkind I don't see why your query would be incorrect.
Also, I believe there might be area of improvement for `DETACH DELETE` code. I am currently working on it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] avowkind commented on issue #1240: Detach Delete performance questions
Posted by "avowkind (via GitHub)" <gi...@apache.org>.
avowkind commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1730514697
Some tools for diagnosis.
[observations_generator.py.txt](https://github.com/apache/age/files/12693958/observations_generator.py.txt)
The attached python script will generate a test graph with mock data to illustrate the above schema. it outputs two sql files
- create_graph.sql: creates a graph with 1000 features, 100 properties, and 10 datasets
- create_observations.sql: creates 1000 batches of 100 observations each
create observations take about 3 minutes to create 1M observations.
These queries list and count observations
```sql
LOAD 'age';
-- list some observations in a dataset
SET search_path = ag_catalog, "$user", public;
SELECT * FROM cypher('perf_test_graph', $$
MATCH (d:Dataset {id: 'dataset:1'})<-[:in]-(o:Observation)
RETURN o
$$) as (o agtype);
This is fast - 100ms to return >10000 results.
-- count obs in each dataset.
SELECT * FROM cypher('perf_test_graph', $$
MATCH (d:Dataset)<-[:in]-(o:Observation)
RETURN d.id, count(o) as observation_count
$$) as (d_id text, observation_count bigint);
```
This is also fast: but noting that here if I omit the arrow on the edge <- then the query takes forever.
Code to delete observations
```sql
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT * FROM cypher('perf_test_graph', $$
MATCH (d:Dataset {id: 'dataset:1'})<-[:in]-(o:Observation)
DETACH delete o
RETURN o
$$) as (o agtype);
```
This is slow. Taking 1:46 mins.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Detach Delete performance questions [age]
Posted by "rafsun42 (via GitHub)" <gi...@apache.org>.
rafsun42 commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1821753254
Closing due to inactivity.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] CapnSpek commented on issue #1240: Detach Delete performance questions
Posted by "CapnSpek (via GitHub)" <gi...@apache.org>.
CapnSpek commented on issue #1240:
URL: https://github.com/apache/age/issues/1240#issuecomment-1735903715
Assign me!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org