Bug #23096

Indexing subscription facet pools generates sql query per consumer

Added by Partha Aji 19 days ago. Updated 17 days ago.

Status:Closed
Priority:Normal
Assigned To:Partha Aji
Category:Subscriptions
Target version:Team Brad - Iteration 27
Difficulty: Pull request:https://github.com/Katello/katello/pull/7280
Bugzilla link:1563002
Story points-
Velocity based estimate-
ReleaseKatello 3.7.0Release relationshipAuto

Description

When a system registers/unregisters the import_hosts method in https://github.com/Katello/katello/blob/master/app/models/katello/glue/candlepin/pool.rb#L146-L155 does the following
  1. get consumer ids belonging to this pool from candlepin (around 15s)
  2. loop through the consumer ids and create an entry in SubscriptionFacetPool if it does not exist
  3. delete bad entries in SubscriptionFacetPool (i.e. delete where facet ids no longer exists)

step 2 generates a query for every consumer id in the pool to verify it exists. In some high volume customer cases that could mean 50K sql queries (all doing a where clause to verify the existence of a subscription facet pool) on every registration. This could also result in edge cases and pain points related errors like

PG::Error: ERROR:  insert or update on table \"katello_subscription_facet_pools\" violates foreign key constraint \"katello_sub_facet_pools_sf_id_fk\"\nDETAIL:  Key (subscription_facet_id)=(1000) is not present in table \"katello_subscription_facets\".\n: INSERT INTO \"katello_subscription_facet_pools\" (\"pool_id\", \"subscription_facet_id\") VALUES ($1, $2) RETURNING \"id\" 

where the slow response causes the registering client to time out and retry with --force causing race conditions.

Associated revisions

Revision 3298bb1a
Added by Partha Aji 18 days ago

Fixes #23096 - Improve SubFacetPool indexing perf

This commit intends to improve the performance of the import hosts
method used while indexing Pools. It reduces the number of queries made
on SubscriptionFacetPool to determine what needs to be added or removed
from it. In some high volume consumers this could mean reductions from
50K sql queries to 4 (depending on the number of consumers in a pool).

History

#1 Updated by Partha Aji 19 days ago

  • Bugzilla link set to 1563002

#2 Updated by The Foreman Bot 19 days ago

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

#3 Updated by Partha Aji 18 days ago

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

#4 Updated by John Mitsch 17 days ago

  • Release set to Katello 3.7.0

Also available in: Atom PDF