DocsICP FilingConsole
Documentation
Home Page Database Autonomy Service User Guide MongoDB Slow query log

Slow query log

Updated at:
Copy as MD
Product
My Favorites

Database Autonomy Service (DAS) collects and analyzes slow query logs — requests that exceed a specified execution time threshold — to help you quickly identify and resolve database performance issues.

Video introduction

Prerequisites

The target database must be an ApsaraDB for MongoDB replica set instance or sharded cluster instance.

Background

Slow query logs are generated by the database engine. The configuration parameters and thresholds that define a slow query vary by database engine. For more information, see the official documentation for your database engine.

Procedure

  1. Log on to the DAS console.

  2. In the navigation pane on the left, 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-side navigation pane, click Request Analysis > Slow Logs.

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

    Note

    The end time must be later than the start time, and the time range cannot exceed seven days. You can query slow query logs from the last month.

    • In the Slow Log Trend chart, you can select a point in time to view the Slow Log Statistics and Slow Log Details for that time.

      Note

      If a slow SQL statement is too long to be fully displayed, you can hover the mouse pointer over the statement to view the complete SQL statement in a pop-up box.

    • For sharded cluster instances, use the Node ID drop-down list to view the number of slow queries for each node.

    • On the Slow Query Log Statistics and Slow Query Log Details tabs, you can click image to save the slow query log information to your computer.

    • You can click image to go to the OpenAPI Explorer console. The currently selected and entered parameters are automatically carried over for API debugging.

    • In the Event Distribution area, you can query for slow log events within a specified time range. Click an event to view its details.

    • In the Slow Log Statistics section, click Details in the Actions column of a log template to view its Slow Log Sample .

    • In the Slow Log Statistics section, click Optimize in the Actions column of a log template to view the diagnostic results in the Optimize dialog box.

      Note

      The optimization feature is available only for slow queries where the Operation Type is query. It primarily provides index optimization suggestions.

      Supported scenarios

      • Single-field query: For example, if DAS flags a db.col.find({ a: 1 }) query as slow, it recommends creating the corresponding index: db.col.createIndex({ a: 1 }).

      • Multi-field query: For example, if DAS flags a db.col.find({ a: 1, b: 1 }) query as slow, it recommends creating a compound index: db.col.createIndex({ a: 1, b: 1 }).

      Unsupported scenarios

      • DAS does not recommend indexes that already exist.

      • Special index types, such as text, unique, and hash, are not supported.

      • Queries that contain complex logical operators, such as $or and $and, are not supported.

      • DAS cannot generate index suggestions for operations other than find, such as sorting ($sort) and collation ($collation).

      If you accept the suggestion, click Copy in the upper-right corner, then paste and run the statement in a database client or DMS. To reject the suggestion, click Cancel.

      Important

      Adding an index can degrade the execution plans of other queries and consume resources such as storage. Assess the potential impact on related queries and resource consumption before applying a suggestion.

FAQ

Q: Why can I access the legacy slow query log page but receive a permission error (such as NoPermission or Forbidden.RAM) when trying to access the new page?

A: The new slow query log page uses more powerful data filtering features that require different permission policies than the legacy page. If you lack the required permissions, you cannot access the new page. See RAM user authorization to grant the following permission policies to your RAM user:

  • Required permissions: slow query log trend chart data (DescribeSlowLogHistogramAsync), slow query log template statistics (DescribeSlowLogStatistic), slow query log details (DescribeSlowLogRecords), Event Center data (GetInstanceEventWithGroupType), and performance trend data (GetPerformanceMetrics).

  • Recommended permissions: tag SQL statements (CreateSqlTag) and retrieve SQL tags (DescribeDasQueryTagNames).

Q: Why is command field empty in a request template?

A: When DAS templatizes MongoDB requests, it retains only the following whitelist fields from the original command to avoid an excessive number of templates: filter, updates, query, key, geoNear, isMaster, listDatabases, listCollections, dbStats, splitVector, moveChunk, _migrateClone, _recvChunkCommit, _recvChunkStart, _recvChunkStatus, prepareTransaction, commitTransaction, coordinateCommitTransaction, saslStart, and saslContinue. All other fields are filtered out. If all fields are filtered out, command:{} is displayed, which does not indicate a parsing failure.

Previous：Instance sessionsNext：Storage analysis