Investigate growth in total_time for project_authorizations query
It is suspected that this query might contribute to the growth in CPU usage in the primary DB server (https://gitlab.com/gitlab-org/gitlab/-/issues/395711)
We can see in the charts below, this query is taking up more time compared to Dec 2022/Jan 2023
queryid: 2068844745664171016
/application:sidekiq,correlation_id:38a19786a1660f170ca89320d2ef1bbc,jid:a7b83037175417d7d4789674,endpoint_id:AuthorizedProjectUpdate::UserRefreshFromReplicaWorker,db_config_name:main_replica/ WITH RECURSIVE "namespaces_cte" AS ((SELECT "namespaces"."id", "members"."access_level" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" = $1 AND "members"."source_type" = $2 AND "namespaces"."type" = $3 AND "members"."user_id" = $4 AND "members"."requested_at" IS NULL AND (access_level >= $5) AND "members"."state" = $6) UNION (SELECT "namespaces"."id", LEAST("members"."access_level", "group_group_links"."group_access") AS access_level FROM "namespaces" INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id" INNER JOIN "members" ON "group_group_links"."shared_with_group_id" = "members"."source_id" AND "members"."source_type" = $7 AND "members"."requested_at" IS NULL AND "members"."user_id" = $8 AND "members"."state" = $9 AND "members"."access_level" > $10 WHERE "namespaces"."type" = $11) UNION (SELECT "namespaces"."id", GREATEST("members"."access_level", "namespaces_cte"."access_level") AS access_level FROM "namespaces" INNER JOIN "namespaces_cte" ON "namespaces_cte"."id" = "namespaces"."parent_id" LEFT OUTER JOIN "members" ON "members"."source_id" = "namespaces"."id" AND "members"."source_type" = $12 AND "members"."requested_at" IS NULL AND "members"."user_id" = $13 AND "members"."state" = $14 AND "members"."access_level" > $15 WHERE "namespaces"."type" = $16)) SELECT "project_authorizations"."project_id", MAX(access_level) AS access_level FROM ((SELECT projects.id AS project_id, "members"."access_level" FROM "projects" INNER JOIN "members" ON "projects"."id" = "members"."source_id" WHERE "members"."type" IN ($17, $18) AND "members"."source_type" = $19 AND "members"."user_id" = $20 AND "members"."requested_at" IS NULL AND "members"."state" = $21) UNION (SELECT projects.id AS project_id, $22 AS access_level FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = $23 AND "namespaces"."type" = $24) UNION (SELECT "projects"."id" AS project_id, "namespaces"."access_level" FROM "namespaces_cte" "namespaces" INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id") UNION (SELECT "project_group_links"."project_id", LEAST("namespaces"."access_level", "project_group_links"."group_access") AS access_level FROM "namespaces_cte" "namespaces" INNER JOIN project_group_links ON project_group_links.group_id = namespaces.id INNER JOIN projects ON projects.id = project_group_links.project_id INNER JOIN namespaces p_ns ON p_ns.id = projects.namespace_id WHERE (p_ns.share_with_group_lock IS FALSE))) project_authorizations GROUP BY "project_authorizations"."project_id"
Thanos (total_time):
8 weeks up to 2012-01-31 | 8 weeks up to 2023-03-21 |
---|---|
Thanos (total_calls):
8 weeks up to 2012-01-31 | 8 weeks up to 2023-03-21 |
---|---|