Bug 1062469 - Add missing index for command_queue.status field
Summary: Add missing index for command_queue.status field
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Beaker
Classification: Retired
Component: general
Version: 0.15
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: 0.16
Assignee: Dan Callaghan
QA Contact: tools-bugs
URL:
Whiteboard:
: 1079094 (view as bug list)
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-02-07 01:04 UTC by Nick Coghlan
Modified: 2018-02-06 00:41 UTC (History)
7 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2014-03-17 03:01:56 UTC
Embargoed:


Attachments (Terms of Use)

Description Nick Coghlan 2014-02-07 01:04:44 UTC
There is no index defined for the command_queue.status field, which results in unnecessarily slow queries when lab controllers look up their current list of pending requests.

Comment 1 Dan Callaghan 2014-02-07 01:07:58 UTC
Specifically we should examine the following query from mysqld slow query log, which appears to be from labcontroller.get_queued_commands:

SELECT command_queue.status AS command_queue_status, [...]
FROM activity INNER JOIN command_queue ON activity.id = command_queue.id INNER JOIN system ON system.id = command_queue.system_id 
WHERE 8 = system.lab_controller_id AND command_queue.status = 'Queued' ORDER BY activity.id;

to verify that it is indeed picking an expensive query plan (check EXPLAIN) and that adding the index fixes it (check EXPLAIN again after adding the index).

Comment 2 Dan Callaghan 2014-02-24 04:21:35 UTC
(In reply to Dan Callaghan from comment #1)

Adding the index on command_queue.status reduces the time for this query from 29 seconds down to 0.01 seconds in my testing.

EXPLAIN before:

+----+-------------+---------------+--------+---------------------------+-------------------+---------+----------------------------------+------+-----------------------------------------------------------+
| id | select_type | table         | type   | possible_keys             | key               | key_len | ref                              | rows | Extra                                                     |
+----+-------------+---------------+--------+---------------------------+-------------------+---------+----------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | system        | ref    | PRIMARY,lab_controller_id | lab_controller_id | 5       | const                            | 2225 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | command_queue | ref    | PRIMARY,system_id         | system_id         | 4       | beaker_dcallagh.system.id        |  696 | Using where                                               |
|  1 | SIMPLE      | activity      | eq_ref | PRIMARY                   | PRIMARY           | 4       | beaker_dcallagh.command_queue.id |    1 |                                                           |
+----+-------------+---------------+--------+---------------------------+-------------------+---------+----------------------------------+------+-----------------------------------------------------------+

EXPLAIN after:

+----+-------------+---------------+--------+---------------------------+---------+---------+-----------------------------------------+------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys             | key     | key_len | ref                                     | rows | Extra                                        |
+----+-------------+---------------+--------+---------------------------+---------+---------+-----------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | command_queue | ref    | PRIMARY,system_id,status  | status  | 1       | const                                   |   25 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | system        | eq_ref | PRIMARY,lab_controller_id | PRIMARY | 4       | beaker_dcallagh.command_queue.system_id |    1 | Using where                                  |
|  1 | SIMPLE      | activity      | eq_ref | PRIMARY                   | PRIMARY | 4       | beaker_dcallagh.command_queue.id        |    1 |                                              |
+----+-------------+---------------+--------+---------------------------+---------+---------+-----------------------------------------+------+----------------------------------------------+

Comment 3 Dan Callaghan 2014-02-24 04:25:58 UTC
On Gerrit: http://gerrit.beaker-project.org/2834

Comment 5 xjia 2014-02-26 07:40:18 UTC
if no regression bug is found in acceptance test for version 0.16, will move this bug to "VERIFIED"

Comment 7 Dan Callaghan 2014-03-17 03:01:56 UTC
Beaker 0.16.0 has been released.

Comment 8 Dan Callaghan 2014-03-24 01:45:18 UTC
*** Bug 1079094 has been marked as a duplicate of this bug. ***


Note You need to log in before you can comment on or make changes to this bug.