Global slow query log analysis

Video introduction

Slow query logs for a single instance

This section uses an RDS for MySQL database instance as an example.

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. On the Slow Log Analysis tab, select a time range to view the Slow Log Trend, Event Distribution, Slow Log Statistics, and Slow Log Details for that period.

   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 specific point in time to view the corresponding Slow Log Statistics and Slow Log Details.

     Note

     If a slow query statement is too long to be fully displayed, hover over the statement to view its complete content in a tooltip.

   • Click to save the slow query log information locally.

   • Click to open the OpenAPI Explorer with the current parameters pre-filled for API debugging.

   • In the Event Distribution section:

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

   • In the Slow Log Statistics section:

     • Above the list, you can select filters to refine the data. The available filters vary by database engine.

     • Click the ID in the SQL ID column for a SQL template to view its correlation analysis and details, including user distribution, client distribution, and metric trends.

     • In the Actions column for a SQL template, click Optimize. In the SQL Diagnostic Optimization dialog box, view the SQL diagnosis results.

       If you accept the suggestion, click Copy in the upper-right corner and paste the optimized SQL into your database client or DMS to execute it. If not, click Cancel to close the dialog.

       Note

       DAS performs SQL diagnosis based on the complexity of the SQL statement, the data volume in the related tables, and the current database load. The diagnosis may take more than 20 seconds. After the diagnosis is complete, DAS provides a diagnosis result, optimization suggestions, and an estimated performance improvement, allowing you to decide whether to adopt the suggestions.

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

     • For a PolarDB for MySQL database instance, click IMCI in the Actions column for a SQL template to view the documentation for in-memory column index (IMCI).

       Note

       • The IMCI button appears if a PolarDB for MySQL instance lacks an in-memory column index node and a slow query meets two criteria: its Max Execution Time exceeds 20 seconds, and its Max Scanned Rows exceed 200,000.

       • For complex queries on large datasets, we recommend using in-memory column index (IMCI) to improve query performance.

   • In the Slow Log Details section, you can also click Optimize and Throttling in the Actions column for a target SQL statement to perform SQL Diagnostic Optimization or apply SQL Throttling.

Global slow query logs

If you have multiple database instances, you can go to the Global Slow Log Trend page to view the overall slow query 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 left-side navigation pane, click Intelligent O&M Center > Slow Logs to view the Global Slow Log Trend and Top Instance Statistics.

   Note

   The end time must be later than the start time. You can query data from the last 14 days.

FAQ

• Q: For RDS for MySQL and PolarDB for MySQL instances, why does the completion time recorded in the slow query log sometimes differ from the actual SQL execution time?

  A: This usually happens when a session-level SQL statement changes the time zone. The slow query log records timestamps based on the database's time zone by default, or the system's time zone if a database-level one is not set. If a session modifies its time zone after connecting, the timestamp recorded in the log may not be converted correctly, leading to a discrepancy.

• 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 has been redesigned with more powerful data filtering and screening features, which require different permission policies than the legacy page. If you do not have the required permissions, you cannot access the new page. For instructions, 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).