Bug #16779

Locks queries that include exclusive lock search can be slow

Added by Ivan Necas 11 months ago. Updated 10 months ago.

Status:Closed
Priority:Normal
Assigned To:Ivan Necas
Category:-
Target version:Foreman - Team Ivan Iteration 5
Difficulty: Bugzilla link:1381352
Found in release: Pull request:https://github.com/theforeman/foreman-tasks/pull/205
Story points-
Velocity based estimate-

Description

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

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';

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.

EDIT:

it turns out in Rails < 4.2, the way we added indexes was not working - this is prbably the reason we've seen the performance degradation lately in existing setups https://github.com/rails/rails/commit/9a0d35e820464f872b0340366dded639f00e19b9

Associated revisions

Revision 389a9573
Added by Ivan Necas 10 months ago

Fixes #16779 - add missing locks

The support for defining index at column definition was added in Rails here
https://github.com/rails/rails/commit/9a0d35e820464f872b0340366dded639f00e19b9
We need to fix migrations that happened before Rails 4.2

History

#1 Updated by The Foreman Bot 11 months ago

  • Status changed from Assigned to Ready For Testing
  • Pull request https://github.com/theforeman/foreman-tasks/pull/205 added

#2 Updated by Bryan Kearney 11 months ago

  • Bugzilla link set to 1381352

#3 Updated by Ivan Necas 11 months ago

  • Description updated (diff)

#4 Updated by Ivan Necas 10 months ago

  • Target version changed from Team Ivan Iteration 4 to Team Ivan Iteration 5

#5 Updated by Ivan Necas 10 months ago

  • Status changed from Ready For Testing to Closed
  • % Done changed from 0 to 100

Also available in: Atom PDF