Add count of all alert issues to usage ping
What does this MR do?
Issue: #217680 (closed)
Adds a count of alerts from all sources (the new alert management feature and the old prometheus alerting features) created either manually or automatically.
Explain plans for database and telemetry review:
explain SELECT MIN("issues"."id") FROM "issues" INNER JOIN "issues_self_managed_prometheus_alert_events" ON "issues_self_managed_prometheus_alert_events"."issue_id" = "issues"."id"
Time: 46.595 ms
- planning: 0.359 ms
- execution: 46.236 ms
- I/O read: 45.663 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 61 (~488.00 KiB) from the buffer pool
- reads: 27 (~216.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=42.39..42.40 rows=1 width=4) (actual time=46.120..46.121 rows=1 loops=1)
Buffers: shared hit=61 read=27
I/O Timings: read=45.663
-> Nested Loop (cost=0.70..42.35 rows=18 width=4) (actual time=16.200..46.094 rows=18 loops=1)
Buffers: shared hit=61 read=27
I/O Timings: read=45.663
-> Index Only Scan using issue_id_self_managed_prometheus_alert_event_id_index on public.issues_self_managed_prometheus_alert_events (cost=0.14..3.41 rows=18 width=8) (actual time=0.020..0.037 rows=18 loops=1)
Heap Fetches: 0
Buffers: shared hit=2
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.16 rows=1 width=4) (actual time=2.556..2.556 rows=1 loops=18)
Index Cond: (issues.id = issues_self_managed_prometheus_alert_events.issue_id)
Heap Fetches: 1
Buffers: shared hit=59 read=27
I/O Timings: read=45.663
-----------
explain SELECT MAX("issues"."id") FROM "issues" INNER JOIN "issues_self_managed_prometheus_alert_events" ON "issues_self_managed_prometheus_alert_events"."issue_id" = "issues"."id"
Time: 0.526 ms
- planning: 0.357 ms
- execution: 0.169 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 85 (~680.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=42.39..42.40 rows=1 width=4) (actual time=0.130..0.130 rows=1 loops=1)
Buffers: shared hit=85
-> Nested Loop (cost=0.70..42.35 rows=18 width=4) (actual time=0.047..0.123 rows=18 loops=1)
Buffers: shared hit=85
-> Index Only Scan using issue_id_self_managed_prometheus_alert_event_id_index on public.issues_self_managed_prometheus_alert_events (cost=0.14..3.41 rows=18 width=8) (actual time=0.022..0.026 rows=18 loops=1)
Heap Fetches: 0
Buffers: shared hit=2
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.16 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=18)
Index Cond: (issues.id = issues_self_managed_prometheus_alert_events.issue_id)
Heap Fetches: 1
Buffers: shared hit=83
-------
explain SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "issues_self_managed_prometheus_alert_events" ON "issues_self_managed_prometheus_alert_events"."issue_id" = "issues"."id" WHERE "issues"."id" BETWEEN 625 AND 100624
Time: 0.595 ms
- planning: 0.466 ms
- execution: 0.129 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 8 (~64.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=42.44..42.45 rows=1 width=8) (actual time=0.085..0.085 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.70..42.44 rows=1 width=4) (actual time=0.083..0.083 rows=0 loops=1)
Buffers: shared hit=8
-> Index Only Scan using issue_id_self_managed_prometheus_alert_event_id_index on public.issues_self_managed_prometheus_alert_events (cost=0.14..3.41 rows=18 width=8) (actual time=0.009..0.013 rows=18 loops=1)
Heap Fetches: 0
Buffers: shared hit=2
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.17 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=18)
Index Cond: ((issues.id = issues_self_managed_prometheus_alert_events.issue_id) AND (issues.id >= 625) AND (issues.id <= 100624))
Heap Fetches: 0
Buffers: shared hit=6
explain SELECT MIN("issues"."id") FROM "issues" INNER JOIN "issues_prometheus_alert_events" ON "issues_prometheus_alert_events"."issue_id" = "issues"."id"
Time: 10.543 ms
- planning: 0.434 ms
- execution: 10.109 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 13837 (~108.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=6204.44..6204.45 rows=1 width=4) (actual time=10.072..10.072 rows=1 loops=1)
Buffers: shared hit=13837
-> Nested Loop (cost=0.84..6197.11 rows=2935 width=4) (actual time=0.056..9.622 rows=2944 loops=1)
Buffers: shared hit=13837
-> Index Only Scan using issue_id_prometheus_alert_event_id_index on public.issues_prometheus_alert_events (cost=0.28..72.81 rows=2935 width=8) (actual time=0.038..0.557 rows=2944 loops=1)
Heap Fetches: 90
Buffers: shared hit=20
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.09 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2944)
Index Cond: (issues.id = issues_prometheus_alert_events.issue_id)
Heap Fetches: 48
Buffers: shared hit=13817
------
explain SELECT MAX("issues"."id") FROM "issues" INNER JOIN "issues_prometheus_alert_events" ON "issues_prometheus_alert_events"."issue_id" = "issues"."id"
Time: 12.773 ms
- planning: 0.546 ms
- execution: 12.227 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 13837 (~108.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=6204.44..6204.45 rows=1 width=4) (actual time=12.186..12.186 rows=1 loops=1)
Buffers: shared hit=13837
-> Nested Loop (cost=0.84..6197.11 rows=2935 width=4) (actual time=0.043..11.681 rows=2944 loops=1)
Buffers: shared hit=13837
-> Index Only Scan using issue_id_prometheus_alert_event_id_index on public.issues_prometheus_alert_events (cost=0.28..72.81 rows=2935 width=8) (actual time=0.020..0.646 rows=2944 loops=1)
Heap Fetches: 90
Buffers: shared hit=20
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.09 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2944)
Index Cond: (issues.id = issues_prometheus_alert_events.issue_id)
Heap Fetches: 48
Buffers: shared hit=13817
-------
explain SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "issues_prometheus_alert_events" ON "issues_prometheus_alert_events"."issue_id" = "issues"."id" WHERE "issues"."id" BETWEEN 2 AND 100001
Time: 33.335 ms
- planning: 0.618 ms
- execution: 32.717 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 41837 (~326.90 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=3576.06..3576.07 rows=1 width=8) (actual time=32.668..32.669 rows=1 loops=1)
Buffers: shared hit=41837
-> Merge Join (cost=2484.99..3576.04 rows=8 width=4) (actual time=32.663..32.663 rows=0 loops=1)
Merge Cond: (issues.id = issues_prometheus_alert_events.issue_id)
Buffers: shared hit=41837
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..3307.01 rows=77084 width=4) (actual time=0.018..24.217 rows=78945 loops=1)
Index Cond: ((issues.id >= 2) AND (issues.id <= 100001))
Heap Fetches: 956
Buffers: shared hit=41834
-> Index Only Scan using issue_id_prometheus_alert_event_id_index on public.issues_prometheus_alert_events (cost=0.28..72.81 rows=2935 width=8) (actual time=0.009..0.009 rows=1 loops=1)
Heap Fetches: 0
Buffers: shared hit=3
----
explain SELECT MIN("issues"."id") FROM "issues" INNER JOIN "alert_management_alerts" ON "alert_management_alerts"."issue_id" = "issues"."id"
Time: 35.504 ms
- planning: 0.472 ms
- execution: 35.032 ms
- I/O read: 27.295 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 124 (~992.00 KiB) from the buffer pool
- reads: 24 (~192.00 KiB) from the OS file cache, including disk I/O
- dirtied: 9 (~72.00 KiB)
- writes: 0
Aggregate (cost=382.34..382.35 rows=1 width=4) (actual time=34.983..34.984 rows=1 loops=1)
Buffers: shared hit=124 read=24 dirtied=9
I/O Timings: read=27.295
-> Nested Loop (cost=0.71..381.92 rows=169 width=4) (actual time=1.340..34.967 rows=11 loops=1)
Buffers: shared hit=124 read=24 dirtied=9
I/O Timings: read=27.295
-> Index Only Scan using index_alert_management_alerts_on_issue_id on public.alert_management_alerts (cost=0.14..25.90 rows=169 width=8) (actual time=0.014..19.180 rows=145 loops=1)
Heap Fetches: 139
Buffers: shared hit=80 read=11 dirtied=9
I/O Timings: read=11.839
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.11 rows=1 width=4) (actual time=0.108..0.108 rows=0 loops=145)
Index Cond: (issues.id = alert_management_alerts.issue_id)
Heap Fetches: 2
Buffers: shared hit=44 read=13
I/O Timings: read=15.456
----
explain SELECT MAX("issues"."id") FROM "issues" INNER JOIN "alert_management_alerts" ON "alert_management_alerts"."issue_id" = "issues"."id"
Time: 0.875 ms
- planning: 0.436 ms
- execution: 0.439 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 143 (~1.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=382.34..382.35 rows=1 width=4) (actual time=0.390..0.390 rows=1 loops=1)
Buffers: shared hit=143
-> Nested Loop (cost=0.71..381.92 rows=169 width=4) (actual time=0.043..0.383 rows=11 loops=1)
Buffers: shared hit=143
-> Index Only Scan using index_alert_management_alerts_on_issue_id on public.alert_management_alerts (cost=0.14..25.90 rows=169 width=8) (actual time=0.018..0.087 rows=145 loops=1)
Heap Fetches: 134
Buffers: shared hit=86
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.11 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=145)
Index Cond: (issues.id = alert_management_alerts.issue_id)
Heap Fetches: 2
Buffers: shared hit=57
-----
explain SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "alert_management_alerts" ON "alert_management_alerts"."issue_id" = "issues"."id" WHERE "issues"."id" BETWEEN 621 AND 100620
Time: 0.688 ms
- planning: 0.492 ms
- execution: 0.196 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 86 (~688.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Aggregate (cost=382.76..382.77 rows=1 width=8) (actual time=0.157..0.157 rows=1 loops=1)
Buffers: shared hit=86
-> Nested Loop (cost=0.71..382.76 rows=1 width=4) (actual time=0.155..0.155 rows=0 loops=1)
Buffers: shared hit=86
-> Index Only Scan using index_alert_management_alerts_on_issue_id on public.alert_management_alerts (cost=0.14..25.90 rows=169 width=8) (actual time=0.014..0.079 rows=145 loops=1)
Heap Fetches: 134
Buffers: shared hit=86
-> Index Only Scan using issues_pkey on public.issues (cost=0.56..2.11 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=145)
Index Cond: ((issues.id = alert_management_alerts.issue_id) AND (issues.id >= 621) AND (issues.id <= 100620))
Heap Fetches: 0Screenshots
Does this MR meet the acceptance criteria?
Conformity
- [-] Changelog entry
-
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec - [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Rémy Coutable