Bug 1381352
| 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: | Lukas Pramuk <lpramuk> | |
| Severity: | medium | Docs Contact: | ||
| Priority: | medium | |||
| Version: | 6.2.0 | CC: | bbuckingham, bkearney, inecas, jcallaha, ktordeur, lpramuk, oshtaier, zhunting | |
| Target Milestone: | Unspecified | Keywords: | Triaged | |
| Target Release: | Unused | |||
| Hardware: | Unspecified | |||
| OS: | Unspecified | |||
| Whiteboard: | ||||
| Fixed In Version: | tfm-rubygem-foreman-tasks-0.7.14.11-1 | Doc Type: | If docs needed, set a value | |
| Doc Text: | Story Points: | --- | ||
| Clone Of: | ||||
| : | 1394374 (view as bug list) | Environment: | ||
| Last Closed: | 2016-12-12 17:14:08 UTC | Type: | --- | |
| Regression: | --- | Mount Type: | --- | |
| Documentation: | --- | CRM: | ||
| Verified Versions: | Category: | --- | ||
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | ||
| Cloudforms Team: | --- | Target Upstream Version: | ||
| Embargoed: | ||||
| Bug Depends On: | ||||
| Bug Blocks: | 1394374 | |||
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 |
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.