Scheduled Query Acceleration

Introduced 2.17

Scheduled Query Acceleration (SQA) is designed to optimize queries sent directly from OpenSearch to external data sources, such as Amazon Simple Storage Service (Amazon S3). It uses automation to address issues commonly encountered when managing and refreshing indexes, views, and data.

Query acceleration is facilitated by secondary indexes like skipping indexes, covering indexes, or materialized views. When queries run, they use these indexes instead of directly querying Amazon S3.

The secondary indexes need to be refreshed periodically in order to remain current with the Amazon S3 data. This refresh operation can be scheduled using either an internal scheduler (within Spark) or an external scheduler.

SQA provides the following benefits:

• Cost reduction through optimized resource usage: SQA reduces the operational load on driver nodes, lowering the costs associated with maintaining auto-refresh for indexes and views.

• Improved observability of refresh operations: SQA provides visibility into index states and refresh timing, offering insights into data processing and the current system state.

• Better control over refresh scheduling: SQA allows flexible scheduling of refresh intervals, helping you to manage resource usage and refresh frequency according to specific requirements.

• Simplified index management: SQA enables updates to index settings, such as refresh intervals, in a single query, which simplifies workflows.

Concepts

Before configuring SQA, familiarize yourself with the following topics:

• Optimizing query performance using OpenSearch indexing
• Flint index refresh
• Index State Management

Prerequisites

Before configuring SQA, verify that the following requirements are met:

• Ensure you’re running OpenSearch version 2.17 or later.
• Ensure you have the SQL plugin installed. The SQL plugin is included in most OpenSearch distributions. For more information, see Installing plugins.
• Ensure you have configured a data source (in this example, Amazon S3): Configure a skipping index, covering index, or materialized view. These secondary data sources are additional data structures that improve query performance by optimizing queries sent to external data sources, such as Amazon S3. For more information, see Optimizing query performance using OpenSearch indexing.
• Configure Amazon EMR Serverless (needed for access to Apache Spark).

Configuring SQA settings

If you want to override default configuration values, change the following cluster settings:

• Enable asynchronous query execution: Set plugins.query.executionengine.async_query.enabled to true (default value):

   PUT /_cluster/settings
   {
     "transient": {
       "plugins.query.executionengine.async_query.enabled": "true"
     }
   }
  

  For more information, see Settings.

• Configure the external scheduler interval for asynchronous queries: This setting defines how often the external scheduler checks for tasks, allowing customization of refresh frequency. There is no default value for this setting: if this value is empty, the default comes from opensearch-spark and is 5 minutes. Adjusting the interval based on workload volume can help you to optimize resources and manage costs:

    PUT /_cluster/settings
    {
      "transient": {
        "plugins.query.executionengine.async_query.external_scheduler.interval": "10 minutes"
      }
    }
  

  For more information, see Settings.

Running an accelerated query

You can run accelerated queries in Query Workbench. To run an accelerated query, use the following syntax:

CREATE SKIPPING INDEX example_index
WITH (
    auto_refresh = true,
    refresh_interval = '15 minutes'
);

By default, the query uses an external scheduler. To use an internal scheduler, set scheduler_mode to internal:

CREATE SKIPPING INDEX example_index
WITH (
    auto_refresh = true,
    refresh_interval = '15 minutes',
    scheduler_mode = 'internal'
);

Parameters

When creating indexes using an accelerated query, you can specify the following parameters in the WITH clause to control refresh behavior, scheduling, and timing.

For more information and additional available parameters, see Flint index refresh.

Time units

You can specify the following time units when defining time intervals:

• Milliseconds: ms, millisecond, or milliseconds
• Seconds: s, second, or seconds
• Minutes: m, minute, or minutes
• Hours: h, hour, or hours
• Days: d, day, or days

Monitoring index status

To monitor the status of an index, use the following statement:

SHOW FLINT INDEXES IN spark_catalog.default;

Managing scheduled jobs

Use the following commands to manage scheduled jobs.

Enabling jobs

To disable auto-refresh using an internal or external scheduler, set auto_refresh to false:

ALTER MATERIALIZED VIEW myglue_test.default.count_by_status_v9 WITH (auto_refresh = false);

Updating schedules

To update the schedule and modify the refresh settings, specify the refresh_interval in the WITH clause:

ALTER INDEX example_index
WITH (refresh_interval = '30 minutes');

Switching the scheduler mode

To switch the scheduler mode, specify the scheduler_mode in the WITH clause:

ALTER MATERIALIZED VIEW myglue_test.default.count_by_status_v9 WITH (scheduler_mode = 'internal');

Inspecting scheduler metadata

To inspect scheduler metadata, use the following request:

GET /.async-query-scheduler/_search

Best practices

We recommend the following best practices when using SQA.

Performance optimization

• Recommended refresh intervals: Choosing the right refresh interval is crucial for balancing resource usage and system performance. Consider your workload requirements and the freshness of the data you need when setting intervals.

• Concurrent job limits: Limit the number of concurrent running jobs to avoid overloading system resources. Monitor system capacity and adjust job limits accordingly to ensure optimal performance.

• Resource usage: Efficient resource allocation is key to maximizing performance. Properly allocate memory, CPU, and I/O based on the workload and the type of queries you’re running.

Cost management

• Use an external scheduler: An external scheduler offloads refresh operations, reducing demand on core driver nodes.

• Configure a refresh interval for your use case: Longer refresh intervals lead to reduced costs but may impact data freshness.

• Optimize the refresh schedule: Adjust refresh intervals based on workload patterns to reduce unnecessary refresh operations.

• Monitor costs: Regularly monitor costs related to scheduled queries and refresh operations. Using observability tools can help you gain insights into resource usage and costs over time.

Validating settings

You can validate your settings by running a test query and verifying the scheduler configuration:

SHOW FLINT INDEXES EXTENDED

For more information, see the OpenSearch Spark documentation.

Troubleshooting

If the refresh operation is not triggering as expected, ensure that the auto_refresh setting is enabled and the refresh interval is properly configured.