All Products
Search
Document Center

Database Autonomy Service:Full request feature

Last Updated:Mar 01, 2024

This topic introduces the full request feature and describes how to enable the full request feature.

Prerequisites

Introduction

When you use a MySQL database, you may encounter the following issues:

  • It is hard to identify historical issues. For example, an alert for 100% CPU utilization was generated at 03:00 (UTC+8) for the database, but no slow SQL statements were collected at that time. In this case, it is hard to identify the alert cause.

  • It is hard to obtain SQL templates for stress testing. For example, if stress testing for promotional events is about to be carried out in the following week, the database administrator must collect SQL templates and the execution frequency of each SQL statement from individual data providers. This method is inefficient and can cause errors.

  • It is hard to determine whether to optimize slow SQL statements. Not all the slow SQL statements need to be immediately optimized. You must know the most resource-consuming SQL statements in the database, and optimize these SQL statements to improve the instance stability.

You can use the full request feature to perform diagnostics on SQL statements:

  • Performance issue troubleshooting: You can check the health of SQL statements, the resource consumption of top resource-consuming SQL statements, the number of executions, and the execution durations.

  • SQL templates: You can use the SQL templates to perform various operations, such as business traffic analysis, stress testing for promotional events, and capacity planning.

  • SQL sample data: You can check the traffic information about your instance, such as the sources from which the access requests are sent and the users who send the access requests.

Procedure

  1. Log on to the DAS console.

  2. In the left-side navigation pane, choose Request Analysis > Full Request, as shown in the following figure.

    1

    Note
    • You can specify a time range to view the Full Request Trend chart and the Full Request Statistics table.

    • You can click Export to export the data of the Full Request Statistics table. You can export up to 10 million rows of data at a time.

Description

  • Execution Duration Distribution: On the Execution Duration Distribution tab, you can view the distribution of execution durations of SQL statements based on the time range that you specify. The statistical data is collected every minute. The execution durations are divided into the following ranges:

    • [0,1] ms: indicates that the execution duration ranges from 0 ms to 1 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    • (1,2] ms: indicates that the execution duration is greater than 1 ms and less than or equal to 2 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    • (2,3] ms: indicates that the execution duration is greater than 2 ms and less than or equal to 3 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    • (3,10] ms: indicates that the execution duration is greater than 3 ms and less than or equal to 10 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    • (10,100] ms: indicates that the execution duration is greater than 10 ms and less than or equal to 100 ms. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    • (0.1,1]s: indicates that the execution duration is greater than 0.1s and less than or equal to 1s. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    • > 1s: indicates that the execution duration is greater than 1s. The chart shows the percentage of SQL queries whose execution durations fall within this range.

    Examples:

    • The specified time range is from 10:40:00 on October 19, 2020 to 10:40:00 on November 19, 2020.

    • The percentage of SQL statements whose execution durations fall within the [0,1] ms range is 34.30%.

    • The percentage of SQL statements whose execution durations fall within the (10,100] ms range is 23.13%.

    • The percentage of SQL statements whose execution durations fall within the (0.1,1]s range is 25.98%.

    Note

    The section on the Execution Duration Distribution tab shows the execution time of SQL statements on the instance over time. A larger blue area of the chart indicates a healthier instance if the SQL statements are executed on the instance. Larger orange and red areas of the chart indicate a less healthy instance if the SQL statements are executed on the instance.

  • Execution Duration (SQL response time): On the Execution Duration tab, you can view the execution durations of SQL statements based on the specified time range.

  • Full Request Statistics: In the Full Request Statistics section, you can view the SQL statement details based on the specified time range. The details include the SQL text, execution duration percentage, average execution duration, and execution trend for each SQL statement.

    Note

    You can calculate the execution duration percentage for each SQL statement based on the following formula: Execution duration percentage = (Execution duration of an SQL statement × Number of executions of the SQL statement)/(Total execution duration of all the SQL statements × Total number of executions) × 100%. Higher execution duration percentages indicate that the corresponding SQL statements occupy a larger number of MySQL resources for execution. You can optimize these SQL statements to reduce costs.

  • SQL ID: You can click an SQL ID to view the performance trend and sample data of the SQL statement.

  • SQL Sample: On the SQL Sample tab, you can view the client that initiates the SQL request.