Description of problem:

If the Satellite is managing hundreds or thousands of hosts and they all uploaded their config reports to the Satellite frequently.  This will build up massive number of config reports in the Satellite database in the short period of time.

For example:

> Report.all.size
=> 151102

Having large number of config report types in the Satellite database, somehow causes high memory consumption and slow page rendering when rendering "Arf report" index page and order the list by host, policy or capsule.

In production.log we see:

Started GET "/compliance/arf_reports?locale=en_GB&order=host+ASC&page=4&per_page=50" for xx.xx.xx.xx at xxxxxxxxxxxxx
 Processing by ArfReportsController#index as HTML
   Parameters: {"locale"=>"en_GB", "order"=>"host ASC", "page"=>"4", "per_page"=>"50"}
   Rendered /usr/share/gems/gems/foreman_openscap-7.1.1/app/views/arf_reports/index.html.erb within layouts/application (Duration: 46416.7ms | Allocations: 10147246)  <=========================
   Rendered layouts/base.html.erb (Duration: 9.3ms | Allocations: 19700)
   Rendered layout layouts/application.html.erb (Duration: 46431.7ms | Allocations: 10180179)
 Completed 200 OK in 46550ms (Views: 32064.4ms | ActiveRecord: 14415.3ms | Allocations: 10350816) <=======================

It is caused by the bad sql query below:

#/usr/share/gems/gems/foreman_openscap-7.1.1/app/controllers/arf_reports_controller.rb

  def index
    @arf_reports = resource_base.includes(:policy, :openscap_proxy, :host => %i[policies last_report_object host_statuses])
                                .search_for(params[:search], :order => params[:order])
                                .paginate(:page => params[:page], :per_page => params[:per_page])
  end 

The "includes()" of both last_report_object" and "host_statuses" together appear to cause some overheads in Rails when doing SQL "order by".

I am not exactly sure the reason behind. My guess is it is causing Rails to load many objects for the config reports.

Based on my understanding below, "last_report_object" is simply not needed for Arf report query because it is simply irrelevant.

#/usr/share/foreman/app/models/host/managed.rb
has_one :last_report_object, -> { order("#{Report.table_name}.id DESC") }, :foreign_key => :host_id, :class_name => 'ConfigReport'

I think the reason that we need "last_report_object" here is to calculate and display the host global status icon and the tooltips. I am wondering whether this icon is needed in the arf report page or not.

Proposing solutions:

Remove "last_report_object" from includes() fixed the issue. The tradeoff is multiple sql queries could be called to retrieve the last_report_object for each report but I think the performance impact might not be significant since pagination is used.

Workaround without code change:

To mitigate this issue, we can clear the reports older than 3 days or maybe less.

NOTE: Satellite has a cron job to clear old report older than 7 days, so apparently many reports could still be generated in 7 days in an busy environment.

/usr/sbin/foreman-rake reports:expire days=3 

However,  I guess keeping only 3 days old or less reports might not be a reasonable workaround for some customers.