View slow query logs of a Redis instance - Database Autonomy Service

Slow SQL queries significantly decrease the database stability. When issues such as high workloads and unstable performance occur on databases, database administrators (DBAs) or developers first check whether slow query logs are generated. Database Autonomy Service (DAS) provides the log analysis feature for slow queries. DAS collects statistics on and analyzes the SQL queries whose execution duration exceeds the specified threshold value and provides solutions. You can troubleshoot database performance issues with ease. This enhances system reliability and stability.

Prerequisites

The database instance that you want to manage is an ApsaraDB for Redis instance.
The database instance is connected to DAS and is in the Normal Access state. For more information, see Connect an Alibaba Cloud database instance to DAS.

Background information

The slow query log analysis feature collects statistics on slow query logs on database instances. This helps you identify the most frequently executed slow SQL statement, the most time-consuming slow SQL statement, and the slow SQL statement that scans the largest number of rows on average.

Slow query logs are generated by the database kernel. Relevant parameters and thresholds vary based on the database engine. For more information, see the corresponding official documentation.

Procedure

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, choose Request Analysis > Slow Logs.
On the Slow Logs page, specify a time range and view the trends and details of the slow query logs within the specified time range.
Note
When you specify the time range, the end time must be later than the start time, and the interval between the start time and the end time cannot exceed 24 hours. You can query slow query logs within the previous month.
- In the Slow Log Trend section, you can click a point in time in the trend chart and view the details of slow query logs at the point in time.
  Note
  For cluster instances and read/write splitting instances, the following information is displayed: the details of the slow query logs for data nodes and proxy nodes, and the number of slow queries on each node.
- You can click Export Slow Log to download slow query logs to your computer.
- By default, the Slow Log Details section displays the following information of all slow query logs: Query Started At, Database Name, Slow Query Statement, Elapsed, and Host Address. You can click Set to filter the information.
  Note
  By default, the Host Address column displays the IP address of the proxy node for cluster instances and read/write splitting instances. To obtain the IP address of a specific client, perform the following operations:
  Enhanced Edition (Tair) performance-enhanced instances that use local disks: On the System Parameters tab of the instance details page, set the ptod_enabled parameter to 1. For more information, see Configure instance parameters.
  Community Edition instances: Enable the audit log feature and view the client IP address in specific events of the audit logs for the proxy node. For more information, see Enable the audit log feature.

Execution durations of special slow SQL statements

Note

The special slow SQL statements are related to the kernel logic of an instance and are not directly related to the actual execution speed of your requests. You can ignore the following slow SQL statements:

latency:eventloop
ApsaraDB for Redis uses the event-driven mode during runtime. An event includes the process of reading, parsing, and running commands and returning results. The execution duration of a latency:eventloop statement indicates the overall amount of time required for an event loop.
latency:pipeline
ApsaraDB for Redis allows the client to work in pipeline mode. In this mode, the client sends commands in batches and returns results in batches after all commands are run. By default, proxy nodes of ApsaraDB for Redis cluster instances send requests to the backend Redis server in pipeline mode. The execution duration of a latency:pipeline statement indicates the overall amount of time required to handle all requests from the client in pipeline mode.
latency:fork
The execution duration of a latency:fork statement indicates the amount of time required to fork a child process. A larger amount of data requires a longer duration.