Project

General

Profile

Actions
Copy link

Bug #18845

closed

Avoid N+1 query in foreman hosts index by declaring proper scope

Added by Shimon Shtein over 7 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Justin Sherrill
Category:
Hosts
Target version:
Katello 3.4.1
Difficulty:
Triaged:
Bugzilla link:
Pull request:
https://github.com/Katello/katello/pull/6804
Fixed in Releases:
Found in Releases:
Red Hat JIRA:

Description

The following query is executed for each host during api hosts controller's index action:

SELECT COUNT(*) FROM "katello_errata" INNER JOIN "katello_content_facet_errata" ON "katello_errata"."id" = "katello_content_facet_errata"."erratum_id" WHERE "katello_content_facet_errata"."content_facet_id" = $1 AND "katello_errata"."errata_type" = 'security'

This is caused by the following stack:

/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/postgresql_adapter.rb:832:in `block in exec_cache'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activesupport-4.1.5/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/postgresql_adapter.rb:831:in `exec_cache'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/postgresql/database_statements.rb:138:in `exec_query'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/postgresql_adapter.rb:954:in `select'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `block in select_all'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/abstract/query_cache.rb:83:in `cache_sql'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/connection_adapters/abstract/query_cache.rb:68:in `select_all'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/relation/calculations.rb:262:in `execute_simple_calculation'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/relation/calculations.rb:224:in `perform_calculation'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/relation/calculations.rb:119:in `calculate'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/relation/calculations.rb:34:in `count'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/relation.rb:251:in `empty?'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activerecord-4.1.5/lib/active_record/relation.rb:261:in `any?'
/opt/theforeman/tfm/root/usr/share/gems/gems/katello-3.0.0.95/app/models/katello/errata_status.rb:52:in `to_status'
/opt/theforeman/tfm/root/usr/share/gems/gems/katello-3.0.0.95/app/models/katello/errata_status.rb:29:in `to_global'
/usr/share/foreman/app/models/host_status/global.rb:19:in `block in build'
/usr/share/foreman/app/models/host_status/global.rb:19:in `map'
/usr/share/foreman/app/models/host_status/global.rb:19:in `build'
/usr/share/foreman/app/models/host/managed.rb:883:in `global_status_label'
/usr/share/foreman/app/views/api/v2/hosts/main.json.rabl:6:in `eval_source'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:417:in `instance_eval'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:417:in `eval_source'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:35:in `apply'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/helpers.rb:136:in `object_to_engine'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/partials.rb:15:in `partial_as_engine'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/builder.rb:196:in `extends'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/builder.rb:114:in `block in compile_settings'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/builder.rb:113:in `each'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/builder.rb:113:in `compile_settings'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/builder.rb:33:in `engines'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/multi_builder.rb:45:in `block in map_engines_to_builders'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/multi_builder.rb:44:in `each'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/multi_builder.rb:44:in `map_engines_to_builders'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/multi_builder.rb:22:in `to_a'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:88:in `to_hash'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:101:in `to_dumpable'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:110:in `to_json'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:48:in `block in render'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:382:in `cache_results'
/opt/theforeman/tfm/root/usr/share/gems/gems/rabl-0.11.6/lib/rabl/engine.rb:47:in `render'
/usr/share/foreman/app/views/api/v2/hosts/index.json.rabl:3:in `_54bcd28489908e6a80cc10544a40c553'
/opt/rh/rh-ror41/root/usr/share/gems/gems/actionview-4.1.5/lib/action_view/template.rb:145:in `block in render'
/opt/rh/rh-ror41/root/usr/share/gems/gems/activesupport-4.1.5/lib/active_support/notifications.rb:161:in `instrument'
/opt/rh/rh-ror41/root/usr/share/gems/gems/actionview-4.1.5/lib/action_view/template.rb:339:in `instrument'
/opt/rh/rh-ror41/root/usr/share/gems/gems/actionview-4.1.5/lib/action_view/template.rb:143:in `render'

The problem is that Erratum.security is a scope, and not relation, hence can't be preloaded by using includes.

Related issues 1 (1 open — 0 closed)

Related to Foreman - Refactor #18975: Store info about relevancy of a host status in databaseNew03/22/2017Actions
Actions
Copy link
 #1

Updated by John Mitsch over 7 years ago

  • Category set to Hosts
  • Translation missing: en.field_release set to 228
Actions
Copy link
 #2

Updated by Justin Sherrill over 7 years ago

  • Target version set to 187
Actions
Copy link
 #3

Updated by Shimon Shtein over 7 years ago

  • Related to Refactor #18975: Store info about relevancy of a host status in database added
Actions
Copy link
 #4

Updated by Brad Buckingham over 7 years ago

  • Target version changed from 187 to 193
Actions
Copy link
 #5

Updated by Justin Sherrill over 7 years ago

  • Status changed from New to Assigned
  • Assignee set to Justin Sherrill
Actions
Copy link
 #6

Updated by The Foreman Bot over 7 years ago

  • Status changed from Assigned to Ready For Testing
  • Pull request https://github.com/Katello/katello/pull/6804 added
Actions
Copy link
 #7

Updated by Justin Sherrill over 7 years ago

  • Status changed from Ready For Testing to Closed
  • % Done changed from 0 to 100
Actions
Copy link

Also available in: Atom PDF