Draft: Add usage ping metrics for releases with milestones and group milestones (!66063) · Merge requests · GitLab.org / GitLab

Vladimir Shushlin requested to merge 235798-add-telemetry-tracking-for-users-associating-group-milestones-to-releases into master Jul 13, 2021

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 Aug 03, 2021 by Vladimir Shushlin

Draft: Add usage ping metrics for releases with milestones and group milestones