Updated on 2024-04-01 GMT+08:00

Slow Query Logs

Slow query logs record statements that exceed operationProfiling.slowOpThresholdMs (500 seconds by default). You can view log details and statistics to identify statements that are executing slowly and optimize the statements. You can also download slow query logs for service analysis.

Precautions

  • Community Edition instances allow you to view and export log details, enable Show Original Log, and download log files on the management console.
  • The Show Original Log function cannot be enabled when you delete DB instances, add nodes, change DB instance class, rebuild secondary node, or the DB instance is frozen.
  • If Show Original Log is being enabled, you cannot delete instances, add nodes, or change instance class.
  • By default, if the execution time of a SQL statement exceeds 500 ms, a slow query log is recorded.
  • When the size of slow query logs reaches a specified threshold, old data is automatically deleted. If you need to analyze slow query logs, download the logs on the console in a timely manner.
  • You can query slow logs for the last 30 days.
  • You cannot delete slow query logs of DDS.
  • When you export data on the Log Details page, all logs displayed on the current page will be exported.
  • For details about how to sort slow query logs by field, such as execution completion time, SQL statement, client IP address, user, execution duration, lock wait time, scanned documents, returned documents, and scanned indexes, see "Slow Query Logs" in Data Admin Service (DAS) User Guide.
  • To apply for the advanced search permission, submit a service ticket by choosing Service Tickets > Create Service Ticket in the upper right corner of the management console. DB instances of 230830 and later versions support the advanced search function.

Parameter description

Table 1 Parameters related to DDS slow query logs

Parameter

Description

operationProfiling.slowOpThresholdMs

Queries that exceed the threshold in the unit of ms are deemed slow. The default value is 500 ms.

Unless otherwise specified, keeping the default value is recommended.

Enabling Show Original Log

  • If Show Original Log is enabled, original logs are displayed. By default, the system automatically deletes original logs after 30 days, and the period cannot be changed.
  • If the instance a slow query log belongs to is deleted, related logs are deleted along with it.
  • Show Original Log can be disabled after it is enabled. The slow query logs reported before the function is disabled are displayed. The slow query logs reported after the function is disabled are not displayed.
  1. Log in to the management console.
  2. Click in the upper left corner and select a region and a project.
  3. Click in the upper left corner of the page and choose Databases > Document Database Service.
  4. On the Instances page, click the instance name.
  5. In the navigation pane on the left, choose Slow Query Logs.
  6. On the displayed page, click Slow Query Logs. Then, click on the Log Details tab.

    Figure 1 Enabling Show Original Log

  7. In the displayed dialog box, click Yes to enable the function of slowing original logs.

Viewing and Exporting Log Details

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and a project.
  3. Click in the upper left corner of the page and choose Databases > Document Database Service.
  4. On the Instances page, click the instance name.
  5. In the navigation pane on the left, choose Slow Query Logs.
  6. On the Slow Query Logs page, set search criteria on the Log Details tab to view log information.

    Figure 2 Querying slow query logs
    • Log records of all shards of a cluster instance
    • Log records of all nodes in a replica set instance
    • Slow query logs of a node in different time periods
    • Slow query statements of the following levels
      • All statement type
      • INSERT
      • QUERY
      • UPDATE
      • REMOVE
      • GETMORE
      • COMMAND
      • KILLCURSORS
    • You can view up to 2,000 slow logs of a specified node type, at a specified level, and within a specified period.

  7. On the Log Details tab, click Advanced Search.

    Figure 3 Advanced search
    • To apply for the advanced search permission, submit a service ticket by choosing Service Tickets > Create Service Ticket in the upper right corner of the management console. DB instances of 230830 and later versions support the advanced search function.

  8. Specify Keyword, Execution Time (ms), Returned Documents, Scanned Documents, Database, Table, Scaned Indexes, Username, and Client IP Address and click Search to view log information.

    Figure 4 Setting advanced search parameters

  9. To clear the parameter settings of Advanced Search, click Reset.

    Figure 5 Resetting advanced search parameters

  10. On the Log Details tab, click in the upper right corner of the log list to export log details.

    • View the .csv file exported to your local PC.
    • Up to 2,000 log details can be exported at a time.

Downloading Logs

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and a project.
  3. Click in the upper left corner of the page and choose Databases > Document Database Service.
  4. On the Instances page, click the instance name.
  5. In the navigation pane on the left, choose Slow Query Logs.
  6. On the Slow Query Logs page, click the Log Files tab. Locate a log whose status is Preparation completed and click Download in the Operation column.

    Figure 6 Slow Query Logs
    • The system automatically loads the downloading preparation tasks. The time required depends on the log file size and the network environment.
      • During the downloading preparation, the log status is Preparing.
      • Once the logs are ready for download, the log status changes to Preparation completed.
      • If the downloading preparation fails, the log status is Abnormal.
    • You can download only one log file from a node. The maximum size of a log file to be downloaded is 40 MB.
    • The download link is valid for 5 minutes. After the download link expires, a message is displayed indicating that the download link has expired. To download the log, click OK.