Bug 720533

Summary: Satellite 5.4: erratamailer_fill_work_queue causes high CPU usage
Product: Red Hat Satellite 5 Reporter: Xixi <xdmoon>
Component: ServerAssignee: Jan Pazdziora <jpazdziora>
Status: CLOSED ERRATA QA Contact: Martin Minar <mminar>
Severity: high Docs Contact:
Priority: high    
Version: 540CC: cperry, mkoci, mminar, tlestach, xdmoon, yoguma
Target Milestone: ---Keywords: Patch
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2011-08-16 13:57:33 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Bug Depends On:    
Bug Blocks: 715348    
Attachments:
Description Flags
explain plan output before patch
none
explain plan output after patch
none
patch none

Description Xixi 2011-07-12 00:59:58 UTC 
Description of problem:
This is a spin-off from Bug 701457.  After applying hotfix packages, there is still another query that's resulting in high CPU consumption by Oracle.

top - 10:35:25 up 133 days, 22:22, 2 users, load average: 1.35, 1.14, 1.09 Tasks: 304 total, 2 running, 302 sleeping, 0 stopped, 0 zombie Cpu(s): 14.3%us, 0.2%sy, 0.0%ni, 85.0%id, 0.3%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 37037420k total, 33964124k used, 3073296k free, 425924k buffers Swap: 37736676k total, 268344k used, 37468332k free, 31141524k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32322 oracle 25 0 5898m 1.1g 1.0g R 99.8 3.2 90:18.50 oracle TEXT ---------------------------------------------------------------- INSERT INTO rhnPaidErrataTempCache (user_id, server_id, errata_id) SELECT urn.user_id, snv.server_id, snv.errata_id FROM rhnServerNeededView snv JOIN rhnUserReceiveNotifications urn ON snv.server_id = urn.server_id JOIN rhnServerChannel sc ON sc.server_id = snv.server_id JOIN rhnServer S ON S.id = SC.server_id WHERE sc.channel_id = :1 AND snv.errata_id = :2 AND S.org_id = :3

... which appears to be "erratamailer_fill_work_queue" query in src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml -
...
<write-mode name="erratamailer_fill_work_queue">
   <query params="errata_id,org_id, channel_id">
  INSERT INTO rhnPaidErrataTempCache
    (user_id, server_id, errata_id)
    SELECT urn.user_id, snv.server_id, snv.errata_id
      FROM rhnServerNeededView snv
      JOIN rhnUserReceiveNotifications urn
        ON snv.server_id = urn.server_id
      JOIN rhnServerChannel sc
        ON sc.server_id = snv.server_id
      JOIN rhnServer S
        ON S.id = SC.server_id
     WHERE sc.channel_id = :channel_id
       AND snv.errata_id = :errata_id
       AND S.org_id = :org_id
   </query>
</write-mode>
...

...which is called by src/com/redhat/rhn/taskomatic/task/ErrataMailer.java in populateWorkQueue(Long errataId, Long orgId, Long channelId).  Basically this is the task that works on processing/sending out notifications for errata.

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

How reproducible:
Always.

Steps to Reproduce:
1. A large Satellite deployment, e.g., with >30K clients registered (import customer db)
2. Run the SQL above and/or watch taskomatic cpu consumption

Actual results:
SQL is too slow (1.5 hrs at least according to customer / Oracle high load

Expected results:
SQL runs as expected / Oracle does not consume high cpu

Additional Information:
Comment 4 Xixi 2011-07-12 22:08:00 UTC 
Created attachment 512531 [details]
explain plan output before patch
Comment 5 Xixi 2011-07-12 22:11:59 UTC 
Created attachment 512532 [details]
explain plan output after patch
Comment 6 Xixi 2011-07-12 23:43:09 UTC 
Created attachment 512540 [details]
patch
Comment 10 Jan Pazdziora 2011-07-13 07:44:38 UTC 
Fixed in Spacewalk master, 438f6e1820ae9ce7fad8529c484ce4b6bebabacd. In Spacewalk master it's erratamailer_get_relevant_servers.

We replace rhnServerNeededView with custom subselect which is generally equivalent.
Comment 27 errata-xmlrpc 2011-08-16 13:57:33 UTC 
An advisory has been issued which should help the problem
described in this bug report. This report is therefore being
closed with a resolution of ERRATA. For more information
on therefore solution and/or where to find the updated files,
please follow the link below. You may reopen this bug report
if the solution does not work for you.

http://rhn.redhat.com/errata/RHBA-2011-1162.html