Optimize ldap_keys count in usage_data (!27309) · Merge requests · GitLab.org / GitLab

What does this MR do?

Optimize the ldap_keys counters

# 1. https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L149 
ldap_keys: count(::LDAPKey)

# 2. https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L245
ldap_keys: distinct_count(::LDAPKey.where(time_period), :user_id)

# 3. https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb#L245
time_period = { created_at: 28.days.ago..Time.current }
ldap_keys: distinct_count(::LDAPKey.where(time_period), :user_id),

== 20200316111759 AddIndexOnIdAndLdapKeyToKeys: migrating =====================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:keys, [:id], {:where=>"type = 'LDAPKey'", :name=>"index_keys_on_id_and_ldap_key_type", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:keys, [:id], {:where=>"type = 'LDAPKey'", :name=>"index_keys_on_id_and_ldap_key_type", :algorithm=>:concurrently})
   -> 0.0134s
-- execute("RESET ALL")
   -> 0.0004s
== 20200316111759 AddIndexOnIdAndLdapKeyToKeys: migrated (0.0173s) ============

Index added

exec CREATE INDEX CONCURRENTLY index_keys_on_id_and_ldap_key_type ON keys USING btree(id) WHERE type = 'LDAPKey'

The query has been executed. Duration: 52.938 s

Queries in database-lab

query MIN Calculation no period

explain SELECT MIN("keys"."id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: Time: 4.285 min https://explain.depesz.com/s/LppF

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.309 ms https://explain.depesz.com/s/f3d3

No Filter

query MAX Calculation no period

explain SELECT MAX("keys"."id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: Time: 21.815 s https://explain.depesz.com/s/5UfA

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.235 ms https://explain.depesz.com/s/9Yt6

No Filter

query COUNT DISTINCT no period

explain SELECT COUNT("keys"."id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."id" BETWEEN 0 AND 99999

Before: Time: 46.027 ms https://explain.depesz.com/s/1Yj7L

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.272 ms https://explain.depesz.com/s/wIoV

No Filter

query MIN Calculation with no period

explain SELECT MIN("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: Time: 4.581 min https://explain.depesz.com/s/UtrX

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.287 ms https://explain.depesz.com/s/FeN3

No filter

query MAX Calculation no period

explain SELECT MAX("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey'

Before: 27.428 s https://explain.depesz.com/s/FyVw

Filter: ((keys.type)::text = 'LDAPKey'::text

After: Time: 0.307 ms https://explain.depesz.com/s/ow8f

No Filter

query COUNT DISTINCT no period

explain SELECT COUNT(DISTINCT "keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."user_id" BETWEEN 0 AND 9999

Before: 20.946 ms https://explain.depesz.com/s/C4Ge

Filter: ((keys.type)::text = 'LDAPKey'::text)

After: Time: 0.400 ms https://explain.depesz.com/s/Oumx

Filter: ((keys.user_id >= 0) AND (keys.user_id <= 9999))

query MIN Calculation with period

explain SELECT MIN("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."created_at" BETWEEN '2020-02-17 08:28:45.247119' AND '2020-03-16 08:28:45.247354'

Before: Time 28.575 s https://explain.depesz.com/s/1Kbu

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND ((keys.type)::text = 'LDAPKey'::text))

After: Time: 0.255 ms https://explain.depesz.com/s/jO1i

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone))

query MAX Calculation with period

explain  SELECT MAX("keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."created_at" BETWEEN '2020-02-17 08:28:45.247119' AND '2020-03-16 08:28:45.247354'

Before: Time: 40.053 s https://explain.depesz.com/s/iJqs

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND ((keys.type)::text = 'LDAPKey'::text))

After: Time: 0.604 ms https://explain.depesz.com/s/yx44

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone))

query COUNT DISTINCT Calculation with period

explain  SELECT COUNT(DISTINCT "keys"."user_id") FROM "keys" WHERE "keys"."type" = 'LDAPKey' AND "keys"."created_at" BETWEEN '2020-02-17 08:28:45.247119' AND '2020-03-16 08:28:45.247354' AND "keys"."user_id" BETWEEN 0 AND 9999

Before: Time: 20.403 ms https://explain.depesz.com/s/OFIQ

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND ((keys.type)::text = 'LDAPKey'::text))

After: Time: 0.453 ms https://explain.depesz.com/s/JYGa

Filter: ((keys.created_at >= '2020-02-17 08:28:45.247119+00'::timestamp with time zone) AND (keys.created_at <= '2020-03-16 08:28:45.247354+00'::timestamp with time zone) AND (keys.user_id >= 0) AND (keys.user_id <= 9999))

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

Edited Mar 18, 2020 by Alina Mihaila

Optimize ldap_keys count in usage_data

What does this MR do?

Index added

Queries in database-lab

Before: Time: 4.285 min https://explain.depesz.com/s/LppF

After: Time: 0.309 ms https://explain.depesz.com/s/f3d3

Before: Time: 21.815 s https://explain.depesz.com/s/5UfA

After: Time: 0.235 ms https://explain.depesz.com/s/9Yt6

Before: Time: 46.027 ms https://explain.depesz.com/s/1Yj7L

After: Time: 0.272 ms https://explain.depesz.com/s/wIoV

Before: Time: 4.581 min https://explain.depesz.com/s/UtrX

After: Time: 0.287 ms https://explain.depesz.com/s/FeN3

Before: 27.428 s https://explain.depesz.com/s/FyVw

After: Time: 0.307 ms https://explain.depesz.com/s/ow8f

Before: 20.946 ms https://explain.depesz.com/s/C4Ge

After: Time: 0.400 ms https://explain.depesz.com/s/Oumx

Before: Time 28.575 s https://explain.depesz.com/s/1Kbu

After: Time: 0.255 ms https://explain.depesz.com/s/jO1i

Before: Time: 40.053 s https://explain.depesz.com/s/iJqs

After: Time: 0.604 ms https://explain.depesz.com/s/yx44

Before: Time: 20.403 ms https://explain.depesz.com/s/OFIQ

After: Time: 0.453 ms https://explain.depesz.com/s/JYGa

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Merge request reports