Skip to content

Add individual usage counters for snippet types

Amparo Luna requested to merge al-215200-snippets-by-type-usage-counter into master

What does this MR do?

Currently, we have only one counter reporting the total number of snippets. In this MR, we add a counter for Personal Snippets and another one for Project Snippets to the usage data. This will provide more details about the usage of each snippet type.

Additionally, we've added an index to the Snippets table to optimize the queries generated by the count method as suggested in the telemetry review and telemetry guide. See !33008 (merged) for the discussion regarding the index.

Relates to #215200 (closed)

Migration Output

Up

VERBOSE=true bundle exec rake db:migrate:up VERSION=20200522235146
== 20200522235146 AddIndexOnSnippetTypeAndId: migrating =======================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:snippets, [:id, :type], {:algorithm=>:concurrently})
   -> 0.0050s
-- add_index(:snippets, [:id, :type], {:algorithm=>:concurrently})
   -> 0.0040s
== 20200522235146 AddIndexOnSnippetTypeAndId: migrated (0.0094s) ==============

Down

VERBOSE=true bundle exec rake db:migrate:down VERSION=20200522235146
== 20200522235146 AddIndexOnSnippetTypeAndId: reverting =======================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:snippets, [:id, :type], {:algorithm=>:concurrently})
   -> 0.0059s
-- remove_index(:snippets, {:algorithm=>:concurrently, :column=>[:id, :type]})
   -> 0.0086s
== 20200522235146 AddIndexOnSnippetTypeAndId: reverted (0.0149s) ==============

Execution times

Index Creation Duration: 12.123 s

Query Before Index After Index
Project Snippets - SELECT MIN Time: 14.969 ms - plan: 0.181 ms - exec: 14.788 ms Time: 0.384 ms - plan: 0.163 ms - exec: 0.221 ms
Project Snippets - SELECT MAX Time: 14.278 ms - plan: 0.169 ms - exec: 14.109 ms Time: 0.486 ms - plan: 0.219 ms - exec: 0.267 ms
Project Snippets - SELECT COUNT Time: 11.619 s - plan: 0.206 ms - exec: 11.619 s Time: 7.062 ms - plan: 0.164 ms - exec: 6.898 ms
Personal Snippets - SELECT MIN Time: 7.088 ms - plan: 0.203 ms - exec: 6.885 ms Time: 0.207 ms - plan: 0.158 ms - exec: 0.049 ms
Personal Snippets - SELECT MAX Time: 0.237 ms - plan: 0.173 ms - exec: 0.064 ms Time: 0.317 ms - plan: 0.199 ms - exec: 0.118 ms
Personal Snippets - SELECT COUNT Time: 30.632 ms - plan: 0.205 ms - exec: 30.427 ms Time: 9.455 ms - plan: 0.160 ms - exec: 9.295 ms

Generated queries and plan for ProjectSnippet count

pry(main)> Gitlab::UsageData.count(ProjectSnippet)
   (0.5ms)  SELECT "features"."key" FROM "features"
   (1.4ms)  SELECT MIN("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'ProjectSnippet'
   (0.3ms)  SELECT MAX("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'ProjectSnippet'
   (0.4ms)  SELECT COUNT("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'ProjectSnippet' AND "snippets"."id" BETWEEN 50 AND 100049
Plan Before After
SELECT MIN https://explain.depesz.com/s/x3BJ https://explain.depesz.com/s/YGAN
SELECT MAX https://explain.depesz.com/s/zsCc https://explain.depesz.com/s/RbKE
SELECT COUNT Attempt 1: https://explain.depesz.com/s/OVGi
Attempt 2: https://explain.depesz.com/s/Put5 		Attempt 1: https://explain.depesz.com/s/eV9v
Attempt 2: https://explain.depesz.com/s/c8yt

Generated queries and plan for PersonalSnippet count

pry(main)> Gitlab::UsageData.count(PersonalSnippet)
   (0.5ms)  SELECT MIN("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'PersonalSnippet'
   (0.4ms)  SELECT MAX("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'PersonalSnippet'
   (0.3ms)  SELECT COUNT("snippets"."id") FROM "snippets" WHERE "snippets"."type" = 'PersonalSnippet' AND "snippets"."id" BETWEEN 0 AND 99999
Plan Before After
SELECT MIN https://explain.depesz.com/s/nv8h https://explain.depesz.com/s/WGF
SELECT MAX https://explain.depesz.com/s/9cPv https://explain.depesz.com/s/VMRA
SELECT COUNT Attempt 1: https://explain.depesz.com/s/x9Rq
Attempt 2: https://explain.depesz.com/s/3e3a 		Attempt 1: https://explain.depesz.com/s/WXM8
Attempt 2: https://explain.depesz.com/s/lN1s

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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 🤖 GitLab Bot 🤖

Merge request reports