Shard heartbeat runs costly query when getting HQEs for jobs |
||||||||||
Issue descriptiontl;dr --> HQE table needs an index by job_id alone. It currently has an index by (job_id, host_id) but the shard heartbeat lists HQEs by job_id alone. ============================================================================================================================= Here's an example of a slow query from the blocked bug: Below are killed queries: Id: 25156463 User: cros-infra-admin Host: chromeos-server2.mtv.corp.google.com:58480 db: chromeos_autotest_db Command: Query Time: 300 State: Copying to tmp table Info: SELECT DISTINCT(t1.id) FROM afe_jobs t1 INNER JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1 AND NOT (t2.aborted = 0 AND t1.id IN (129592794,129592825,129592854,129592887,129592916,129593009,129593069,129593095,129593123))) LEFT OUTER JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id OR t4.label_id = t2.meta_host) WHERE t4.shard_id = 169 [ More jobs in the IN clause redacted for brevity ] Asking mysql to explain the query plan: mysql> explain SELECT DISTINCT(t1.id) FROM afe_jobs t1 INNER JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1 AND NOT (t2.aborted = 0 AND t1.id IN (129592794,129592825,129592854,129592887))) LEFT OUTER JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id OR t4.label_id = t2.meta_host) WHERE t4.shard_id = 169 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: ref possible_keys: shard_label_id_uc,shard_shard_id_fk key: shard_shard_id_fk key_len: 4 ref: const rows: 3 Extra: Using temporary *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: range possible_keys: host_queue_entries_job_id_and_host_id,host_queue_entries_meta_host,host_queue_entries_abort_incomplete,host_queue_entries_complete key: host_queue_entries_complete key_len: 2 ref: NULL rows: 169681 Extra: Using where; Using join buffer *************************** 3. row *************************** id: 1 select_type: SIMPLE table: t1 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: chromeos_autotest_db.t2.job_id rows: 1 Extra: Using index *************************** 4. row *************************** id: 1 select_type: SIMPLE table: t3 type: ref possible_keys: job_id key: job_id key_len: 4 ref: chromeos_autotest_db.t1.id rows: 2 Extra: Using where; Using index; Distinct The probelematic step is (2). It's doing a range scan of all rows in the HQE table with complete=0. The problem is that it did not use an index in the HQE table that would quickly grab the HQEs for the given jobs. That's because we don't have any index on the HQE table by job_id alone (also needs host_id): mysql> show index from afe_host_queue_entries \G *************************** 1. row *************************** Table: afe_host_queue_entries Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 17442841 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: afe_host_queue_entries Non_unique: 0 Key_name: host_queue_entries_job_id_and_host_id Seq_in_index: 1 Column_name: job_id Collation: A Cardinality: 17442841 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: afe_host_queue_entries Non_unique: 0 Key_name: host_queue_entries_job_id_and_host_id Seq_in_index: 2 Column_name: host_id Collation: A Cardinality: 17442841 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_host_id Seq_in_index: 1 Column_name: host_id Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_meta_host Seq_in_index: 1 Column_name: meta_host Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 6. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: atomic_group_id Seq_in_index: 1 Column_name: atomic_group_id Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 7. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_host_active Seq_in_index: 1 Column_name: host_id Collation: A Cardinality: 6200 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 8. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_host_active Seq_in_index: 2 Column_name: active Collation: A Cardinality: 6200 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 9. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entry_status Seq_in_index: 1 Column_name: status Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 10. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_abort_incomplete Seq_in_index: 1 Column_name: aborted Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 11. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_abort_incomplete Seq_in_index: 2 Column_name: complete Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 12. row *************************** Table: afe_host_queue_entries Non_unique: 1 Key_name: host_queue_entries_complete Seq_in_index: 1 Column_name: complete Collation: A Cardinality: 17 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment:
,
Jul 24 2017
Dan, can you take a look of this?
,
Jul 26 2017
seems the only fix is to add a KEY for job_id. hqe table is huge, it will require several hours downtime to add the key.
,
Jul 26 2017
phobbs has this magic way of creating new tables with the wanted index and grandfathering old tables gently ;) phobbs: Care to list the steps here. Then someone can follow those...
,
Jul 26 2017
Actually this is a pretty big tradeoff of code complexity + developer time for lab downtime. It's possible to do the separate index table but I'm not sure it's worth it. See: https://chromium-review.googlesource.com/c/527524/ https://chromium-review.googlesource.com/c/527330/ https://chromium-review.googlesource.com/c/527610/ https://chromium-review.googlesource.com/c/516605/ https://chromium-review.googlesource.com/c/508471/ https://chromium-review.googlesource.com/c/505175/
,
Jul 26 2017
If we added created_on bounds to what the heartbeat is willing to handle (and that seems emimently reasonable; we probably have no jobs that a shard needs to handle for more than ~7 days) then we could re-use the time-bound index that Paul already implemented.
,
Jul 26 2017
Hmm, nm, that was a created_on bound for afe_jobs, not hqes.
,
Jul 31 2017
Issue 746485 has been merged into this issue.
,
Jul 31 2017
What do you mean by #7? The new RPC in crbug.com/704997 was for hqes.
,
Sep 1 2017
Will run a migration this weekend: https://docs.google.com/document/d/1Atpzfuom8cfMXx3bwGoO8UIwG8JYV-aEcJIvDQKhkgM/edit
,
Sep 2 2017
The following revision refers to this bug: https://chromium.googlesource.com/chromiumos/third_party/autotest/+/3a9c64664f6e30e40d1807f169cc8e0e584bd9f7 commit 3a9c64664f6e30e40d1807f169cc8e0e584bd9f7 Author: Paul Hobbs <phobbs@google.com> Date: Sat Sep 02 00:15:31 2017 migrations: Add HQE indices Add various indices for optimizing slow queries that scan the HQE table. BUG= chromium:748099 TEST=Applied locally Change-Id: If869d2e8727f7beac4af1c554cbf528f815e88d8 Reviewed-on: https://chromium-review.googlesource.com/648193 Reviewed-by: Dan Shi <dshi@google.com> Tested-by: Paul Hobbs <phobbs@google.com> Commit-Queue: Paul Hobbs <phobbs@google.com> [add] https://crrev.com/3a9c64664f6e30e40d1807f169cc8e0e584bd9f7/frontend/migrations/118_add_hqe_indices.py
,
Sep 2 2017
,
Sep 2 2017
Will run a migration this weekend: https://docs.google.com/document/d/1Atpzfuom8cfMXx3bwGoO8UIwG8JYV-aEcJIvDQKhkgM/edit Bug tracking the downtime is here: https://bugs.chromium.org/p/chromium/issues/detail?id=761578
,
Sep 3 2017
The HQE indices have been created.
,
Sep 5 2017
The new query is faster, but not fast enough. Notably, it's not faster in the way we expected:
mysql> SELECT DISTINCT(t1.id) FROM afe_jobs t1 INNER JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1 AND NOT (t2.aborted = 0 AND t1.id IN (129592794,129592825,129592854,129592887))) LEFT OUTER JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id OR t4.label_id = t2.meta_host) WHERE t4.shard_id = 169;
..
1760 rows in set (9.27 sec)
I tried forcing it to not use the new INDEX (note the "USE INDEX ()" annotation on the JOIN with the HQE table):
mysql> SELECT DISTINCT(t1.id) FROM afe_jobs t1 INNER JOIN afe_host_queue_entries t2 USE INDEX () ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1 AND NOT (t2.aborted = 0 AND t1.id IN (129592794,129592825,129592854,129592887))) LEFT OUTER JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id OR t4.label_id = t2.meta_host) WHERE t4.shard_id = 169;
...
1760 rows in set (25.34 sec)
So things have improved, but not really enough. Why? Well, EXPLAIN tells all. It's using one of the new indexes: the index on `complete`.
mysql> explain SELECT DISTINCT(t1.id) FROM afe_jobs t1 INNER JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1 AND NOT (t2.aborted = 0 AND t1.id IN (129592794,129592825,129592854,129592887))) LEFT OUTER JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id OR t4.label_id = t2.meta_host) WHERE t4.shard_id = 169 \G
*************************** 1. row ***************************
table: t2
type: range
possible_keys: host_queue_entries_job_id_and_host_id,host_queue_entries_meta_host,host_queue_entries_abort_incomplete,host_queue_entries_complete,afe_host_queue_entries_active,afe_host_queue_entries_complete,afe_host_queue_entries_aborted,afe_host_queue_entries_job_id
key: afe_host_queue_entries_complete
key_len: 2
ref: NULL
rows: 787655
Extra: Using where; Using temporary
*************************** 2. row ***************************
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: chromeos_autotest_db.t2.job_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
table: t3
type: ref
possible_keys: job_id
key: job_id
key_len: 4
ref: chromeos_autotest_db.t1.id
rows: 1
Extra: Using index; Distinct
*************************** 4. row ***************************
table: t4
type: ref
possible_keys: shard_label_id_uc,shard_shard_id_fk
key: shard_shard_id_fk
key_len: 4
ref: const
rows: 3
Extra: Using where; Distinct
I tried rewriting the query to use the job_id index by ignoring the meta_hosts. The new query uses an inner join instead of a left join (see [0])... but that actually slowed things way down [1]. The new query takes 40s instead of 10s, probably because it's not using the "complete" index. Specifically,
- It's not good to find ALL jobs associated with a shard, because there's a lot of old completed jobs (6.4s just to iterate over jobs; joining with HQEs probably add another factor of 5-10x. See [2])
- It only takes 3.09s to iterate over just the non-complete HQES: [3]
[0] mysql> explain SELECT COUNT(DISTINCT(t1.id)) FROM afe_jobs t1 JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id) JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1) WHERE t4.shard_id = 169;
| table | type | key | ref | rows | Extra |
-----------------------------------------------------------------------
| t4 | ref | shard_id_fk | const | 3 | |
| t3 | ref | label_id_fk | t4.label_id | 1309 | |
| t1 | eq_ref | PRIMARY | t3.job_id | 1 | Using index |
| t2 | ref | job_and_host_id | t3.job_id | 1 | Using where |
[1]
mysql> SELECT COUNT(DISTINCT(t1.id)) FROM afe_jobs t1 JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id) JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1 AND t2.active != 1) WHERE t4.shard_id = 169;
+------------------------+
| COUNT(DISTINCT(t1.id)) |
+------------------------+
| 1622 |
+------------------------+
1 row in set (40.52 sec)
[2]
mysql> SELECT COUNT(DISTINCT(t1.id)) FROM afe_jobs t1 JOIN afe_jobs_dependency_labels t3 ON (t1.id = t3.job_id) JOIN afe_shards_labels t4 ON (t4.label_id = t3.label_id) WHERE t4.shard_id = 169;
+------------------------+
| COUNT(DISTINCT(t1.id)) |
+------------------------+
| 315192 |
+------------------------+
1 row in set (6.37 sec)
[3]
mysql> SELECT COUNT(DISTINCT(t1.id)) from afe_jobs t1 JOIN afe_host_queue_entries t2 ON (t1.id = t2.job_id AND t2.complete != 1);
+------------------------+
| COUNT(DISTINCT(t1.id)) |
+------------------------+
| 82343 |
+------------------------+
1 row in set (3.09 sec)
,
Sep 5 2017
Can we maybe resurrect the db_cleanup script on master? Fang commented on http://crosreview.com/c/329250 that it may be possible to revive it. I imagine we could just try reducing the db_cleanup script's batch size and throttling its QPS to make sure we don't overload the master db.
,
Sep 7 2017
,
Sep 14 2017
This should be addressed by the db cleanup. I'm waiting on a push to prod to perform it.
,
Mar 29 2018
Addressed by other mitigations / irrelevant after skylab.
,
Mar 30 2018
|
||||||||||
►
Sign in to add a comment |
||||||||||
Comment 1 by pprabhu@chromium.org
, Jul 24 2017Summary: Shard heartbeat runs costly query when getting HQEs for jobs (was: shard )