Optimize StuckCiJobsWorker running builds query
What does this MR do and why?
In !68891 (merged) we merged a fix that was reverted due to some failing tests.
I believe the tests were flaky because created_at
was not set only updated_at
. This MR sets created_at
in those tests. It also freezes time.
Database
explain SELECT "ci_builds".*
FROM "ci_builds"
WHERE "ci_builds"."type" = 'Ci::Build'
AND "ci_builds"."id"
IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE
"ci_builds"."type" = 'Ci::Build'
AND ("ci_builds"."status" IN ('running'))
AND (ci_builds.created_at < '2021-08-24 17:41:01.993524')
order by created_at, project_id
) AND (updated_at < '2021-08-24 17:41:01.994189') limit 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
Limit (cost=472222.26..472594.62 rows=100 width=1289) (actual time=2.670..2.672 rows=0 loops=1)
Buffers: shared hit=821
-> Nested Loop (cost=472222.26..1629886.42 rows=310895 width=1289) (actual time=2.669..2.670 rows=0 loop
s=1)
Buffers: shared hit=821
-> HashAggregate (cost=472221.68..475475.50 rows=325382 width=4) (actual time=0.828..2.394 rows=30
loops=1)
Group Key: ci_builds_1.id
Buffers: shared hit=665
-> Index Scan using ci_builds_gitlab_monitor_metrics on ci_builds ci_builds_1 (cost=0.58..46
8154.40 rows=325382 width=16) (actual time=0.031..0.801 rows=30 loops=1)
Index Cond: (((status)::text = 'running'::text) AND (created_at < '2021-08-24 17:41:01.9
93524'::timestamp without time zone))
Buffers: shared hit=665
-> Index Scan using ci_builds_pkey on ci_builds (cost=0.58..3.55 rows=1 width=1289) (actual time=0
.009..0.009 rows=0 loops=30)
Index Cond: (id = ci_builds_1.id)
Filter: ((updated_at < '2021-08-24 17:41:01.994189'::timestamp without time zone) AND ((type):
:text = 'Ci::Build'::text))
Rows Removed by Filter: 1
Buffers: shared hit=156
Planning Time: 0.818 ms
Execution Time: 10.773 ms
(17 rows)
Time: 13.633 ms
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Allison Browne