Bug 1650662 - Dashboard with search query generates multiple slow queries
Summary: Dashboard with search query generates multiple slow queries
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite 6
Classification: Red Hat
Component: Dashboard
Version: 6.3.2
Hardware: All
OS: Linux
high
high vote
Target Milestone: 6.5.0
Assignee: Lukas Zapletal
QA Contact: Jan Hutař
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2018-11-16 18:50 UTC by Karl Abbott
Modified: 2019-11-05 22:38 UTC (History)
5 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2019-05-14 12:38:54 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Product Errata RHSA-2019:1222 None None None 2019-05-14 12:39:02 UTC
Foreman Issue Tracker 20667 None None None 2019-01-17 10:07:21 UTC

Comment 3 Lukas Zapletal 2018-11-26 13:04:06 UTC
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).

Comment 4 Lukas Zapletal 2018-11-26 13:15:36 UTC
Created redmine issue https://projects.theforeman.org/issues/25551 from this bug

Comment 5 pm-sat@redhat.com 2018-12-06 21:12:11 UTC
Upstream bug assigned to lzap@redhat.com

Comment 6 pm-sat@redhat.com 2018-12-06 21:12:14 UTC
Upstream bug assigned to lzap@redhat.com

Comment 7 Marek Hulan 2019-01-14 16:11:10 UTC
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.

Comment 8 Lukas Zapletal 2019-01-16 09:41:37 UTC
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.

Comment 11 Lukas Zapletal 2019-01-17 09:58:31 UTC
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

Comment 12 Lukas Zapletal 2019-01-17 10:07:21 UTC
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.

Comment 15 errata-xmlrpc 2019-05-14 12:38:54 UTC
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


Note You need to log in before you can comment on or make changes to this bug.