Instrument usage ping: Count number of enabled integrations per project as histogram (!55782) · Merge requests · GitLab.org / GitLab

Peter Leitzen requested to merge 263532-instrument-usage-ping-count-number-of-enabled-integrations-per-project into master Mar 04, 2021

What does this MR do?

This MR create a usage ping histogram of all active alert management http integrations across projects without exposing individual project ids.

Example: amount of enabled integrations => amount of projects.

{
 "usage_activity_by_stage": {
    "monitor": {
      "projects_with_enabled_alert_integrations_histogram": {
        "1": 17,
        "2": 13,
        "3": 5,
        "7": 2  
      }
    }
  }
}

In ☝ this case we'd have 17 projects have 1 integration, 13 projects 2 integrations, 5 projects 3 integrations, and 2 projects 7 integrations enabled. We skip the blanks (amount of projects == 0).

Solves #263532 (closed).

The follow-up #323949 takes care of batching histograms.

database query plan

See https://console.postgres.ai/shared/2a604d4c-d41f-41c9-8553-628b9ba0e2b7

WITH "count_cte" AS (
    SELECT
        count(*) AS count_grouped
    FROM
        "alert_management_http_integrations"
    WHERE
        "alert_management_http_integrations"."active" = TRUE
    GROUP BY
        "alert_management_http_integrations"."project_id"
)
SELECT
    WIDTH_BUCKET("count_cte"."count_grouped", 1, 100, 99) AS buckets,
    "count_cte"."count"
FROM
    "count_cte"
GROUP BY
    buckets
ORDER BY
    buckets;

 Sort  (cost=54.74..55.24 rows=200 width=12) (actual time=12.093..12.095 rows=3 loops=1)
   Sort Key: (width_bucket((count_cte.count_grouped)::double precision, '1'::double precision, '100'::double precision, 99))
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=100 read=11 dirtied=3
   I/O Timings: read=8.838
   CTE count_cte
     ->  Aggregate  (cost=0.28..27.50 rows=553 width=16) (actual time=3.208..11.326 rows=555 loops=1)
           Group Key: alert_management_http_integrations.project_id
           Buffers: shared hit=97 read=11 dirtied=3
           I/O Timings: read=8.838
           ->  Index Only Scan using index_http_integrations_on_active_and_project_and_endpoint on public.alert_management_http_integrations  (cost=0.28..19.18 rows=560 width=8) (actual time=3.199..10.858 rows=562 loops=1)
                 Heap Fetches: 157
                 Buffers: shared hit=97 read=11 dirtied=3
                 I/O Timings: read=8.838
   ->  HashAggregate  (cost=16.59..19.59 rows=200 width=12) (actual time=12.051..12.053 rows=3 loops=1)
         Group Key: width_bucket((count_cte.count_grouped)::double precision, '1'::double precision, '100'::double precision, 99)
         Buffers: shared hit=97 read=11 dirtied=3
         I/O Timings: read=8.838
         ->  CTE Scan on count_cte  (cost=0.00..13.83 rows=553 width=36) (actual time=3.228..11.803 rows=555 loops=1)
               Buffers: shared hit=97 read=11 dirtied=3
               I/O Timings: read=8.838