Add projects_creating_incidents to usage ping (!42934) · Merge requests · GitLab.org / GitLab

Sean Arnold requested to merge 233719-project-creating-incidents-usage-ping into master Sep 22, 2020

What does this MR do?

This adds a count of the Projects that are creating incidents to the usage ping.

SQL queries and plans:

MIN:

Query (https://explain.depesz.com/s/DJt9):

explain SELECT MIN("issues"."project_id") FROM "issues"

MAX:

Query (https://explain.depesz.com/s/OkbQ):

explain SELECT MAX("issues"."project_id") FROM "issues"

Distinct count:

Query (https://explain.depesz.com/s/GJuY):

explain SELECT COUNT(DISTINCT "issues"."project_id") FROM "issues" WHERE "issues"."issue_type" = 1 AND "issues"."project_id" BETWEEN 1 AND 100000

Adding SQL index: index_issues_project_id_issue_type_incident ON issues USING btree (project_id) WHERE (issue_type = 1)

CREATE INDEX CONCURRENTLY index_issues_project_id_issue_type_incident ON issues USING btree (project_id) WHERE (issue_type = 1);

#database-lab timing (https://gitlab.slack.com/archives/CLJMDRD8C/p1601259628316800):

exec CREATE INDEX CONCURRENTLY "issues_issue_type_incident_index" ON "issues"  ("project_id") WHERE issue_type = 1
Session: joe-btokej0350j13v0691gg
The query has been executed. Duration: 14.713 min

UP:

rake db:migrate
== 20200927224750 AddIncidentIssueTypeIndexToIssues: migrating ================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:issues, :project_id, {:where=>"issue_type = 1", :name=>"issues_project_id_issue_type_incident_index", :algorithm=>:concurrently})
   -> 0.0108s
D, [2020-09-28T16:46:23.160625 #56269] DEBUG -- :    (0.1ms)  SHOW statement_timeout
D, [2020-09-28T16:46:23.160976 #56269] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:1279:in `statement_timeout_disabled?'
-- execute("SET statement_timeout TO 0")
D, [2020-09-28T16:46:23.161280 #56269] DEBUG -- :    (0.1ms)  SET statement_timeout TO 0
D, [2020-09-28T16:46:23.161530 #56269] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:279:in `disable_statement_timeout'
   -> 0.0005s
-- add_index(:issues, :project_id, {:where=>"issue_type = 1", :name=>"issues_project_id_issue_type_incident_index", :algorithm=>:concurrently})
D, [2020-09-28T16:46:23.166303 #56269] DEBUG -- :    (3.2ms)  CREATE  INDEX CONCURRENTLY "index_issues_project_id_issue_type_incident" ON "issues"  ("project_id") WHERE issue_type = 1
D, [2020-09-28T16:46:23.166598 #56269] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:95:in `block in add_concurrent_index'
   -> 0.0050s
-- execute("RESET ALL")
D, [2020-09-28T16:46:23.166872 #56269] DEBUG -- :    (0.1ms)  RESET ALL
D, [2020-09-28T16:46:23.167150 #56269] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:283:in `disable_statement_timeout'
   -> 0.0005s
== 20200927224750 AddIncidentIssueTypeIndexToIssues: migrated (0.0178s) =======

D, [2020-09-28T16:46:23.170641 #56269] DEBUG -- :    (0.1ms)  BEGIN
D, [2020-09-28T16:46:23.172181 #56269] DEBUG -- :   primary::SchemaMigration Create (0.4ms)  INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version"  [["version", "20200927224750"]]
D, [2020-09-28T16:46:23.173384 #56269] DEBUG -- :    (0.3ms)  COMMIT
D, [2020-09-28T16:46:23.178137 #56269] DEBUG -- :   ActiveRecord::InternalMetadata Load (0.4ms)  SELECT "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2  [["key", "environment"], ["LIMIT", 1]]
D, [2020-09-28T16:46:23.183339 #56269] DEBUG -- :    (0.2ms)  SELECT pg_advisory_unlock(574671635034264810)
D, [2020-09-28T16:46:24.783263 #56269] DEBUG -- :    (1.1ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
D, [2020-09-28T16:46:24.783693 #56269] DEBUG -- :   ↳ lib/gitlab/database/postgresql_adapter/dump_schema_versions_mixin.rb:10:in `dump_schema_information'
D, [2020-09-28T16:46:25.363416 #56269] DEBUG -- :   Gitlab::Database::PartitioningMigrationHelpers::PartitionedForeignKey Exists? (0.4ms)  SELECT 1 AS one FROM "partitioned_foreign_keys" LIMIT $1  [["LIMIT", 1]]
D, [2020-09-28T16:46:25.363724 #56269] DEBUG -- :   ↳ lib/gitlab/database/custom_structure.rb:35:in `partitioned_foreign_keys_exist?'
D, [2020-09-28T16:46:25.382183 #56269] DEBUG -- :    (1.3ms)  select
  pg_class.relname,
  parent_class.relname as base_table,
  pg_get_expr(pg_class.relpartbound, inhrelid) as condition
from pg_class
inner join pg_inherits i on pg_class.oid = inhrelid
inner join pg_class parent_class on parent_class.oid = inhparent
inner join pg_namespace ON pg_namespace.oid = pg_class.relnamespace
where pg_namespace.nspname = 'gitlab_partitions_dynamic'
  and parent_class.relname = 'audit_events_part_5fc467ac26'
  and pg_class.relispartition
order by pg_class.relname

D, [2020-09-28T16:46:25.382428 #56269] DEBUG -- :   ↳ lib/gitlab/database/partitioning/monthly_strategy.rb:20:in `current_partitions'
D, [2020-09-28T16:46:25.383557 #56269] DEBUG -- :    (0.6ms)  select
  pg_class.relname,
  parent_class.relname as base_table,
  pg_get_expr(pg_class.relpartbound, inhrelid) as condition
from pg_class
inner join pg_inherits i on pg_class.oid = inhrelid
inner join pg_class parent_class on parent_class.oid = inhparent
inner join pg_namespace ON pg_namespace.oid = pg_class.relnamespace
where pg_namespace.nspname = 'gitlab_partitions_dynamic'
  and parent_class.relname = 'audit_events_part_5fc467ac26'
  and pg_class.relispartition
order by pg_class.relname

D, [2020-09-28T16:46:25.383858 #56269] DEBUG -- :   ↳ lib/gitlab/database/partitioning/monthly_strategy.rb:20:in `current_partitions'

DOWN:

➜  gitlab git:(233719-project-creating-incidents-usage-ping) ✗ rake db:rollback
== 20200927224750 AddIncidentIssueTypeIndexToIssues: reverting ================
-- transaction_open?()
   -> 0.0000s
-- indexes(:issues)
   -> 0.0129s
D, [2020-09-28T16:50:05.196946 #56823] DEBUG -- :    (0.1ms)  SHOW statement_timeout
D, [2020-09-28T16:50:05.197283 #56823] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:1279:in `statement_timeout_disabled?'
-- execute("SET statement_timeout TO 0")
D, [2020-09-28T16:50:05.197596 #56823] DEBUG -- :    (0.1ms)  SET statement_timeout TO 0
D, [2020-09-28T16:50:05.197861 #56823] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:279:in `disable_statement_timeout'
   -> 0.0005s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"issues_project_id_issue_type_incident_index"})
D, [2020-09-28T16:50:05.203330 #56823] DEBUG -- :    (5.3ms)  DROP INDEX CONCURRENTLY "index_issues_project_id_issue_type_incident"
D, [2020-09-28T16:50:05.203592 #56823] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:151:in `block in remove_concurrent_index_by_name'
   -> 0.0057s
-- execute("RESET ALL")
D, [2020-09-28T16:50:05.203841 #56823] DEBUG -- :    (0.1ms)  RESET ALL
D, [2020-09-28T16:50:05.204072 #56823] DEBUG -- :   ↳ lib/gitlab/database/migration_helpers.rb:283:in `disable_statement_timeout'
   -> 0.0004s
== 20200927224750 AddIncidentIssueTypeIndexToIssues: reverted (0.0205s) =======

D, [2020-09-28T16:50:05.210197 #56823] DEBUG -- :   primary::SchemaMigration Destroy (0.6ms)  DELETE FROM "schema_migrations" WHERE "schema_migrations"."version" = $1  [["version", "20200927224750"]]
D, [2020-09-28T16:50:05.211053 #56823] DEBUG -- :    (0.2ms)  SELECT pg_advisory_unlock(574671635034264810)
D, [2020-09-28T16:50:06.622891 #56823] DEBUG -- :    (1.1ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
D, [2020-09-28T16:50:06.623256 #56823] DEBUG -- :   ↳ lib/gitlab/database/postgresql_adapter/dump_schema_versions_mixin.rb:10:in `dump_schema_information'
D, [2020-09-28T16:50:07.240644 #56823] DEBUG -- :   Gitlab::Database::PartitioningMigrationHelpers::PartitionedForeignKey Exists? (0.9ms)  SELECT 1 AS one FROM "partitioned_foreign_keys" LIMIT $1  [["LIMIT", 1]]
D, [2020-09-28T16:50:07.241216 #56823] DEBUG -- :   ↳ lib/gitlab/database/custom_structure.rb:35:in `partitioned_foreign_keys_exist?'

Screenshots

Does this MR meet the acceptance criteria?

Conformity

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

Closes #233719 (closed)

Edited Sep 28, 2020 by Sean Arnold

Add projects_creating_incidents to usage ping