Eli Mesika has submitted this change and it was merged. Change subject: core: cleanup old event notification history ...................................................................... core: cleanup old event notification history Description of the problem: Customer is recording each login/logout from the system and sends notifications on those events, in the database there are more than 0.5 Million of records for both audit_log and event_notification_hist tables. engine_357=# select count(*) from public.event_notification_hist; count -------- 540260 (1 row) engine_357=# select count(*) from public.audit_log; count -------- 540250 (1 row) The validatedb.sh script validates that there is no data that violates Foreign Keys. As part of this validation, the following query is generated and executed : select (audit_log_id) from public.event_notification_hist where (audit_log_id) IS NOT NULL and (audit_log_id) not in ( select audit_log_id from public.audit_log); This is a very heavy query considering table sizes above and I can understand why it expands from 1.5 hours to 7 hours, simply because both tables keep on growing One reason for this slow query is luck of suitable index on event_notification_hist Engine has a scheduler job that cleans records older than 30 days. By checking why the audit_log and event_notification_history have records older than 30 days I found that the cleanup mechanism skips records that their audit_log_id exists in event_notification_history. This caused the audit_log to include records older than 30 days and event_notification_history not to be cleaned ever, making the upgrade heavier from version to version as records grows. This patch 1) Adds the missing index on event_notification_history so the FK validation will use index and not do a table scan on such large tables. 2) Modify the cleanup mechanism to delete all old records, since event_notification_history has a FK with DELETE CASCADE on audit_log, it will be cleaned up as well. Patch was validated on the customer problematic DB and solved the problem. Change-Id: Ib93c1a37b0838d9020ca4f8a300346b3fe3b87f0 Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=1323826 Signed-off-by: emesika <emesika@redhat.com> --- M packaging/dbscripts/audit_log_sp.sql A packaging/dbscripts/upgrade/04_00_0420_add_index_event_notification_hist.sql 2 files changed, 5 insertions(+), 9 deletions(-) Approvals: Martin Peřina: Looks good to me, approved Eli Mesika: Verified; Passed CI tests -- To view, visit https://gerrit.ovirt.org/55724 To unsubscribe, visit https://gerrit.ovirt.org/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ib93c1a37b0838d9020ca4f8a300346b3fe3b87f0 Gerrit-PatchSet: 4 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Eli Mesika <emesika@redhat.com> Gerrit-Reviewer: Eli Mesika <emesika@redhat.com> Gerrit-Reviewer: Jenkins CI Gerrit-Reviewer: Martin Peřina <mperina@redhat.com> Gerrit-Reviewer: Moti Asayag <masayag@redhat.com> Gerrit-Reviewer: Oved Ourfali <oourfali@redhat.com> Gerrit-Reviewer: gerrit-hooks <automation@ovirt.org>