Optimize service_desk_issue counters related to the ::Issue
Fix the queries for both monthly, and all-times version of the queries with batch counters enabled.
- remove distinct
- and add index count
Console work to fetch the queries, # reset to master so no need for # Feature.enable(:usage_ping_batch_counter)
Old the queries here are rong
# 2000 is the minimal batch
[ gprd ] production> ::User.support_bot
=> #<User id:1257257 @support-bot>
# 1 Locations https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb
time_period = { }
Gitlab::UsageData.distinct_count(::Issue.service_desk.where(time_period))
SELECT MIN("issues"."id") FROM "issues" WHERE "issues"."author_id" = 1257257
SELECT MAX("issues"."id") FROM "issues" WHERE "issues"."author_id" = 1257257
SELECT COUNT(DISTINCT "issues"."id") FROM "issues" WHERE "issues"."author_id" = 1257257 AND "issues"."id" BETWEEN 910000 AND 912000
# 2 Locations https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb
time_period = { created_at: 28.days.ago..Time.current }
Gitlab::UsageData.distinct_count(::Issue.service_desk.where(time_period))
# Same above for the 28-days "monthly" period
# 3 the count
Gitlab::UsageData.count( ::Issue.where( project: projects_with_service_desk, author: ::User.support_bot, confidential: true)
Guide to optimize the counter
- Follow database query optimization guides https://docs.gitlab.com/ee/development/database_review.html#preparation-when-adding-or-modifying-queries
- Use your rails console to find out the 2 types of queries with
Feature.enable(:usage_ping_batch_counter)
-
- Optimize for the range query like
id between
1 AND 10000and
user_id between1 AND 100_000
- Optimize for the range query like
-
- Check also for the min and max query
- Optional: Optimize if possible also related counters which are for the same table or create indexes that cover them too
Edited by Alper Akgun