Bug 861430

+++ 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 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 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 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