Bug 861430 - 3.0.z - host_interface_samples table is not cleaned properly - log entries over 1000 lines and 30 days old stay in the db
3.0.z - host_interface_samples table is not cleaned properly - log entries ov...
Status: CLOSED ERRATA
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine-dwh (Show other bugs)
3.0.7
Unspecified Unspecified
urgent Severity high
: ---
: 3.0.8
Assigned To: Yaniv Lavi
David Botzer
infra
: ZStream
Depends On: 856152
Blocks:
  Show dependency treegraph
 
Reported: 2012-09-28 11:23 EDT by Idith Tal-Kohen
Modified: 2016-02-10 14:18 EST (History)
15 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Once records in the history tables reach a certain (configurable) age they are removed. This cleanup activity was previously not being performed for the "host_interface_samples_history" table. This resulted in the table growing in size over time and taking up excessive amounts of disk space. The ETL process has been updated and now removes old records from the table correctly.
Story Points: ---
Clone Of: 856152
Environment:
Last Closed: 2012-11-27 14:57:38 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: Infra
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Idith Tal-Kohen 2012-09-28 11:23:25 EDT
+++ This bug was initially created as a clone of Bug #856152 +++

Description of problem:
Customers contacted us about an issue where it turned out that host_interface_samples_history table occupies more than 11 GB of disk space.

This table is not cleaned up properly as supposed.
(by default 1000 lines and 30 days)

Version-Release number of selected component (if applicable):
ic157 - rhevm 3.0.7

How reproducible:
100 %

Steps to Reproduce:
1. Have a rhev-m environment (running more than 30 days) - can be simulated

Actual results:
DB excesively grows up

Expected results:
tables should be properly cleaned up

Additional info:
Yaniv Dary already proposed workaround script (thanks Yaniv):

Create or replace FUNCTION delete_host_interface_samples() returns void
AS $procedure$
BEGIN
    DELETE FROM host_interface_samples_history WHERE history_id in (SELECT history_id
                                                                    FROM host_interface_samples_history
                                                                    WHERE history_datetime < CURRENT_TIMESTAMP - interval '3 days'
                                                                    LIMIT 100000);
COMMIT; $procedure$
LANGUAGE plpgsql;

Create or replace FUNCTION run_delete_host_interface_samples() returns void
AS $procedure$
BEGIN
LOOP
    IF (SELECT history_id
        FROM host_interface_samples_history
        WHERE history_datetime < CURRENT_TIMESTAMP - interval '3 days'
        LIMIT 1) IS NULL THEN EXIT;  -- exit loop
    END IF;
    PERFORM delete_host_interface_samples();
    PERFORM pg_sleep(5);
END LOOP;
END; $procedure$
LANGUAGE plpgsql;

SELECT run_delete_host_interface_samples();
VACUUM VERBOSE ANALYZE;

--- Additional comment from mkalinin@redhat.com on 2012-09-13 13:10:03 EDT ---

This bug is fixed on 3.1 on BZ#783787.
I would like to request this for 3.0.z.

Current workaround provided to the customer:
Run the script from comment 1 as a cron job on a weekly basis.

--- Additional comment from dbotzer@redhat.com on 2012-09-25 04:58:10 EDT ---

On what build should I verify the BZ ?
Who in Pre-integration tested this ?

BZ was found on ic157 - rhevm 3.0.7
we dont have anything later than ic157...

--- Additional comment from dbotzer@redhat.com on 2012-09-25 05:14:59 EDT ---

Fixed 3.1/si18.1
v3_1_host_interface_samples_history_view shows correct age (1 days, I configure 24H)
whereas other tables shows more than 5 days of data
Fixed 3.1/si18.1
Comment 3 David Botzer 2012-10-28 09:18:44 EDT
Fixed, 3.1/si22.1
v3_1_host_samples_history_view is cleaned
Fixed, 3.1/si22.1
Comment 4 David Botzer 2012-10-28 09:21:08 EDT
will test it on ic158.1
Comment 5 David Botzer 2012-11-08 03:24:08 EST
Fixed, 3.1/ic158.1
v3_1_host_samples_history_view is cleaned
Shows as configured in dwh.conf 48H
Fixed, 3.1/ic158.1
Comment 7 errata-xmlrpc 2012-11-27 14:57:38 EST
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

http://rhn.redhat.com/errata/RHBA-2012-1497.html

Note You need to log in before you can comment on or make changes to this bug.