Skip to content

Add container expiration policies tracking to usage data

David Fernandez requested to merge 205577-add-container-expiration-policiy-counts-in-usage-ping into master

What does this MR do?

This adds Container Expiration Policies usage data in the usage ping (see https://docs.gitlab.com/ee/user/admin_area/settings/usage_statistics.html)

See #205577 (closed). In particular, here is the list of the different counts we need for container expiration policies.

See my notes below for some thoughts on the approach I used.

Screenshots

n/a

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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

Telemetry review

Below we present the SQL queries and their explan plans that this MR is adding to the usage ping in both modes: batch count enabled and disabled.

We avoid presenting duplicated queries as this MR changes will generate count queries for all the different values of the different attributes of ContainerExpirationPolicy. For example, this MR generates 6 times the same queries for all the values of keep_n (https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/container_expiration_policy.rb#L19). Here, we only attach the explain plain for the first value.

For Batch count enabled, we also present the two queries done for MIN and MAX.

Mode: Batch enabled

MIN

SELECT MIN(“projects”.“id”) FROM “projects”

Query plan: https://explain.depesz.com/s/T3OR

MAX

SELECT MAX(“projects”.“id”) FROM “projects”

Query plan: https://explain.depesz.com/s/E1HA

Conditions: enabled: false

Query plan: https://explain.depesz.com/s/SpIX

Conditions: enabled: true

SELECT COUNT(DISTINCT "container_expiration_policies"."project_id") FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."project_id" BETWEEN 1 AND 10000

Query plan: https://explain.depesz.com/s/7H7D

Conditions: enabled: true, keep_n: 1

SELECT COUNT(DISTINCT "container_expiration_policies"."project_id") FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."keep_n" = 1 AND "container_expiration_policies"."project_id" BETWEEN 1 AND 10000

Query plan: https://explain.depesz.com/s/pYCL

Conditions: enabled: true, keep_n: nil

SELECT COUNT(DISTINCT "container_expiration_policies"."project_id") FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."keep_n" IS NULL AND "container_expiration_policies"."project_id" BETWEEN 1 AND 10000

Query plan: https://explain.depesz.com/s/a5id

Conditions: enabled: true, cadence: 1d

SELECT COUNT(DISTINCT "container_expiration_policies"."project_id") FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."cadence" = '1d' AND "container_expiration_policies"."project_id" BETWEEN 1 AND 10000

Query plan: https://explain.depesz.com/s/XAaG

Conditions: enabled: true, older_than: 7d

SELECT COUNT(DISTINCT "container_expiration_policies"."project_id") FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."older_than" = '7d' AND "container_expiration_policies"."project_id" BETWEEN 1 AND 10000

Query plan: https://explain.depesz.com/s/4IOG

Conditions: enabled: true, older_than: nil

SELECT COUNT(DISTINCT "container_expiration_policies"."project_id") FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."older_than" IS NULL AND "container_expiration_policies"."project_id" BETWEEN 1 AND 10000

Query plan: https://explain.depesz.com/s/OjLZ

Mode: Batch disabled

Conditions: enabled: false

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = FALSE

Query plan: https://explain.depesz.com/s/cZTy

Conditions: enabled: true

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE

Query plan: https://explain.depesz.com/s/cHvy

Conditions: enabled: true, keep_n: 1

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."keep_n" = 1

Query plan: https://explain.depesz.com/s/AO7q

Conditions: enabled: true, keep_n: nil

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."keep_n" IS NULL

Query plan: https://explain.depesz.com/s/GrZ0

Conditions: enabled: true, cadence: 1d

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."cadence" = '1d'

Query plan: https://explain.depesz.com/s/CI0

Conditions: enabled: true, older_than: 7d

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."older_than" = '7d'

Query plan: https://explain.depesz.com/s/M3kO

Conditions: enabled: true, older_than: nil

SELECT COUNT(*) FROM "container_expiration_policies" WHERE "container_expiration_policies"."enabled" = TRUE AND "container_expiration_policies"."older_than" IS NULL

Query plan: https://explain.depesz.com/s/jwyG

Edited by David Fernandez

Merge request reports