OHDSI Home | Forums | Wiki | Github

Peak usage of WebAPI every Friday lasting over weekend because of looped query

Implementers
, ,
#1

Hi everyone,

We are currently using WebAPI and Atlas version 2.12.0. We started to see peak usage of WebAPI every Friday lasting till the weekend which is causing problem with loading Atlas concept sets and cohort definitions. All other tools that depend on WebAPI starts to crash on Friday and will be back to normal on Monday.

We found out that this is because of a looped query being run on PostgreSQL database. Will someone confirm is this looped query part of the WebAPI cache run?

Thank you,

Best Regards,
Sam.

#2

Could you provide more detail about which looped query you’re seeing on your database?

#3

Hi @Chris_Knoll here is the query details that our system administrator extracted.

image
image720×488 59.6 KB

#4

Ok that query is fetching job params for a given execution. This could be done when the UI looks at the Jobs view in the UI. I’m not sure why this would just kick off suddenly on a friday.

Is there any way you could capture what the paramater of $1 was for those calls, if they are focused around one specific job execution ID or maybe a set of them. I think 7.4M calls in 1 hour seems crazy, we need to have some of the tech folks look at where this query is invoked and why it would be invoked so often…thre is a notion of an ‘N+1’ query where when you query an entity, you fetch the ‘root’ object once, and then for each associated N entities to it it does N addional queries (root (1) + associated (N) is N+1). Will need to investigate this further, I’m not hearing a lot of reports on this happening, but clearly it’s happenign for you so we’ll have to hypothsize about what would lead to that behavior.

Btw: if you were to look at your Jobs panel in AtlasUI, coudl you tell me how many job executions are in your environment?

#5

Hi @Chris_Knoll thank you for your explanation.

I have asked our system administrator to get the information. As for the job executions in the panel, there are 20,668 job executions.

#6

Hi @Chris_Knoll,

I have started to look into Atlas jobs and it appears there are quite a lot of failed cache jobs
for different CDM databases we are using. They coincide with the WebAPI peak usage. Here is the image of failed cache job

image
image1706×177 16.8 KB

This goes on for one day untill they are completed. Is there a way we can why this cache run failed?

Will you please let us know if you have faced something similar or suggest any idea to fix this problem as rest of the applications would not work when WebAPI has peak usage.

#7

2.14 has a better implementation of the cacheing (it’s more performant) so you might want to upgrade there.

The error may be caused by a missing table or misconfiguration. You should be able to see an error message in your WebAPI log.

I’m not sure if 2.12 has the option, but there is an option in the source table called ‘is_cache_enabled’. If you set this to false, it will not attempt to run the cache jobs when the application starts.

#8

Hi @Chris_Knoll,

Thank you very much for your reply. We will start looking into WebAPI logs to see what is the reason for this failed cache.

I will try out the option is_cache_enabled option at source table to see this works for the WebAPI 2.12.0 version.

t