Database Autonomy Service:Slow query logs

Core concept: User group

User groups are a core component of the slow log analysis feature. A user group functions as a custom instance monitoring dashboard. You can create a user group to perform the following operations:

• Logical grouping: Group database instances from different lines of business for easier management. Each user group can contain up to 10 instances.

• Unified view: View the slow log trends, event distribution, and SQL statistics for all instances in a group from a single page.

• Efficient switching: Quickly switch between different business focus areas without having to repeatedly select instances.

Prerequisites

• Supported database engines: Your database instance must use one of the following engine types:

  • Relational databases:

    • RDS MySQL, PolarDB for MySQL, MyBase MySQL

    • RDS SQL Server, MyBase SQL Server

    • RDS PostgreSQL, PolarDB for PostgreSQL, PolarDB for PostgreSQL (Compatible with Oracle)

    • PolarDB-X 2.0

  • NoSQL databases:

    • Tair (Redis-compatible), MyBase Redis

    • ApsaraDB for MongoDB

• Instance access: The target database instance must be connected to DAS. For more information, see Connect to a database instance.

• Region support:

  • Slow query log details: This feature is supported in all regions.

  • Real-time slow query log statistics: This feature is supported in the Chinese mainland, Hong Kong (China), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Japan (Tokyo), Germany (Frankfurt), UK (London), US (Silicon Valley), and US (Virginia) regions.

• Self-managed databases: The slow log analysis page does not support self-managed database instances.

• Slow query log threshold: The database kernel controls the recording of slow query logs. DAS is responsible for analysis and does not set the threshold. To configure the slow query log threshold, such as the long_query_time parameter for MySQL, you must log on to the specific database instance.

Procedure

FAQ

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

  A: This may be because real-time computing and window aggregation techniques are used, which cause the latest slow query log statistics to be displayed with a delay of about 3 minutes. You can also check the following:

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

  • Confirm that slow query logs were generated within the selected time range.

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

• Q: What should I do if a RAM user does not have permission to view or manage user groups?

  A: An administrator must grant account-level action permissions to the RAM user. This authorization affects only the permissions to operate on global slow log groups and does not change the RAM user's data permissions for other instances.

  • Global group administrator policy: For example, DASGlobalGroupAdmin. This policy allows the user to create, delete, modify, and query global groups.

    {
      "Version": "1",
      "Statement": [
        {
          "Action": [
            "hdm:DescribeGlobalGroups",
            "hdm:CreateGlobalGroup",
            "hdm:DeleteGlobalGroup",
            "hdm:ModifyGlobalGroup"
          ],
          "Resource": "*",
          "Effect": "Allow"
        }
      ]
    }

  • Global group read-only policy: For example, DASGlobalGroupReadOnly. This policy allows the user to only view global groups.

    {
      "Version": "1",
      "Statement": [
        {
          "Action": "hdm:DescribeGlobalGroups",
          "Resource": "*",
          "Effect": "Allow"
        }
      ]
    }

• Q: Why are some instances highlighted in yellow, indicating that the current user does not have access permissions?

  A: The 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 two ways:

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

  • Grant global group permissions: Grant the DASGlobalGroupAdmin permission to the RAM user. This allows the RAM user to create user groups and view data for instances that they have permission to access in batches.

• Q: Why is the number of returned rows (Rows_sent) in the slow query log 0, even though the query actually returns data?

  A: This is usually because the application uses the server-side cursor mode. In cursor mode, the execution of an SQL statement is divided into two stages:

  • EXECUTE stage: The server executes the query and generates a result set but does not immediately send the data rows to the client. It returns only metadata, such as column definitions.

  • FETCH stage: The client pulls the data rows in batches using the FETCH command.

  The slow query log records statistics from the EXECUTE stage. In this stage, MySQL has scanned the data (so Rows_examined is not 0), but has not yet sent any data rows to the client (the data is sent in the subsequent FETCH stage). Therefore, Rows_sent is recorded as 0.

  Common scenarios:

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

  • Python: MySQLdb.cursors.SSCursor or pymysql.cursors.SSCursor is used.

  • ORM frameworks: Some Object-Relational Mapping (ORM) frameworks use streaming queries by default when processing large dataset queries, which is based on the cursor mode.

  To confirm whether this is the cause, check if `fetchSize` is configured or if a streaming cursor is used in your application code. You can also run the SHOW GLOBAL STATUS LIKE 'Com_stmt_fetch'; command in the database to check for FETCH requests.

• Q: 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 because the time zone was changed when the SQL statement was executed. The time zone for the SQL execution time recorded in the slow query log can be at the session, database, or system level. The logic for determining the time in the slow query log is as follows: If a time zone is set for the database, the database time zone is used. Otherwise, the system time zone is used. If the session-level time zone is modified using an SQL statement, the time zone in the slow query log record may not be converted correctly.

References

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

• Automatic SQL optimization

• Automatic SQL throttling