Bug 1434918 - Orphaned Rows in vim_performance_states not being purged
Summary: Orphaned Rows in vim_performance_states not being purged
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat CloudForms Management Engine
Classification: Red Hat
Component: Appliance
Version: 5.7.0
Hardware: Unspecified
OS: Unspecified
medium
medium
Target Milestone: GA
: 5.10.0
Assignee: Nick Carboni
QA Contact: Tasos Papaioannou
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2017-03-22 15:15 UTC by Jared Deubel
Modified: 2019-02-07 23:02 UTC (History)
11 users (show)

Fixed In Version: 5.10.0.0
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2019-02-07 23:02:25 UTC
Category: ---
Cloudforms Team: ---
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Product Errata RHSA-2019:0212 None None None 2019-02-07 23:02:31 UTC

Description Jared Deubel 2017-03-22 15:15:24 UTC
Description of problem:
When using the script tools/purge_archived_vms.rb to delete 4333 VMs in one of our regions.

Because we had problems with the large number of rows in table vim_performance_states, I checked the the row count after we deleted the 4333 VMs. Instead of going down, it went up slightly. We had 73 million rows a few days ago, now we have 74.8 million.

What I found is that we have 10.9 million rows in the table where resource_type is VmOrTemplate, but where the resource_id does not match any id in the vms table. 

Queries I used to check this:

vmdb_production=# select count(*) from vim_performance_states where resource_type = 'VmOrTemplate' and resource_id not in (select id from vms);
  count
----------
 10885103
(1 row)

vmdb_production=# select count(*) from vim_performance_states where resource_type = 'VmOrTemplate' and resource_id in (select id from vms);
  count
----------
 52692047
(1 row)

Since this table doesn't get purged is it possible to come up with a script to remove vim_performance_state data for VMs that are in the archived state? 

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


How reproducible:

Comment 3 Nick Carboni 2018-01-03 22:55:35 UTC
If it's just VMs that you want to remove orphaned records, you can get that done in one (long) line in the rails console:

VimPerformanceState.joins("LEFT OUTER JOIN vms ON vim_performance_states.resource_id = vms.id").where(:vms => {:id => nil}, :resource_type => "VmOrTemplate").in_batches(:of => 10000).delete_all

This will likely take quite a while.
You can also increase that batch number if that seems to help.

I'm working on a more generic way to purge vim_performance_states for all the objects that they can point to. Ideally this will get rolled into our existing purging schedules.

Comment 4 Nick Carboni 2018-01-03 22:57:17 UTC
The script in comment 3 is also for vms that have been *deleted* not *archived*

Archived vms can still be reported on so I don't think we would want to remove the vim_performance_states for those.

Comment 6 Nick Carboni 2018-01-08 21:37:17 UTC
The change made in the PR in comment 5 will create a schedule (daily by default) to remove vim_performance_state records which point to a resource which no-longer exists.

Comment 7 CFME Bot 2018-01-18 17:32:00 UTC
New commit detected on ManageIQ/manageiq/master:
https://github.com/ManageIQ/manageiq/commit/84ca04ce3c833cd94896ece5f6db662b4bb494ab

commit 84ca04ce3c833cd94896ece5f6db662b4bb494ab
Author:     Nick Carboni <ncarboni@redhat.com>
AuthorDate: Fri Jan 5 11:28:38 2018 -0500
Commit:     Nick Carboni <ncarboni@redhat.com>
CommitDate: Mon Jan 15 13:26:21 2018 -0500

    Purge orphans from VimPerformanceState
    
    This uses the new "orhaned" purging mode to remove rows from
    vim_performance_states which have dangling pointers
    
    Fixes https://bugzilla.redhat.com/show_bug.cgi?id=1434918

 app/models/vim_performance_state.rb               |  2 ++
 app/models/vim_performance_state/purging.rb       | 16 ++++++++++
 config/settings.yml                               |  3 ++
 spec/models/vim_performance_state/purging_spec.rb | 37 +++++++++++++++++++++++
 4 files changed, 58 insertions(+)
 create mode 100644 app/models/vim_performance_state/purging.rb
 create mode 100644 spec/models/vim_performance_state/purging_spec.rb

Comment 8 CFME Bot 2018-01-18 17:32:10 UTC
New commit detected on ManageIQ/manageiq/master:
https://github.com/ManageIQ/manageiq/commit/ea5394d58e5a60efcbd2261481b94dbe14e26d57

commit ea5394d58e5a60efcbd2261481b94dbe14e26d57
Author:     Nick Carboni <ncarboni@redhat.com>
AuthorDate: Fri Jan 5 11:59:09 2018 -0500
Commit:     Nick Carboni <ncarboni@redhat.com>
CommitDate: Mon Jan 15 13:26:21 2018 -0500

    Add purge schedule for VimPerformanceState
    
    Fixes https://bugzilla.redhat.com/show_bug.cgi?id=1434918

 app/models/miq_schedule_worker/jobs.rb   | 4 ++++
 app/models/miq_schedule_worker/runner.rb | 5 +++++
 config/settings.yml                      | 1 +
 3 files changed, 10 insertions(+)

Comment 11 Tasos Papaioannou 2018-10-02 15:53:29 UTC
Verified on 5.10.0.17.

Comment 13 errata-xmlrpc 2019-02-07 23:02:25 UTC
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.

https://access.redhat.com/errata/RHSA-2019:0212


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