Optimize notes and suggestion counters
Fix the queries for both monthly, and all-times version of the queries with batch counters enabled.
- Notes is one of the large tables with with
230 millions
rows on gitlab.com.
# 1 Locations https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/lib/ee/gitlab/usage_data.rb
time_period = { }
distinct_count(::Note.where(time_period), :author_id),
distinct_count(::Note.with_suggestions.where(time_period), :author_id)
# 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 }
distinct_count(::Note.where(time_period), :author_id),
distinct_count(::Note.with_suggestions.where(time_period), :author_id)
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