Help Center/ TaurusDB/ User Guide/ DBA Assistant/ SQL Analysis and Tunning/ Viewing Slow Query Logs
Updated on 2025-08-21 GMT+08:00
View PDF
Share

Viewing Slow Query Logs

Scenarios

Slow Query Logs displays a chart of SQL statements that are taking too long to execute and allows you to sort slow SQL statements by multiple dimensions, such as by user, host, or SQL template. It helps you quickly identify bottlenecks and improve instance performance.

Constraints

  • If you did not subscribe to Intelligent O&M, you can only view the data of the last hour. The data will be automatically deleted when it expires. You are advised to subscribe to Intelligent O&M. This will allow data to be stored for up to 30 days. For details, see Subscribing to Intelligent O&M.
  • After Collect Slow Query Logs is enabled, SQL text will be stored in OBS.
  • Incorrectly optimizing slow queries may cause service exceptions. Exercise caution when performing this operation.

Slow Query Log Configurations

Subscribing to Intelligent O&M

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > TaurusDB.
  4. On the Instances page, click the instance name.
  5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
  6. Click the Slow Query Logs tab.
  7. Click Subscribe. In the displayed dialog box, you can learn about Intelligent O&M functions and pricing.

    Your instance will get 5 GB of storage for free after it has subscribed to Intelligent O&M.

    Figure 1 Subscribing to Intelligent O&M

  8. Select "I have read and understand the billing rules." and click Subscribe.

Collecting Slow Query Logs

Click Log Settings in the upper right corner. In the displayed dialog box, enable Collect Slow Query Logs and click OK. SQL statement logs can then be stored for analysis.

If you do not need to collect slow query logs later, you can disable Collect Slow Query Logs. If Collect Slow Query Logs is disabled, new SQL statements will not be collected anymore and the collected SQL data will be deleted.

Slow Query Log Storage

  • Intelligent O&M subscribed:
    Click Log Settings in the upper right corner to set slow query log retention days.
    • Slow Query Log Period: The default value is 7. The value ranges from 1 to 30. After the period expires, the logs are automatically deleted.
    • SQL Insights Retention Period: The default value is 7. The value ranges from 1 to 180.
    • Log Size: Each paid instance can use 5 GB of storage for slow query logs for free. Any storage used in excess of 5 GB will be billed on a pay-per-use basis.
    Figure 2 Log storage and archiving

  • Intelligent O&M not subscribed:
    • Slow Query Log Period: The default value is 1 hour and cannot be changed. After the period expires, the logs are automatically deleted.
    • SQL Insights Retention Period: 1 hour
    Figure 3 Log storage and archiving

Viewing Slow Query Logs

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > TaurusDB.
  4. On the Instances page, click the instance name.
  5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
  6. Click the Slow Query Logs tab.
  7. Select a time range, and view slow queries over time by instance or node.

    You can view slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period.

  8. Above the chart, switch to another instance or node to view its slow queries over time.

    Move the cursor to a point in time of the chart to view the number of slow query logs and CPU usage at the point in time.

    Figure 4 Viewing slow queries over time

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > TaurusDB.
  4. On the Instances page, click the instance name.
  5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
  6. Click the Slow Query Logs tab.
  7. Select an instance, node, and time range, and view slow query log details. The details include the SQL statement, execution start time, database, client, user, execution duration, lock wait duration, and scanned and returned rows.

    You can view slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period.

    Figure 5 Viewing slow query log details

    • Export slow query log details.
      1. Click Export.
      2. In the displayed dialog box, select Quick export or Export all for Export Mode.
        Figure 6 Exporting slow query log details

        • Quick export: Log details are exported to your local PC. Only the first 1,000 records can be exported.
        • Export all: Select an OBS bucket and a directory to export log details to the OBS bucket. Up to 100,000 records can be exported.

          If no OBS bucket is available, click Create. In the displayed dialog box, enter an OBS bucket name, and click OK.

          Creating an OBS bucket is free, but you will be billed for storing data in the bucket. For pricing details, see OBS Pricing Details.

          A bucket name:
          • Cannot be the same as that of any existing bucket.
          • Can contain 3 to 63 characters. Only lowercase letters, digits, hyphens (-), and periods (.) are allowed.
          • Cannot start or end with a period (.) or hyphen (-), and cannot contain two consecutive periods (.) or contain a period (.) and a hyphen (-) adjacent to each other.
          • Cannot be an IP address.
          • If the bucket name contains a period (.), certificate-based verification is required when you use the name to access an OBS bucket or object.
      3. Click OK.
      4. After the slow query log details are exported, click View All Export Records. In the displayed dialog box, click Download in the Operation column. After the download is complete, view the slow query log details in the ZIP package.
    • To add a SQL throttling rule, locate a record and click SQL Throttling in the Operation column. In the displayed dialog box, specify SQL Type, Keyword, and Max. Concurrent Requests, and click OK. For details, see Configuring SQL Throttling.
    • To view SQL diagnosis results, locate a record and click Diagnose in the Operation column. In the displayed dialog box, confirm the slow SQL statement to be diagnosed and click OK. In the Diagnosis Details area, you can see the SQL diagnosis results.
      Figure 7 Diagnosing a SQL statement

      Figure 8 Viewing diagnosis details

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > TaurusDB.
  4. On the Instances page, click the instance name.
  5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
  6. Click the Slow Query Logs tab.
  7. Select an instance, node, and time range. In the Top 5 Slow Query Logs area, view the top 5 slow SQL statements by user or client IP address.

    You can view slow query logs in the last 1 hour, 3 hours, 12 hours, or a custom time period.

    Figure 9 Viewing top 5 slow query logs

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner of the page and choose Databases > TaurusDB.
  4. On the Instances page, click the instance name.
  5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
  6. Click the Slow Query Logs tab.
  7. Select an instance, node, and time range, and view the template statistics.

    Figure 10 Viewing template statistics

    • Click View Sample in the Operation to view the sample of a SQL template.
    • Export template statistics.
      1. Click Export.
      2. In the displayed dialog box, select Quick export or Export all for Export Mode.
        Figure 11 Exporting slow query log details

        • Quick export: Log details are exported to your local PC. Only the first 1,000 records can be exported.
        • Export all: Select an OBS bucket and a directory to export log details to the OBS bucket. Up to 100,000 records can be exported.

          If no OBS bucket is available, click Create. In the displayed dialog box, enter an OBS bucket name, and click OK.

          Creating an OBS bucket is free, but you will be billed for storing data in the bucket. For pricing details, see OBS Pricing Details.

          A bucket name:
          • Cannot be the same as that of any existing bucket.
          • Can contain 3 to 63 characters. Only lowercase letters, digits, hyphens (-), and periods (.) are allowed.
          • Cannot start or end with a period (.) or hyphen (-), and cannot contain two consecutive periods (.) or contain a period (.) and a hyphen (-) adjacent to each other.
          • Cannot be an IP address.
          • If the bucket name contains a period (.), certificate-based verification is required when you use the name to access an OBS bucket or object.
      3. Click OK.
      4. After the slow query log details are exported, click View All Export Records. In the displayed dialog box, click Download in the Operation column. After the download is complete, view the slow query log details in the ZIP package.

Parent Topic: SQL Analysis and Tunning