Performance issues with DagsterGraphQL API

[metinsenturk]

Hi,

I use DagsterGraphQL to query run data from dagster API, and when I use tags in my query. query times out. Below is my query:

query runsOrErrorQuery($job_name: String!, $my_tag: String!, $my_tag2: String!, $limit: Int = 10) {
  runsOrError(
    filter: {
      pipelineName: $job_name
      tags: [
        { key: "my_tag", value: $my_tag }
        { key: "my_tag2", value: $my_tag2 }
      ]
    }
    limit: $limit
  ) {
    __typename
    ... on Runs {
      results {
        ... basicRunInformation
      }
    }
  }
}


fragment basicRunInformation on Run {
  # runId
  jobName
  # statusa
  # updateTime
  # startTime
  # endTime
  # tags {
  #   key
  #   value
  # }
}

And this errors out timeout in GraphiQL UI and in my code. See the error below.

{
  "error": {
    "data": null,
    "errors": [
      {
        "message": "(psycopg2.errors.QueryCanceled) canceling statement due to statement timeout\n\n[SQL: SELECT runs.id, runs.run_body, runs.status, runs.create_timestamp, runs.update_timestamp, runs.start_time, runs.end_time \nFROM runs JOIN (SELECT run_tags.id AS id, run_tags.run_id AS run_id, run_tags.key AS key, run_tags.value AS value \nFROM run_tags) AS run_tags_subquery_1 ON runs.run_id = run_tags_subquery_1.run_id AND run_tags_subquery_1.key = %(key_1)s AND run_tags_subquery_1.value = %(value_1)s JOIN (SELECT run_tags.id AS id, run_tags.run_id AS run_id, run_tags.key AS key, run_tags.value AS value \nFROM run_tags) AS run_tags_subquery_2 ON runs.run_id = run_tags_subquery_2.run_id AND run_tags_subquery_2.key = %(key_2)s AND run_tags_subquery_2.value = %(value_2)s \nWHERE runs.pipeline_name = %(pipeline_name_1)s ORDER BY runs.id DESC \n LIMIT %(param_1)s]\n[parameters: {'key_1': 'my_tag', 'value_1': 'my_tag_value', 'key_2': 'my_tag2', 'value_2': 'my_tag2_value', 'pipeline_name_1': 'my_job_pipeline', 'param_1': 10}]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)",
        "locations": [
          {
            "line": 5,
            "column": 7
          }
        ],
        "path": [
          "runsOrError",
          "results"
        ],
        "extensions": {
          "errorInfo": {
            "__class__": "SerializableErrorInfo",
            "message": "sqlalchemy.exc.OperationalError: (psycopg2.errors.QueryCanceled) canceling statement due to statement timeout\n\n[SQL:....
...

• I have more than 500 runs in total and the query should return 3-5 runs.
• Every job is tagged with my_tag but may not be tagged with my_tag2
• My dagster instance is dagster==1.5.6

My questions and suggestions are:

• What is the total number of runs that will start causing TimeOut in GraphQL API?
• Is there a way to improve this, such as, by adding an index in run_tags table

[alangenfeld]

What is the total number of runs that will start causing TimeOut in GraphQL API?

The timeout error here occurring is a DB statement timeout the webserver uses. You can control the DB statement timeout of your webserver using the --db-statement-timeout argument to dagster-webserver (the default is 15 seconds) .

The data volume that will cause this query to timeout is a function of the resources that your DB has, so its hard to cite any specific numbers.

Is there a way to improve this, such as, by adding an index in run_tags table

The best way to understand why it is not performing well is to run an explain analyze on the SQL query directly against your DB and examine / share the output.

If this is a Dagster deployment that you have been using over a number of versions, you may need to run dagster instance migrate or dagster intance reindex to pick up index changes from more recent versions.

Its also viable to add your own custom indexes that are suited to specific queries you want to be more performant.