Description of problem:
On a system with many applicable errata, the following sort can take upwards of 90 seconds:
Sort (cost=218,484.99..219,063.42 rows=231,373 width=879) (actual time=133,527.641..133,528.216 rows=21 loops=1)
Sort Key: katello_errata.updated, katello_errata.id, katello_errata.uuid, katello_errata.errata_id, katello_errata.created_at, katello_errata.updated_at, katello_errata.issued, katello_errata.errata_type, katello_errata.severity, katello_errata.title, katel
Sort Method: external merge Disk: 271928kB
This causes the dashboard to load slowly.
Version-Release number of selected component (if applicable): 6.2.1
Steps to Reproduce:
1. load postgres db with lots of applicable errata
2. view dashboard page
Actual results:
extremely long load time due to postgres having to sort on-disk instead of in memory.
Expected results:
page should load quickly
Additional info: Each row of the query is about 1K in size. This requires quite a bit of memory to sort when there are 200K+ rows. Note that it is not feasible to set work_mem high enough to handle this type of sort, since work_mem is used per query and even per subquery. The likely fix is to strip the errata text in the query and then select it back in if we still need it after obtaining the top 6 errata.
Secondary note: there were some other bugs in the past related to this query. This particular issue is for the same query, but the issue is different.
Verified in Satellite 6.2.4 Snap 2.
Average Dashboard load times on a system with 1k+ content hosts, each with 150 install-able errata, is 15 seconds. Removing the errata widget from the dashboard does not decrease load times. See verification screenshot 1 for load time with errata widget in place, and shot 2 for times without.
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.
For information on the advisory, and where to find the updated
files, follow the link below.
If the solution does not work for you, open a new bug report.
https://access.redhat.com/errata/RHBA-2016:2699
Description of problem: On a system with many applicable errata, the following sort can take upwards of 90 seconds: Sort (cost=218,484.99..219,063.42 rows=231,373 width=879) (actual time=133,527.641..133,528.216 rows=21 loops=1) Sort Key: katello_errata.updated, katello_errata.id, katello_errata.uuid, katello_errata.errata_id, katello_errata.created_at, katello_errata.updated_at, katello_errata.issued, katello_errata.errata_type, katello_errata.severity, katello_errata.title, katel Sort Method: external merge Disk: 271928kB This causes the dashboard to load slowly. Version-Release number of selected component (if applicable): 6.2.1 Steps to Reproduce: 1. load postgres db with lots of applicable errata 2. view dashboard page Actual results: extremely long load time due to postgres having to sort on-disk instead of in memory. Expected results: page should load quickly Additional info: Each row of the query is about 1K in size. This requires quite a bit of memory to sort when there are 200K+ rows. Note that it is not feasible to set work_mem high enough to handle this type of sort, since work_mem is used per query and even per subquery. The likely fix is to strip the errata text in the query and then select it back in if we still need it after obtaining the top 6 errata. Secondary note: there were some other bugs in the past related to this query. This particular issue is for the same query, but the issue is different.