Bug 1741943

Summary: Postgres usage spikes and disk space completely filled up, when non-admin select Monitor -> Tasks
Product: Red Hat Satellite Reporter: Ranjan Kumar <rankumar>
Component: Tasks PluginAssignee: Ondřej Ezr <oezr>
Status: CLOSED ERRATA QA Contact: Peter Ondrejka <pondrejk>
Severity: high Docs Contact:
Priority: high    
Version: 6.5.0CC: ahumbe, aruzicka, dsynk, egolov, inecas, jpasqual, mhulan, oezr, tbrisker
Target Milestone: 6.9.0Keywords: Performance, Triaged, UserExperience
Target Release: Unused   
Hardware: All   
OS: All   
Whiteboard:
Fixed In Version: foreman-2.3.0-0 Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2021-04-21 13:11:48 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On: 1921150    
Bug Blocks:    

Description Ranjan Kumar 2019-08-16 13:52:06 UTC 
Description of problem: 
When a role is assigned with multiple organization and non-admin user click on Monitor -> Tasks full disk space filled up quickly and satellite service goes down.

Workaround: Keep only two organization in non-admin role.  

Observation: A distinct sql query runs and "/var/lib/pgsql/data/base/pgsql_tmp/" growing abnormally until all space is filled up.

Oops, we're sorry but something went wrong PG::DiskFull: ERROR: could not write to hash-join temporary file: No space left on device : SELECT DISTINCT "foreman_tasks_tasks".* FROM "foreman_tasks_tasks" INNER JOIN foreman_tasks_locks AS foreman_tasks_locks_owner5931e3 ON (foreman_tasks_locks_owner5931e3.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_owner5931e3.resource_type = 'User' AND foreman_tasks_locks_owner5931e3.name = 'task_owner') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy55fe2b ON (foreman_tasks_locks_taxonomy55fe2b.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy55fe2b.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyf6b7fc ON (foreman_tasks_locks_taxonomyf6b7fc.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyf6b7fc.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyed1f21 ON (foreman_tasks_locks_taxonomyed1f21.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyed1f21.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyc2a23d ON (foreman_tasks_locks_taxonomyc2a23d.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyc2a23d.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomya70ca2 ON (foreman_tasks_locks_taxonomya70ca2.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomya70ca2.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy002269 ON (foreman_tasks_locks_taxonomy002269.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy002269.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy25d10d ON (foreman_tasks_locks_taxonomy25d10d.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy25d10d.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyb55bd1 ON (foreman_tasks_locks_taxonomyb55bd1.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyb55bd1.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyae8159 ON (foreman_tasks_locks_taxonomyae8159.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyae8159.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy661f6e ON (foreman_tasks_locks_taxonomy661f6e.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy661f6e.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomya51eda ON (foreman_tasks_locks_taxonomya51eda.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomya51eda.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyd00170 ON (foreman_tasks_locks_taxonomyd00170.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyd00170.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy535468 ON (foreman_tasks_locks_taxonomy535468.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy535468.resource_type = 'Organization') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomyd902c3 ON (foreman_tasks_locks_taxonomyd902c3.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomyd902c3.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy5ee8f4 ON (foreman_tasks_locks_taxonomy5ee8f4.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy5ee8f4.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy896e69 ON (foreman_tasks_locks_taxonomy896e69.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy896e69.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy9be3ca ON (foreman_tasks_locks_taxonomy9be3ca.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy9be3ca.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy287352 ON (foreman_tasks_locks_taxonomy287352.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy287352.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy187d79 ON (foreman_tasks_locks_taxonomy187d79.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy187d79.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomye59625 ON (foreman_tasks_locks_taxonomye59625.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomye59625.resource_type = 'Location') LEFT JOIN foreman_tasks_locks AS foreman_tasks_locks_taxonomy98e6c9 ON (foreman_tasks_locks_taxonomy98e6c9.task_id = foreman_tasks_tasks.id AND foreman_tasks_locks_taxonomy98e6c9.resource_type = 'Organization') WHERE (((foreman_tasks_locks_owner5931e3.resource_id = 6) OR (((foreman_tasks_locks_taxonomy55fe2b.resource_id = '7' OR foreman_tasks_locks_taxonomy55fe2b.resource_id IS NULL) OR (foreman_tasks_locks_taxonomyf6b7fc.resource_id = '6' OR foreman_tasks_locks_taxonomyf6b7fc.resource_id IS NULL) OR (foreman_tasks_locks_taxonomyed1f21.resource_id = '4' OR foreman_tasks_locks_taxonomyed1f21.resource_id IS NULL) OR (foreman_tasks_locks_taxonomyc2a23d.resource_id = '9' OR foreman_tasks_locks_taxonomyc2a23d.resource_id IS NULL) OR (foreman_tasks_locks_taxonomya70ca2.resource_id = '10' OR foreman_tasks_locks_taxonomya70ca2.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy002269.resource_id = '11' OR foreman_tasks_locks_taxonomy002269.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy25d10d.resource_id = '12' OR foreman_tasks_locks_taxonomy25d10d.resource_id IS NULL) OR (foreman_tasks_locks_taxonomyb55bd1.resource_id = '5' OR foreman_tasks_locks_taxonomyb55bd1.resource_id IS NULL) OR (foreman_tasks_locks_taxonomyae8159.resource_id = '14' OR foreman_tasks_locks_taxonomyae8159.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy661f6e.resource_id = '13' OR foreman_tasks_locks_taxonomy661f6e.resource_id IS NULL) OR (foreman_tasks_locks_taxonomya51eda.resource_id = '20' OR foreman_tasks_locks_taxonomya51eda.resource_id IS NULL) OR (foreman_tasks_locks_taxonomyd00170.resource_id = '1' OR foreman_tasks_locks_taxonomyd00170.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy535468.resource_id = '3' OR foreman_tasks_locks_taxonomy535468.resource_id IS NULL)) AND ((foreman_tasks_locks_taxonomyd902c3.resource_id = '23' OR foreman_tasks_locks_taxonomyd902c3.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy5ee8f4.resource_id = '19' OR foreman_tasks_locks_taxonomy5ee8f4.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy896e69.resource_id = '2' OR foreman_tasks_locks_taxonomy896e69.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy9be3ca.resource_id = '21' OR foreman_tasks_locks_taxonomy9be3ca.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy287352.resource_id = '16' OR foreman_tasks_locks_taxonomy287352.resource_id IS NULL) OR (foreman_tasks_locks_taxonomy187d79.resource_id = '18' OR foreman_tasks_locks_taxonomy187d79.resource_id IS NULL) OR (foreman_tasks_locks_taxonomye59625.resource_id = '17' OR foreman_tasks_locks_taxonomye59625.resource_id IS NULL))))) AND ((foreman_tasks_locks_taxonomy98e6c9.resource_id = '5' OR foreman_tasks_locks_taxonomy98e6c9.resource_id IS NULL)) ORDER BY "foreman_tasks_tasks"."started_at" DESC NULLS LAST LIMIT $1 OFFSET $2
Back
Version-Release number of selected component (if applicable): 6.5.0


Actual results: Monitor -> Tasks gets hanged and disk space filled up


Expected results: Task page should be loaded within time
Comment 9 Tomer Brisker 2020-09-08 07:27:45 UTC 
Looking at the database, looks like there are a lot of tasks and locks:

> ForemanTasks::Task.count
=> 51758
> ForemanTasks::Lock.count
=> 115517

This might also be related to the number of locations and organizations, causing the join query to get out of hand:
> Taxonomy.count
=> 21

The relevant part of the stack trace is:
/opt/theforeman/tfm/root/usr/share/gems/gems/foreman-tasks-0.17.5.7/app/models/foreman_tasks/task/summarizer.rb:92:in `group_by'
/opt/theforeman/tfm/root/usr/share/gems/gems/foreman-tasks-0.17.5.7/app/models/foreman_tasks/task/summarizer.rb:92:in `add_to_summary'
/opt/theforeman/tfm/root/usr/share/gems/gems/foreman-tasks-0.17.5.7/app/models/foreman_tasks/task/summarizer.rb:81:in `summary'
/opt/theforeman/tfm/root/usr/share/gems/gems/foreman-tasks-0.17.5.7/app/controllers/foreman_tasks/tasks_controller.rb:20:in `summary'


While the server shouldn't crash even in such a case, a possible workaround until this is fixed is to clean up old tasks.
Comment 10 Ondřej Ezr 2020-09-08 11:02:56 UTC 
Created redmine issue https://projects.theforeman.org/issues/30797 from this bug
Comment 11 Bryan Kearney 2020-09-08 12:06:20 UTC 
Upstream bug assigned to oezr
Comment 12 Bryan Kearney 2020-09-08 12:06:23 UTC 
Upstream bug assigned to oezr
Comment 13 Adam Ruzicka 2020-09-16 06:29:48 UTC 
Upstream PR was merged, moving to POST
Comment 15 Peter Ondrejka 2021-03-12 10:34:33 UTC 
Verified on Satellite 6.9 snap 17 with 20+ organizations, no postgres spikes recorded
Comment 18 errata-xmlrpc 2021-04-21 13:11:48 UTC 
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 (Moderate: Satellite 6.9 Release), 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/RHSA-2021:1313