I have one user who wants to run a report that has some issues over a fairly large dataset. This results in the CPU usage spiking, and causing timeout errors for other OpenEMR users until the report finishes running, which can take up to 10 minutes. In the meantime, I can kill the query if someone comes and tells me, but basically the whole clinic is down for that 10 minutes. Since the average appointment is only 20 minutes long, that is a very long time for the providers.
I am addressing the issue in this case by showing the user how to limit the size of the queries and also fixing some of the index issues, but I would also like to also address the issue at a resource allocation level for the next time this happens. I would prefer for the user running the report to have to wait longer for the report, or get an error message saying that the report is taking too long to run than have the entire clinic shut down if a query is taking too long.
I’ve been searching online for suggestions for mysql, but haven’t found anything so far, other than renicing processes (which doesn’t really help in this case). Nothing at the query level.
I’d like to know if anyone has found a solution to prevent a bad query from consuming all the resources for the whole organization.