Hide Forgot
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.
Created from redmine issue http://projects.theforeman.org/issues/16779
Upstream bug assigned to inecas
Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/16779 has been resolved.
VERIFIED. @satellite-6.2.5-1.0.el6sat.noarch tfm-rubygem-foreman-tasks-0.7.14.11-1.el6sat.noarch Used following manual reproducer: 1. Install Satellite 6.2.4 1. Populate it with data: To have enough tasks and locks I published 950 CVs, each having 35 small repositories. With this setup we have 950 CV publish tasks each having 144 exclusive locks! 2. Run SQL from comment #0: foreman=> explain analyze SELECT COUNT(*) FROM "foreman_tasks_locks"... ... Total runtime: 4.029 ms (9 rows) 3. Check CV publish task planning time Whole task started at: 2016-12-12 09:48:02 +0100 1st task action started at: 2016-12-12 08:49:48 UTC Planning time = 106 seconds !!! 4. Upgrade to Satellite 6.2.5 5. Run SQL from comment #0: foreman=> explain analyze SELECT COUNT(*) FROM "foreman_tasks_locks"... ... Total runtime: 0.208 ms (9 rows) foreman=> explain analyze SELECT COUNT('') FROM "foreman_tasks_locks"... ... Total runtime: 0.088 ms (9 rows) >>> based on altered db structures (new indices) the execution time dropped from ~ 4 sec to 0.088 sec which ~ 50 times faster 6. Check CV publish task planning time after upgrade (this step is pending, upgrade not able to finish - import_rpms taking ages) >>> I'll provide data once upgrade finises
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/RHBA-2016:2940