Refactor #8798
open
Reports.expire uses an unnecessary join on logs
Description
When deleting the logs the line Log.joins(:report) doesn't need the reports table:
https://github.com/theforeman/foreman/blob/d50c79908831be65da0b0005e438c9e819db613a/app/models/report.rb#L122
In mysql the join uses the index and removing it causes the query to take longer.
This needs to be checked in other dbs and refactored to use the index without the join.
Updated by Dominic Cleal over 9 years ago
- Category changed from Reporting to Database
Updated by Martin Jackson over 9 years ago
Our experience is that the reports:expire rake task can get to where it can't finish with large numbers of reports to expire (we have 34,000 nodes and a two-hour runinterval); possibly because of this query. We were running PG 8.4; we upgraded to 9.2 and while the database itself is performing much better, we had to kill the reports:expire task after 2.5 hours because the server started swapping.
Updated by Dominic Cleal over 9 years ago
- Related to Bug #8565: report::expire is running very slowly added
Updated by Ohad Levy over 9 years ago
is this on 1.7? if you remove the join does it perform better? e.g.
--- a/app/models/report.rb +++ b/app/models/report.rb @@ -119,7 +119,7 @@ class Report < ActiveRecord::Base cond = "reports.created_at < \'#{(Time.now.utc - timerange).to_formatted_s(:db)}\'" cond += " and reports.status = #{status}" unless status.nil? - Log.joins(:report).where(:report_id => Report.where(cond)).delete_all + Log.where(:report_id => Report.where(cond)).delete_all Message.where("id not IN (#{Log.select(:message_id).to_sql})").delete_all Source.where("id not IN (#{Log.select(:source_id).to_sql})").delete_all count = Report.where(cond).delete_all