Created attachment 980647 [details] full production log with rails debug trace active for the last UI sort attempt Description of problem:Customer has a regional database with about 3200 datastore instances which he attempts to sort in order by freespace. The UI never returns. Version-Release number of selected component (if applicable): 5.2.4.2 How reproducible: Steps to Reproduce: 1. 2. 3. Actual results: Expected results: Additional info: the UI worker consumes a great amount of virtual memory (> 2 GB)and jumps up to 99% cpu utilization (based on top data) for several minutes before apache decides that the server is not going to respond. Testing with other customer's provided VMDB's confirms that when hundreds of datastore instances are in the VMDB a UI request to order by free space can take a very long time. The bug being reported is that the request for a list of datastores sorted in some order supported by the UI worker never completes. Customer has generated a failing example with rails debug trace active. logs from this will be attached to this case.
Created attachment 980648 [details] gz of full evm.log from the appliance
Created attachment 980649 [details] extract of production log lines assocated with UI worker pid 11645
Created attachment 980650 [details] evm.log expract of log lines associated with pid 11645
Created attachment 980674 [details] ssl_access log from appliance
Created attachment 980675 [details] ssl_error log from /var/www/miq/vmdb/log/apache directory
Removing blocker flag for 5.4 as we believe there is a work around with going through generate reports (or atleast hoping so). In the mean time, it stays as a high priority issue to chase through bug fix days. Tom, is that something you can confirm with the customer?
Alex, is this something you can reproduce (again) for dev?
Dave, I turned on the original appliance I have that reproduces the problem. I can update that appliance with new code or export its database to other appliances if needed to reproduce on different versions.
Does look like it could be optimized if the count were performed in sql. explain analyze select storages.*, ( select count(*) from vms where storage_id = storages.id and ((template = true and ems_id is not null) or host_id is not null) ) as unmanaged_vm_counts from storages order by unmanaged_vm_counts Probably would like to add indexes to the vms and file_storages table to more easily run those sub queries. create_index :vms, [:storage_id, :ems_id, :template, :host_id] Most seem to have the same conditions on those, so adding a where clause to the index would greatly reduce the index size and speed up queries.
The undlerlying issue is the database cannot (at least is not presently) able to perform the filter for us and simply returns all the objects which leaves Ruby with a much larger (everything) set to process. Currently the MIQ_Report code passes / copies the set of objects multiple times during processing which increases memory and cpu utilization. Testing indicates the current (5.5) code filters ~1,000 datastores per second on an essentailly idle appliance with no memory constraint, thus page rendering time is approximately (# of objects / 1,000) per second best case. The following filters are not performed within the database, thus can be expected to experience slowness at scale: - % Free Space - Total Provisioned Space - Total Hosts - Managed/Registered VMs - Managed/Unregistered Vms - Unmanaged VMs
https://github.com/ManageIQ/manageiq/pull/6368
https://github.com/ManageIQ/manageiq/pull/7813
New commit detected on ManageIQ/manageiq/master: https://github.com/ManageIQ/manageiq/commit/9278071094c0bd1ca580f1dfdcf4623ff50c9214 commit 9278071094c0bd1ca580f1dfdcf4623ff50c9214 Author: Keenan Brock <kbrock> AuthorDate: Mon Apr 18 14:55:41 2016 -0400 Commit: Keenan Brock <kbrock> CommitDate: Tue Apr 19 14:52:32 2016 -0400 implement v_pct_free_disk_space in hardware https://bugzilla.redhat.com/show_bug.cgi?id=1182777 app/models/hardware.rb | 23 ++++++++++++++ app/models/vm_or_template.rb | 20 ++---------- spec/models/hardware_spec.rb | 62 ++++++++++++++++++++++++++++++++++++++ spec/models/vm_or_template_spec.rb | 10 ++++++ spec/support/arel_spec_helper.rb | 11 +++++++ 5 files changed, 109 insertions(+), 17 deletions(-)
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/RHBA-2016:1348