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:
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.
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.
https://github.com/ManageIQ/manageiq/pull/16754
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.
New commit detected on ManageIQ/manageiq/master: https://github.com/ManageIQ/manageiq/commit/84ca04ce3c833cd94896ece5f6db662b4bb494ab commit 84ca04ce3c833cd94896ece5f6db662b4bb494ab Author: Nick Carboni <ncarboni> AuthorDate: Fri Jan 5 11:28:38 2018 -0500 Commit: Nick Carboni <ncarboni> 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
New commit detected on ManageIQ/manageiq/master: https://github.com/ManageIQ/manageiq/commit/ea5394d58e5a60efcbd2261481b94dbe14e26d57 commit ea5394d58e5a60efcbd2261481b94dbe14e26d57 Author: Nick Carboni <ncarboni> AuthorDate: Fri Jan 5 11:59:09 2018 -0500 Commit: Nick Carboni <ncarboni> 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(+)
Verified on 5.10.0.17.
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