Draft: Add usage ping metrics for releases with milestones and group milestones
requested to merge 235798-add-telemetry-tracking-for-users-associating-group-milestones-to-releases into master
What does this MR do?
It adds 2 metrics to service ping:
- unique users creating releases with associated milestones
- unique users creating releases with group milestones
Related to #235798 (closed)
Screenshots or Screencasts (strongly suggested)
Does this MR meet the acceptance criteria?
Conformity
-
I have included changelog trailers, or none are needed. (Does this MR need a changelog?) -
I have added/updated documentation, or it's not needed. (Is documentation required?) -
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) -
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides. -
This change is backwards compatible across updates, or this does not apply.
Database queries
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5511/commands/18966:
explain SELECT COUNT(DISTINCT "releases"."author_id") FROM "releases" INNER JOIN "milestone_releases" ON "milestone_releases"."release_id" = "releases"."id" INNER JOIN "milestones" ON "milestones"."id" = "milestone_releases"."milestone_id" WHERE "milestones"."group_id" IS NOT NULL AND "releases"."created_at" BETWEEN '2021-06-29 10:14:41.418623' AND '2021-07-27 10:14:41.418806';
Time: 342.508 ms
- planning: 3.336 ms
- execution: 339.172 ms
- I/O read: 537.029 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 42081 (~328.80 MiB) from the buffer pool
- reads: 17186 (~134.30 MiB) from the OS file cache, including disk I/O
- dirtied: 314 (~2.50 MiB)
- writes: 0
Aggregate (cost=23937.91..23937.92 rows=1 width=8) (actual time=338.843..338.967 rows=1 loops=1)
Buffers: shared hit=42081 read=17186 dirtied=314
I/O Timings: read=537.029 write=0.000
-> Gather (cost=1001.14..23937.62 rows=115 width=4) (actual time=21.501..338.760 rows=112 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=42073 read=17186 dirtied=314
I/O Timings: read=537.029 write=0.000
-> Nested Loop (cost=1.14..22926.12 rows=68 width=4) (actual time=89.315..328.466 rows=56 loops=2)
Buffers: shared hit=42073 read=17186 dirtied=314
I/O Timings: read=537.029 write=0.000
-> Nested Loop (cost=0.72..22247.31 rows=292 width=12) (actual time=28.618..309.213 rows=434 loops=2)
Buffers: shared hit=39529 read=16256 dirtied=233
I/O Timings: read=506.217 write=0.000
-> Parallel Index Only Scan using milestone_releases_pkey on public.milestone_releases (cost=0.29..255.80 rows=8136 width=16) (actual time=0.059..3.923 rows=6938 loops=2)
Heap Fetches: 612
Buffers: shared hit=163 read=69 dirtied=6
I/O Timings: read=2.365 write=0.000
-> Index Scan using releases_pkey on public.releases (cost=0.43..2.70 rows=1 width=8) (actual time=0.043..0.043 rows=0 loops=13875)
Index Cond: (releases.id = milestone_releases.release_id)
Filter: ((releases.created_at >= '2021-06-29 10:14:41.418623'::timestamp without time zone) AND (releases.created_at <= '2021-07-27 10:14:41.418806'::timestamp without time zone))
Rows Removed by Filter: 1
Buffers: shared hit=39366 read=16187 dirtied=227
I/O Timings: read=503.852 write=0.000
-> Index Scan using milestones_pkey on public.milestones (cost=0.43..2.32 rows=1 width=4) (actual time=0.044..0.044 rows=0 loops=868)
Index Cond: (milestones.id = milestone_releases.milestone_id)
Filter: (milestones.group_id IS NOT NULL)
Rows Removed by Filter: 1
Buffers: shared hit=2544 read=930 dirtied=81
I/O Timings: read=30.812 write=0.000
This query is quite slow, but I don't know what timeouts we have for metrics. I assume they higher as we often need to look though the whole database.
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/5511/commands/18963:
explain SELECT COUNT(DISTINCT "releases"."author_id") FROM "releases" INNER JOIN "milestone_releases" ON "milestone_releases"."release_id" = "releases"."id" WHERE "releases"."created_at" BETWEEN '2021-06-29 10:14:29.263384' AND '2021-07-27 10:14:29.264222';
Time: 12.779 s
- planning: 1.669 ms
- execution: 12.778 s
- I/O read: 24.772 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 39534 (~308.90 MiB) from the buffer pool
- reads: 16259 (~127.00 MiB) from the OS file cache, including disk I/O
- dirtied: 233 (~1.80 MiB)
- writes: 0
Aggregate (cost=23298.25..23298.26 rows=1 width=8) (actual time=12777.219..12777.397 rows=1 loops=1)
Buffers: shared hit=39534 read=16259 dirtied=233
I/O Timings: read=24772.104 write=0.000
-> Gather (cost=1000.72..23297.01 rows=497 width=4) (actual time=276.831..12776.035 rows=868 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=39526 read=16259 dirtied=233
I/O Timings: read=24772.104 write=0.000
-> Nested Loop (cost=0.72..22247.31 rows=292 width=4) (actual time=1921.152..12678.719 rows=434 loops=2)
Buffers: shared hit=39526 read=16259 dirtied=233
I/O Timings: read=24772.104 write=0.000
-> Parallel Index Only Scan using milestone_releases_pkey on public.milestone_releases (cost=0.29..255.80 rows=8136 width=8) (actual time=5.301..69.757 rows=6938 loops=2)
Heap Fetches: 612
Buffers: shared hit=160 read=72 dirtied=6
I/O Timings: read=118.952 write=0.000
-> Index Scan using releases_pkey on public.releases (cost=0.43..2.70 rows=1 width=8) (actual time=1.815..1.815 rows=0 loops=13875)
Index Cond: (releases.id = milestone_releases.release_id)
Filter: ((releases.created_at >= '2021-06-29 10:14:29.263384'::timestamp without time zone) AND (releases.created_at <= '2021-07-27 10:14:29.264222'::timestamp without time zone))
Rows Removed by Filter: 1
Buffers: shared hit=39366 read=16187 dirtied=227
I/O Timings: read=24653.152 write=0.000
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) -
I have tested this MR in all supported browsers, or it's not needed. -
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Edited by Vladimir Shushlin