Hide Forgot
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:
Created attachment 512531 [details] explain plan output before patch
Created attachment 512532 [details] explain plan output after patch
Created attachment 512540 [details] patch
Fixed in Spacewalk master, 438f6e1820ae9ce7fad8529c484ce4b6bebabacd. In Spacewalk master it's erratamailer_get_relevant_servers. We replace rhnServerNeededView with custom subselect which is generally equivalent.
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