Project

General

Profile

Bug #18845

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

Added by Shimon Shtein over 5 years ago. Updated almost 4 years ago.

Status:
Closed
Priority:
Normal
Category:
Hosts
Target version:
Difficulty:
Triaged:
Bugzilla link:
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

Related to Foreman - Refactor #18975: Store info about relevancy of a host status in databaseNew2017-03-22

Associated revisions

Revision ba01ad63 (diff)
Added by Justin Sherrill about 5 years ago

Fixes #18845 - Reduce n+1 queries for hosts

this reduces the number of N+1 queries considerably
via three methods:

1) by fetching errata counts using a group queries
to fetch counts for all types in one query. Note this
also affects content view versions and repos as well.
2) By using the cached status when fetching the
errata status rather than re-computing it at read time
3) By not including :applicable_errata in the 'include'
optimization, as its not needed

This change takes the number of queries for 3 systems (2 with errata)
from 15 queries to 2, .58ms to .6ms.

Further improvements could be made to remove all queries, but this
would require caching the data on the host object (or facet), as well
as CVV and repos. I'm not quite ready to do that yet.

History

#1 Updated by John Mitsch about 5 years ago

  • Category set to Hosts
  • Legacy Backlogs Release (now unused) set to 228

#2 Updated by Justin Sherrill about 5 years ago

  • Target version set to 187

#3 Updated by Shimon Shtein about 5 years ago

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

#4 Updated by Brad Buckingham about 5 years ago

  • Target version changed from 187 to 193

#5 Updated by Justin Sherrill about 5 years ago

  • Status changed from New to Assigned
  • Assignee set to Justin Sherrill

#6 Updated by The Foreman Bot about 5 years ago

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

#7 Updated by Justin Sherrill about 5 years ago

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

Also available in: Atom PDF