Bug 863123 - Postgresql server goes full load and spacewalk server becomes unresponsive if you delete more than 3 errata at once
Postgresql server goes full load and spacewalk server becomes unresponsive if...
Status: CLOSED CURRENTRELEASE
Product: Spacewalk
Classification: Community
Component: Server (Show other bugs)
1.7
Unspecified Linux
unspecified Severity medium
: ---
: ---
Assigned To: Michael Mráka
Red Hat Satellite QA List
:
Depends On:
Blocks: space19
  Show dependency treegraph
 
Reported: 2012-10-04 09:28 EDT by hwinther
Modified: 2013-03-06 13:34 EST (History)
1 user (show)

See Also:
Fixed In Version: spacewalk-java-1.9.38-1
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2013-03-06 13:34:52 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 hwinther 2012-10-04 09:28:53 EDT
Description of problem:
We are using the postgresql backend. If you try to delete more than 2 errata at once the postgresql server goes full load on one core and the webui becomes unresponsive.


Steps to Reproduce:
1. use postgresql backend
2. delete more than 3 errata at once in the webui (https://fqdn/rhn/channels/manage/errata/ConfirmRemove.do?cid=...)
  
Actual results:
The postgresql backend goes full load while running this query:
select package1_.id as col_0_0_ from rhnChannel channel0_, rhnPackage package1_, rhnErrata publishede2_ where (package1_.id in (select package4_.id from rhnChannelPackage packages3_, rhnPackage package4_ where channel0_.id=packages3_.channel_id and packages3_.package_id=package4_.id)) and (package1_.id in (select package6_.id from rhnErrataPackage packages5_, rhnPackage package6_ where publishede2_.id=packages5_.errata_id and pack
ages5_.package_id=package6_.id)) and channel0_.id=$1 and (publishede2_.id in ($2 , $3 , $4))


Additional info:
Vacuum, analyze and reindex have already been done. An explain yielded the following explaination:

explain select package1_.id as col_0_0_ from rhnChannel channel0_, rhnPackage package1_, rhnErrata publishede2_ where
 (package1_.id in (select package4_.id from rhnChannelPackage packages3_, rhnPackage package4_ where channel0_.id=packages3_.channel_id
  and packages3_.package_id=package4_.id)) and (package1_.id in (select package6_.id from rhnErrataPackage packages5_, 
  rhnPackage package6_ where publishede2_.id=packages5_.errata_id and packages5_.package_id=package6_.id)) and channel0_.id=291 
  and (publishede2_.id in (12826 , 12654 , 12912)) ;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=12.78..233562123.67 rows=44121 width=8)
   Join Filter: (SubPlan 2)
   ->  Nested Loop  (cost=0.00..231202076.15 rows=29414 width=8)
         Join Filter: (SubPlan 1)
         ->  Index Scan using rhn_channel_id_pk on rhnchannel channel0_  (cost=0.00..8.27 rows=1 width=7)
               Index Cond: (id = 291::numeric)
         ->  Seq Scan on rhnpackage package1_  (cost=0.00..5574.27 rows=58827 width=8)
         SubPlan 1
           ->  Hash Join  (cost=652.05..7202.96 rows=2070 width=8)
                 Hash Cond: (package4_.id = packages3_.package_id)
                 ->  Seq Scan on rhnpackage package4_  (cost=0.00..5574.27 rows=58827 width=8)
                 ->  Hash  (cost=626.18..626.18 rows=2070 width=8)
                       ->  Bitmap Heap Scan on rhnchannelpackage packages3_  (cost=52.30..626.18 rows=2070 width=8)
                             Recheck Cond: ($0 = channel_id)
                             ->  Bitmap Index Scan on rhn_cp_cp_uq  (cost=0.00..51.79 rows=2070 width=0)
                                   Index Cond: ($0 = channel_id)
   ->  Bitmap Heap Scan on rhnerrata publishede2_  (cost=12.78..23.99 rows=3 width=7)
         Recheck Cond: (publishede2_.id = ANY ('{12826,12654,12912}'::numeric[]))
         ->  Bitmap Index Scan on rhn_errata_id_pk  (cost=0.00..12.77 rows=3 width=0)
               Index Cond: (publishede2_.id = ANY ('{12826,12654,12912}'::numeric[]))
   SubPlan 2
     ->  Nested Loop  (cost=0.00..37.47 rows=3 width=8)
           ->  Index Scan using rhn_err_pkg_eid_pid_uq on rhnerratapackage packages5_  (cost=0.00..12.60 rows=3 width=8)
                 Index Cond: ($2 = errata_id)
           ->  Index Scan using rhn_package_id_pk on rhnpackage package6_  (cost=0.00..8.28 rows=1 width=8)
                 Index Cond: (package6_.id = packages5_.package_id)
(26 rows)
Comment 1 Michael Mráka 2013-01-08 03:57:04 EST
I can't reproduce the issue. Anyway the query created by hibernate is awful and has huge cost. So I optimized query manually.

commit d194cc14de0a874cc2ea2f19cb1b2df1e1681772
    863123 - improved query
Comment 2 Tomas Lestach 2013-01-14 12:34:48 EST
Fixing the fixed query ... :-)

spacewalk.git: 460b428823a705196396681c3b9358fad467ab32
Comment 3 Stephen Herr 2013-03-01 12:07:28 EST
Marking bug as ON_QA since tonight's build of Spacewalk nightly is a release candidate for Spacewalk 1.9.
Comment 4 Stephen Herr 2013-03-06 13:34:52 EST
Spacewalk 1.9 has been released.

https://fedorahosted.org/spacewalk/wiki/ReleaseNotes19

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