Slow query log

Prerequisites

This feature is supported for the following database engines:

• ApsaraDB RDS for MySQL

• self-managed MySQL database

Procedure

This topic uses an ApsaraDB RDS for MySQL instance as an example.

1. Log on to the DAS console.

2. In the left-side navigation pane, 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 time range.

   • In the Slow Log Trend chart, you can select a point in time to view the Slow Log Statistics and Slow Log Details for that time.

     Note

     If a slow SQL statement is too long to be fully displayed, you can hover the mouse pointer over the statement to view the complete SQL statement in a pop-up box.

   • On the Slow Query Log Statistics and Slow Query Log Details tabs, you can click to save the slow query log information to your computer.

   • You can click to go to the OpenAPI Explorer console. The currently selected and entered parameters are automatically carried over for API debugging.

   • In the Event Distribution area, you can query for slow log events within a specified time range. Click an event to view its details.

   • In the Slow Log Statistics area:

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

     • Click the data ID in the Query ID column of the target SQL template to view correlations and a detailed list that includes user distribution, client distribution, and metric trends.

     • In the Actions column of the target SQL template, click Optimize. In the SQL Diagnostic Optimization dialog box that appears, view the SQL diagnosis results.

       If you accept the diagnostic suggestion, click Copy in the upper-right corner of the page and paste the optimized SQL statement into your database client or DMS to execute it. If you do not accept the suggestion, click Cancel to end the diagnosis.

       Note

       DAS performs SQL diagnosis based on the complexity of the SQL statement, the data volume of the corresponding table, and the database payload. The diagnostic suggestion may take more than 20 seconds to be returned. After the diagnosis is complete, the diagnostics engine provides a diagnosis result, an optimization suggestion, and the expected optimization benefits. You can decide whether to accept the suggestion based on the diagnosis result.

     • In the Actions column of the target SQL template, click Throttling. On the SQL Throttling page, configure the throttling parameters to apply rate limiting to the target SQL statement. For more information, see SQL throttling.

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

       Note

       • The IMCI button is displayed if a PolarDB for MySQL database instance does not have an In-Memory Column Index node, the Max Execution Time of a slow query log exceeds 20 seconds, and the Max Scanned Rows exceeds 200,000.

       • For complex queries with large data volumes, you can use In-Memory Column Index (IMCI) to improve query performance.

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

FAQ

• Q: Why can't I see any slow query log data?

  A: Slow query log statistics are aggregated by using a real-time computation window, so the latest data appears with a delay of approximately 3 minutes. Also, check the following:

  • The slow query log feature is enabled for the database instance and the threshold is set to a reasonable value.

  • Slow query logs were actually generated within the selected time range.

  • The current account has DAS access permissions for the target instance.

• Q: Why are some instances highlighted in yellow?

  A: A yellow highlight indicates that the RAM user does not have data access permissions for the instance. You can resolve this issue in one of the following ways:

  • Contact an administrator to grant the RAM user access permissions for the instance.

  • Grant Global Group permissions: We recommend that you grant the DASGlobalGroupAdmin permission so that the RAM user can create user groups as needed and view data for all instances to which they have access.

• Q: Why does the slow query log show Rows_sent as 0 even though the query returns data?

  A: This typically occurs when the application uses Server-side Cursor mode. In Cursor mode, a single SQL statement is executed in two separate phases:

  • EXECUTE phase: The server executes the query and generates the result set but does not immediately send data rows to the client. Only metadata such as column definitions is returned.

  • FETCH phase: The client retrieves data rows in batches by issuing FETCH commands.

  The slow query log records statistics for the EXECUTE phase only. During this phase, MySQL has already scanned the data (so Rows_examined is non-zero), but no rows have been sent to the client yet because rows are delivered during the subsequent FETCH phase. This is why Rows_sent is recorded as 0.

  Common scenarios that trigger this behavior:

  • Java/JDBC: The connection URL includes useCursorFetch=true, and PreparedStatement.setFetchSize() is set to a value greater than 0 or Integer.MIN_VALUE.

  • Python: The application uses MySQLdb.cursors.SSCursor or pymysql.cursors.SSCursor.

  • ORM frameworks: Some ORM frameworks default to streaming queries for large result sets. Streaming queries use Cursor mode under the hood.

  To confirm whether this is the cause, check whether your application code configures a fetchSize or uses a streaming cursor. You can also run SHOW GLOBAL STATUS LIKE 'Com_stmt_fetch'; on the database to verify whether FETCH requests are being issued.

• Q: Why is the execution completion time recorded in the slow query log different from the actual execution time of the SQL statement?

  A: This typically happens when an executed SQL statement modifies the time zone. The timestamp in a slow query log can be based on the time zone at the session level, database level, or system level. The log uses the time zone set at the database level, or falls back to the system level time zone if none is set. If an SQL statement changes the time zone at the session level, the timestamp in the log may not be converted correctly, leading to a discrepancy.

Related documents

You can enable the automatic governance features in DAS to automatically optimize slow SQL queries when they occur on your database instance.

• automatic SQL optimization

• automatic SQL throttling