Description of problem: Click on a system profile's Software > Packages > List/Remove tab. For a system with ~1,100 installed packages, subscribed to a channel with ~12,000 packages, the page takes ~120 seconds to load. Any navigation of the list (clicking forward/backward, filtering on package name, clicking on the alphanumeric index) also takes the same amount of time to load. Version-Release number of selected component (if applicable): Reproduced on Satellite 5.6 w/ spacewalk-java-2.0.2-79.el6sat.noarch. Relevant code is the same in spacewalk (see below). How reproducible: 100% Steps to Reproduce: 1. See description section above. Actual results: Slow page loads, inability to click on managed packages if the system isn't currently subscribed to the channel providing the package. Expected results: Fast page loads, package clickable if managed by Satellite and visible to user. Additional info: Both the slow query execution and the inability to click certain packages are due to the changes to system_package_list in ./java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml that were introduced in: spacewalk commit: c94bd801db0357703eacecf3a3066242a2a51913 satellite commit: cb1e9274ff8bb366470daa742ebb585809d0f788 Author: Tomas Lestach <tlestach> Date: Fri Aug 16 16:41:56 2013 +0200 997809 - make unavailable packages non-clickable <mode name="system_package_list" class="com.redhat.rhn.frontend.dto.PackageListItem"> <query params="sid"> SELECT scp.package_id, pn.id || '|' || pe.id || '|' || pa.id as id_combo, pn.name || '-' || pe.version || '-' || pe.release as nvre, pn.name || '-' || evr_t_as_vre_simple(pe.evr) || '.' || pa.label as nvrea, pn.name as name, pe.version as version, pe.release as release, pe.epoch as epoch, pn.id as name_id, pe.id as evr_id, pa.label as arch, sp.installTime FROM rhnServerPackage sp 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 JOIN rhnChannelPackage cp ON (sc.channel_id = cp.channel_id) WHERE cp.package_id = p.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) LEFT OUTER JOIN rhnPackageName pn ON (pn.id = sp.name_id) LEFT OUTER JOIN rhnPackageArch pa ON (pa.id = sp.package_arch_id) LEFT OUTER JOIN rhnPackageEvr pe ON (pe.id = sp.evr_id) WHERE sp.server_id = :sid ORDER BY nvre </query> </mode> The first left join looks only at rhnPackage records that are available in the system's currently-subscribed channels. If the package was installed from a child channel, but then the child channel was later unsubscribed, then no rhnPackage id will be returned, and the package in the list will be unclickable. This left join is also the reason for the slow page load. Explain output from a Satellite 5.6 (spacewalk-java-2.0.2-79.el6sat.noarch / satellite-schema-5.6.0.18-1.el6sat.noarch / spacewalk-schema-2.0.2-13.el6sat.noarch) with embedded postgres db: QUERY PLAN -------------------------------------------------------------------------------- Sort (cost=13466.85..13466.86 rows=1 width=113) (actual time=130823.326..130823.455 rows=1138 loops=1) Sort Key: ((((((pn.name)::text || '-'::text) || (pe.version)::text) || '-'::text) || (pe.release)::text)) Sort Method: quicksort Memory: 349kB -> Nested Loop Left Join (cost=10401.89..13466.84 rows=1 width=113) (actual time=113.076..130814.365 rows=1138 loops=1) -> Nested Loop Left Join (cost=10401.89..13458.26 rows=1 width=98) (actual time=111.725..130728.096 rows=1138 loops=1) Join Filter: (pa.id = sp.package_arch_id) -> Nested Loop Left Join (cost=10401.89..13456.25 rows=1 width=88) (actual time=111.687..130693.650 rows=1138 loops=1) -> Nested Loop Left Join (cost=10401.89..13447.97 rows=1 width=37) (actual time=111.645..130666.701 rows=1138 loops=1) Join Filter: ((sc.server_id = sp.server_id) AND (sp.name_id = p.name_id) AND (sp.evr_id = p.evr_id) AND (sp.package_arch_id = p.package_arch_id)) -> Index Scan using rhn_sp_snep_uq on rhnserverpackage sp (cost=0.00..8.28 rows=1 width=40) (actual time=0.037..7.609 rows=1138 loops=1) Index Cond: (server_id = 1000010207::numeric) -> Hash Join (cost=10401.89..13276.49 rows=8160 width=40) (actual time=78.654..109.324 rows=12602 loops=1138) Hash Cond: (cp.package_id = p.id) -> Nested Loop (cost=160.03..2208.64 rows=8160 width=19) (actual time=2.354..7.724 rows=12602 loops=1138) -> Index Scan using rhn_sc_sid_cid_uq on rhnserverchannel sc (cost=0.00..8.27 rows=1 width=18) (actual time=0.017..0.018 rows=1 loops=1138) Index Cond: (server_id = 1000010207::numeric) -> Bitmap Heap Scan on rhnchannelpackage cp (cost=160.03..2136.29 rows=5126 width=15) (actual time=2.322..4.515 rows=12602 loops=1138) Recheck Cond: (cp.channel_id = sc.channel_id) -> Bitmap Index Scan on rhn_cp_cp_uq (cost=0.00..158.75 rows=5126 width=0) (actual time=2.289..2.289 rows=12602 loops=1138) Index Cond: (cp.channel_id = sc.channel_id) -> Hash (cost=8610.49..8610.49 rows=84349 width=29) (actual time=76.249..76.249 rows=77162 loops=1138) -> Seq Scan on rhnpackage p (cost=0.00..8610.49 rows=84349 width=29) (actual time=0.005..40.578 rows=77162 loops=1138) -> Index Scan using rhn_pe_id_pk on rhnpackageevr pe (cost=0.00..8.27 rows=1 width=58) (actual time=0.018..0.018 rows=1 loops=1138) Index Cond: (pe.id = sp.evr_id) -> Seq Scan on rhnpackagearch pa (cost=0.00..1.45 rows=45 width=17) (actual time=0.006..0.012 rows=46 loops=1138) -> Index Scan using rhn_pn_id_pk on rhnpackagename pn (cost=0.00..8.27 rows=1 width=22) (actual time=0.011..0.012 rows=1 loops=1138) Index Cond: (pn.id = sp.name_id) Total runtime: 130823.753 ms (28 rows) It's also not clear to me why rhnPackageName, rhnPackageEvr, and rhnPackageArch are joined to rhnServerPackage with a left join instead of an inner join, since the columns in rhnServerPackage are all required to be non-null and have foreign key constraints on the corresponding tables: rhnschema=# \d rhnServerPackage Table "public.rhnserverpackage" Column | Type | Modifiers -----------------+--------------------------+------------------------ server_id | numeric | not null name_id | numeric | not null evr_id | numeric | not null package_arch_id | numeric | created | timestamp with time zone | not null default now() installtime | timestamp with time zone | Indexes: "rhn_sp_snep_uq" UNIQUE, btree (server_id, name_id, evr_id, package_arch_id) Foreign-key constraints: "rhnserverpackage_evr_id_fkey" FOREIGN KEY (evr_id) REFERENCES rhnpackageevr(id) "rhnserverpackage_name_id_fkey" FOREIGN KEY (name_id) REFERENCES rhnpackagename(id) "rhnserverpackage_package_arch_id_fkey" FOREIGN KEY (package_arch_id) REFERENCES rhnpackagearch(id) "rhnserverpackage_server_id_fkey" FOREIGN KEY (server_id) REFERENCES rhnserver(id) ON DELETE CASCADE I think these left joins should be replaced with inner joins, and the first left join should be improved so that it (1) returns a matching package id if available, regardless of whether the system is subscribed to the channel that provides it, and (2) doesn't take a couple minutes to complete.
Fixed in spacewalk master by commit d833ac9d217570bf3e7a7d87ed77bedaab031e73 1119447 - show package link if package is in database
Moving bugs to ON_QA as we move to release Spacewalk 2.3
Spacewalk 2.3 has been released. See https://fedorahosted.org/spacewalk/wiki/ReleaseNotes23