Bug #37058: Cleanup orphans task generates inefficient queries consuming resources and taking long time to run - Katello - Foreman

Actions

Copy link

Bug #37058

closed

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

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

Status:

Closed

Priority:

Normal

Assignee:

Category:

Target version:

Katello 4.12.0

Difficulty:

Triaged:

Yes

Bugzilla link:

2258876

Pull request:

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

Fixed in Releases:

Katello 4.12.0

Found in Releases:

Red Hat JIRA:

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

Copy link

Updated by Joniel Pasqualetto 10 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

Copy link