Problem:
High cpu load on postgres after update. ~15 load avg (host only has 6 cores)
I’ve narrowed it down to being caused by dynflow-sidekiq@worker-1, not sure how to narrow it down further and fix it
Expected outcome:
Postgresql load not using 100x more cpu than before upgrade
Foreman and Proxy versions:
Katello 4.9
Foreman 3.7
all latest updates installed
Foreman and Proxy plugin versions:
Distribution and version:
Rocky 8
Other relevant data:
This appears to be the offending query:
SELECT “katello_rpms”.* FROM “katello_rpms” WHERE “katello_rpms”.“id” NOT IN (SELECT “katello_repository_rpms”.“rpm_id” FROM “katello_repository_rpms” WHERE “katello_repository_rpms”.“repository_id” IN (SELECT “katello_repositories”.“id” FROM “katello_repositories”))
as soon as dynflow worker goes online about 20 of those pop up on the postgres. Running it manually also hangs
su - postgres -c ‘psql -d foreman -c “SELECT "katello_rpms".* FROM "katello_rpms" WHERE "katello_rpms"."id" NOT IN (SELECT "katello_repository_rpms"."rpm_id" FROM "katello_repository_rpms" WHERE "katello_repository_rpms"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"))” > /tmp/pg.out’
