Skip to content

Optimize or remove ldap_users counter

Alina Mihailarequested to merge
210051-optimize-or-remove-ldap_users-counter into master

What does this MR do?

Optimize ldap_users counter

time_period = { created_at: 28.days.ago..Time.current }
ldap_users: distinct_count(::GroupMember.of_ldap_type.where(time_period), :user_id)

time_period = { }
ldap_users: distinct_count(::GroupMember.of_ldap_type.where(time_period), :user_id)

Add specialised index

in database-lab

exec CREATE INDEX CONCURRENTLY index_members_on_user_id_and_created_at ON members USING btree (user_id,created_at) WHERE ldap = TRUE AND type = 'GroupMember' AND source_type = 'Namespace'

The query has been executed. Duration: 31.115 s

Migration output

== 20200313123934 AddIndexOnUserIdTypeSourceTypeLdapAndCreatedAtToMembers: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:members, [:user_id, :created_at], {:where=>"ldap = TRUE AND type = 'GroupMember' AND source_type = 'Namespace'", :name=>"index_members_on_user_id_created_at", :algorithm=>:concurrently})
   -> 0.0047s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- add_index(:members, [:user_id, :created_at], {:where=>"ldap = TRUE AND type = 'GroupMember' AND source_type = 'Namespace'", :name=>"index_members_on_user_id_created_at", :algorithm=>:concurrently})
   -> 0.0062s
-- execute("RESET ALL")
   -> 0.0007s
== 20200313123934 AddIndexOnUserIdTypeSourceTypeLdapAndCreatedAtToMembers: migrated (0.0125s)

Queries in database-lab

query MIN Calculation no period 
explain SELECT MIN("members"."user_id") FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."ldap" = TRUE

Before: Time: 4.198 min https://explain.depesz.com/s/kU3j

Filter: (members.ldap AND ((members.type)::text = 'GroupMember'::text))

After: Time: 0.314 ms https://explain.depesz.com/s/9Giz

No Filter

query MAX Calculation no period 
explain SELECT MAX("members"."user_id") FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."ldap" = TRUE

Before: Time: 1.578 min https://explain.depesz.com/s/EqqW

Filter: (members.ldap AND ((members.type)::text = 'GroupMember'::text))

After: Time: 0.373 ms https://explain.depesz.com/s/d8LV

No Filter

query COUNT DISTINCT no period 
explain SELECT COUNT(DISTINCT "members"."user_id") FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."ldap" = TRUE AND "members"."user_id" BETWEEN 0 AND 9999

Before: Time: 1.311 s https://explain.depesz.com/s/nIMJ

Filter: (members.ldap AND ((members.type)::text = 'GroupMember'::text) AND ((members.source_type)::text = 'Namespace'::text))

After: Time: 0.426 ms https://explain.depesz.com/s/ZIuZ

No Filter

query MIN Calculation with period 
explain SELECT MIN("members"."user_id") FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."ldap" = TRUE AND "members"."created_at" BETWEEN '2020-02-14 11:20:49.490714' AND '2020-03-13 11:20:49.490890'

Before: Time: 36.523 https://explain.depesz.com/s/sOrB

Filter: (members.ldap AND (members.created_at >= '2020-02-14 11:20:49.490714'::timestamp without time zone) AND (members.created_at <= '2020-03-13 11:20:49.49089'::timestamp without time zone) AND ((members.type)::text = 'GroupMember'::text))

After: Time: 0.353 ms https://explain.depesz.com/s/DiLB

No Filter

query MAX Calculation with period 
explain SELECT MAX("members"."user_id") FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."ldap" = TRUE AND "members"."created_at" BETWEEN '2020-02-14 11:20:49.490714' AND '2020-03-13 11:20:49.490890'

Before: Time: 15.351 s https://explain.depesz.com/s/aeQX

Filter: (members.ldap AND (members.created_at >= '2020-02-14 11:20:49.490714'::timestamp without time zone) AND (members.created_at <= '2020-03-13 11:20:49.49089'::timestamp without time zone) AND ((members.type)::text = 'GroupMember'::text))

After: Time: 0.346 ms https://explain.depesz.com/s/gVt4

No Filter

query COUNT DISTINCT Calculation with period 
explain SELECT COUNT(DISTINCT "members"."user_id") FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."ldap" = TRUE AND "members"."created_at" BETWEEN '2020-02-14 11:20:49.490714' AND '2020-03-13 11:20:49.490890' AND "members"."user_id" BETWEEN 0 AND 9999

Before: Time: 637.299 ms https://explain.depesz.com/s/CjfE

Filter: (members.ldap AND (members.created_at >= '2020-02-14 11:20:49.490714'::timestamp without time zone) AND (members.created_at <= '2020-03-13 11:20:49.49089'::timestamp without time zone) AND ((members.type)::text = 'GroupMember'::text) AND ((members.source_type)::text = 'Namespace'::text))

After: Time: 0.376 ms https://explain.depesz.com/s/rXSi

No Filter

Conformity

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 #210051 (closed)

Edited by Alina Mihaila

Merge request reports