GitLab Next

Production Change

Change Summary

We want to disable - not remove - index_issues_on_relative_position to verify that it's safe to delete without adverse performance impact. We know that this index can cause problematic query plans in some cases, like gitlab-org/gitlab#254249 (closed).

Once this change is done we should roll it back as we'll then delete the index entirely in gitlab-org/gitlab!43991 (merged).

Change Details

1. Services Impacted - ServicePostgres (and everything that uses that; here, ServiceWeb and ServiceSidekiq specifically)
2. Change Technician - @abrandl
3. Change Criticality - C3
4. Change Type - changescheduled
5. Change Reviewer - TODO
6. Due Date - 2020-10-07
7. Time tracking - TODO
8. Downtime Component - None

Detailed steps for the change

Pre-Change Steps - steps to be completed before execution of the change

Estimated Time to Complete (mins) - 2 mins

• Verify index is chosen by the planner

  EXPLAIN SELECT pos, next_pos, ABS(pos::bigint - next_pos::bigint) FROM (SELECT relative_position AS pos, LEAD(relative_position) OVER (ORDER BY relative_position DESC) AS next_pos FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'transgourmet-ecommerce/aufbau-co.-sites/%')) AND (relative_position <= 2147483647) ORDER BY "issues"."relative_position" DESC) items WHERE pos IS NULL OR ABS(pos::bigint - next_pos::bigint) >= 2 LIMIT 1;

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - TODO

• Disable index on the primary

  update pg_index set indisvalid = false where indexrelid = (select oid from pg_class where relname = 'index_issues_on_relative_position');

Post-Change Steps - steps to take to verify the change

Estimated Time to Complete (mins) - 60 mins

• Verify the index is no longer chosen (running with ANALYZE should also now execute quickly)

  EXPLAIN SELECT pos, next_pos, ABS(pos::bigint - next_pos::bigint) FROM (SELECT relative_position AS pos, LEAD(relative_position) OVER (ORDER BY relative_position DESC) AS next_pos FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'transgourmet-ecommerce/aufbau-co.-sites/%')) AND (relative_position <= 2147483647) ORDER BY "issues"."relative_position" DESC) items WHERE pos IS NULL OR ABS(pos::bigint - next_pos::bigint) >= 2 LIMIT 1;

• Verify the index is invalid on replicas

  SELECT indisvalid FROM pg_index WHERE indexrelid = (select oid from pg_class where relname = 'index_issues_on_relative_position');

• Check that boards issue listing timings are not affected: https://dashboards.gitlab.net/d/web-rails-controller/web-rails-controller?orgId=1&var-PROMETHEUS_DS=Global&var-environment=gprd&var-stage=main&var-controller=Boards::IssuesController&var-action=index.json

• Look for https://sentry.gitlab.net/gitlab/gitlabcom/issues/1845708/?referrer=gitlab_plugin to stop reporting

Rollback

Rollback steps - steps to be taken in the event of a need to rollback this change

Estimated Time to Complete (mins) - TODO

• Re-enable index

  update pg_index set indisvalid = true where indexrelid = (select oid from pg_class where relname = 'index_issues_on_relative_position');

• Verify that it's used again

  EXPLAIN SELECT pos, next_pos, ABS(pos::bigint - next_pos::bigint) FROM (SELECT relative_position AS pos, LEAD(relative_position) OVER (ORDER BY relative_position DESC) AS next_pos FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'transgourmet-ecommerce/aufbau-co.-sites/%')) AND (relative_position <= 2147483647) ORDER BY "issues"."relative_position" DESC) items WHERE pos IS NULL OR ABS(pos::bigint - next_pos::bigint) >= 2 LIMIT 1;

Monitoring

Key metrics to observe

• Metric: Latency, SQL latency per controller request
  • Location: https://dashboards.gitlab.net/d/web-rails-controller/web-rails-controller?orgId=1&var-PROMETHEUS_DS=Global&var-environment=gprd&var-stage=main&var-controller=Boards::IssuesController&var-action=index.json
  • What changes to this metric should prompt a rollback: any noticeable increase

Summary of infrastructure changes

• Does this change introduce new compute instances?
• Does this change re-size any existing compute instances?
• Does this change introduce any additional usage of tooling like Elastic Search, CDNs, Cloudflare, etc?

No.

Summary of the above

Changes checklist

• This issue has a criticality label (e.g. C1, C2, C3, C4) and a change-type label (e.g. changeunscheduled, changescheduled).
• This issue has the change technician as the assignee.
• Pre-Change, Change, Post-Change, and Rollback steps and have been filled out and reviewed.
• Necessary approvals have been completed based on the Change Management Workflow.
• Change has been tested in staging and resultes noted in a comment on this issue.
• A dry-run has been conducted and results noted in a comment on this issue.
• SRE on-call has been informed prior to change being rolled out. (In #production channel, mention @sre-oncall and this issue.)
• There are currently no active incidents.