Skip to content

Optimize Project related count service desk enabled

Doug Stull requested to merge 208887-optimize-project-counters-service-desk into master

What does this MR do?

Optimize query produced by https://gitlab.com/gitlab-org/gitlab/-/blob/908902d1d4f6fbcd7780150c780837fd4fa8b301/ee/lib/ee/gitlab/usage_data.rb#L103

query with batching
SELECT
    COUNT("projects"."id")
FROM
    "projects"
WHERE
    "projects"."service_desk_enabled" = TRUE
    AND "projects"."id" BETWEEN 10000 AND 11250;

explain data from database-lab

  • id between 10_000 AND 11_250

Before

Observations

  1. Filter being applied on service_desk_enabled
Index Cond: ((projects.id >= 10000) AND (projects.id <= 11250))
Filter: projects.service_desk_enabled
  • combat this with adding index
CREATE INDEX CONCURRENTLY index_projects_on_id_sd ON public.projects USING btree (id)
WHERE
    service_desk_enabled = TRUE;
  1. MAX/MIN calculations
  • Query
SELECT
    MAX("projects"."id")
FROM
    "projects"
WHERE
    "projects"."service_desk_enabled" = TRUE;

Plan

  • Add these indexes
CREATE INDEX CONCURRENTLY index_projects_on_id_sd ON public.projects USING btree (id)
WHERE
    service_desk_enabled = TRUE;

After only index conditions are hit

Timing

After the index for batch counting takes 2.2 seconds super pessimistic using database-lab

  • 5.5 million users,
  • with 1_250 batch sizes
  • 5.5M/1_250 = 4_400 loops
  • Time: < 0.5ms ( cold cache )

Migration output

12:34 $ be rails db:migrate:up VERSION=20200312163407
== 20200312163407 AddIndexOnIdAndServiceDeskEnabledToProjects: migrating ======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, :id, {:where=>"service_desk_enabled = true", :name=>"index_projects_on_id_service_desk_enabled", :algorithm=>:concurrently})
   -> 0.0114s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:projects, :id, {:where=>"service_desk_enabled = true", :name=>"index_projects_on_id_service_desk_enabled", :algorithm=>:concurrently})
   -> 0.0053s
-- execute("RESET ALL")
   -> 0.0004s
== 20200312163407 AddIndexOnIdAndServiceDeskEnabledToProjects: migrated (0.0176s)

 ~/projects/gdk/gitlab [208887-optimize-project-counters-service-desk ↑·2| 11 1]
12:47 $ be rails db:migrate:down VERSION=20200312163407
== 20200312163407 AddIndexOnIdAndServiceDeskEnabledToProjects: reverting ======
-- transaction_open?()
   -> 0.0000s
-- indexes(:projects)
   -> 0.0114s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_on_id_service_desk_enabled"})
   -> 0.0025s
-- execute("RESET ALL")
   -> 0.0004s
== 20200312163407 AddIndexOnIdAndServiceDeskEnabledToProjects: reverted (0.0148s)

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

Related to #208887 (closed)

Edited by Doug Stull

Merge request reports

Loading