Bug 1567978 - slow query when updating content facet applicability counts
Summary: slow query when updating content facet applicability counts
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Hosts - Content
Version: 6.2.0
Hardware: Unspecified
OS: Unspecified
unspecified
high
Target Milestone: Unspecified
Assignee: Justin Sherrill
QA Contact: Harshad More
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2018-04-16 14:01 UTC by Justin Sherrill
Modified: 2019-08-12 14:03 UTC (History)
10 users (show)

Fixed In Version: tfm-rubygem-katello-3.4.5.69-1
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
: 1572588 (view as bug list)
Environment:
Last Closed: 2018-06-19 20:17:00 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Foreman Issue Tracker 23270 0 None None None 2018-04-16 14:01:09 UTC
Foreman Issue Tracker 23296 0 None None None 2018-04-17 14:10:54 UTC
Red Hat Product Errata RHBA-2018:1950 0 None None None 2018-06-19 20:17:33 UTC

Description Justin Sherrill 2018-04-16 14:01:08 UTC
When there are a lot of hosts and a lot of rpms, this query can be slow:

 SELECT COUNT(*) FROM "katello_rpms" INNER JOIN "katello_content_facet_applicable_rpms" ON "katello_rpms"."id" = "katello_content_facet_appl
icable_rpms"."rpm_id" WHERE "katello_content_facet_applicable_rpms"."content_facet_id" = $1  [["content_facet_id", 51438]]


it is generated by:

<pre>
      def update_applicability_counts
        self.update_attributes!(
            :installable_security_errata_count => self.installable_errata.security.count,
            :installable_bugfix_errata_count => self.installable_errata.bugfix.count,
            :installable_enhancement_errata_count => self.installable_errata.enhancement.count,
            :applicable_rpm_count => self.applicable_rpms.count,
            :upgradable_rpm_count => self.installable_rpms.count
        )
      end
</pre>

Comment 1 Justin Sherrill 2018-04-16 14:01:12 UTC
Created from redmine issue http://projects.theforeman.org/issues/23270

Comment 2 Justin Sherrill 2018-04-16 14:01:15 UTC
Upstream bug assigned to jsherril@redhat.com

Comment 4 pm-sat@redhat.com 2018-04-16 16:18:10 UTC
Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/23270 has been resolved.

Comment 6 Justin Sherrill 2018-04-27 15:35:26 UTC
To test this, just:

* sync some rhel repos
* attach some client that is not fully up to date (with katello-host-tools installed)
* Notice errata showing up for that clent
* yum update on the client
* errata should not show up any more

Comment 8 Mike McCune 2018-06-11 20:57:31 UTC
Registered 6000 hosts that had errata applicable.

Registered 1 more host that had ~400 errata applicable.

Ran yum update, installed ~500 RPMs and all errata. The UI showed 0 errata immediately after completion of the yum transaction on the client.

Moving to VERIFIED

Comment 10 errata-xmlrpc 2018-06-19 20:17:00 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/RHBA-2018:1950


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