Bug 706399

Summary:	errataqueue_find_autoupdate_servers causes high CPU usage
Product:	[Community] Spacewalk	Reporter:	Jan Pazdziora (Red Hat) <jpazdziora>
Component:	Server	Assignee:	Michael Mráka <mmraka>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Red Hat Satellite QA List <satqe-list>
Severity:	high	Docs Contact:
Priority:	high
Version:	1.3	CC:	cnoffsin, cperry, mmello, mzazrivec, tlestach, xdmoon
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:	spacewalk-java-1.5.33-2	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:	701457	Environment:
Last Closed:	2011-07-21 14:42:39 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	695242, 701457

Description Jan Pazdziora (Red Hat) 2011-05-20 12:42:46 UTC

+++ This bug was initially created as a clone of Bug #701457 +++

Description of problem:

On Satellite with more than 30k clients registered, the query below does not scale. 

SQL> SELECT DISTINCT
       S.id AS server_id, S.org_id org_id
  FROM rhnServer S
  JOIN rhnServerNeededView SNV
    on SNV.server_id = S.id
 WHERE SNV.channel_id = 1181
   AND SNV.errata_id = 31276
 AND EXISTS
   (SELECT 1 FROM rhnServerFeaturesView SFV WHERE
    SFV.server_id = S.id AND SFV.label = 'ftr_auto_errata_updates')
   AND UPPER(s.auto_update) = 'Y'
ORDER BY S.org_id  2    3    4    5    6    7    8    9   10   11
12  ;
ORDER BY S.org_id
               *
ERROR at line 12:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TBS

oracle@244531-rhndb1 ~]$ top

top - 10:09:48 up 87 days, 21:57,  2 users,  load average: 2.06, 2.42, 2.75
Tasks: 303 total,   2 running, 301 sleeping,   0 stopped,   0 zombie
Cpu(s): 25.9%us,  0.8%sy,  0.0%ni, 62.6%id, 10.1%wa,  0.1%hi,  0.5%si,  0.0%st
Mem:  37037420k total, 36516056k used,   521364k free,   398348k buffers
Swap: 37736676k total,   399272k used, 37337404k free, 29397360k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+ COMMAND
26629 oracle    25   0 5914m 1.4g 1.3g R 99.6  4.0   3623:27 oracle
11208 oracle    15   0 8031m 3.7g 1.6g S 75.3 10.4   4717:38 oracle
23601 oracle    15   0 8095m 3.6g 1.5g S 24.3 10.2   2388:48 oracle
5449 oracle    15   0 5771m 125m 122m S 12.4  0.3   0:01.10 oracle

Comment 1 Jan Pazdziora (Red Hat) 2011-05-20 12:53:44 UTC

Fixed in Spacewalk master, b23243af080795e44268cb2a67e1442d1e970966.

Comment 2 Tomas Lestach 2011-05-20 16:58:14 UTC

One more commit: 210dbc4a9669ea5b381787791a90d5f26408a254

Comment 3 Jan Pazdziora (Red Hat) 2011-07-19 19:36:31 UTC

This bugzilla is currently MODIFIED, so we believe the fix is in the Spacewalk nightly yum repository at http://spacewalk.redhat.com/yum/nightly/

Therefore, moving ON_QA.

Comment 4 Jan Pazdziora (Red Hat) 2011-07-21 14:42:39 UTC

Spacewalk 1.5 was released.