Database Autonomy Service:Slow query logs

Video introduction

%E6%96%B0%E7%89%88%E6%85%A2%E6%97%A5%E5%BF%97%E4%BB%8B%E7%BB%8D.mp4

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB for MongoDB replica set instance or sharded cluster instance

• RAM user permissions for the slow query log page. The redesigned page requires a different set of API actions than the previous version. Grant the following permissions to the RAM user before accessing the page:

  Permission	API action
  View slow log trend chart	DescribeSlowLogHistogramAsync
  View slow log template statistics	DescribeSlowLogStatistic
  View slow log details	DescribeSlowLogRecords
  View Event Center data	GetInstanceEventWithGroupType
  View Performance Trend data	GetPerformanceMetrics
  (Recommended) Tag SQL statements	CreateSqlTag
  (Recommended) Retrieve SQL tags	DescribeDasQueryTagNames

  For instructions, see RAM user authorization.

Background information

Slow query logs are generated by the database kernel. The parameters and thresholds that define a slow query vary by database engine. Refer to the official documentation for your database engine to understand the applicable settings.

View and analyze slow query logs

1. Log on to the DAS console.

2. In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.

3. Find the target instance and click the instance ID to open the instance details page.

4. In the left navigation pane, click Request Analysis > Slow Logs.

5. Select a time range to load the Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details sections.

   Note

   The end time must be later than the start time. The maximum time range is seven days. Logs from the past month are available for query.

   The page provides four views of your slow query data:

   • Slow Log Trend: A time-series chart of slow query counts. Click any point in the chart to filter Slow Log Statistics and Slow Log Details to that specific moment.

   • Event Distribution: A breakdown of slow log events within the selected time range. Click an event to see its details.

   • Slow Log Statistics: A grouped view of slow queries by log template. In the Actions column, you can:

     • Click Details to view the Slow Log Sample for a specific log template.

     • Click Optimize to get index optimization suggestions for queries with Operation Type = query.

   • Slow Log Details: Individual slow query records.

   Additional options:

   • If a slow SQL statement is truncated in the table, hover over it to see the complete statement.

   • For sharded cluster instances, use the Node ID dropdown to filter slow queries by node.

   • Click to save the slow log information locally.

   • Click to open OpenAPI Explorer and debug the API with the current parameters.

Get index optimization suggestions

In Slow Log Statistics, click Optimize in the Actions column for a log template. The Optimize dialog displays index recommendations generated from the slow query pattern.

This feature is available only for queries with Operation Type = query.

Supported patterns:

Unsupported patterns:

• Indexes that already exist on the collection are not suggested again.

• Special index types (text, unique, hash) are not supported.

• Queries with complex logical operators ($or, $and) are not supported.

• Only find operations are analyzed. Sorting ($sort) and collation ($collation) operations are excluded.

To apply a suggestion:

Click Copy in the upper-right corner of the dialog, then paste and run the index creation statement in a database client or DMS.

To dismiss the suggestion without applying it, click Cancel.

FAQ