Backend: GraphQL query times out with 503 when loading environment and variables

Summary

Attempting to load https://gitlab.com/gitlab-org/gitlab-foss/-/settings/ci_cd results in 503 timeout errors:

It looks like this GraphQL query times out on occasion:

Kibana shows this SQL timeout (https://log.gprd.gitlab.net/goto/5be38d60-5962-11ed-8d37-e9a2f393ea2a):

SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = $1 ORDER BY "environments"."id" DESC LIMIT $2

This stems from this GraphQL query:

[
    {
      "operationName": "getProjectEnvironments",
      "variables": "[FILTERED]",
      "query": "query getProjectEnvironments($fullPath: ID!) {
          project(fullPath: $fullPath) {
            id
            environments {
              nodes {
                id
                name
                __typename
              }
            __typename
          }
          __typename
        }
      }
      "
    },
    {
      "operationName": "getProjectVariables",
      "variables": "[FILTERED]",
      "query": "query getProjectVariables($after: String, $first: Int = 100, $fullPath: ID!) {
          project(fullPath: $fullPath) {
            id
            ciVariables(after: $after, first: $first) {
              pageInfo {
                ...PageInfo
                __typename
              }
            nodes {
                ...BaseCiVariable
                environmentScope
                masked
                protected
                __typename
              }
            __typename
          }
          __typename
        }
      }
      
      fragment BaseCiVariable on CiVariable {
          __typename
          id
          key
          value
          variableType
        }
      
      fragment PageInfo on PageInfo {
          hasNextPage
          hasPreviousPage
          startCursor
          endCursor
          __typename
        }
      "
    }
  ]

Backtrace

     "exception.backtrace": [
        "lib/gitlab/database/load_balancing/connection_proxy.rb:104:in `block in read_using_load_balancer'",
        "lib/gitlab/database/load_balancing/load_balancer.rb:55:in `read'",
        "lib/gitlab/database/load_balancing/connection_proxy.rb:103:in `read_using_load_balancer'",
        "lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all'",
        "lib/gitlab/graphql/pagination/keyset/connection.rb:135:in `block in limited_nodes'",
        "lib/gitlab/utils/strong_memoize.rb:44:in `strong_memoize'",
        "lib/gitlab/graphql/pagination/keyset/connection.rb:117:in `limited_nodes'",
        "lib/gitlab/graphql/pagination/keyset/connection.rb:96:in `nodes'",
        "lib/gitlab/graphql/connection_redaction.rb:23:in `block in nodes'",
        "lib/gitlab/graphql/connection_redaction.rb:16:in `redacted'",
        "lib/gitlab/graphql/connection_redaction.rb:23:in `nodes'",
        "lib/gitlab/graphql/tracers/timer_tracer.rb:20:in `trace'",
        "lib/gitlab/graphql/generic_tracing.rb:48:in `with_labkit_tracing'",
        "lib/gitlab/graphql/generic_tracing.rb:38:in `platform_trace'",
        "lib/gitlab/graphql/tracers/logger_tracer.rb:14:in `trace'",
        "lib/gitlab/graphql/tracers/metrics_tracer.rb:13:in `trace'",
        "lib/gitlab/graphql/tracers/application_context_tracer.rb:23:in `trace'",
        "lib/gitlab/graphql/tracers/timer_tracer.rb:20:in `trace'",
        "lib/gitlab/graphql/generic_tracing.rb:48:in `with_labkit_tracing'",
        "lib/gitlab/graphql/generic_tracing.rb:38:in `platform_trace'",
        "lib/gitlab/graphql/tracers/logger_tracer.rb:14:in `trace'",
        "lib/gitlab/graphql/tracers/metrics_tracer.rb:13:in `trace'",
        "lib/gitlab/graphql/tracers/application_context_tracer.rb:23:in `trace'",
        "lib/gitlab/graphql/tracers/timer_tracer.rb:20:in `trace'",
        "lib/gitlab/graphql/generic_tracing.rb:48:in `with_labkit_tracing'",
        "lib/gitlab/graphql/generic_tracing.rb:38:in `platform_trace'",
        "lib/gitlab/graphql/tracers/logger_tracer.rb:14:in `trace'",
        "lib/gitlab/graphql/tracers/metrics_tracer.rb:13:in `trace'",
        "lib/gitlab/graphql/tracers/application_context_tracer.rb:23:in `trace'",
        "app/graphql/gitlab_schema.rb:51:in `multiplex'",
        "app/controllers/graphql_controller.rb:160:in `execute_multiplex'",
        "app/controllers/graphql_controller.rb:57:in `execute'",
        "ee/lib/gitlab/ip_address_state.rb:10:in `with'",

Proposal

Implement pagination in getProjectEnvironments query to ensure SQL queries do not timeout.

Additional details

Some relevant technical details, if applicable, such as:

• Does this need a feature flag?
• Is there an example response showing the data structure that should be returned (new endpoints only)?
• What permissions should be used?
• Is this EE or CE?
  • EE
  • CE
• Additional comments:

Implementation Table

Group	Issue Link
backend	👈 You are here
backend	#387803 (closed)
frontend	#380873 (closed)

Links/References