Bug 1292827
Summary: | Error 503 on page /rhn/systems/ExtraPackagesSystems.do | ||
---|---|---|---|
Product: | Red Hat Satellite 5 | Reporter: | Pavel StudenÃk <pstudeni> |
Component: | Server | Assignee: | Grant Gainey <ggainey> |
Status: | CLOSED DEFERRED | QA Contact: | Red Hat Satellite QA List <satqe-list> |
Severity: | medium | Docs Contact: | |
Priority: | unspecified | ||
Version: | 570 | CC: | dyordano, ggainey, jhutar, ktordeur, tlestach |
Target Milestone: | --- | ||
Target Release: | --- | ||
Hardware: | Unspecified | ||
OS: | Unspecified | ||
Whiteboard: | |||
Fixed In Version: | Doc Type: | Bug Fix | |
Doc Text: | Story Points: | --- | |
Clone Of: | Environment: | ||
Last Closed: | 2018-04-09 14:53:34 UTC | Type: | Bug |
Regression: | --- | Mount Type: | --- |
Documentation: | --- | CRM: | |
Verified Versions: | Category: | --- | |
oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
Cloudforms Team: | --- | Target Upstream Version: | |
Embargoed: |
The heavy lifting in ExtraPackages is being done by this mode-query: === <mode name="extra_packages_systems_count" class="com.redhat.rhn.frontend.dto.SystemOverview"> <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, cp.package_id, p.name_id, p.evr_id, p.package_arch_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 </query> === 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 h=23) -> 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 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 =8) 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 h=23) -> Seq Scan on rhnpackage p (cost=0.00..4462.50 rows=50150 width=21) Investigation continues. Fun - see https://bugzilla.redhat.com/show_bug.cgi?id=1099938 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". 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. Yes, this would work. I'd however vote for a separate table and don't use rhnServerInfo table for this purpose. We have re-reviewed this bug, as part of an ongoing effort to improve Satellite/Proxy feature and bug updates, review and backlog. This is a low priority bug and has no currently open customer cases. While this bug may still valid, we do not see it being implemented prior to the EOL of the Satellite 5.x product. As such, this is being CLOSED DEFERRED. Closing now to help set customer expectations as early as possible. You are welcome to re-open this bug if needed. |
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): spacewalk-java-2.3.8-120.el6sat.noarch 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