Bug 856665 - Need to profile/identify expensive db queries and setup indices
Need to profile/identify expensive db queries and setup indices
Status: CLOSED CURRENTRELEASE
Product: Red Hat Satellite 6
Classification: Red Hat
Component: Infrastructure (Show other bugs)
6.0.1
Unspecified Unspecified
unspecified Severity medium (vote)
: Unspecified
: 6.0
Assigned To: Dmitri Dolguikh
sthirugn@redhat.com
: Triaged
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2012-09-12 10:32 EDT by Partha Aji
Modified: 2014-09-18 12:47 EDT (History)
6 users (show)

See Also:
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: ---


Attachments (Terms of Use)

  None (edit)
Description Partha Aji 2012-09-12 10:32:33 EDT
Description of problem:
Copied from 
https://github.com/Katello/katello/pull/615#issuecomment-8491564

Adding keys for *_id columns are quite obvious. But please spend time on /var/log/katello/production_sql.log with 
KATELLO_LOGGING_SQL=debug
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)

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

Adding index:
  environment_priors (prior_id, environment_id)

addressing:
  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.

Note You need to log in before you can comment on or make changes to this bug.