I have got plenty of audits:
foreman=# select count(*) from audits;
count
---------
3061306
(1 row)
but I suppose this is a problem:
[root@foreman8 ~]# foreman-rake audits:expire
The interval for keeping the Audits is not defined in the settings, exiting...
I have set it to 7 days now and that clear up a lot:
[root@foreman8 ~]# foreman-rake audits:expire
Deleting audits older than 2024-11-12 17:19:49 UTC. This might take a few minutes...
Successfully deleted 3034244 audits!
Still the query isn’t really fast:
2024-11-19 18:21:37 CET LOG: duration: 1843.762 ms execute <unnamed>: SELECT "audits".* FROM "audits" WHERE "audits"."auditable_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories" INNER JOIN "katello_root_repositories" ON "katello_repositories"."root_id" = "katello_root_repositories"."id" WHERE "katello_root_repositories"."product_id" = $1) AND "audits"."auditable_type" = $2 AND "audits"."action" = $3 ORDER BY "audits"."created_at" DESC LIMIT $4
2024-11-19 18:21:37 CET DETAIL: parameters: $1 = '116', $2 = 'Katello::Repository', $3 = 'sync', $4 = '1'
That is about the same amount of time as before for the second and later visits of the product page after the extremely slow first one. However, now, following refreshes of the page are faster and don’t log a long running query anymore. Thus, at the moment it seems to have helped.
That query does not return any rows anyway:
foreman=# SELECT "audits".* FROM "audits" WHERE "audits"."auditable_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories" INNER JOIN "katello_root_repositories" ON "katello_repositories"."root_id" = "katello_root_repositories"."id" WHERE "katello_root_repositories"."product_id" = 116 ) AND "audits"."auditable_type" = 'Katello::Repository' AND "audits"."action" = 'sync' ORDER BY "audits"."created_at" DESC limit 1;
id | auditable_id | auditable_type | user_id | user_type | username | action |
audited_changes | version | comment | associated_id | associated_type | request_
uuid | created_at | remote_address | auditable_name | associated_name
----+--------------+----------------+---------+-----------+----------+--------+-
----------------+---------+---------+---------------+-----------------+---------
-----+------------+----------------+----------------+-----------------
(0 rows)
Product 116 is our company internal product, containing some local repositories with custom build rpms which are uploaded to foreman. None of the repositories are synced from elsewhere which is, I guess, why there are not audit entries. The status of the product is always (correctly) “Never synced”. Thus, it probably has to scan the full table which takes a while because the closest index would be
"index_audits_on_auditable_type_and_auditable_id_and_version" btree (auditable_type, auditable_id, version)
which doesn’t help because of the lack of the version value.
