Bug 603240

Summary: Satellite slow to bring up list of available channels - "select count(*) from rhnavailablechannels;" takes ~15 seconds
Product: Red Hat Satellite 5 Reporter: Xixi <xdmoon>
Component: ServerAssignee: Justin Sherrill <jsherril>
Status: CLOSED CURRENTRELEASE QA Contact: Jan Hutař <jhutar>
Severity: medium Docs Contact:
Priority: medium    
Version: 530CC: cperry, dejohnso, jhutar, xdmoon
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2010-10-28 14:52:34 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---
Bug Depends On:    
Bug Blocks: 487678    
Attachments:
Description Flags
Patch for this bug none

Description Xixi 2010-06-12 01:00:56 UTC
Description of problem:
Customer is running into a problem when bringing up certain parts of the UI.  One example is the Channels section.  Based on a couple of queries on the db he's seeing at least one view that is responding very slowly:
select count(*) from rhnavailablechannels;

This returns a value of 425 but takes 10-15 seconds to complete.  He tried to run this on a separate (non-production) db that has near duplicate data and it at least seems to return much quicker, about 1.5 seconds. 

Another example would be: try running through uploading a package and going through the errata publishing process.  Each step takes a big of time causing errata publishing to take quite a while.
create errata -> attach packages -> publish -> select channels

Version-Release number of selected component (if applicable):
Red Hat Network (RHN) Satellite 5.3.0

How reproducible:
Always (cusotmer db dump avilable).

Steps to Reproduce:
1. Import customer db onto 5.3 Satellite;
2. via sqlplus query "select count(*) from rhnavailablechannels;"
3. click on Channels tab to browse channels section
4. upload package and go through errata publication process

Actual results:
step 2 takes 10-15 seconds
step 3 is slow
step 4 takes about 10 seconds on each stage (I could only see this once though)

Expected results:
Queries/pages return faster.

Additional info:

Rough deployment size indicators -

SQL> select count(*) from rhnServerNeededErrataCache;

COUNT(*)
----------
 1847052

SQL> select count(*) from rhnServerNeededCache;

COUNT(*)
----------
 4295066

SQL> select count(*) from rhnServer;

COUNT(*)
----------
   26921

SQL> select count(*) from rhnChannel;

COUNT(*)
----------
     391

SQL> select count(*) from rhnPackage;

COUNT(*)
----------
   61514

SQL> select count(*) from rhnErrata;

COUNT(*)
----------
   10973

SQL> select count(*) from rhnServerpackage;

COUNT(*)
----------
13795711

Comment 3 Justin Sherrill 2010-07-08 17:01:45 UTC
Taking a look at this.

Comment 4 Justin Sherrill 2010-07-08 22:48:14 UTC
Ok, i think should be fixed now:

spacewalk master:
78172547e9e0b5263c20ac991b71b0e2520391f9


Since this is a view change, we cannot release it for everyone via the errata process, but there is no harm in teh customer applying a one-off fix.  It should not affect upgrades either.

Will attach upgrade script for the customer.

Comment 5 Justin Sherrill 2010-07-08 22:49:22 UTC
Created attachment 430497 [details]
Patch for this bug

Comment 6 Justin Sherrill 2010-07-08 22:50:38 UTC
To apply the patch simply download it and run:

sqlplus rhnsat/rhnsat@rhnsat   @rhnAvailableChannels.sql

(assuming the db connect string is rhnsat/rhnsat@rhnsat). 

The database will need to be running while this is run.

Comment 17 Clifford Perry 2010-10-28 14:47:45 UTC
The 5.4.0 RHN Satellite and RHN Proxy release has occurred. This issue has been resolved with this release. 


RHEA-2010:0801 - RHN Satellite Server 5.4.0 Upgrade
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10332

RHEA-2010:0803 - RHN Tools enhancement update
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10333

RHEA-2010:0802 - RHN Proxy Server 5.4.0 bug fix update
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10334

RHEA-2010:0800 - RHN Satellite Server 5.4.0
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10335

Docs are available:

http://docs.redhat.com/docs/en-US/Red_Hat_Network_Satellite/index.html 

Regards,
Clifford