Bug 618219 - RHNS 5.2 Performance problem - high cpu usage while deleting a single system from Satellite
Summary: RHNS 5.2 Performance problem - high cpu usage while deleting a single system ...
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Spacewalk
Classification: Community
Component: Server
Version: 1.1
Hardware: All
OS: Linux
high
high
Target Milestone: ---
Assignee: Michael Mráka
QA Contact: Red Hat Satellite QA List
URL:
Whiteboard:
Depends On: 502107
Blocks: space11
TreeView+ depends on / blocked
 
Reported: 2010-07-26 12:52 UTC by Michael Mráka
Modified: 2010-08-19 08:24 UTC (History)
4 users (show)

Fixed In Version: spacewalk-schema-1.1.21-1
Clone Of: 502107
Environment:
Last Closed: 2010-08-19 08:24:33 UTC
Embargoed:


Attachments (Terms of Use)

Description Michael Mráka 2010-07-26 12:52:30 UTC
Public bug summary:
Customer sees high cpu usage while deleting a single system from 5.2 Satellite, and this is reproduced in-house with their db-dump.  E.g., attempting to delete a single system takes 4-6 minutes and top output shows ~100% CPU on oracle. 

General usage stats:

SQL> select count(*) from rhnServer;

  COUNT(*)
----------
      4832

SQL> select count(*) from rhnServerGroup;

  COUNT(*)
----------
       113

SQL> select count(*) from rhnChannel;

  COUNT(*)
----------
      1048

SQL> select count(*) from rhnPackage;

  COUNT(*)
----------
     33692

SQL> select count(*) from rhnServerChannel;
select
  COUNT(*)
----------
     35876

SQL> select count(*) from rhnServerPackage;

  COUNT(*)
----------
   1478795

Based on statspack reports, the following sql function/procs are highlighted -

...
    133.75            1     133.75   89.4     179.98      78,710,070
1604354683
Module: /usr/sbin/httpd
BEGIN   delete_server(:p1); END;

    132.10           16       8.26   88.3     132.10      78,205,269
3381249446
Module: /usr/sbin/httpd
UPDATE RHNPRIVATECHANNELFAMILY SET CURRENT_MEMBERS = ( RHN_CHANN
EL.CHANNEL_FAMILY_CURRENT_MEMBERS(:B1 , :B2 ) ) WHERE ORG_ID = :
B2 AND CHANNEL_FAMILY_ID = :B1

    132.07           16       8.25   88.3     132.07      78,204,755
1641884697
Module: /usr/sbin/httpd
SELECT COUNT(SC.SERVER_ID) FROM RHNCHANNELFAMILYMEMBERS CFM, RHN
SERVERCHANNEL SC, RHNSERVER S WHERE S.ORG_ID = :B2 AND S.ID = SC
.SERVER_ID AND CFM.CHANNEL_FAMILY_ID = :B1 AND CFM.CHANNEL_ID =
SC.CHANNEL_ID AND EXISTS ( SELECT 1 FROM RHNCHANNELFAMILYSERVERP
HYSICAL CFSP WHERE CFSP.CHANNEL_FAMILY_ID = :B1 AND CFSP.SERVER_

...

and possibly
...
      4.90            1       4.90   24.6       4.90       2,884,225 
721418455
SELECT             G.ID AS ID, G.NAME AS NAME,
     (SELECT COUNT(*) FROM rhnUserManagedServerGroups UMSG WHERE
 UMSG.server_group_id = G.id)          AS GROUP_ADMINS,    (SELE
CT COUNT(*) FROM rhnServerGroupMembers SGM WHERE SGM.server_grou
p_id = G.id                  AND EXISTS ( SELECT 1
...

Deleting a system with no channel or group memberships is faster (<20 sec) than otherwise.  Internal reproducer info and more background to follow.

Comment 1 Michael Mráka 2010-07-26 14:56:02 UTC
Fixed in 
commit fc86b832f262feb6cf4b92ee9beaa493fb54a566
    618219 - update family counts only once per family

Re-counting family only once decreased elapsed time for delete_server() (PL/SQL procedure)
from 1:30 min to 5sec for client with 8 channels (3 different families) on a satellite with 10k systems of such kind.

Comment 2 Milan Zázrivec 2010-07-27 10:35:39 UTC
Additional fixes in spacewalk.git master:
9a3dc0641c5299f605290867d7263c9b946398c9
ff2714abbe25734d6259e1f9d14a7e67667cb808

Comment 3 Milan Zázrivec 2010-08-19 08:24:33 UTC
Spacewalk 1.1 has been released.


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