Performance analytics queries

The current design of dhis2 analytics api is hurting it self
Let’s say you have a dashboard with some queries
Same or different people with same access rights/pyramid access it or refresh their browser page.

Since it’s often long running queries (think a program tracker to count enrolled entities during a period)
IMO dhis2 should :

  • keeping track of what is running and
  • NOT launching the same query again and
  • answering “come back later in 30 sec your query is still running” (and probably populating a cache)

would have at a huge effect on the stability of dhis2 and db resources.

One of the last incident we had 24 similar queries (and suspect the same) running at the same time

Dear @Stephan_Mestach,

Thank you for sharing your observation and experience! And it’s good to see that you came around to monitoring the system. :+1:


Hi @Stephan_Mestach . You highlight a very good point. The situation can in fact be a lot worse where users have configured a query which might take some hours to complete (I’ve seen these!). By the time it returns, if ever, the http session would be long gone. Not a full mitigation to the problem I know, but one defensive measure would be to simply kill these analytics queries which are running longer than a configurable time (eg 30s). So a cron running ever 30s which finds the pids of queries matching along the lines of “select … from analytics_…” which have run for 30s or more then terminating them. Its kind of a hacky workaround but this approach has kept some instances alive which would otherwise have succumbed to friendly fire. I’ll share a script example later this week.


the problem we want these queries to complete… and show the results
we just don’t want to put a 10x size server needed because of the api letting things “run again”

1 Like