Bug 1741943 - Postgres usage spikes and disk space completely filled up, when non-admin select Monitor -> Tasks
Summary: Postgres usage spikes and disk space completely filled up, when non-admin sel...
Keywords:
Status: ON_QA
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Tasks Plugin
Version: 6.5.0
Hardware: All
OS: All
high
high
Target Milestone: 6.9.0
Assignee: Ondřej Ezr
QA Contact: Peter Ondrejka
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2019-08-16 13:52 UTC by Ranjan Kumar
Modified: 2020-11-12 12:48 UTC (History)
9 users (show)

Fixed In Version: foreman-2.3.0-0
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed:
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Foreman Issue Tracker 30847 Normal Closed Filters needs to rebuild the taxonomy_search 2020-11-26 15:51:55 UTC

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@redhat.com

Comment 12 Bryan Kearney 2020-09-08 12:06:23 UTC
Upstream bug assigned to oezr@redhat.com

Comment 13 Adam Ruzicka 2020-09-16 06:29:48 UTC
Upstream PR was merged, moving to POST


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