Add usage ping and index for DAST On-Demand Scans

What does this MR do?

this merge request adds a usage ping for dast on-demand scans in order to determine whether the feature is being used.

Related Issue(s)

#220951 (closed)

Migrations

% rake db:migrate:up VERSION=20200826053152; rake db:migrate:down VERSION=20200826053152                                                                                                                                                                                                                                                                                          
== 20200826053152 AddIndexOnCiPipelinesSourceForOnDemandDast: migrating =======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, :id, {:where=>"source = 13", :name=>"index_ci_pipelines_for_ondemand_dast_scans", :algorithm=>:concurrently})
   -> 0.0082s
-- add_index(:ci_pipelines, :id, {:where=>"source = 13", :name=>"index_ci_pipelines_for_ondemand_dast_scans", :algorithm=>:concurrently})
   -> 0.0074s
== 20200826053152 AddIndexOnCiPipelinesSourceForOnDemandDast: migrated (0.0162s)

== 20200826053152 AddIndexOnCiPipelinesSourceForOnDemandDast: reverting =======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipelines, :id, {:where=>"source = 13", :name=>"index_ci_pipelines_for_ondemand_dast_scans", :algorithm=>:concurrently})
   -> 0.0081s
-- remove_index(:ci_pipelines, {:where=>"source = 13", :name=>"index_ci_pipelines_for_ondemand_dast_scans", :algorithm=>:concurrently, :column=>:id})
   -> 0.0073s
== 20200826053152 AddIndexOnCiPipelinesSourceForOnDemandDast: reverted (0.0158s)

Performance Analysis

Queries

SELECT MIN("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13
SELECT MAX("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13
SELECT COUNT("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13 AND "ci_pipelines"."id" BETWEEN 166609192 AND 166709192

Before

MIN

SELECT MIN("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13

 Aggregate  (cost=2464144.50..2464144.51 rows=1 width=4) (actual time=837871.186..837871.186 rows=1 loops=1)
   Buffers: shared hit=373519 read=408652 dirtied=1
   I/O Timings: read=426525.133
   ->  Index Scan using index_ci_pipelines_on_project_id_and_source on public.ci_pipelines  (cost=0.57..2464144.49 rows=1 width=4) (actual time=109797.933..837871.032 rows=74 loops=1)
         Index Cond: (ci_pipelines.source = 13)
         Buffers: shared hit=373519 read=408652 dirtied=1
         I/O Timings: read=426525.133

Time: 13.965 min
  - planning: 0.379 ms
  - execution: 13.965 min
    - I/O read: 7.109 min
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 373519 (~2.80 GiB) from the buffer pool
  - reads: 408652 (~3.10 GiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599180204239800
• https://explain.depesz.com/s/vWmv

MAX

SELECT MAX("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13

 Aggregate  (cost=2464144.50..2464144.51 rows=1 width=4) (actual time=837874.785..837874.785 rows=1 loops=1)
   Buffers: shared hit=408655 read=373516 dirtied=3
   I/O Timings: read=388829.183
   ->  Index Scan using index_ci_pipelines_on_project_id_and_source on public.ci_pipelines  (cost=0.57..2464144.49 rows=1 width=4) (actual time=109801.557..837874.634 rows=74 loops=1)
         Index Cond: (ci_pipelines.source = 13)
         Buffers: shared hit=408655 read=373516 dirtied=3
         I/O Timings: read=388829.183

Time: 13.965 min
  - planning: 0.625 ms
  - execution: 13.965 min
    - I/O read: 6.480 min
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 408655 (~3.10 GiB) from the buffer pool
  - reads: 373516 (~2.80 GiB) from the OS file cache, including disk I/O
  - dirtied: 3 (~24.00 KiB)
  - writes: 0

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599180204239700
• https://explain.depesz.com/s/P7G4

COUNT

SELECT COUNT("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13 AND "ci_pipelines"."id" BETWEEN 166609192 AND 166709192

 Aggregate  (cost=23717.21..23717.22 rows=1 width=8) (actual time=47970.569..47970.570 rows=1 loops=1)
   Buffers: shared hit=65846 read=31970 dirtied=505
   I/O Timings: read=47457.732
   ->  Index Scan using ci_pipelines_pkey on public.ci_pipelines  (cost=0.57..23717.21 rows=1 width=4) (actual time=12.901..47970.554 rows=1 loops=1)
         Index Cond: ((ci_pipelines.id >= 166609192) AND (ci_pipelines.id <= 166709192))
         Filter: (ci_pipelines.source = 13)
         Rows Removed by Filter: 98258
         Buffers: shared hit=65846 read=31970 dirtied=505
         I/O Timings: read=47457.732

Time: 47.971 s
  - planning: 0.276 ms
  - execution: 47.971 s
    - I/O read: 47.458 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 65846 (~514.40 MiB) from the buffer pool
  - reads: 31970 (~249.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 505 (~3.90 MiB)
  - writes: 0

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599180229241700
• https://explain.depesz.com/s/NZ83

After

CREATE INDEX index_ci_pipelines_for_ondemand_dast_scans ON public.ci_pipelines USING btree (project_id, id) WHERE (source = 13);

The query has been executed. Duration: 13.772 min

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599181632263400

MIN

SELECT MIN("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13

 Result  (cost=1.79..1.80 rows=1 width=4) (actual time=0.252..0.252 rows=1 loops=1)
   Buffers: shared hit=1 read=1
   I/O Timings: read=0.186
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.14..1.79 rows=1 width=4) (actual time=0.248..0.248 rows=1 loops=1)
           Buffers: shared hit=1 read=1
           I/O Timings: read=0.186
           ->  Index Only Scan using index_ci_pipelines_for_ondemand_dast_scans on public.ci_pipelines  (cost=0.14..1.79 rows=1 width=4) (actual time=0.247..0.247 rows=1 loops=1)
                 Index Cond: (ci_pipelines.id IS NOT NULL)
                 Heap Fetches: 0
                 Buffers: shared hit=1 read=1
                 I/O Timings: read=0.186

Time: 0.901 ms
  - planning: 0.599 ms
  - execution: 0.302 ms
    - I/O read: 0.186 ms
    - I/O write: 0.000 ms

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

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599184386275200
• https://explain.depesz.com/s/LMg1f

MAX

SELECT MAX("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13

 Result  (cost=1.79..1.80 rows=1 width=4) (actual time=0.195..0.195 rows=1 loops=1)
   Buffers: shared hit=2 read=1
   I/O Timings: read=0.143
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.14..1.79 rows=1 width=4) (actual time=0.192..0.193 rows=1 loops=1)
           Buffers: shared hit=2 read=1
           I/O Timings: read=0.143
           ->  Index Only Scan using index_ci_pipelines_for_ondemand_dast_scans on public.ci_pipelines  (cost=0.14..1.79 rows=1 width=4) (actual time=0.191..0.191 rows=1 loops=1)
                 Index Cond: (ci_pipelines.id IS NOT NULL)
                 Heap Fetches: 1
                 Buffers: shared hit=2 read=1
                 I/O Timings: read=0.143

Time: 1.024 ms
  - planning: 0.765 ms
  - execution: 0.259 ms
    - I/O read: 0.143 ms
    - I/O write: 0.000 ms

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

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599184559277600
• https://explain.depesz.com/s/RYL2

COUNT

SELECT COUNT("ci_pipelines"."id") FROM "ci_pipelines" WHERE "ci_pipelines"."source" = 13 AND "ci_pipelines"."id" BETWEEN 166609192 AND 166709192

 Aggregate  (cost=1.79..1.80 rows=1 width=8) (actual time=0.041..0.041 rows=1 loops=1)
   Buffers: shared hit=2
   ->  Index Only Scan using index_ci_pipelines_for_ondemand_dast_scans on public.ci_pipelines  (cost=0.14..1.79 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1)
         Index Cond: ((ci_pipelines.id >= 166609192) AND (ci_pipelines.id <= 166709192))
         Heap Fetches: 0
         Buffers: shared hit=2

Time: 0.404 ms
  - planning: 0.319 ms
  - execution: 0.085 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

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

• https://gitlab.slack.com/archives/CLJMDRD8C/p1599184643280100
• https://explain.depesz.com/s/6Wmv

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