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
Thank you for sharing your observation and experience! And it’s good to see that you came around to monitoring the system.
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”