Optimize project counters related to the ::Project

Fix the queries for both monthly, and all-times version of the queries with batch counters enabled.

Feature.enable(:usage_ping_batch_counter)

1. No time constraint
   • locations:
     • https://gitlab.com/gitlab-org/gitlab/-/blob/908902d1d4f6fbcd7780150c780837fd4fa8b301/ee/lib/ee/gitlab/usage_data.rb#L210
     • https://gitlab.com/gitlab-org/gitlab/-/blob/908902d1d4f6fbcd7780150c780837fd4fa8b301/ee/lib/ee/gitlab/usage_data.rb#L232
     • https://gitlab.com/gitlab-org/gitlab/-/blob/908902d1d4f6fbcd7780150c780837fd4fa8b301/ee/lib/ee/gitlab/usage_data.rb#L299
   • specifically:

code

time_period = { }

projects_slack_notifications_active: distinct_count(::Project.with_slack_service.where(time_period), :creator_id),
projects_slack_slash_active: distinct_count(::Project.with_slack_slash_commands_service.where(time_period), :creator_id),
projects_with_repositories_enabled: distinct_count(::Project.with_repositories_enabled.where(time_period), :creator_id),
projects_mirrored_with_pipelines_enabled: distinct_count(::Project.mirrored_with_enabled_pipelines.where(time_period), :creator_id),
protected_branches: distinct_count(::Project.with_protected_branches.where(time_period), :creator_id),

2. With a time constraint of 28 days

   • time_period = { created_at: 28.days.ago..Time.current }
   • List is the same as above, but with the time constraint

3. service_desk_enabled_projects

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 10000anduser_id between 1 AND 100_000
  • Check also for the min and max query(meaning, no range on id/user_id above)
• Optional: Optimize if possible also related counters which are for the same table or create indexes that cover them too

Resolving MR's

1. !26686 (merged)
   • Covering

projects_slack_notifications_active: distinct_count(::Project.with_slack_service.where(time_period), :creator_id),
projects_slack_slash_active: distinct_count(::Project.with_slack_slash_commands_service.where(time_period), :creator_id),
protected_branches: distinct_count(::Project.with_protected_branches.where(time_period), :creator_id)

2. !26698 (merged)
   • Covering

projects_with_repositories_enabled: distinct_count(::Project.with_repositories_enabled.where(time_period), :creator_id),

3. !26802 (merged)
   • Covering

projects_mirrored_with_pipelines_enabled: distinct_count(::Project.mirrored_with_enabled_pipelines.where(time_period), :creator_id),

4. !27115 (merged)
   • Covering service_desk_enabled_projects