Problem:
Postgres is running at 100% CPU (command: postmaster) after updating. Analyzing a bit deeper shows commits running for 4h+, same with SQL statements.
Tried restarting the services, the server, to no avail.
There are no running tasks in Forman.
Expected outcome:
CPU should not be hogged.
Foreman and Proxy versions:
F 3.7.0
K 4.9.1
Foreman and Proxy plugin versions:
Distribution and version:
RockyLinux 8.8
Other relevant data:
Output from psql:
postgres=# SELECT pid
, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes';
pid | duration | query | state
-------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
27977 | 04:21:02.731169 | SHOW max_identifier_length | idle
27280 | 04:27:58.368644 | SET SEARCH_PATH TO public, "$user","public" | idle in transaction
35779 | 00:02:06.334108 | COMMIT | idle
27397 | 00:17:32.267334 | COMMIT | idle
28032 | 04:21:00.312294 | LISTEN "world:f03793db-f814-4d1e-8cba-51f70b348230" | idle
28030 | 04:21:00.231867 | LISTEN "world:46351195-dd65-4098-8537-7b69260186bd" | idle
28042 | 04:21:00.255052 | LISTEN "world:3684daf7-02f0-4853-8dbd-26291f4ce1b2" | idle
28046 | 04:21:00.231453 | LISTEN "world:611155dd-b19a-409f-b2b6-60e857893802" | idle
28044 | 04:21:00.0342 | COMMIT | idle
27938 | 04:21:12.616127 | SELECT a.attname +| idle
| | FROM ( +|
| | SELECT indrelid, indkey, generate_subscripts(indkey, 1) idx +|
| | FROM pg_index +|
| | WHERE indrelid = '"foreman_internals"'::regclass +|
| | AND indisprimary +|
| | ) i +|
| | JOIN pg_attribute a +|
| | ON a.attrelid = i.indrelid +|
| | AND a.attnum = i.indkey[i.idx] +|
| | ORDER BY i.idx +|
| | |
27959 | 04:21:07.100952 | LISTEN "world:a8c40e28-3d21-403c-ba3e-7b1147a31dbb" | idle
27967 | 04:21:05.755084 | LISTEN "world:c600e696-ca73-48e1-97b6-8c863aa88141" | idle
28051 | 04:20:59.979337 | COMMIT | idle
28053 | 04:20:59.997073 | COMMIT | idle
28060 | 04:20:59.782868 | COMMIT | idle
27396 | 04:27:14.224919 | SHOW TRANSACTION ISOLATION LEVEL | idle
28073 | 04:20:59.916761 | LISTEN "world:3b9b5b06-baf6-4d70-885a-0331d03f4329" | idle
28092 | 04:20:59.625279 | COMMIT | idle
28086 | 04:20:59.724265 | LISTEN "world:61701e46-4c19-4286-91a1-2823d243e35a" | idle
28094 | 04:20:59.522137 | COMMIT | idle
28160 | 04:19:13.749531 | LISTEN "world:9a0d4579-b32f-4af9-91cb-07b8781386e9" | idle
28171 | 00:02:19.416345 | COMMIT | idle
35796 | 00:02:04.677418 | COMMIT | idle
28182 | 04:19:10.457003 | UPDATE "katello_events" SET "in_progress" = $1 WHERE "katello_events"."in_progress" = $2 | idle
25271 | 04:37:26.441562 | 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")) | active
25272 | 04:38:04.202929 | 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")) | active
25273 | 04:37:51.036916 | 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")) | active
25274 | 04:37:50.386298 | 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")) | active
25275 | 04:38:08.6714 | 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")) | active
25303 | 04:38:04.202929 | 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")) | active
25315 | 04:37:51.036916 | 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")) | active
25299 | 04:38:08.6714 | 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")) | active
25298 | 04:38:08.6714 | 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")) | active
25304 | 04:38:04.202929 | 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")) | active
25314 | 04:37:51.036916 | 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")) | active
25316 | 04:37:50.386298 | 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")) | active
35795 | 00:02:06.129208 | COMMIT | idle
(37 rows)