Bug #18376: showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory when on scale - Katello - Foreman

Actions

Copy link

Bug #18376

closed

showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory when on scale

Added by Thomas McKay almost 8 years ago. Updated over 6 years ago.

Status:

Closed

Priority:

High

Assignee:

Partha Aji

Category:

Errata Management

Target version:

Katello 3.3.1

Difficulty:

Triaged:

Bugzilla link:

1417642

Pull request:

https://github.com/Katello/katello/pull/6587

Fixed in Releases:

Found in Releases:

Red Hat JIRA:

Description

Cloned from https://bugzilla.redhat.com/show_bug.cgi?id=1417642

Description of problem:
With 5 errata and 6138 clients registered, showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory

Version-Release number of selected component (if applicable):
satellite-6.2.6-2.0.el7sat.noarch

How reproducible:
always

Steps to Reproduce:
1. Have 6138 clients registered, each with 5 installable erratas
(same 5 in our case)
2. Go to Content -> Errata

Actual results:
Takes more than 8 minutes to load the page, Ruby RSS memory goes from 4 to 10 GB

Expected results:
Should be faster

Additional info:

Related issues 1 (0 open — 1 closed)

Actions

Copy link

Updated by Thomas McKay almost 8 years ago

Subject changed from showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory when on scale to showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory when on scale
Target version set to 161

Actions

Copy link

Updated by Partha Aji almost 8 years ago

Assignee changed from Walden Raines to Partha Aji

Actions

Copy link

Updated by Partha Aji almost 8 years ago

I noticed duplicated inner join that is causing the craziness..

SELECT DISTINCT COUNT
FROM "katello_errata"
INNER JOIN "katello_repository_errata" ON "katello_repository_errata"."erratum_id" = "katello_errata"."id"
INNER JOIN "katello_content_facet_errata" ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id"
INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id"
INNER JOIN "katello_content_facet_errata" "content_facet_errata_katello_errata" ON "content_facet_errata_katello_errata"."erratum_id" = "katello_errata"."id"
..........

Notice the double inner join

INNER JOIN "katello_content_facet_errata" ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id"

Followed by

INNER JOIN "katello_content_facet_errata" "content_facet_errata_katello_errata" ON "content_facet_errata_katello_errata"."erratum_id" = "katello_errata"."id"
.

Removing that one redundant change made query 500 times faster (43633 -> 84 ms) in my test db with 400 hosts. So the fix is probably along those lines

The Content => Errata => (applicable + installed) runs the equivalent of
::Katello::Erratum.installable_for_hosts(h).applicable_to_hosts(h) which generates that double join.

Should have a fix on this hopefully soon.

Actions

Copy link