Bug 1079093

Summary: [SLOW QUERY] Watchdog kill time
Product: [Community] Beaker Reporter: Raymond Mancy <rmancy>
Component: generalAssignee: Raymond Mancy <rmancy>
Status: CLOSED CURRENTRELEASE QA Contact: tools-bugs <tools-bugs>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 0.15CC: aigao, asaha, dcallagh, ebaak, rmancy, tools-bugs, xma
Target Milestone: 0.17.1   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2014-07-18 08:17:44 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:

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.