After some testing I found that that following two patches: https://github.com/ManageIQ/manageiq/pull/17141 https://github.com/ManageIQ/manageiq/pull/17429 Did not seem to help in this case. I have put together an additional two patches: https://github.com/ManageIQ/manageiq/pull/17469 https://github.com/ManageIQ/manageiq-ui-classic/pull/3984 That should prevent the issue being solved here. Of note, all of the above patches will need to be in place for this to be resolved, and only 17141 has been backported at this time.
I have put together an alternative set of patches that also fix the issue independently of the first couple: https://github.com/ManageIQ/manageiq/pull/17473 https://github.com/ManageIQ/manageiq/pull/17474 https://github.com/ManageIQ/manageiq/pull/17475
I forgot that another patch was really necessary, and that this one could work on it's own to a degree to solve this issue as well: https://github.com/ManageIQ/manageiq-ui-classic/pull/3989 This, without the above, would (re-)introduce an N+1, but that would be far better than the memory situation that we are currently dealing with. With the above 3 from PRs from comment 9, the N+1 is completely removed as well where ever `last_compliance_status` or `last_compliance_timezone` is needed.
The final patch that we will be going forward with to fix the bug is https://github.com/ManageIQ/manageiq-ui-classic/pull/3989, and that will be merged into master shortly. Reason being is that fixes the issue on it's own, with limited impact to the rest of the system and only a minor compromise in database performance.
Fix merged.
To replicate this bug, you can run the following script to populate a fresh database with data to replicate the issue: https://gist.github.com/NickLaMuro/225833358423723ed17ff294415fa6b4 To run script, you can do the following in the project dir on an appliance: $ bin/rails r bz_1580569_db_replication_script.rb * * * To replicate the issue, the easiest way is through the console: $ bin/rails c irb> MiqReport.load_from_view_options(Host, User.where(:userid => 'admin').first).paged_view_search Without the patch, this will cause the process to consume roughly 10+ Gigs of RAM do to a poorly optimized database query. The patch (mentioned above) prevents this.
Verified in 5.10.0.0. With provided script I was able to view 250 VmWare hosts without errors and view was loaded fast. Also this script was executed was with no issues: MiqReport.load_from_view_options(Host, User.where(:userid => 'admin').first).paged_view_search