Bug 856665

Summary: Need to profile/identify expensive db queries and setup indices
Product: Red Hat Satellite 6 Reporter: Partha Aji <paji>
Component: InfrastructureAssignee: Dmitri Dolguikh <dmitri>
Status: CLOSED CURRENTRELEASE QA Contact: sthirugn <sthirugn>
Severity: medium Docs Contact:
Priority: unspecified    
Version: 6.0.1CC: bkearney, dmitri, ehelms, mmccune, msuchy, sthirugn
Target Milestone: UnspecifiedKeywords: Triaged
Target Release: 6.0   
Hardware: Unspecified   
OS: Unspecified   
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2014-09-11 08:20:44 EDT Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Description Partha Aji 2012-09-12 10:32:33 EDT
Description of problem:
Copied from 

Adding keys for *_id columns are quite obvious. But please spend time on /var/log/katello/production_sql.log with 
and with some data in db.

I investigated some random slow queries and find that katello can utilize indexes:

help_tips (key, user_id);
environments ( organization_id, library)
organizations (cp_key, task_id)
task_statuses (uuid, type)
verb (verb)
environment_priors (prior_id, environment_id)
And for example index
environment_priors (prior_id, environment_id)
can be used instead of 
environment_priors (prior_id)
so such simple index is not really needed.

Also it would be very usefull to add each index in separate commit, with explanation which SQL query it is addressing so we can see, it is actually doing something. For example:

Adding index:
  verb (verb)

SELECT "verbs".* FROM "verbs" WHERE "verbs"."verb" = 'register_systems' LIMIT 1

Adding index:
  environment_priors (prior_id, environment_id)

  SELECT "environments".* FROM "environ
Comment 1 Mike McCune 2013-08-16 13:54:55 EDT
getting rid of 6.0.0 version since that doesn't exist
Comment 2 Bryan Kearney 2013-09-23 10:31:55 EDT
commit 7df07dcd15d0a30ff77a0be820a5e26b7708a5b0
Author: Partha Aji <paji@redhat.com>
Date:   Mon Aug 20 18:30:59 2012 -0400

    Initial commit on updated indexing appropriate stuff
Comment 5 sthirugn@redhat.com 2014-09-04 17:04:52 EDT
Marking this Verified since this is a developer task. Please open new bugs if necessary.

Version Tested:
GA Snap 7 - Satellite-6.0.4-RHEL-6-20140829.0
Comment 6 Bryan Kearney 2014-09-11 08:20:44 EDT
This was delivered with Satellite 6.0 which was released on 10 September 2014.