Bug 1292827 - Error 503 on page /rhn/systems/ExtraPackagesSystems.do
Error 503 on page /rhn/systems/ExtraPackagesSystems.do
Status: NEW
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Server (Show other bugs)
Unspecified Unspecified
unspecified Severity medium
: ---
: ---
Assigned To: Grant Gainey
Red Hat Satellite QA List
Depends On:
  Show dependency treegraph
Reported: 2015-12-18 07:56 EST by Pavel Studeník
Modified: 2016-11-30 07:47 EST (History)
4 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Pavel Studeník 2015-12-18 07:56:25 EST
Description of problem:
I have two system satellite 5.7 with registered 2000+ systems and synced RHEL's channels. When I try to show page "Extra package", I
received error 503  Service Temporarily Unavailable. 

Other pages is showed rather quickly:

first system:

200 rhn/systems/Overview.do 1.495
200 rhn/systems/SystemList.do 1.547
200 rhn/systems/PhysicalList.do 0.744
200 rhn/systems/VirtualSystemsList.do 5.125
200 rhn/systems/OutOfDate.do 9.731
200 rhn/systems/RequiringReboot.do 16.530
503 rhn/systems/ExtraPackagesSystems.do 210.367 <-----
200 rhn/systems/Unentitled.do 0.331
200 rhn/systems/Ungrouped.do 1.372
200 rhn/systems/Inactive.do 1.812
200 rhn/systems/Registered.do 0.608
200 rhn/systems/ProxyList.do 1.199
200 rhn/systems/DuplicateIPList.do 1.366
200 rhn/systems/SystemCurrency.do 13.942

second system:

200 rhn/systems/Overview.do 0.457
200 rhn/systems/SystemList.do 0.636
200 rhn/systems/PhysicalList.do 0.760
200 rhn/systems/VirtualSystemsList.do 5.383
200 rhn/systems/OutOfDate.do 43.846
200 rhn/systems/RequiringReboot.do 17.090
503 rhn/systems/ExtraPackagesSystems.do 210.250 <-----
200 rhn/systems/Unentitled.do 26.594
200 rhn/systems/Ungrouped.do 1.765
200 rhn/systems/Inactive.do 0.709
200 rhn/systems/Registered.do 0.295
200 rhn/systems/ProxyList.do 0.362
200 rhn/systems/DuplicateIPList.do 1.603
200 rhn/systems/SystemCurrency.do 23.800

Version-Release number of selected component (if applicable):

How reproducible:
always with a lot of systems

Steps to Reproduce:
1. synced RHELs channels 
2. registered 2000+ systems
3. go to page /rhn/systems/ExtraPackagesSystems.do

Actual results:
very slow rendering page
503  Service Temporarily Unavailable. 

Expected results:
faster and without http error 503  (210+ seconds is too many)

Additional info:
>> tail /var/log/tomcat6/catalina.out
Dec 18, 2015 6:52:28 AM org.apache.jk.common.ChannelSocket processConnection
WARNING: processCallbacks status 2
Comment 2 Grant Gainey 2016-11-07 15:35:48 EST
The heavy lifting in ExtraPackages is being done by this mode-query:

<mode name="extra_packages_systems_count"
  <query params="userid, orgid">
select s.id as id,
       s.name as name,
       1 as selectable,
       count(sp.name_id) as extra_pkg_count
       from rhnServer s
  join rhnUserServerPerms usp on (usp.server_id = s.id and
                                  usp.user_id = :userid)
  join rhnServerPackage sp on (s.id = sp.server_id)
       left outer join (select sc.server_id,
                          from rhnPackage p,
                               rhnServerChannel sc,
                               rhnServerPackage sp2,
                               rhnChannelPackage cp,
                               rhnUserServerPerms usp2
                         where cp.package_id = p.id
                           and cp.channel_id = sc.channel_id
                           and sc.server_id = usp2.server_id
                           and usp2.user_id = :userid
                           and sc.server_id = sp2.server_id
                           and sp2.name_id = p.name_id
                           and sp2.evr_id = p.evr_id
                           and sp2.package_arch_id = p.package_arch_id
                       ) scp on (scp.server_id = sp.server_id and
                                 sp.name_id = scp.name_id and
                                 sp.evr_id = scp.evr_id and
                                 sp.package_arch_id = scp.package_arch_id)
 where scp.package_id is null and
       s.org_id = :orgid
 group by s.name, s.id
 order by name, id

The explain plan for that query (on postgres in this example, 250 systems registered, running as sat-admin) looks like so:

 GroupAggregate  (cost=33950.01..33950.03 rows=1 width=28)
   ->  Sort  (cost=33950.01..33950.02 rows=1 width=28)
         Sort Key: s.name, s.id
         ->  Nested Loop  (cost=28954.68..33950.00 rows=1 width=28)
               Join Filter: (usp.server_id = s.id)
               ->  Nested Loop  (cost=28954.68..33949.72 rows=1 width=21)
                     Join Filter: (sp.server_id = usp.server_id)
                     ->  Index Scan using rhn_usperms_sid_idx on rhnuserserverperms usp  (cost=0.00..20.11 rows=219 width=8)
                           Filter: (user_id = 1::numeric)
                     ->  Materialize  (cost=28954.68..33926.32 rows=1 width=13)
                           ->  Hash Left Join  (cost=28954.68..33926.32 rows=1 width=13)
                                 Hash Cond: ((sp.name_id = p.name_id) AND (sp.evr_id = p.evr_id) AND (sp.package_arch_id = p.package_arch_id) A
ND (sp.server_id = sc.server_id))
                                 Filter: (cp.package_id IS NULL)
                                 ->  Seq Scan on rhnserverpackage sp  (cost=0.00..2745.25 rows=148425 width=23)
                                 ->  Hash  (cost=28954.32..28954.32 rows=18 width=29)
                                       ->  Nested Loop  (cost=27123.41..28954.32 rows=18 width=29)
                                             Join Filter: (sc.server_id = usp2.server_id)
                                             ->  Nested Loop  (cost=27123.41..28949.19 rows=18 width=37)
                                                   Join Filter: (sc.channel_id = cp.channel_id)
                                                   ->  Nested Loop  (cost=27123.41..28913.29 rows=51 width=42)
                                                         ->  Merge Join  (cost=27123.41..28897.31 rows=57 width=29)
                                                               Merge Cond: ((sp2.evr_id = p.evr_id) AND (sp2.name_id = p.name_id) AND (sp2.pack
age_arch_id = p.package_arch_id))
                                                               ->  Sort  (cost=18539.49..18910.56 rows=148425 width=23)
                                                                     Sort Key: sp2.evr_id, sp2.name_id, sp2.package_arch_id
                                                                     ->  Seq Scan on rhnserverpackage sp2  (cost=0.00..2745.25 rows=148425 widt
                                                               ->  Sort  (cost=8377.70..8503.08 rows=50150 width=21)
                                                                     Sort Key: p.evr_id, p.name_id, p.package_arch_id
                                                                     ->  Seq Scan on rhnpackage p  (cost=0.00..4462.50 rows=50150 width=21)
                                                         ->  Index Only Scan using rhn_sc_sid_cid_uq on rhnserverchannel sc  (cost=0.00..0.27 r
ows=1 width=13)
                                                               Index Cond: (server_id = sp2.server_id)
                                                   ->  Index Scan using rhn_cp_pid_idx on rhnchannelpackage cp  (cost=0.00..0.62 rows=7 width=1
                                                         Index Cond: (package_id = p.id)
                                             ->  Index Scan using rhn_usperms_sid_idx on rhnuserserverperms usp2  (cost=0.00..0.27 rows=1 width
                                                   Index Cond: (server_id = sp2.server_id)
                                                   Filter: (user_id = 1::numeric)
               ->  Index Scan using rhn_server_oid_id_idx on rhnserver s  (cost=0.00..0.27 rows=1 width=23)
                     Index Cond: ((org_id = 1::numeric) AND (id = sp.server_id))
(37 rows)

Note three Seq Scans:

->  Seq Scan on rhnserverpackage sp  (cost=0.00..2745.25 rows=148425 width=23)
->  Seq Scan on rhnserverpackage sp2  (cost=0.00..2745.25 rows=148425 widt
->  Seq Scan on rhnpackage p  (cost=0.00..4462.50 rows=50150 width=21)

Investigation continues.
Comment 3 Grant Gainey 2016-11-07 15:47:36 EST
Fun - see


for the last time this came up.

Based on the discussion in '9938, this may be a case of "we can't do any better".
Comment 4 Grant Gainey 2016-11-08 11:03:50 EST
One possible approach would be to make 'extra packages' be calculated as part of update_needed_cache, since it's affected by the exact same transitions. The result could be kept in a column in rhnServerInfo, and the page would just display what's there.
Comment 5 Tomas Lestach 2016-11-10 10:18:23 EST
Yes, this would work. I'd however vote for a separate table and don't use rhnServerInfo table for this purpose.

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