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.
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:
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")
- Status changed from New to Ready For Testing
- Pull request https://github.com/Katello/katello/pull/10851 added
- Fixed in Releases Katello 4.12.0 added
- Status changed from Ready For Testing to Closed
- Target version set to Katello 4.12.0
- Triaged changed from No to Yes
Also available in: Atom
PDF
Updated by 
Updated by 
Updated by