ArangoDB Performance

Question

I am exploring the use of Arangodb as a graph engine for a project I am working on that needs shortest path analysis.

My collections look like this:

a route network of ~3.5M edges in an edge collection (_to/_from)
a vertex collection ~2.7M vertices (geo index on [lat,lng]).
a trips collection with start/end locations (not mapped to nodes).

The first task is to snap the origin and destination coordinates of the trips to vertices in on the network. I am using the following query to do that:

FOR t IN trips
    let snappedFrom = (
        FOR x IN nodes
          SORT GEO_DISTANCE([t.Orig_Long, t.Orig_Lat], [x.lng, x.lat]) ASC
          LIMIT 1
          RETURN x._id
        )[0]
    let snappedTo = (
        FOR x IN nodes
          SORT GEO_DISTANCE([t.Dest_Long, t.Dest_Lat], [x.lng, x.lat]) ASC
          LIMIT 1
          RETURN x._id
        )[0]
    UPDATE t._key WITH {snappedFrom,snappedTo} IN trips

This is taking around 3.5 hours, and I want to reduce that significantly if possible.

I am running on an AWS instance with 32GB of RAM and 8 cores. I notice that when running this query, it is only using a single core which is killing me.

I am curious about setting up the arangodb for pure performance. My use case is using the DB as a calculator really. In fact is likely it will be part of a CI/CD workflow when done. I don't need any safe guards in there, there wont be any parallel user requests, and if the data is bad, I just blow it away and start again.

I am using a standard install with docker

docker run -it --name=adb --rm -p 8528:8528 -v arangodb:/data -d -v /var/run/docker.sock:/var/run/docker.sock arangodb/arangodb-starter --starter.address=<$IP> --starter.mode=single

I am going to run into the same issue when I run shortest_path on all trips too, that will take forever if single core.

Any help with the config, better query, or even better AWS setups would be truly appreciated.

Someone mentioned the use of DISTANCE instead of GEO_DISTANCE, but it made little to no difference — Shane
It was also suggested to create a simple script to schedule multiple queries (one for each CPU) as each query runs in its own CPU. Simple as scheduling trip_count/cpu_count to each query with a LIMIT clause. — Shane

sevcik.tk sevcik.tk · Accepted Answer · 2020-07-31T05:28:49

add Geo-Spatial Indexes on Orig and Dest fields, that will enable server to optimize / speed up sub-queries

for further speeding up of processing run main query in batches, processing more smaller batches is faster than running over all documents at once

ArangoDB Performance

1 Answers