Project

General

Profile

Actions

Bug #16779

closed

Locks queries that include exclusive lock search can be slow

Added by Ivan Necas over 7 years ago. Updated over 5 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
-
Target version:
Difficulty:
Triaged:
Fixed in Releases:
Found in Releases:

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

Actions #1

Updated by The Foreman Bot over 7 years ago

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

Updated by Bryan Kearney over 7 years ago

  • Bugzilla link set to 1381352
Actions #3

Updated by Ivan Necas over 7 years ago

  • Description updated (diff)
Actions #4

Updated by Ivan Necas over 7 years ago

  • Target version changed from 1.5.1 to 1.4.1
Actions #5

Updated by Ivan Necas over 7 years ago

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

Also available in: Atom PDF