Bug 1297609 - [SLOW QUERY] beaker-usage-reminder: open reservations on in-demand systems
[SLOW QUERY] beaker-usage-reminder: open reservations on in-demand systems
Status: NEW
Product: Beaker
Classification: Community
Component: scheduler (Show other bugs)
21
Unspecified Unspecified
unspecified Severity unspecified (vote)
: ---
: ---
Assigned To: beaker-dev-list
tools-bugs
: FutureFeature
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2016-01-11 20:48 EST by Dan Callaghan
Modified: 2016-01-11 20:48 EST (History)
3 users (show)

See Also:
Fixed In Version:
Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Dan Callaghan 2016-01-11 20:48:27 EST
The following query, from beaker-usage-reminder's open_in_demand_systems routine, shows up in our slow query logs:

Count: 834480  Time=0.02s (13407s)  Lock=0.00s (75s)  Rows_sent=0.0 (24569), Rows_examined=19369.8 (16163676217), beaker[beaker]@localhost
  SELECT reservation.start_time AS reservation_start_time, anon_1.waiting_recipes_count AS anon_1_waiting_recipes_count, system.fqdn AS system_fqdn 
  FROM reservation INNER JOIN system ON system.id = reservation.system_id INNER JOIN (SELECT system.id AS id, count(system.id) AS waiting_recipes_count 
  FROM system INNER JOIN system_recipe_map AS system_recipe_map_1 ON system.id = system_recipe_map_1.system_id INNER JOIN recipe ON recipe.id = system_recipe_map_1.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id INNER JOIN job ON job.id = recipe_se
t.job_id 
  WHERE recipe.status = 'S' AND recipe_set.queue_time <= 'S' AND job.deleted IS NULL GROUP BY system.id) AS anon_1 ON reservation.system_id = anon_1.id 
  WHERE N = reservation.user_id AND reservation.start_time <= 'S' AND reservation.finish_time IS NULL

The explain indicates it's using a temp table filesort for the derived table:

MySQL [beaker_dcallagh]> explain SELECT reservation.start_time AS reservation_start_time, anon_1.waiting_recipes_count AS anon_1_waiting_recipes_count, system.fqdn AS system_fqdn    FROM reservation INNER JOIN system ON system.id = reservation.system_id INNER JOIN (SELECT system.id AS id, count(system.id) AS waiting_recipes_count    FROM system INNER JOIN system_recipe_map AS system_recipe_map_1 ON system.id = system_recipe_map_1.system_id INNER JOIN recipe ON recipe.id = system_recipe_map_1.recipe_id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id INNER JOIN job ON job.id = recipe_set.job_id    WHERE recipe.status = 'Queued' AND recipe_set.queue_time <= '2016-01-01 00:00:00' AND job.deleted IS NULL GROUP BY system.id) AS anon_1 ON reservation.system_id = anon_1.id    WHERE 1104 = reservation.user_id AND reservation.start_time <= '2016-01-01 00:00:00' AND reservation.finish_time IS NULL;
+----+-------------+---------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+-----------------------------------------------+------+------------------------------------------------------------------+
| id | select_type | table               | type        | possible_keys                                                          | key                                | key_len | ref                                           | rows | Extra                                                            |
+----+-------------+---------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+-----------------------------------------------+------+------------------------------------------------------------------+
|  1 | PRIMARY     | reservation         | index_merge | system_id,user_id,ix_reservation_start_time,ix_reservation_finish_time | user_id,ix_reservation_finish_time | 4,9     | NULL                                          |    1 | Using intersect(user_id,ix_reservation_finish_time); Using where |
|  1 | PRIMARY     | <derived2>          | ALL         | NULL                                                                   | NULL                               | NULL    | NULL                                          |  585 | Using where                                                      |
|  1 | PRIMARY     | system              | eq_ref      | PRIMARY                                                                | PRIMARY                            | 4       | beaker_dcallagh.reservation.system_id         |    1 |                                                                  |
|  2 | DERIVED     | recipe              | ref         | PRIMARY,recipe_set_id,status                                           | status                             | 1       |                                               |  314 | Using where; Using temporary; Using filesort                     |
|  2 | DERIVED     | system_recipe_map_1 | ref         | PRIMARY,recipe_id                                                      | recipe_id                          | 4       | beaker_dcallagh.recipe.id                     |    8 | Using index                                                      |
|  2 | DERIVED     | system              | eq_ref      | PRIMARY                                                                | PRIMARY                            | 4       | beaker_dcallagh.system_recipe_map_1.system_id |    1 | Using index                                                      |
|  2 | DERIVED     | recipe_set          | eq_ref      | PRIMARY,job_id                                                         | PRIMARY                            | 4       | beaker_dcallagh.recipe.recipe_set_id          |    1 | Using where                                                      |
|  2 | DERIVED     | job                 | eq_ref      | PRIMARY,ix_job_deleted                                                 | PRIMARY                            | 4       | beaker_dcallagh.recipe_set.job_id             |    1 | Using where                                                      |
+----+-------------+---------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+-----------------------------------------------+------+------------------------------------------------------------------+
8 rows in set (0.02 sec)

We can simplify the query to use a dependent subquery instead, which gets us a nicer-looking EXPLAIN (although it might still end up in the slow query log due to some parts not using indexes):

MySQL [beaker_dcallagh]> explain SELECT system.fqdn, reservation.start_time, (SELECT COUNT(*) FROM system_recipe_map INNER JOIN recipe ON system_recipe_map.recipe_id = recipe.id INNER JOIN recipe_set ON recipe_set.id = recipe.recipe_set_id INNER JOIN job ON job.id = recipe_set.job_id WHERE system_recipe_map.system_id = system.id AND recipe.status = 'Queued' AND recipe_set.queue_time <= '2016-01-01 00:00:00' AND job.deleted IS NULL) AS waiting_recipes_count FROM system INNER JOIN reservation ON reservation.system_id = system.id AND reservation.finish_time IS NULL WHERE reservation.user_id = 1104 AND reservation.start_time <= '2016-01-01 00:00:00';
+----+--------------------+-------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+---------------------------------------------+------+------------------------------------------------------------------+
| id | select_type        | table             | type        | possible_keys                                                          | key                                | key_len | ref                                         | rows | Extra                                                            |
+----+--------------------+-------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+---------------------------------------------+------+------------------------------------------------------------------+
|  1 | PRIMARY            | reservation       | index_merge | system_id,user_id,ix_reservation_start_time,ix_reservation_finish_time | user_id,ix_reservation_finish_time | 4,9     | NULL                                        |    1 | Using intersect(user_id,ix_reservation_finish_time); Using where |
|  1 | PRIMARY            | system            | eq_ref      | PRIMARY                                                                | PRIMARY                            | 4       | beaker_dcallagh.reservation.system_id       |    1 |                                                                  |
|  2 | DEPENDENT SUBQUERY | system_recipe_map | ref         | PRIMARY,recipe_id                                                      | PRIMARY                            | 4       | beaker_dcallagh.system.id                   |    6 | Using index                                                      |
|  2 | DEPENDENT SUBQUERY | recipe            | eq_ref      | PRIMARY,recipe_set_id,status                                           | PRIMARY                            | 4       | beaker_dcallagh.system_recipe_map.recipe_id |    1 | Using where                                                      |
|  2 | DEPENDENT SUBQUERY | recipe_set        | eq_ref      | PRIMARY,job_id                                                         | PRIMARY                            | 4       | beaker_dcallagh.recipe.recipe_set_id        |    1 | Using where                                                      |
|  2 | DEPENDENT SUBQUERY | job               | eq_ref      | PRIMARY,ix_job_deleted                                                 | PRIMARY                            | 4       | beaker_dcallagh.recipe_set.job_id           |    1 | Using where                                                      |
+----+--------------------+-------------------+-------------+------------------------------------------------------------------------+------------------------------------+---------+---------------------------------------------+------+------------------------------------------------------------------+
6 rows in set (0.00 sec)

This version of the query is pulling systems joined to their open reservations with a subquery for counting the number of waiting recipes. We need to remember to filter out rows with 0 waiting recipes in Python land (that's done in SQL land in the current version of the query, but MySQL can't reference a dependency subquery in a WHERE condition so we can't do it in this proposed new version).

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