Database Autonomy Service (DAS) provides the log analysis feature for slow queries. You can use this feature to identify, analyze, and track slow query logs of your ApsaraDB for Redis instances. This helps you create indexes to make better use of instance resources.

Prerequisites

Background information

The slow query log analysis feature displays statistics about slow query logs. 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 average number of rows.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring.
  3. On the page that appears, click the ID of the database instance that you want to manage. The instance details page appears.
  4. In the left-side navigation pane, choose Request Analysis > Slow Logs.
  5. On the Slow Log Analysis tab, specify a time range and view the trends, statistics, and details of the slow queries within the time range.
    Note When you select a 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 a month.
    • You can click a time point in the trend chart and view details of the slow queries at the time point.
      Note For cluster instances and read/write splitting instances, the following information is displayed: the slow query log details 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.
    • By default, slow query log details include the following information about all slow query logs: Query Started At, Database Name, Slow Query Statement, Elapsed, and Host Address. You can click Set to filter logs.
      Note By default, the Host Address column for cluster instances and read/write splitting instances displays the IP address of the proxy node. To obtain the IP address of a specific client, perform the following operations:
      • Performance-enhanced instances of the Enhanced Edition (Tair) that use local disks: Set the ptod_enabled parameter in System Parameters to 1. For more information, see Modify parameters of an instance.
      • Community Edition instances: Enable the audit log feature and view the client IP address in the specific events of the audit logs for the proxy node.

View the global trends off slow query logs

  1. Log on to the DAS console.
  2. In the left-side navigation pane, choose Request Analysis > Slow Logs. Select Redis, and then view the statistics on the Global Slow Log Trend and Top Instance Statistics tabs.
    Note When you select a 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 7 days. You can query slow query logs within 14 days.
    Slow query logs

Execution durations of special slow SQL statements

  • latency:eventloop

    ApsaraDB for Redis uses the event-driven mode during runtime. An event loop includes reading command, parsing, and running command, and returning results. The execution duration of a latency:eventloop statement indicates the overall amount of time taken 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. The execution duration of a latency:pipeline statement indicates the overall amount of time taken to handle all requests from the client in pipeline mode.

    Note By default, proxy servers of ApsaraDB for Redis cluster instances send requests to the backend Redis server in pipeline mode.