Updated on 2023-03-15 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.
  • The default threshold of slow query logs is 500 ms. If the execution time of a statement exceeds the threshold, a slow query log is recorded in the system.profile table in the current database.
  • The attribute of the slow query log table system.profile is capped. When the size of the table 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.

Parameter description

Table 1 Parameters related to DDS slow query logs

Parameter

Description

operationProfiling.mode

Specifies database profiling (analysis) level. The default value is slowOp.

  • off: Disables the analyzer and data collection.
  • slowOp: Collects data related to queries that exceed a given threshold of execution time.
  • all: Collects all operations data.

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 and click Search 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

  8. Specify Keyword, Execution Time (ms), Database, and Table, 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.