GET /api/:version/projects/:id/deployments with updated_before param is slow
Spun from gitlab-com/gl-infra/production#4024 (closed)...
Out of 6,342,556 requests to /api/:version/projects/:id/deployments
for last 7 days:
- 571,218 requests had 1 or more requests on primary DB (9%)
- 30,620 requests had 1s or more on primary DB
- Out of these 26,057 requests includes the
updated_before
param
- Out of these 26,057 requests includes the
- 78 (not from this one root namespace)
*NOTE 4.3 million of hits last 7 days come from this one ~customer root namespace. See https://log.gprd.gitlab.net/goto/d34f8655c2696d7abe7d72e55edccd5c
Proposal
Improve this query that uses updated_before
param:
https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/2834/commands/8874
SELECT "deployments".* FROM "deployments" INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id" WHERE "deployments"."project_id" = 22679027 AND "deployments"."updated_at" <= '2020-12-04 05:06:26' AND "environments"."name" = '*****' ORDER BY "deployments"."iid" DESC LIMIT 100 OFFSET 0
SELECT
"deployments".*
FROM
"deployments"
INNER JOIN "environments" ON "environments"."id" = "deployments"."environment_id"
WHERE
"deployments"."project_id" = 22679027
AND "deployments"."updated_at" <= '2020-12-04 05:06:26'
AND "environments"."name" = '*****'
ORDER BY
"deployments"."iid" DESC
LIMIT 100 OFFSET 0
Links
See also !57043 (comment 534300638)
Edited by Thong Kuah