Bug 1394374

Summary:	Locks queries that include exclusive lock search can be slow
Product:	Red Hat Satellite	Reporter:	Bryan Kearney <bkearney>
Component:	Tasks Plugin	Assignee:	Ivan Necas <inecas>
Status:	CLOSED ERRATA	QA Contact:	Adam Ruzicka <aruzicka>
Severity:	medium	Docs Contact:
Priority:	medium
Version:	6.2.0	CC:	aruzicka, bbuckingham, bkearney, inecas, jcallaha, ktordeur
Target Milestone:	Unspecified	Keywords:	Triaged
Target Release:	Unused
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:	1381352	Environment:
Last Closed:	2018-02-21 16:49:54 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Bug Depends On:	1381352
Bug Blocks:

Description Bryan Kearney 2016-11-11 20:07:56 UTC

+++ This bug was initially created as a clone of Bug #1381352 +++

When a locking query includes limit on 'exclusive' flag, the execution plan
can be inefficient due to missing index.

<pre>
foreman=# explain analyze SELECT COUNT(*) FROM "foreman_tasks_locks"
   INNER JOIN "foreman_tasks_tasks" ON "foreman_tasks_tasks"."id" = "foreman_tasks_locks"."task_id" WHERE (foreman_tasks_tasks.state != 'stopped')
   AND ("foreman_tasks_locks"."task_id" NOT IN ('8abb9ca3-815f-49ea-b97b-6aa505f2a29a')) AND "foreman_tasks_locks"."name" = 'task_owner' AND
   "foreman_tasks_locks"."resource_id" = 1 AND "foreman_tasks_locks"."resource_type" = 'User' AND "foreman_tasks_locks"."exclusive" = 't';
</pre>

This sql is generated when locks are getting checked when starting a new task, that requires exclusive lock.

I've also noticed we use (COUNT(*)), while we are only interested into whether any such a record exist or not, so
using `exists?` instead of `any?` in https://github.com/theforeman/foreman-tasks/blob/4868e9df20c86fb54ab182fca4e7c1dd8fd8b6d0/app/models/foreman_tasks/lock.rb#L55
would be a bit more efficient. The most important thing though is the index here.

--- Additional comment from bkearney on 20161003T19:37:59

Created from redmine issue http://projects.theforeman.org/issues/16779

--- Additional comment from bkearney on 20161003T19:38:02

Upstream bug assigned to inecas

--- Additional comment from bkearney on 20161101T14:01:09

Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/16779 has been resolved.

Comment 2 Bryan Kearney 2016-11-11 21:20:33 UTC

Upstream bug assigned to inecas

Comment 3 Bryan Kearney 2016-11-11 21:20:35 UTC

Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/16779 has been resolved.

Comment 4 Adam Ruzicka 2017-08-02 13:19:24 UTC

Verified.
Satellite-6.3 Snap 9
Package versions:
satellite-6.3.0-16.0.beta.el7sat.noarch
tfm-rubygem-foreman-tasks-0.9.4-1.fm1_15.el7sat.noarch

Indexes for foreman_tasks_tasks and foreman_tasks_locks are created properly.

foreman=> \d foreman_tasks_tasks;
            Table "public.foreman_tasks_tasks"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 id             | character varying           | not null
 type           | character varying           | not null
 label          | character varying           |
 started_at     | timestamp without time zone |
 ended_at       | timestamp without time zone |
 state          | character varying           | not null
 result         | character varying           | not null
 external_id    | character varying           |
 parent_task_id | character varying           |
 start_at       | timestamp without time zone |
 start_before   | timestamp without time zone |
Indexes:
    "index_foreman_tasks_id_state" btree (id, state)
    "index_foreman_tasks_tasks_on_ended_at" btree (ended_at)
    "index_foreman_tasks_tasks_on_external_id" btree (external_id)
    "index_foreman_tasks_tasks_on_id" btree (id)
    "index_foreman_tasks_tasks_on_label" btree (label)
    "index_foreman_tasks_tasks_on_parent_task_id" btree (parent_task_id)
    "index_foreman_tasks_tasks_on_result" btree (result)
    "index_foreman_tasks_tasks_on_start_at" btree (start_at)
    "index_foreman_tasks_tasks_on_start_before" btree (start_before)
    "index_foreman_tasks_tasks_on_started_at" btree (started_at)
    "index_foreman_tasks_tasks_on_state" btree (state)
    "index_foreman_tasks_tasks_on_type" btree (type)
    "index_foreman_tasks_tasks_on_type_and_label" btree (type, label)

foreman=> \d foreman_tasks_locks;
                                  Table "public.foreman_tasks_locks"
    Column     |       Type        |                            Modifiers
---------------+-------------------+------------------------------------------------------------------
 id            | integer           | not null default nextval('foreman_tasks_locks_id_seq'::regclass)
 task_id       | character varying | not null
 name          | character varying | not null
 resource_type | character varying |
 resource_id   | integer           |
 exclusive     | boolean           |
Indexes:
    "foreman_tasks_locks_pkey" PRIMARY KEY, btree (id)
    "index_foreman_tasks_locks_name_resource_type_resource_id" btree (name, resource_type, resource_id)
    "index_foreman_tasks_locks_on_exclusive" btree (exclusive)
    "index_foreman_tasks_locks_on_name" btree (name)
    "index_foreman_tasks_locks_on_resource_type" btree (resource_type)
    "index_foreman_tasks_locks_on_resource_type_and_resource_id" btree (resource_type, resource_id)
    "index_foreman_tasks_locks_on_task_id" btree (task_id)

Comment 5 Satellite Program 2018-02-21 16:49:54 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, 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-2018:0336