Bug #11261

[API] full text search on pipeline_instances table is extremely slow (wrong index?)

Added by Tom Clegg 8 months ago. Updated 6 months ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Performance
Target version:Arvados Future Sprints
Story points-
Velocity based estimate-

Related issues

Copied from Arvados - Bug #11071: [Workbench] qr1hi performance tests are broken Resolved 03/14/2017

History

#1 Updated by Tom Clegg 8 months ago

Copied from #11071:

Version query:

curl https://${ARVADOS_API_HOST}/discovery/v1/apis/arvados/v1/rest | jq .source_version

Test query:

time curl -H "Authorization: OAuth2 $ARVADOS_API_TOKEN" -d count=none -d filters='[["any","@@","hash:*"]]' -d limit=1 -d _method=GET https://${ARVADOS_API_HOST}/arvados/v1/groups/contents | jq '.items[0].href'

results:

version cluster count= real
7eda588-7 qr1hi none 1m19.801s
7eda588-7 qr1hi exact 1m20.374s
b1aa6c8-7 4xphq none 0m0.046s
b1aa6c8-7 4xphq exact 0m7.106s

Another test query that ensures we query the collections table:

time curl -H "Authorization: OAuth2 $ARVADOS_API_TOKEN" -d count=none -d filters='[["any","@@","hash:*"],["uuid","is_a","arvados#collection"]]' -d limit=1 -d _method=GET https://${ARVADOS_API_HOST}/arvados/v1/groups/contents | jq '.items[0].href'

results:

version cluster count= real
7eda588-7 qr1hi none 0m0.134s
7eda588-7 qr1hi exact 0m0.143s
b1aa6c8-7 4xphq none 0m0.051s
b1aa6c8-7 4xphq exact 0m0.063s

Hm. Tried a few other queries on qr1hi, and all of the slowness seems to be the fault of the pipeline_instances table:

count= uuid is_a ... real
exact arvados#pipelineTemplate 0m0.059s
exact arvados#containerRequest 0m0.186s
exact arvados#job 0m0.106s
exact arvados#workflow 0m0.119s
exact arvados#group 0m0.068s
exact arvados#collection 0m0.143s
exact arvados#pipelineInstance 2m40.890s
none arvados#pipelineInstance 2m41.996s

#2 Updated by Tom Clegg 8 months ago

tomclegg@shell.qr1hi:~$ time curl -H "Authorization: OAuth2 $ARVADOS_API_TOKEN" -d count=none -d filters='[["any","@@","hash:*"]]' -d limit=1 -d _method=GET https://${ARVADOS_API_HOST}/arvados/v1/pipeline_instances | jq '.items[0].href'
...

real    2m41.471s
user    0m0.028s
sys    0m0.048s

#3 Updated by Tom Morris 6 months ago

  • Category set to Performance
  • Target version set to 2017-06-07 sprint

#4 Updated by Tom Morris 6 months ago

  • Target version changed from 2017-06-07 sprint to Arvados Future Sprints

Also available in: Atom PDF