Bug 1005056 - listing extra packages for RHEL5-Server-GOLD system is slow on PostgreSQL
listing extra packages for RHEL5-Server-GOLD system is slow on PostgreSQL
Status: CLOSED CURRENTRELEASE
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Server (Show other bugs)
560
Unspecified Unspecified
medium Severity medium
: ---
: ---
Assigned To: Michael Mráka
Jan Hutař
:
Depends On:
Blocks: sat570-lowbug
  Show dependency treegraph
 
Reported: 2013-09-06 03:08 EDT by Jan Hutař
Modified: 2015-01-13 04:54 EST (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2015-01-13 04:54:08 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Jan Hutař 2013-09-06 03:08:51 EDT
Description of problem:
When I register RHEL5-Server-GOLD system into PostgreSQL satellite and Oracle one, displaying page Systems -> <my_system> -> Software -> Packages -> Extra Packages takes ~33 seconds on PostgreSQL and ~1 second on Oracle.


Version-Release number of selected component (if applicable):
spacewalk-schema-2.0.2-11.el6sat.noarch
satellite-schema-5.6.0.10-1.el6sat.noarch
spacewalk-postgresql-2.0.1-3.el6sat.noarch
postgresql-server-8.4.13-1.el6_3.x86_64


How reproducible:
always on mine setup


Steps to Reproduce:
1. Have Satellite 5.6.0 with RHEL5 and RHEL6 channels synced
2. Register RHEL5-Server-GOLD
3. Go to Systems -> <my_system> -> Software -> Packages -> Extra Packages


Actual results:
Takes ~33 seconds on PostgreSQL and ~1 second on Oracle variants.


Expected results:
Times should be +- equal as HW for both variants is equal.


Additional info:
With DB set to log queries longer than 1000ms it logs this:

2013-09-06 03:01:42.174 EDT LOG:  duration: 33464.016 ms  execute <unnamed>: 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 = $1 and
	         (scp.package_id is null)
	   order by nvre
2013-09-06 03:01:42.174 EDT DETAIL:  parameters: $1 = '1000010052'
Comment 3 Stephen Herr 2013-09-06 16:45:24 EDT
I wasn't able to make progress on this. Someone else can pick it up if they want, here are my findings:

Current query:
  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 = :serverid and
         (scp.package_id is null)
   order by nvre;

Current explain plan (on Postgres 8):

 Sort  (cost=4233.92..4233.93 rows=1 width=273)
   Sort Key: ((((((pn.name)::text || '-'::text) || (pe.version)::text) || '-'::text) || (pe.release)::text))
   ->  Nested Loop Left Join  (cost=3253.53..4233.91 rows=1 width=273)
         ->  Nested Loop Left Join  (cost=3253.53..4225.34 rows=1 width=258)
               ->  Nested Loop Left Join  (cost=3253.53..4217.06 rows=1 width=87)
                     ->  Nested Loop Left Join  (cost=3253.53..4208.78 rows=1 width=37)
                           Join Filter: ((sc.server_id = sp.server_id) AND (sp.name_id = p.name_id) AND (sp.evr_i
d = p.evr_id) AND (sp.package_arch_id = p.package_arch_id))
                           Filter: (cp.package_id IS NULL)
                           ->  Index Scan using rhn_sp_snep_uq on rhnserverpackage sp  (cost=0.00..8.28 rows=1 wi
dth=39)
                                 Index Cond: (server_id = 1000010052::numeric)
                           ->  Hash Join  (cost=3253.53..3910.52 rows=14499 width=39)
                                 Hash Cond: (cp.package_id = p.id)
                                 ->  Nested Loop  (cost=193.39..551.29 rows=15410 width=18)
                                       ->  Seq Scan on rhnserverchannel sc  (cost=0.00..1.64 rows=1 width=17)
                                             Filter: (server_id = 1000010052::numeric)
                                       ->  Bitmap Heap Scan on rhnchannelpackage cp  (cost=193.39..463.95 rows=68
56 width=15)
                                             Recheck Cond: (cp.channel_id = sc.channel_id)
                                             ->  Bitmap Index Scan on rhn_cp_cp_uq  (cost=0.00..191.68 rows=6856 
width=0)
                                                   Index Cond: (cp.channel_id = sc.channel_id)
                                 ->  Hash  (cost=2737.84..2737.84 rows=25784 width=29)
                                       ->  Seq Scan on rhnpackage p  (cost=0.00..2737.84 rows=25784 width=29)
                     ->  Index Scan using rhn_pe_id_pk on rhnpackageevr pe  (cost=0.00..8.27 rows=1 width=57)
                           Index Cond: (pe.id = sp.evr_id)
               ->  Index Scan using rhn_parch_id_pk on rhnpackagearch pa  (cost=0.00..8.27 rows=1 width=178)
                     Index Cond: (pa.id = sp.package_arch_id)
         ->  Index Scan using rhn_pn_id_pk on rhnpackagename pn  (cost=0.00..8.27 rows=1 width=22)
               Index Cond: (pn.id = sp.name_id) 


I tried inserting a "and sc.server_id = :serverid into the sub-select to see if that would make a difference, but it did not. The time to execute and the explain plan were both the same as with above.

Next I tried converting the left join on the sub-query to a not exists statement, even though the left join is generally faster. It was a little faster, but not a lot.

New query:
  select null as 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 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 = 1000010052
     and not exists (select 1
                       from rhnPackage p,
                            rhnServerChannel sc,
                            rhnChannelPackage cp
                      where cp.package_id = p.id
                        and sc.channel_id = cp.channel_id
                        and sc.server_id = sp.server_id
                        and p.name_id = sp.name_id
                        and p.evr_id = sp.evr_id
                        and p.package_arch_id = sp.package_arch_id)
   order by nvre;

New explain plan:

 Sort  (cost=4233.92..4233.93 rows=1 width=265)
   Sort Key: ((((((pn.name)::text || '-'::text) || (pe.version)::text) || '-'::text) || (pe.release)::text))
   ->  Nested Loop Left Join  (cost=3253.53..4233.91 rows=1 width=265)
         ->  Nested Loop Left Join  (cost=3253.53..4225.34 rows=1 width=250)
               ->  Nested Loop Left Join  (cost=3253.53..4217.06 rows=1 width=79)
                     ->  Nested Loop Anti Join  (cost=3253.53..4208.78 rows=1 width=29)
                           Join Filter: ((sc.server_id = sp.server_id) AND (p.name_id = sp.name_id) AND (p.evr_id
 = sp.evr_id) AND (p.package_arch_id = sp.package_arch_id))
                           ->  Index Scan using rhn_sp_snep_uq on rhnserverpackage sp  (cost=0.00..8.28 rows=1 wi
dth=39)
                                 Index Cond: (server_id = 1000010052::numeric)
                           ->  Hash Join  (cost=3253.53..3910.52 rows=14499 width=31)
                                 Hash Cond: (cp.package_id = p.id)
                                 ->  Nested Loop  (cost=193.39..551.29 rows=15410 width=18)
                                       ->  Seq Scan on rhnserverchannel sc  (cost=0.00..1.64 rows=1 width=17)
                                             Filter: (server_id = 1000010052::numeric)
                                       ->  Bitmap Heap Scan on rhnchannelpackage cp  (cost=193.39..463.95 rows=68
56 width=15)
                                             Recheck Cond: (cp.channel_id = sc.channel_id)
                                             ->  Bitmap Index Scan on rhn_cp_cp_uq  (cost=0.00..191.68 rows=6856 
width=0)
                                                   Index Cond: (cp.channel_id = sc.channel_id)
                                 ->  Hash  (cost=2737.84..2737.84 rows=25784 width=29)
                                       ->  Seq Scan on rhnpackage p  (cost=0.00..2737.84 rows=25784 width=29)
                     ->  Index Scan using rhn_pe_id_pk on rhnpackageevr pe  (cost=0.00..8.27 rows=1 width=57)
                           Index Cond: (pe.id = sp.evr_id)
               ->  Index Scan using rhn_parch_id_pk on rhnpackagearch pa  (cost=0.00..8.27 rows=1 width=178)
                     Index Cond: (pa.id = sp.package_arch_id)
         ->  Index Scan using rhn_pn_id_pk on rhnpackagename pn  (cost=0.00..8.27 rows=1 width=22)
               Index Cond: (pn.id = sp.name_id)

As you can see it's not a lot different, and we still do a sequential scan on rhnpackage, which is where most of the pain comes from.

Not quite sure where to go from here.
Comment 6 Clifford Perry 2015-01-13 04:54:08 EST
With the release of Red Hat Satellite 5.7 on January 12th 2015 this bug is
being moved to a Closed Current Release state. 

The Satellite 5.7 GA Errata:
 - https://rhn.redhat.com/errata/RHSA-2015-0033.html 

Satellite 5.7 Release Notes:
 -
https://access.redhat.com/documentation/en-US/Red_Hat_Satellite/5.7/html-single/Release_Notes/index.html

Satellite Customer Portal Blog announcement for release:
 - https://access.redhat.com/blogs/1169563/posts/1315743 

Cliff

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