Optimize ci_builds counters in usage data
Fix the queries for both monthly, and all-times version of the queries with batch counters enabled. They have been found to fail as per #205281 (comment 298541599)
Feature.enable(:usage_ping_batch_counter)
time_period = { }
ci_builds: distinct_count(::Ci::Build.where(time_period), :user_id),
query before change
SELECT
COUNT(DISTINCT "ci_builds"."user_id")
FROM
"ci_builds"
WHERE
"ci_builds"."type" = 'Ci::Build'
AND "ci_builds"."user_id" >= 1
AND "ci_builds"."user_id" < 100000;
- explain (database-lab)
- Same above, but for the 28-days "monthly" period
time_period = { created_at: 28.days.ago..Time.current }
ci_builds: distinct_count(::Ci::Build.where(time_period), :user_id),
query before change
SELECT
COUNT(DISTINCT "ci_builds"."user_id")
FROM
"ci_builds"
WHERE
"ci_builds"."type" = 'Ci::Build'
AND "ci_builds"."created_at" BETWEEN '2020-02-06 18:32:05.610669'
AND '2020-03-05 18:32:05.610721'
AND "ci_builds"."user_id" >= 1
AND "ci_builds"."user_id" < 100000;
- explain (database-lab)
-
Also all counters in security_products_usage
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
- Check also for the min and max query(meaning, no range on
id
/user_id
above)
- Optimize for the range query like
- Optional: Optimize if possible also related counters which are for the same table or create indexes that cover them too
Resolving MR's
The analysis of the failed counters in the usage ping from 2020-03-18
https://gitlab.com/gitlab-org/gitlab/-/issues/208233 the below ci_builds counters fail.
178: "container_scanning_jobs": -1,
179: "dast_jobs": -1,
180: "dependency_scanning_jobs": -1,
182: "sast_jobs": -1,
345: "ci_builds": -1,
432: "user_container_scanning_jobs": -1,
433: "user_dast_jobs": -1,
436: "user_sast_jobs": -1
439: "ci_builds": -1,
Edited by Alper Akgun