Database Autonomy Service:Slow query logs

Video introduction

View the slow query logs of a single instance

This topic uses an ApsaraDB RDS for MySQL database as an example to describe how to view the slow query logs of a single instance.

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, click the instance ID, and then go to the instance details page.

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

5. On the Slow Log Analysis tab, you can select a time range to view the Slow Query Log Trends, Event Distribution, Slow Query Log Statistics, and Slow Query Log Details.

   Note

   When you select a time range, the end time must be later than the start time. The maximum time range is 7 days. You can query slow query log information generated within the last month.

   • In the Slow Query Log Trends chart, you can select a point in time to view the corresponding Slow Query Log Statistics, and Slow Query Log Details.

     Note

     If a slow SQL statement is too long to be fully displayed, you can hover the pointer over the statement. The complete statement appears in a dialog box.

   • Click to save the slow log information locally.

   • Click to go to OpenAPI Explorer and debug the API with the parameters that you selected.

   • In the Event Distribution section:

     You can query slow log events within the specified time range. Click an event to view its details.

   • In the Slow Query Log Statistics section:

     • Above the list, you can select filter conditions to filter the data. The available filter conditions vary based on the database engine.

     • Click the ID of a specific SQL template in the SQL ID column to view its correlations and a list of details, such as user distribution, client distribution, and metric trends.

     • In the Actions column for the target SQL template, click Optimize. In the Optimize dialog box, you can view the SQL diagnosis results.

       Note

       DAS diagnoses the SQL statement based on its complexity, the data volume of the related table, and the database payload. The diagnosis may take more than 20 seconds to complete. After the diagnosis is complete, the diagnostics engine provides a diagnosis result, optimization suggestions, and expected performance improvements. You can decide whether to accept the suggestions based on the result.

     • In the Actions column for the target SQL template, click Throttling. On the Create Throttling Rule page, configure the throttling parameters for the SQL statement. For more information, see SQL throttling.

     • For a PolarDB for MySQL database instance, click IMCI in the Actions column for the target SQL template to view the In-Memory Column Index (IMCI) documentation.

       Note

       • The IMCI button is displayed for a PolarDB for MySQL instance if it does not have an IMCI node, the Maximum Execution Duration of its slow query log exceeds 20 seconds, and the Maximum Scanned Rows exceeds 200,000.

       • You can use IMCI to improve query performance for complex queries on large data volumes.

   • In the Slow Query Log Details section, you can also perform SQL Diagnostic Optimization and SQL Throttling for a target SQL statement by clicking Optimize and Throttling in the Actions column.

View global slow query logs

If you have multiple database instances, you can go to the Global Slow Log Trend page. This page displays the overall slow log trend for the selected database engine and the slow query log statistics for the top 10 instances.

1. Log on to the DAS console.

2. In the navigation pane on the left, go to Intelligent O&M Center > Slow Logs to view the Global Slow Log Trend and Top Instance Statistics.

   Note

   When you select a time range, the end time must be later than the start time. You can query slow query log information generated within the last 14 days.

FAQ

• Q: For ApsaraDB RDS for MySQL and PolarDB for MySQL instances, why is the completion time in the slow query log different from the actual execution time of the SQL statement?

  A: This issue usually occurs when the time zone is modified by an SQL statement. The time zone for the execution time recorded in a slow query log can be at the session, database, or system level. The logic for setting the time in the slow query log is as follows: If a database time zone is set, that time zone is used. Otherwise, the system time zone is used. If you modify the time zone at the session level using an SQL statement, the time zone in the slow query log record may not be converted correctly.

• Q: Why can I access the old version of the Slow Query Log page but encounter a permission error, such as NoPermission or Forbidden.RAM, when I try to access the new version?

  A: The new Slow Query Log page is redesigned to provide more powerful data filtering and selection features, and the required access policies (actions) are different from those for the old page. If you encounter a permission error, see RAM user authorization and grant the following permissions to the RAM user:

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

  • Recommended permissions: CreateSqlTag (to tag an SQL statement) and DescribeDasQueryTagNames (to retrieve SQL tags).

References

You can enable the automatic management feature of DAS to automatically optimize slow SQL statements when they are detected in your database instance.

• Automatic SQL optimization

• Automatic SQL throttling