Project

General

Profile

Actions

Bug #37058

closed

Cleanup orphans task generates inefficient queries consuming resources and taking long time to run

Added by Joniel Pasqualetto 11 months ago. Updated 11 months ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
-
Target version:
Difficulty:
Triaged:
Yes
Fixed in Releases:
Found in Releases:

Description

Description of problem:

katello:delete_orphaned_content triggers queries on the DB to identify what are the orphan units to be removed. However, such query is not efficient on systems where table katello_repository_rpms is big.

Query is slow, consumes a lot of cpu and the task may take long time to run.

Version-Release number of selected component (if applicable):

How reproducible:

Always, on big databases.

Steps to Reproduce:

Conditions to reproduce it simply require big tables katello_repository_rpms and katello_rpms

Actual results:

Task works, but may take long time.

Expected results:

Faster execution.

Additional info:

Actions #1

Updated by Joniel Pasqualetto 11 months ago

Examples showing the query and long it took to run:

postgresql-Fri.log:2024-01-12 10:41:24 EST LOG: duration: 22159297.683 ms execute <unnamed>: SELECT "katello_rpms".* FROM "katello_rpms" WHERE "katello_rpms"."id" NOT IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms")
postgresql-Mon.log:2024-01-15 05:07:14 EST LOG: duration: 25619032.228 ms execute <unnamed>: SELECT "katello_rpms".* FROM "katello_rpms" WHERE "katello_rpms"."id" NOT IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms")
postgresql-Mon.log:2024-01-15 13:04:45 EST LOG: duration: 30758054.922 ms execute <unnamed>: SELECT "katello_rpms".* FROM "katello_rpms" WHERE "katello_rpms"."id" NOT IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms")
postgresql-Sat.log:2024-01-13 11:32:09 EST LOG: duration: 25201996.464 ms execute <unnamed>: SELECT "katello_rpms".* FROM "katello_rpms" WHERE "katello_rpms"."id" NOT IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms")
postgresql-Tue.log:2024-01-16 10:53:36 EST LOG: duration: 22865189.741 ms execute <unnamed>: SELECT "katello_rpms".* FROM "katello_rpms" WHERE "katello_rpms"."id" NOT IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms")

Actions #2

Updated by The Foreman Bot 11 months ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/Katello/katello/pull/10851 added
Actions #3

Updated by The Foreman Bot 11 months ago

  • Fixed in Releases Katello 4.12.0 added
Actions #4

Updated by Joniel Pasqualetto 11 months ago

  • Status changed from Ready For Testing to Closed
Actions #5

Updated by Partha Aji 11 months ago

  • Target version set to Katello 4.12.0
  • Triaged changed from No to Yes
Actions

Also available in: Atom PDF