Original report: Dashboard page generates many slow SQL queries, when auto refresh is turned on it puts high load on Satellite Server and PostgreSQL database (queries: Started GET "/widgets/1168?search=xxxx).
Created redmine issue https://projects.theforeman.org/issues/25551 from this bug
Upstream bug assigned to lzap
Lzap, is there something in particular that we could optimize? Caching or not reloading the page feels as a workaround. Were you able to isolate the problem to some subset of widgets? Note that the issue is not reported to a generic dashboard view but when search is used. We had similar issues like that in past since we let Foreman search in freetext on too many host attributes.
Marek, so I had an attempt to enable caching for dashboard but during the review I realized it's actually not a good idea and it could make things worse. So I turned the issue to a different task - disable auto refreshing for inactive tabs. I talked o Avi and he says this should be possible, but let's have this as an upsteam feature only. However, Tomer went ahead and solved the issue using a more opiniated solution wihch I like - auto-refresh is now *disabled* by default. This is at least something. I vote for making this bug a dupe of the issue which is small enough to backport: https://bugzilla.redhat.com/show_bug.cgi?id=1650641 Since caching was not approved in upstream I do not really have ideas how to speed things up since the dashboard really does lot of queries and we want these to be fresh data. It's either caching or pregenerating data in advance, which is I think out of table for 6.5 so let's just close this one for now.
I did some testing and major reason for such slow queries is that scoped search by default tries to match all fields creating the huge tablescans with LIKE/ILIKE SQL statenemts: Query: XXX However a simple change in the query makes the query much faster: Query: name = XXX Unfortunately, there is no easy way of fixing this without some agreement. Created: https://community.theforeman.org/t/scoped-search-across-all-implicit-fields/12585
Also note that Tomer improved this vastly for 6.4, so performance should be better: https://projects.theforeman.org/issues/20667 QA: Please verify this for 6.5 and 6.4. This one is tough tho, we have improved performance of searching hosts in All hosts and in Dashboard. The details are in the issue/PR text - the best verification would be probably measuring time and also making sure those SQL queries contain less fields in the LIKE statements.
Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory, and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. https://access.redhat.com/errata/RHSA-2019:1222