Database Autonomy Service:Slow query log

Prerequisites

The database engine is PolarDB for MySQL.

Procedure

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.

   • 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 SQL statement is too long to be fully displayed, hover the pointer over the statement to view the complete text in a pop-up window.

   • In the Event Distribution section, you can find slow log events within the specified time range. Click an event to view its details.

   • From the Node ID drop-down list, you can view the number of slow requests for each node.

   • On the Slow Query Log Statistics and Slow Query Log Details tabs, click to save the slow query log information to a local file.

   • Click to go to OpenAPI Explorer and debug the API. The currently selected and entered parameters are passed automatically.

   • In the Slow Log Statistics section:

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

     • Click an ID in the Query ID column of a SQL template to view its correlation analysis and a detailed list, including user distribution, client distribution, and metric trends.

     • In the Actions column for the target 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 statement into a database client or DMS to run it. If you do not accept the suggestion, click Cancel to close the dialog box.

       Note

       DAS diagnoses SQL statements based on their complexity, the data volume of related tables, and the database load. The diagnosis may take more than 20 seconds to complete. Once complete, the SQL diagnostics engine provides a result, an optimization suggestion, and the expected performance improvement. You can then decide whether to accept the suggestion.

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

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

       Note

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

       • For complex queries on large volumes of data, we recommend that you use In-Memory Column Index (IMCI) to improve query performance.

   • In the Slow Log Details section, you can also click Optimize or Throttling in the Actions column for a target SQL statement to perform SQL Diagnostic Optimization or 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 documentation

You can enable the autonomy features of DAS to automatically optimize slow SQL queries on your database instance.

• Automatic SQL Optimization

• Automatic SQL Throttling