Project

General

Profile

Refactor #8798

Reports.expire uses an unnecessary join on logs

Added by Ori Rabin almost 8 years ago. Updated almost 8 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Difficulty:
Triaged:
No
Bugzilla link:
Pull request:
Fixed in Releases:
Found in Releases:
Red Hat JIRA:

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.


Related issues

Related to Foreman - Bug #8565: report::expire is running very slowlyClosed2014-12-03

History

#1 Updated by Dominic Cleal almost 8 years ago

  • Category changed from Reporting to Database

#2 Updated by Martin Jackson almost 8 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.

#3 Updated by Dominic Cleal almost 8 years ago

  • Related to Bug #8565: report::expire is running very slowly added

#4 Updated by Ohad Levy almost 8 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

Also available in: Atom PDF