Spline getting hanged due to long running queries on Arango

[vishalag001]

Hi @wajda,
Spline is getting hanged due to long running arango queries. I see there are lot of delete queries running for long on Arango. These queries are mostly coming from rollback queries. And it is getting stuck on expression collection.
On checking I found that expression collection is of size around 900 mil. And similarly other collections are also huge.
Arango queue is also getting filled. We are using Arango community version 3.9 with 3 nodes cluster of 32 cores and 128 GB memory.
I am using arango starter to start arango cluster.
Do you recommend any change so that we can easily scale ?

[wajda]

Honestly we did't use it at such big scale. I am happy to assist you of cause, it's just I don't have any prepared answer for you right now :)

So, first of all, please confirm what Spline server version are you using (there is a difference in the persistent logic between versions 0.7 and 1.0).
Assuming it's 1.0 and that the delete query is coming from the transaction rollback then the slowness might likely be caused by missing index on the _txInfo property. Looking at the code now I also spotted a bug (this is a development version after all). Let me make a PR and get back to you shortly.

On the other hand, what causes transactions rollback? Is there any error in the server or arangodb log?

[wajda]

Download the following Spline Admin CLI build (PR #1267) - https://teamcity.jetbrains.com/repository/download/OpenSourceProjects_AbsaOSSSpline_AutomaticBuildsWithTests_Spark24/4301078:id/admin-1.0.0-SNAPSHOT.jar

Then execute the Spline Admin CLI to update your existing database accordingly:

java -jar admin-1.0.0-SNAPSHOT.jar db-exec --foxx-reinstall arangodb://HOST/DATABASE

That one should be quick.

Then you need to add missing indexes. You can use Admin CLI for that as well, but it would recerate all indexes, and considering the size of your DB I would rather just add missing ones manually. Login into the arangosh and execute the commands below:

arangosh --server.database=YOUR_HUGE_DATABASE

db.dataSource.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.executionPlan.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.operation.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.progress.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.schema.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.attribute.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.expression.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.follows.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.writesTo.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.readsFrom.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.executes.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.depends.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.affects.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.progressOf.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.emits.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.produces.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.consistsOf.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.computedBy.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.derivesFrom.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.takes.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});
db.uses.ensureIndex({type: "persistent", fields: ["_txInfo.uid"], estimates: false});

It might take some time, I cannot estimate how long. You can try to create index on some collections first to see how long does it take, and if it makes any difference in performance (it should speed up rollbacks dramatically, but it will also slow writes down a tiny bit (hopefully by negligible amount).

[wajda]

@vishalag001 did you have a chance to try the change out?

[vishalag001]

Hi @wajda, I created the indexes as suggested and changed the rollback logic to include uid filter. Arango DB is stable since then. Thanks a lot for this !