1

Under testing, our Foxx app is getting into "deadlock detected" problems. These seem to be caused by traversal queries. Apriori, it is difficult if not impossible to know which tables will be used during traversal. However, I did take one specific case where I could determine the number of tables and wrap the AQL in a transaction for testing:

var result = db._executeTransaction({ "collections" : { "read" : [ "pmlibrary", "pmvartype", "pmvariant", "pmproject", "pmsite", "pmpath", "pmattic" ] }, "action" : "function(){var db=require(\"@arangodb\").db;var res=db._query(\"FOR o IN ['pmlibrary/199340787'] FOR v,e,p IN 0..7 INBOUND o pm_child RETURN p.vertices\");return res.toArray()}" });

FYI, the list of tables in the collections do not include the edge tables.

However, the deadlocks on this statement continue. I'm not sure what to try next. Thanks.

ggendel
  • 403
  • 3
  • 9

2 Answers2

1

I'm not an indexing/performance expert with ArangoDB, but I recently had some issues with deadlocking too and by reconstructing and reshaping the queries had huge performance gains, sometimes queries that took 120 seconds then took 0.2 seconds.

A key thing I did was try to help ArangoDB know when to use an Index, and make sure that index is there to be used.

In your example query, there is a problem that stops ArangoDB from knowing an index occurs:

FOR o IN ['pmlibrary/199340787'] 
FOR v,e,p IN 0..7 INBOUND o pm_child 
  RETURN p.vertices

The key issue with this is that your original FOR loop is using a value out of an array, which may hinder ArangoDB identifying an index. You could easily replace your o with a parameter, and put in your 'pmlibrary/199340787' value directly, and with the Explain button see if it identifies it can use an index.

One issue I found was trying to make it super clear to use an index, which sometimes meant adding additional LET commands to allow ArangoDB to build the contents of arrays, and then it seems to know what index to use better.

If you were to test the performance of the query above, versus something like:

LET my_targets = (FOR o IN pmlibrary FILTER o._key == '199340787' RETURN o._id)

FOR o IN my_targets
FOR v,e,p IN 0..7 INBOUND o._id pm_child 
  RETURN p.vertices

It may seem counter intuitive, but with testing I found amazing performance gains by breaking queries apart to help ArangoDB notice it could use an Index.

If any queries are against the values within Arrays or if you are using dynamic attribute names, then it won't always use an index even if one exists.

Also I found that the server will cache response for queries, so if you want to test changes to a long running query and want to avoid cache hits on your second + queries, I had to stop and restart the arangodb3 service.

I hope that helps give you a target for shuffling around your query to work with indexes.

Remember you already have inbuilt indexes on _id, _key, _from, _to, so you need to try and make sure it's going to use other indexes that can help speed up your query.

David Thomas
  • 2,264
  • 2
  • 18
  • 20
  • David, Thanks for the information. In my case there may be multiple items in the array and they may be in different collections. It turned out that there were two deadlock errors thrown. I figured that the AQL statements were playing against each other, but it turned out that it must be concurrent invocations of the same AQL statements. The QA tester didn't tell me that I had actually fixed the first by using a WITH clause. I just thought I missed something. With WITH clauses on both statements, there is no more deadlock. – ggendel Apr 11 '17 at 15:23
1

The proper solution was to use a WITH clause rather than a transaction.

ggendel
  • 403
  • 3
  • 9