Add count of projects with new incidents to usage ping
What does this MR do?
Related issue: #263549 (closed)
This MR adds two new items to the usage ping:
- Count of projects with a new incident created in the last month
- Count of projects with a new incident created in the last month from an alert
Before index changes
Query performance for projects with incidents
Queries (uses Gitlab::Database::BatchCount):
SELECT MIN(issues.project_id) FROM issues
WHERE issues.issue_type = 1
AND issues.created_at BETWEEN '2020-09-30 20:18:00' AND '2020-10-28 20:18:00';
SELECT MAX(issues.project_id) FROM issues
WHERE issues.issue_type = 1
AND issues.created_at BETWEEN '2020-09-30 20:18:00' AND '2020-10-28 20:18:00';
SELECT COUNT(DISTINCT issues.project_id) FROM issues
WHERE issues.issue_type = 1
AND issues.created_at BETWEEN '2020-09-30 20:18:00' AND '2020-10-28 20:18:00'
AND "issues"."project_id" >= 21
AND "issues"."project_id" < 10021; Min database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1604089881301400 Min visualization: https://explain.depesz.com/s/qGqV
Database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1604090064303900 Visual rep: https://explain.depesz.com/s/uMoW
Query performance for project with incidents from alerts
Queries (uses Gitlab::Database::BatchCount):
SELECT MIN(issues.project_id) FROM issues
INNER JOIN alert_management_alerts ON alert_management_alerts.issue_id = issues.id
WHERE issues.issue_type = 1
AND issues.created_at BETWEEN '2020-09-30 20:39:41.087683' AND '2020-10-28 20:39:41.087806';
SELECT MAX(issues.project_id) FROM issues
INNER JOIN alert_management_alerts ON alert_management_alerts.issue_id = issues.id
WHERE issues.issue_type = 1
AND issues.created_at BETWEEN '2020-09-30 20:39:41.087683' AND '2020-10-28 20:39:41.087806';
SELECT COUNT(DISTINCT issues.project_id) FROM issues
INNER JOIN alert_management_alerts ON alert_management_alerts.issue_id = issues.id
WHERE issues.issue_type = 1
AND issues.created_at BETWEEN '2020-09-30 20:39:41.087683' AND '2020-10-28 20:39:41.087806'
AND issues.project_id >= 0
AND issues.project_id < 10000; Min database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1604090759306300 Min visualization: https://explain.depesz.com/s/cBXe
Database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1604090869309400 Visual rep: https://explain.depesz.com/s/xfdm
With new index
exec CREATE INDEX index_issues_on_project_id_and_created_at_issue_type_incident ON issues USING btree (project_id, created_at) WHERE (issue_type = 1);
exec DROP INDEX index_issues_project_id_issue_type_incident;
Query performance for projects with incidents
- Min database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1605302713042400
- Min visualization: https://explain.depesz.com/s/mOdy
- Database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1605302781044800
- Visual rep: https://explain.depesz.com/s/Dnpo
Query performance for project with incidents from alerts
- Min database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1605302816047300
- Min visualization: https://explain.depesz.com/s/eueR
- Database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1605302855049900
- Visual rep: https://explain.depesz.com/s/psSm
Up migration
$ rails db:migrate
== 20201102184743 AddIndexToIncidentIssuesOnProjectAndCreatedAt: migrating ====
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issues, [:project_id, :created_at], {:where=>"issue_type = 1", :name=>"index_issues_on_project_id_and_created_at_issue_type_incident", :algorithm=>:concurrently})
-> 0.0101s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:issues, [:project_id, :created_at], {:where=>"issue_type = 1", :name=>"index_issues_on_project_id_and_created_at_issue_type_incident", :algorithm=>:concurrently})
-> 0.0039s
-- execute("RESET ALL")
-> 0.0002s
-- transaction_open?()
-> 0.0000s
-- indexes(:issues)
-> 0.0085s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_project_id_issue_type_incident"})
-> 0.0039s
== 20201102184743 AddIndexToIncidentIssuesOnProjectAndCreatedAt: migrated (0.0276s) SQL output via dbconsole
$ rails dbconsole
psql (11.9)
Type "help" for help.
gitlabhq_development=# \d issues
Table "public.issues"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('issues_id_seq'::regclass)
title | character varying | | |
author_id | integer | | |
project_id | integer | | |
created_at | timestamp without time zone | | |
updated_at | timestamp without time zone | | |
description | text | | |
milestone_id | integer | | |
iid | integer | | |
updated_by_id | integer | | |
weight | integer | | |
confidential | boolean | | not null | false
due_date | date | | |
moved_to_id | integer | | |
lock_version | integer | | | 0
title_html | text | | |
description_html | text | | |
time_estimate | integer | | |
relative_position | integer | | |
service_desk_reply_to | character varying | | |
cached_markdown_version | integer | | |
last_edited_at | timestamp without time zone | | |
last_edited_by_id | integer | | |
discussion_locked | boolean | | |
closed_at | timestamp with time zone | | |
closed_by_id | integer | | |
state_id | smallint | | not null | 1
duplicated_to_id | integer | | |
promoted_to_epic_id | integer | | |
health_status | smallint | | |
external_key | character varying(255) | | |
sprint_id | bigint | | |
issue_type | smallint | | not null | 0
blocking_issues_count | integer | | not null | 0
Indexes:
"issues_pkey" PRIMARY KEY, btree (id)
"index_issues_on_project_id_and_external_key" UNIQUE, btree (project_id, external_key) WHERE external_key IS NOT NULL
"index_issues_on_project_id_and_iid" UNIQUE, btree (project_id, iid)
"idx_issues_on_health_status_not_null" btree (health_status) WHERE health_status IS NOT NULL
"idx_issues_on_project_id_and_created_at_and_id_and_state_id" btree (project_id, created_at, id, state_id)
"idx_issues_on_project_id_and_due_date_and_id_and_state_id" btree (project_id, due_date, id, state_id) WHERE due_date IS NOT NULL
"idx_issues_on_project_id_and_rel_position_and_state_id_and_id" btree (project_id, relative_position, state_id, id DESC)
"idx_issues_on_project_id_and_updated_at_and_id_and_state_id" btree (project_id, updated_at, id, state_id)
"idx_issues_on_state_id" btree (state_id)
"index_issue_on_project_id_state_id_and_blocking_issues_count" btree (project_id, state_id, blocking_issues_count)
"index_issues_on_author_id" btree (author_id)
"index_issues_on_author_id_and_id_and_created_at" btree (author_id, id, created_at)
"index_issues_on_closed_by_id" btree (closed_by_id)
"index_issues_on_confidential" btree (confidential)
"index_issues_on_description_trigram" gin (description gin_trgm_ops)
"index_issues_on_duplicated_to_id" btree (duplicated_to_id) WHERE duplicated_to_id IS NOT NULL
"index_issues_on_incident_issue_type" btree (issue_type) WHERE issue_type = 1
"index_issues_on_last_edited_by_id" btree (last_edited_by_id)
"index_issues_on_milestone_id" btree (milestone_id)
"index_issues_on_moved_to_id" btree (moved_to_id) WHERE moved_to_id IS NOT NULL
"index_issues_on_project_id_and_closed_at" btree (project_id, closed_at)
"index_issues_on_project_id_and_created_at_issue_type_incident" btree (project_id, created_at) WHERE issue_type = 1
"index_issues_on_promoted_to_epic_id" btree (promoted_to_epic_id) WHERE promoted_to_epic_id IS NOT NULL
"index_issues_on_sprint_id" btree (sprint_id)
"index_issues_on_title_trigram" gin (title gin_trgm_ops)
"index_issues_on_updated_at" btree (updated_at)
"index_issues_on_updated_by_id" btree (updated_by_id) WHERE updated_by_id IS NOT NULL
"tmp_idx_index_issues_with_outdate_blocking_count" btree (id) WHERE state_id = 1 AND blocking_issues_count = 0Down migration
$ rails db:migrate:down VERSION=20201102184743
== 20201102184743 AddIndexToIncidentIssuesOnProjectAndCreatedAt: reverting ====
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issues, :project_id, {:where=>"issue_type = 1", :name=>"index_issues_project_id_issue_type_incident", :algorithm=>:concurrently})
-> 0.0105s
-- execute("SET statement_timeout TO 0")
-> 0.0002s
-- add_index(:issues, :project_id, {:where=>"issue_type = 1", :name=>"index_issues_project_id_issue_type_incident", :algorithm=>:concurrently})
-> 0.0041s
-- execute("RESET ALL")
-> 0.0002s
-- transaction_open?()
-> 0.0000s
-- indexes(:issues)
-> 0.0081s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_project_id_and_created_at_issue_type_incident"})
-> 0.0035s
== 20201102184743 AddIndexToIncidentIssuesOnProjectAndCreatedAt: reverted (0.0274s) SQL output via dbconsole
$ rails dbconsole
psql (11.9)
Type "help" for help.
gitlabhq_development=# \d issues
Table "public.issues"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('issues_id_seq'::regclass)
title | character varying | | |
author_id | integer | | |
project_id | integer | | |
created_at | timestamp without time zone | | |
updated_at | timestamp without time zone | | |
description | text | | |
milestone_id | integer | | |
iid | integer | | |
updated_by_id | integer | | |
weight | integer | | |
confidential | boolean | | not null | false
due_date | date | | |
moved_to_id | integer | | |
lock_version | integer | | | 0
title_html | text | | |
description_html | text | | |
time_estimate | integer | | |
relative_position | integer | | |
service_desk_reply_to | character varying | | |
cached_markdown_version | integer | | |
last_edited_at | timestamp without time zone | | |
last_edited_by_id | integer | | |
discussion_locked | boolean | | |
closed_at | timestamp with time zone | | |
closed_by_id | integer | | |
state_id | smallint | | not null | 1
duplicated_to_id | integer | | |
promoted_to_epic_id | integer | | |
health_status | smallint | | |
external_key | character varying(255) | | |
sprint_id | bigint | | |
issue_type | smallint | | not null | 0
blocking_issues_count | integer | | not null | 0
Indexes:
"issues_pkey" PRIMARY KEY, btree (id)
"index_issues_on_project_id_and_external_key" UNIQUE, btree (project_id, external_key) WHERE external_key IS NOT NULL
"index_issues_on_project_id_and_iid" UNIQUE, btree (project_id, iid)
"idx_issues_on_health_status_not_null" btree (health_status) WHERE health_status IS NOT NULL
"idx_issues_on_project_id_and_created_at_and_id_and_state_id" btree (project_id, created_at, id, state_id)
"idx_issues_on_project_id_and_due_date_and_id_and_state_id" btree (project_id, due_date, id, state_id) WHERE due_date IS NOT NULL
"idx_issues_on_project_id_and_rel_position_and_state_id_and_id" btree (project_id, relative_position, state_id, id DESC)
"idx_issues_on_project_id_and_updated_at_and_id_and_state_id" btree (project_id, updated_at, id, state_id)
"idx_issues_on_state_id" btree (state_id)
"index_issue_on_project_id_state_id_and_blocking_issues_count" btree (project_id, state_id, blocking_issues_count)
"index_issues_on_author_id" btree (author_id)
"index_issues_on_author_id_and_id_and_created_at" btree (author_id, id, created_at)
"index_issues_on_closed_by_id" btree (closed_by_id)
"index_issues_on_confidential" btree (confidential)
"index_issues_on_description_trigram" gin (description gin_trgm_ops)
"index_issues_on_duplicated_to_id" btree (duplicated_to_id) WHERE duplicated_to_id IS NOT NULL
"index_issues_on_incident_issue_type" btree (issue_type) WHERE issue_type = 1
"index_issues_on_last_edited_by_id" btree (last_edited_by_id)
"index_issues_on_milestone_id" btree (milestone_id)
"index_issues_on_moved_to_id" btree (moved_to_id) WHERE moved_to_id IS NOT NULL
"index_issues_on_project_id_and_closed_at" btree (project_id, closed_at)
"index_issues_on_promoted_to_epic_id" btree (promoted_to_epic_id) WHERE promoted_to_epic_id IS NOT NULL
"index_issues_on_sprint_id" btree (sprint_id)
"index_issues_on_title_trigram" gin (title gin_trgm_ops)
"index_issues_on_updated_at" btree (updated_at)
"index_issues_on_updated_by_id" btree (updated_by_id) WHERE updated_by_id IS NOT NULL
"index_issues_project_id_issue_type_incident" btree (project_id) WHERE issue_type = 1
"tmp_idx_index_issues_with_outdate_blocking_count" btree (id) WHERE state_id = 1 AND blocking_issues_count = 0Screenshots (strongly suggested)
| Monthly ping | All time ping |
|---|---|
 |
 |
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 Sarah Yasonik