Enable slow query logging

Identify inefficient or time-consuming queries by enabling slow query logging in your Aiven for OpenSearch® service.

Slow query logging records queries that exceed a specified time threshold, helping you diagnose performance issues and optimize query patterns. This is an advanced feature for power users who need to deep dive into query performance analysis. You configure slow query logging using advanced parameters that control the logging behavior at the cluster level.

important

Both the log level and its corresponding threshold must be configured for slow query logging to work. level controls which threshold is applied. Setting only the log level without a threshold will not generate any logs.

"slowlog": {
    "level": "info",
    "threshold": {
        "trace": "1s",
        "debug": "10s",
        "info": "30s",
        "warn": "60s"
    }
}

In this example, info and warn level messages can appear in logs.

note

Slow query logging can impact CPU performance. Start with a higher threshold value and monitor your service performance after enabling logging.

Prerequisites

• Aiven for OpenSearch service
• Access to manage the service configuration:
  • Aiven Console or
  • Aiven CLI with a personal token or
  • Aiven API with a personal token or
  • Aiven Provider for Terraform or
  • Aiven Operator for Kubernetes®

About the configuration parameters

Slow query logging uses two advanced configuration parameters:

• Log level (opensearch.cluster.search.request.slowlog.level): Determines the severity level for logged queries. Choose from debug, info, trace, or warn.
• Threshold (opensearch.cluster.search.request.slowlog.threshold.<level>): Sets the time limit for queries. Queries exceeding this time are logged. The threshold parameters must match the log level you choose.

Enable slow query logging

Console

1. Log in to the Aiven Console.

2. On the Services page, select your Aiven for OpenSearch service.

3. On the Service settings page, scroll to the Advanced configuration section and click Configure.

4. In the Advanced configuration window:

   1. Click Add configuration options. From the list, select opensearch.cluster.search.request.slowlog.level.

   2. Set the value to one of the following: debug, info, trace, or warn.

   3. Click Add configuration options. From the list, select threshold configuration options:

      • opensearch.cluster.search.request.slowlog.threshold.debug
      • opensearch.cluster.search.request.slowlog.threshold.info
      • opensearch.cluster.search.request.slowlog.threshold.trace
      • opensearch.cluster.search.request.slowlog.threshold.warn

   4. Set each threshold value as a number followed by a time unit with no space. Queries exceeding this time will be logged. Start with a higher value like 10s or 20s and adjust based on your needs.

      note

      • Default value: -1 (disabled)
      • Allowed units: s (seconds), m (minutes), h (hours), d (days), nanos (nanoseconds), ms (milliseconds), micros (microseconds)
      • Example values: 1s, 500ms, 2m

   5. Click Save configuration.

CLI

Use the avn service update command to configure slow query logging:

avn service update SERVICE_NAME \
  -c opensearch.cluster.search.request.slowlog.level=LEVEL_A \
  -c opensearch.cluster.search.request.slowlog.threshold.LEVEL_A=THRESHOLD_A \
  -c opensearch.cluster.search.request.slowlog.threshold.LEVEL_B=THRESHOLD_B \

Parameters:

• SERVICE_NAME: Your Aiven for OpenSearch service name
• LEVEL: Log level (debug, info, trace, or warn)
• THRESHOLD: Time threshold (for example, 1s, 500ms, 2m)

Example:

avn service update my-opensearch \
  -c opensearch.cluster.search.request.slowlog.level=info \
  -c opensearch.cluster.search.request.slowlog.threshold.info=10s \
  -c opensearch.cluster.search.request.slowlog.threshold.warn=30s

API

Call the ServiceUpdate endpoint to configure slow query logging:

curl --request PUT \
  --url "https://api.aiven.io/v1/project/PROJECT_NAME/service/SERVICE_NAME" \
  --header "Authorization: Bearer API_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
    "user_config": {
      "opensearch": {
        "cluster_search_request_slowlog": {
          "level": "LEVEL",
          "threshold": {
              "warn": "TIME_LIMIT",
              "info": "TIME_LIMIT",
              "trace": "TIME_LIMIT",
              "debug": "TIME_LIMIT",
            }
          }
        }
      }
    }
  }'

Parameters:

• PROJECT_NAME: Your Aiven project name
• SERVICE_NAME: Your Aiven for OpenSearch service name
• API_TOKEN: Your personal token
• LEVEL: Log level (debug, info, trace, or warn)
• THRESHOLD: Time threshold (for example, 1s, 500ms, 2m)

Example:

curl --request PUT \
  --url "https://api.aiven.io/v1/project/my-project/service/my-opensearch" \
  --header "Authorization: Bearer your-api-token" \
  --header "Content-Type: application/json" \
  --data '{
    "user_config": {
      "opensearch": {
        "cluster_search_request_slowlog": {
          "level": "warn",
          "threshold": {
            "warn": "10s"
          }
        }
      }
    }
  }'

Terraform

Add the slow query logging configuration to your aiven_opensearch resource:

resource "aiven_opensearch" "example_opensearch" {
  project      = var.aiven_project_name
  cloud_name   = "google-europe-west1"
  plan         = "startup-4"
  service_name = "my-opensearch"

  opensearch_user_config {
    opensearch {
      cluster_search_request_slowlog {
        level = "warn"
        threshold {
          warn = "10s"
        }
      }
    }
  }
}

For more configuration options, see the aiven_opensearch resource documentation.

Kubernetes

Add the slow query logging configuration to your OpenSearch resource:

apiVersion: aiven.io/v1alpha1
kind: OpenSearch
metadata:
  name: my-opensearch
spec:
  project: PROJECT_NAME
  cloudName: google-europe-west1
  plan: startup-4

  userConfig:
    opensearch:
      cluster_search_request_slowlog:
        level: warn
        threshold:
          warn: 10s

For more configuration options, see the OpenSearch resource documentation.

View slow query logs

After configuring slow query logging, view the logs in the Aiven Console:

1. Log in to the Aiven Console.
2. On the Services page, select your Aiven for OpenSearch service.
3. Click Logs in the sidebar.
4. Search for slow query entries using the search field or filter by log level.

Slow query log entries include:

• Query execution time
• Query details
• Index name
• Number of shards queried

To send logs to another Aiven for OpenSearch service, see Enable logs integration.

Adjust the threshold

To capture more or fewer slow queries, adjust the threshold value:

• Lower the threshold to capture more queries (for example, change from 10s to 5s)
• Raise the threshold to capture only slower queries (for example, change from 10s to 30s)

tip

Start with a higher threshold (such as 20s to 30s) and gradually lower it to avoid generating excessive logs.

Disable slow query logging

To disable slow query logging, set the threshold to -1:

Console

1. Log in to the Aiven Console.
2. On the Services page, select your Aiven for OpenSearch service.
3. Go to Service settings > Advanced configuration.
4. Locate the threshold parameters you configured.
5. Change its value to -1.
6. Click Save configuration.

CLI

avn service update SERVICE_NAME \
  -c opensearch.cluster.search.request.slowlog.threshold.LEVEL=-1

Replace SERVICE_NAME with your service name and LEVEL with the log level you configured (for example, warn, info).

API

Call the ServiceUpdate endpoint:

curl --request PUT \
  --url "https://api.aiven.io/v1/project/PROJECT_NAME/service/SERVICE_NAME" \
  --header "Authorization: Bearer API_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
    "user_config": {
      "opensearch": {
        "cluster_search_request_slowlog": {
          "threshold": {
            "LEVEL": "-1"
          }
        }
      }
    }
  }'

Replace PROJECT_NAME, SERVICE_NAME, API_TOKEN, and LEVEL with your values.

Terraform

Update the threshold in your aiven_opensearch resource:

resource "aiven_opensearch" "example_opensearch" {
  project      = var.aiven_project_name
  cloud_name   = "google-europe-west1"
  plan         = "startup-4"
  service_name = "my-opensearch"

  opensearch_user_config {
    opensearch {
      cluster_search_request_slowlog {
        level = "warn"
        threshold {
          warn = "-1"
        }
      }
    }
  }
}

Kubernetes

Update the threshold in your OpenSearch resource:

apiVersion: aiven.io/v1alpha1
kind: OpenSearch
metadata:
  name: my-opensearch
spec:
  project: PROJECT_NAME
  cloudName: google-europe-west1
  plan: startup-4

  userConfig:
    opensearch:
      cluster_search_request_slowlog:
        level: warn
        threshold:
          warn: "-1"

Alternative: Query Insights plugin

For continuous query monitoring with less manual configuration, consider using the Query Insights plugin. This plugin provides:

• Automatic tracking of top N queries by CPU usage, latency, or memory
• Query grouping and aggregation
• Integration with OpenSearch Dashboards for visualization
• Lower performance overhead compared to detailed slow query logging

Configure Query Insights using the search.insights.top_queries advanced configuration parameters.

Related pages

• OpenSearch slow query logging documentation
• Advanced parameters for Aiven for OpenSearch
• OpenSearch Query Insights plugin
• OpenSearch top N queries