Add usage_data for coverage_fuzzing
What does this MR do?
This MR adds usage data for new coverage fuzzing security feature
related MRs !34648 (merged) !36011 (merged)
Issue: #217152 (closed)
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
Performance Analysis
CREATE INDEX index_security_ci_builds_on_name_and_id_cov_fuzz ON public.ci_builds USING btree (name, id) WHERE (((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('sast'::character varying)::text, ('secret_detection'::character varying)::text, ('coverage_fuzzing'::character varying)::text, ('license_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text));
The query has been executed. Duration: 107.406min
CREATE INDEX index_security_ci_builds_on_name_and_id_cov_fuzz ON public.ci_builds USING btree (name, id) WHERE (((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('sast'::character varying)::text, ('secret_detection'::character varying)::text, ('coverage_fuzzing'::character varying)::text, ('license_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text));
The query has been executed. Duration: 172.672 min
Query Type 1
pry(main)> Gitlab::UsageData.count(::Ci::Build.where(name: "coverage_fuzzing"))
(13.9ms) SELECT MIN("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = $1 AND "ci_builds"."name" = $2 [["type", "Ci::Build"], ["name", "coverage_fuzzing"]]
(0.5ms) SELECT MAX("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = $1 AND "ci_builds"."name" = $2 [["type", "Ci::Build"], ["name", "coverage_fuzzing"]]
(1.1ms) SELECT COUNT("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = $1 AND "ci_builds"."name" = $2 AND "ci_builds"."id" BETWEEN $3 AND $4 [["type", "Ci::Build"], ["name", "coverage_fuzzing"], ["id", 0], ["id", 99999]]
SELECT MIN("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing'
SELECT MAX("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing'
SELECT COUNT("ci_builds"."id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."id" BETWEEN 0 AND 99999
Query Type 2
[6] pry(main)> time_period = {}
=> {}
[7] pry(main)> Gitlab::UsageData.distinct_count(::Ci::Build.where(name: "coverage_fuzzing").where(time_period), :user_id)
(0.9ms) SELECT MIN("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing'
(0.6ms) SELECT MAX("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing'
(0.4ms) SELECT COUNT(DISTINCT "ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."user_id" BETWEEN 0 AND 9999
SELECT MIN("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing'
SELECT MAX("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing'
SELECT COUNT(DISTINCT "ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."user_id" BETWEEN 0 AND 9999
Query Type 3
[8] pry(main)> time_period = { created_at: 28.days.ago..Time.current }
=> {:created_at=>Thu, 25 Jun 2020 11:50:37 UTC +00:00..Thu, 23 Jul 2020 11:50:37 UTC +00:00}
[9] pry(main)> Gitlab::UsageData.distinct_count(::Ci::Build.where(name: secure_type).where(time_period), :user_id)
(1.2ms) SELECT MIN("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."created_at" BETWEEN '2020-06-25 11:50:37.473478' AND '2020-07-23 11:50:37.473636'
(0.7ms) SELECT MAX("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."created_at" BETWEEN '2020-06-25 11:50:37.473478' AND '2020-07-23 11:50:37.473636'
(0.4ms) SELECT COUNT(DISTINCT "ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."created_at" BETWEEN '2020-06-25 11:50:37.473478' AND '2020-07-23 11:50:37.473636' AND "ci_builds"."user_id" BETWEEN 0 AND 9999
SELECT MIN("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."created_at" BETWEEN '2020-04-21 21:40:24.288642' AND '2020-05-19 21:40:24.288763'
SELECT MAX("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."created_at" BETWEEN '2020-04-21 21:40:24.288642' AND '2020-05-19 21:40:24.288763'
SELECT COUNT(DISTINCT "ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'coverage_fuzzing' AND "ci_builds"."created_at" BETWEEN '2020-04-21 21:40:24.288642' AND '2020-05-19 21:40:24.288763' AND "ci_builds"."user_id" BETWEEN 0 AND 9999
Migration Output
== 20200721140507 UpdateIndexForCoverageFuzzingTelemetry: migrating ===========
-- transaction_open?()
-> 0.0000s
-- index_exists?(:ci_builds, [:name, :id], {:name=>"index_security_ci_builds_on_name_and_id_cov_fuzz", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n ('dast'::character varying)::text,\n ('dependency_scanning'::character varying)::text,\n ('license_management'::character varying)::text,\n ('sast'::character varying)::text,\n ('secret_detection'::character varying)::text,\n ('coverage_fuzzing'::character varying)::text,\n ('license_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
-> 0.0094s
-- add_index(:ci_builds, [:name, :id], {:name=>"index_security_ci_builds_on_name_and_id_cov_fuzz", :where=>"((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text,\n ('dast'::character varying)::text,\n ('dependency_scanning'::character varying)::text,\n ('license_management'::character varying)::text,\n ('sast'::character varying)::text,\n ('secret_detection'::character varying)::text,\n ('coverage_fuzzing'::character varying)::text,\n ('license_scanning'::character varying)::text])) AND ((type)::text = 'Ci::Build'::text)", :algorithm=>:concurrently})
-> 0.0031s
-- transaction_open?()
-> 0.0000s
-- indexes(:ci_builds)
-> 0.0076s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_security_ci_builds_on_name_and_id"})
-> 0.0018s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:ci_builds, [:user_id, :created_at], {:where=>"(((type)::text = 'Ci::Build'::text) AND ((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('license_scanning'::character varying)::text, ('sast'::character varying)::text, ('coverage_fuzzing'::character varying)::text, ('secret_detection'::character varying)::text])))", :name=>"index_secure_ci_builds_on_user_id_created_at_cov_fuzz", :algorithm=>:concurrently})
-> 0.0066s
-- add_index(:ci_builds, [:user_id, :created_at], {:where=>"(((type)::text = 'Ci::Build'::text) AND ((name)::text = ANY (ARRAY[('container_scanning'::character varying)::text, ('dast'::character varying)::text, ('dependency_scanning'::character varying)::text, ('license_management'::character varying)::text, ('license_scanning'::character varying)::text, ('sast'::character varying)::text, ('coverage_fuzzing'::character varying)::text, ('secret_detection'::character varying)::text])))", :name=>"index_secure_ci_builds_on_user_id_created_at_cov_fuzz", :algorithm=>:concurrently})
-> 0.0021s
-- transaction_open?()
-> 0.0000s
-- indexes(:ci_builds)
-> 0.0074s
-- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_secure_ci_builds_on_user_id_created_at"})
-> 0.0023s
== 20200721140507 UpdateIndexForCoverageFuzzingTelemetry: migrated (0.0417s) ==
Edited by Mayra Cabrera