Bug 1079093 - [SLOW QUERY] Watchdog kill time
Summary: [SLOW QUERY] Watchdog kill time
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Beaker
Classification: Community
Component: general
Version: 0.15
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified vote
Target Milestone: 0.17.1
Assignee: Raymond Mancy
QA Contact: tools-bugs
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-03-21 01:08 UTC by Raymond Mancy
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-07-18 08:17:44 UTC


Attachments (Terms of Use)

Description Raymond Mancy 2014-03-21 01:08:14 UTC
The following query runs quite slowly.

# Time: 140301 11:51:08
# User@Host: beaker[beaker] @ localhost [127.0.0.67]
# Query_time: 18  Lock_time: 0  Rows_sent: 37  Rows_examined: 1046418
SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time 
FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id 
WHERE 11 = recipe_set.lab_controller_id AND watchdog.kill_time IS NOT NULL AND recipe_set.id IN (SELECT recipe_set.id AS recipe_set_id 
FROM recipe_set INNER JOIN recipe ON recipe_set.id = recipe.recipe_set_id INNER JOIN watchdog ON recipe.id = watchdog.recipe_id GROUP BY recipe_set.id 
HAVING max(watchdog.kill_time) > '2014-03-01 16:50:50');

Comment 2 Raymond Mancy 2014-05-26 02:32:35 UTC
An alternative and much faster query is the following:

SELECT watchdog.id FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id  WHERE 9 = recipe_set.lab_controller_id AND watchdog.kill_time IS NOT NULL AND EXISTS (SELECT 1 FROM recipe AS r2 INNER JOIN watchdog ON watchdog.recipe_id = r2.id WHERE r2.recipe_set_id = recipe_set.id GROUP BY r2.recipe_set_id HAVING max(watchdog.kill_time) > '2014-03-01 16:50:50')

Comment 3 Raymond Mancy 2014-05-26 06:19:48 UTC
http://gerrit.beaker-project.org/#/c/3104/

Comment 6 Dan Callaghan 2014-06-11 22:40:04 UTC
For future reference: with this bug fix, the query Beaker produces is not exactly the same as the one given in comment 2. Specifically the GROUP BY and HAVING have been replaced with a WHERE clause.

Here is an example:

SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time 
FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id 
WHERE watchdog.kill_time IS NOT NULL AND (EXISTS (SELECT 1 
FROM recipe INNER JOIN watchdog ON recipe.id = watchdog.recipe_id 
WHERE recipe.recipe_set_id = recipe_set.id AND watchdog.kill_time > '2014-06-11 22:37:15')) AND 15 = recipe_set.lab_controller_id;

When checking the EXPLAIN output for this query it would be important to use a production database dump and a valid lab controller ID in the WHERE clause, in order to select a realistic number of rows.

Comment 7 xuezhi ma 2014-06-12 04:17:22 UTC
Verified:

With Dan's help, check the response with the new sql, it is faster than before:
Old sql
SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time  FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id  WHERE 8 = recipe_set.lab_controller_id AND watchdog.kill_time IS NOT NULL AND recipe_set.id IN (SELECT recipe_set.id AS recipe_set_id  FROM recipe_set INNER JOIN recipe ON recipe_set.id = recipe.recipe_set_id INNER JOIN watchdog ON recipe.id = watchdog.recipe_id GROUP BY recipe_set.id  HAVING max(watchdog.kill_time) < '2014-06-12 16:50:50');
+-------------+--------------------+------------------------+------------------+---------------------+
| watchdog_id | watchdog_recipe_id | watchdog_recipetask_id | watchdog_subtask | watchdog_kill_time  |
+-------------+--------------------+------------------------+------------------+---------------------+
|      888865 |             963015 |               14150462 | NULL             | 2014-01-11 18:20:01 |
...
+-------------+--------------------+------------------------+------------------+---------------------+
289 rows in set (0.79 sec)

New sql:
SELECT watchdog.id AS watchdog_id, watchdog.recipe_id AS watchdog_recipe_id, watchdog.recipetask_id AS watchdog_recipetask_id, watchdog.subtask AS watchdog_subtask, watchdog.kill_time AS watchdog_kill_time FROM watchdog INNER JOIN recipe ON recipe.id = watchdog.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id  WHERE watchdog.kill_time IS NOT NULL AND (EXISTS (SELECT 1 FROM recipe INNER JOIN watchdog ON recipe.id = watchdog.recipe_id WHERE recipe.recipe_set_id = recipe_set.id AND watchdog.kill_time < '2014-06-12 16:50:50')) AND 8 = recipe_set.lab_controller_id;
+-------------+--------------------+------------------------+------------------+---------------------+
| watchdog_id | watchdog_recipe_id | watchdog_recipetask_id | watchdog_subtask | watchdog_kill_time  |
+-------------+--------------------+------------------------+------------------+---------------------+
|      888865 |             963015 |               14150462 | NULL             | 2014-01-11 18:20:01 |
...
+-------------+--------------------+------------------------+------------------+---------------------+
289 rows in set (0.10 sec)


0.10 sec is better than 0.79 sec.

Change it to verified

Comment 8 Dan Callaghan 2014-07-18 08:17:44 UTC
Beaker 0.17.1 has been released.


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