-
Bug
-
Resolution: Unresolved
-
Normal
-
None
-
6.16.0
-
False
-
Moderate
-
sat-artemis
-
None
-
None
-
None
-
To Do
Searching errata with certain keywords may cause high cpu and high memory consumption if any errata stored in the database are associated to hundreds of bugzilla and hundreds of CVEs , For example, RHSA-2024:9315 is associated to 453 bugzillas and 484 CVEs.
The issue appears to be caused by the combination of table joins, searching errata title, bugzilla links and cves together. When an erratum has many bugzilla links and many cves, the joined table will be extremely large (458 x 484 = 221672 rows). Sequence scanning through this rows caused high cpu and memory consumption.
This is the query that cause issue.
select * from katello_errata ke left join katello_erratum_bugzillas keb on ke.id = keb.erratum_id left join katello_erratum_cves kec on ke.id = kec.erratum_id where ke.id in (197) and (ke.title ilike '%kernel%' or keb.bug_id ilike '%kernel%');
select count(*) from katello_errata ke left join katello_erratum_bugzillas keb on ke.id = keb.erratum_id left join katello_erratum_cves kec on ke.id = kec.erratum_id where ke.id in (197) and (ke.title ilike '%kernel%' or keb.bug_id ilike '%kernel%'); count -------- 221672 (1 row)
If I exclude the large errata from the installable errata search, the respond is quick and no high cpu and high memory consumption.
irb(main):094:1* Benchmark.measure do irb(main):095:1* host = Host.find(6) irb(main):096:1* content_view = host.organization.default_content_view irb(main):097:1* environment = host.organization.library irb(main):098:1* etquery = host.content_facet.installable_errata(environment, content_view).where(:errata_type => Katello::Erratum::SECURITY).where.not(id: 197).select(:id) irb(main):099:1* query = Katello::Erratum.search_for("kernel", :order => "errata_id asc").where("#{Katello::Erratum.table_name}.id" => etquery).includes([:cves]) irb(main):100:1* query.each {|e| p e.id} irb(main):101:0> end 57 477 509 426 244 363 379 31 404 22359 22493 => #<Benchmark::Tms:0x00007ff053d68418 @cstime=0.0, @cutime=0.0, @label="", @real=0.04009211400989443, @stime=0.0, @total=0.013305000000002565, @utime=0.013305000000002565>
Otherwise, it took 72 seconds++ and almost ran out of memory
=> #<Benchmark::Tms:0x00007ff053d68418 @cstime=0.0, @cutime=0.0, @label="", @real=0.04009211400989443, @stime=0.0, @total=0.013305000000002565, @utime=0.013305000000002565> irb(main):102:0> irb(main):103:0> irb(main):104:0> irb(main):105:1* Benchmark.measure do irb(main):106:1* host = Host.find(6) irb(main):107:1* content_view = host.organization.default_content_view irb(main):108:1* environment = host.organization.library irb(main):109:1* etquery = host.content_facet.installable_errata(environment, content_view).where(:errata_type => Katello::Erratum::SECURITY).select(:id) irb(main):110:1* query = Katello::Erratum.search_for("kernel", :order => "errata_id asc").where("#{Katello::Erratum.table_name}.id" => etquery).includes([:cves]) irb(main):111:1* query.each {|e| p e.id} irb(main):112:0> end 57 477 509 197 426 244 363 379 31 404 22359 22493 => #<Benchmark::Tms:0x00007ff05dbcc488 @cstime=0.0, @cutime=0.0, @label="", @real=72.51452481999877, @stime=4.261692000000004, @total=61.183326999999984, @utime=56.92163499999998>
How to reproduce:
1. Sync the RHEL 9 baseos and RHEL9 appstream repositories.
2. Register RHEL9 host and make "RHSA-2024:9315" applicable or installable to it.
3. Navigate the the host page -> Content -> Errata -> do a search with "kernel" keyword.
4. Alternatively, Skip step 1 and step 2. Simply navigate to the Content -> Content Types -> Errata page and do a search with "kernel" keyword.
Actual result
Run "top" on Satellite and notice high cpu and high memory consumption which might trigger OOM.
Unable to search errata by keyword.
Expected result
Able to search errata by keyword without issue and shouldn't cause high cpu and high memory consumption.
