Query optimization
Slow queries are a common issue in OpenSearch, often caused by inefficient user operations, an excessive number of filter conditions, or a sudden spike in QPS. The slow query analysis feature in OpenSearch helps you identify slow queries with high Logical Computing Unit (LCU) consumption within a specified time range. It also provides optimization suggestions to help you reduce costs.
Benefits
-
Free of charge: This feature is free to use.
-
On-demand analysis: Analyze slow query records from the past month and receive optimization suggestions.
-
Comprehensive metrics: Analyze the causes of slow queries with various metrics, including average consumption, peak consumption, and percentage of total queries. The results can be sorted by any of these metrics.
Usage notes
-
You can analyze data from the previous day only. Historical analysis results are retained for up to 30 days.
Procedure
1. Enable the slow query analysis service: The first time you use query optimization, navigate to Feature Extensions > Query Optimization in the left-side navigation pane.
Select the application for which you want to enable slow query analysis, select the Enable Slow Query Analysis and Read Commitment for Use checkboxes, and then click Enable.
2. Analyze query data: After the service is enabled, you can analyze data either by date or by RequestId:
By date: Click Analyze.
Wait for the analysis to complete.
By RequestId: Enter a valid RequestId and click Query.
The query returns a results table with columns for optimization suggestion and optimization example. Common suggestions include: reducing sorted_doc_count_in_qrs (by lowering the start+hit value, for example, optimizing hit:500 to hit:50), reducing seek_times (by adding more query conditions or using a filter clause), and reducing match_doc_count (by adding more query or filter conditions, such as appending AND status:4). Each suggestion includes a side-by-side comparison of the original and optimized query.
The system displays an error message if the input format is incorrect.
For example, if you enter an invalid RequestId such as 123 and click Query, an error message appears, stating "No query analysis suggestion found. Check whether the RequestId is valid." The error code is Optimizer.OperationFailed.NoData.
3. View analysis results: Select a time range to view the corresponding slow query results.
You can also sort the analysis results by different metrics.
Click a slow query category to view its search request details, including the RequestId, Query, total consumption (LCU), retrieval consumption (LCU), statistics consumption (LCU), sort consumption (LCU), summary consumption (LCU), other consumption (LCU), and optimization suggestion.