WIP: Instrument time period for usage ping metric incident_labeled_issues (!34312) · Merge requests · GitLab.org / GitLab

Laura Montemayor requested to merge lm-time-period-usage-ping into master Jun 10, 2020

What does this MR do?

Counts the number of issues created from alerts within a week.

database query plans with `created_at`

SQL queries produced by

Gitlab::UsageData.incident_labeled_issues_usage_data(1.week.ago)

SELECT MIN("label_links"."id") FROM "label_links" WHERE "label_links"."target_type" = 'Issue' AND (label_links.created_at >= '2020-06-24 11:07:02.292600')

SELECT MAX("label_links"."id") FROM "label_links" WHERE "label_links"."target_type" = 'Issue' AND (label_links.created_at >= '2020-06-24 11:07:02.292600')

explain SELECT COUNT("label_links"."id") FROM "label_links" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "label_links"."target_type" = 'Issue' AND "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "label_links"."id" BETWEEN 2295287 AND 2395287;

`SELECT MIN` 🐢

Takes ~10s on GitLab.com with a cold cache (first query) cache

Time: 10.625 s
  - planning: 1.491 ms
  - execution: 10.624 s
    - I/O read: 10.384 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 29400 (~229.70 MiB) from the buffer pool
  - reads: 5340 (~41.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

Click to expand

Query:

SELECT MIN("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'

Plan: https://explain.depesz.com/s/V2eK

Recommendations:

❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.

❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).

`SELECT MAX` 🏃

Takes only 39ms on GitLab.com with a warm (due to previous query) cache

Time: 39.543 ms
  - planning: 1.328 ms
  - execution: 38.215 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 34737 (~271.40 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Click to expand

Query:

SELECT MAX("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'

Plan: https://explain.depesz.com/s/9Eyf

Recommendations:

❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.

❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).

`SELECT COUNT` 🏃

Takes only 39ms on GitLab.com with a warm (due to previous query) cache

Time: 24.091 ms
  - planning: 1.400 ms
  - execution: 22.691 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5863 (~45.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Click to expand

Query:

SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678' AND "issues"."id" BETWEEN 0 AND 99999

Plan: https://explain.depesz.com/s/y7lX

Recommendations:

❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.

❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).

Previous database query plans without `created_at`

`SELECT MIN` 🐢

Takes ~13s on GitLab.com with a cold cache (first query) cache

Time: 13.344 s
  - planning: 1.190 ms
  - execution: 13.342 s
    - I/O read: 12.811 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 24167 (~188.80 MiB) from the buffer pool
  - reads: 8799 (~68.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 420 (~3.30 MiB)
  - writes: 0

Click to expand

Query:

SELECT MIN("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'

Plan: https://explain.depesz.com/s/ruRi

Recommendations:

❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.

❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).

❗ VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum).

`SELECT MAX` 🏃

Takes only 41ms on GitLab.com with a warm (due to previous query) cache

Time: 41.887 ms
  - planning: 1.745 ms
  - execution: 40.142 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 32966 (~257.50 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Click to expand

Query:

SELECT MAX("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678'

Plan: https://explain.depesz.com/s/9b5C

Recommendations:

❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.

❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).

❗ VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run VACUUM ANALYZE on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum).

`SELECT COUNT` 🏃

Takes only 13ms on GitLab.com with a warm (due to previous query) cache

Time: 13.425 ms
  - planning: 1.215 ms
  - execution: 12.210 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5863 (~45.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Click to expand

Query:

SELECT COUNT("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-08 10:02:37.402678' AND "issues"."id" BETWEEN 0 AND 99999

Plan: https://explain.depesz.com/s/vDap

Recommendations:

❗ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks.

❗ Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).

Old database query plans

Click to unfold

explain SELECT COUNT(“issues”.“id”) FROM “issues” INNER JOIN “label_links” ON “label_links”.“target_type” = ‘Issue’ AND “label_links”.“target_id” = “issues”.“id” INNER JOIN “labels” ON “labels”.“id” = “label_links”.“label_id” WHERE “labels”.“title” = ‘incident’ AND “labels”.“color” = ‘#CC0033’ AND “labels”.“description” = ‘Denotes a disruption to IT services and the associated issues require immediate attention’ AND “issues”.“created_at” >= ‘2020-06-04 19:48:44.608519’ AND “issues”.“id” BETWEEN 0 AND 99999

Time: 1.590 s
  - planning: 1.038 ms
  - execution: 1.589 s
    - I/O read: 1.557 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 355 (~2.80 MiB) from the buffer pool
  - reads: 5444 (~42.50 MiB) from the OS file cache, including disk I/O
  - dirtied: 168 (~1.30 MiB)
  - writes: 0

Aggregate  (cost=149.70..149.71 rows=1 width=8) (actual time=1589.349..1589.350 rows=1 loops=1)
   Buffers: shared hit=355 read=5444 dirtied=168
   I/O Timings: read=1557.370
   ->  Nested Loop  (cost=1.56..149.70 rows=1 width=4) (actual time=1589.345..1589.345 rows=0 loops=1)
         Buffers: shared hit=355 read=5444 dirtied=168
         I/O Timings: read=1557.370
         ->  Nested Loop  (cost=1.00..147.57 rows=3 width=4) (actual time=21.955..1582.595 rows=5704 loops=1)
               Buffers: shared hit=355 read=5444 dirtied=168
               I/O Timings: read=1557.370
               ->  Index Scan using index_labels_on_title on public.labels  (cost=0.43..68.92 rows=1 width=4) (actual time=8.431..257.055 rows=61 loops=1)
                     Index Cond: ((labels.title)::text = 'incident'::text)
                     Filter: (((labels.color)::text = '#CC0033'::text) AND ((labels.description)::text = 'Denotes a disruption to IT services and the associated issues require immediate attention'::text))
                     Rows Removed by Filter: 72
                     Buffers: shared read=137 dirtied=6
                     I/O Timings: read=255.549
               ->  Index Scan using index_label_links_on_label_id on public.label_links  (cost=0.56..77.21 rows=144 width=8) (actual time=3.342..21.693 rows=94 loops=61)
                     Index Cond: (label_links.label_id = labels.id)
                     Filter: ((label_links.target_type)::text = 'Issue'::text)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=355 read=5307 dirtied=162
                     I/O Timings: read=1301.820
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..0.71 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=5704)
               Index Cond: ((issues.id = label_links.target_id) AND (issues.id >= 0) AND (issues.id <= 99999))
               Filter: (issues.created_at >= '2020-06-04 19:48:44.608519+00'::timestamp with time zone)
               Rows Removed by Filter: 0

--------------------------------------------------------------------------------------

explain SELECT MAX("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-04 19:48:44.608519'

Time: 8.162 s
  - planning: 0.917 ms
  - execution: 8.161 s
    - I/O read: 8.022 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 25900 (~202.30 MiB) from the buffer pool
  - reads: 8456 (~66.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 64 (~512.00 KiB)
  - writes: 0

Aggregate  (cost=149.69..149.70 rows=1 width=4) (actual time=8160.579..8160.580 rows=1 loops=1)
   Buffers: shared hit=25900 read=8456 dirtied=64
   I/O Timings: read=8022.372
   ->  Nested Loop  (cost=1.56..149.69 rows=1 width=4) (actual time=15.087..8160.403 rows=117 loops=1)
         Buffers: shared hit=25900 read=8456 dirtied=64
         I/O Timings: read=8022.372
         ->  Nested Loop  (cost=1.00..147.57 rows=3 width=4) (actual time=0.046..29.460 rows=5704 loops=1)
               Buffers: shared hit=5799
               ->  Index Scan using index_labels_on_title on public.labels  (cost=0.43..68.92 rows=1 width=4) (actual time=0.031..0.540 rows=61 loops=1)
                     Index Cond: ((labels.title)::text = 'incident'::text)
                     Filter: (((labels.color)::text = '#CC0033'::text) AND ((labels.description)::text = 'Denotes a disruption to IT services and the associated issues require immediate attention'::text))
                     Rows Removed by Filter: 72
                     Buffers: shared hit=137
               ->  Index Scan using index_label_links_on_label_id on public.label_links  (cost=0.56..77.21 rows=144 width=8) (actual time=0.013..0.418 rows=94 loops=61)
                     Index Cond: (label_links.label_id = labels.id)
                     Filter: ((label_links.target_type)::text = 'Issue'::text)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=5662
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..0.70 rows=1 width=4) (actual time=1.423..1.423 rows=0 loops=5704)
               Index Cond: (issues.id = label_links.target_id)
               Filter: (issues.created_at >= '2020-06-04 19:48:44.608519+00'::timestamp with time zone)
               Rows Removed by Filter: 1
               Buffers: shared hit=20101 read=8456 dirtied=64
               I/O Timings: read=8022.372
---------------------------------------------------------------------------------------

explain SELECT MIN("issues"."id") FROM "issues" INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."target_id" = "issues"."id" INNER JOIN "labels" ON "labels"."id" = "label_links"."label_id" WHERE "labels"."title" = 'incident' AND "labels"."color" = '#CC0033' AND "labels"."description" = 'Denotes a disruption to IT services and the associated issues require immediate attention' AND "issues"."created_at" >= '2020-06-04 19:48:44.608519'

Time: 32.359 ms
  - planning: 0.979 ms
  - execution: 31.380 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 34353 (~268.40 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Aggregate  (cost=149.69..149.70 rows=1 width=4) (actual time=31.302..31.302 rows=1 loops=1)
   Buffers: shared hit=34353
   ->  Nested Loop  (cost=1.56..149.69 rows=1 width=4) (actual time=0.107..31.275 rows=117 loops=1)
         Buffers: shared hit=34353
         ->  Nested Loop  (cost=1.00..147.57 rows=3 width=4) (actual time=0.052..8.671 rows=5704 loops=1)
               Buffers: shared hit=5799
               ->  Index Scan using index_labels_on_title on public.labels  (cost=0.43..68.92 rows=1 width=4) (actual time=0.032..0.232 rows=61 loops=1)
                     Index Cond: ((labels.title)::text = 'incident'::text)
                     Filter: (((labels.color)::text = '#CC0033'::text) AND ((labels.description)::text = 'Denotes a disruption to IT services and the associated issues require immediate attention'::text))
                     Rows Removed by Filter: 72
                     Buffers: shared hit=137
               ->  Index Scan using index_label_links_on_label_id on public.label_links  (cost=0.56..77.21 rows=144 width=8) (actual time=0.006..0.119 rows=94 loops=61)
                     Index Cond: (label_links.label_id = labels.id)
                     Filter: ((label_links.target_type)::text = 'Issue'::text)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=5662
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..0.70 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=5704)
               Index Cond: (issues.id = label_links.target_id)
               Filter: (issues.created_at >= '2020-06-04 19:48:44.608519+00'::timestamp with time zone)
               Rows Removed by Filter: 1
               Buffers: shared hit=28554
Collapse

#219317 (closed)

Edited Jul 10, 2020 by Peter Leitzen

WIP: Instrument time period for usage ping metric incident_labeled_issues

What does this MR do?

database query plans with created_at

SELECT MIN 🐢

SELECT MAX 🏃

SELECT COUNT 🏃

Previous database query plans without created_at

SELECT MIN 🐢

SELECT MAX 🏃

SELECT COUNT 🏃

Old database query plans

Merge request reports

database query plans with `created_at`

`SELECT MIN` 🐢

`SELECT MAX` 🏃

`SELECT COUNT` 🏃

Previous database query plans without `created_at`

`SELECT MIN` 🐢

`SELECT MAX` 🏃

`SELECT COUNT` 🏃