ApsaraDB for OceanBase:View a performance assessment report

Limitations

• You can view the performance assessment report only for a full performance assessment task or SQL file replay task that is in the Completed state.

• Performance assessment reports are retained for at most 15 days.

Procedure

1. Log on to the ApsaraDB for OceanBase console.

2. In the left-side navigation pane, choose Migration Assessment > Performance Assessment.

3. On the Assessment Tasks tab, click View Report next to the target assessment task.

4. On the Load Replay Report page, view details of the report.

Details of a report

A load replay report comprises the following modules: Replay Analysis, Performance Trend, SQL Diagnostic Information, SQL Optimization Information, and Replay Information List.

• The Replay Analysis module displays information in the following sections: Total Replays, Replay Success Rate, and Replay Traffic Comparison. In the Replay Traffic Comparison section, you can filter the replay traffic to view by selecting All, Select, or Write.

  If you select All and replay read-only traffic, the source traffic curve chart contains both write traffic and read traffic. In this case, it is normal if the target curve is lower than the source curve.

  When you replay traffic at a speed that is a multiple of the original speed, the replay time is shorter than the traffic recording time in the source. In this case, the target curve in the replay traffic comparison chart drops to 0 at the end.

• The Performance Trend section displays information in the following fields: QPS (number of SQL statements processed per second), Query Response Time (average time consumed in processing a single SQL statement), and CPU Utilization. Such information can help you find the performance trend.

• The SQL Diagnostic Information module displays information in the following tabs: TopSQL and SlowSQL. You can view the SQL text, database accessed by the SQL statement, CPU utilization, SQL ID, total number of executions within the specified period, total time (in ms) consumed by the database in executing the SQL statement, and error summary. In the upper-right corner of this section, you can filter the information that you want to check.

• The SQL Optimization Information section displays information in the following fields: Type, Tenant, Database, Object, Diagnosis, and Recommendations. You can optimize the related SQL statements based on such information.

• The Replay Information List module displays the following information: SQL ID, Total Replays, and SQL Type, as well as 90th Percentile Execution Time, Median Execution Time, and Average Execution Time of the source and target.

  In a load replay report for migration assessment, data is aggregated and measured by the source SQL ID. In the replay information list, each row represents the replay statistics of a type of SQL statements. At present, you can filter the data in a load replay report for migration assessment only by replay times in descending order. The first 50 rows are displayed.

  Parameter	Description
  SQL ID	The ID of a type of SQL statements, which is unique within a certain period. Here is an example: SELECT * FROM TABLEA WHERE COL1 = 1; SELECT * FROM TABLEA WHERE COL1 = 2; The preceding two SQL statements are of the same type and share the same SQL statement ID. Source SQL ID: the SQL statement ID in logs of the source database. This ID can be calculated by the migration assessment service. Important When an SQL statement is truncated in the source, the migration assessment service calculates the hash value based on the SQL text. It is considered that each truncated SQL statement is of an independent type. Target SQL ID: the SQL statement ID calculated by ApsaraDB for OceanBase.
  Total Replays	The total number of times that this type of SQL statements is replayed.
  SQL Type	The SQL statement type. Valid values: SELECT, INSERT, UPDATE, and DELETE.
  90th Percentile Execution Time	The unit is milliseconds, and the value is accurate to three decimal places. Assume that this type of SQL statements is executed n times and the replay time is [x_1,x_2,x_3,....,x_n], which is [x_1',x_2',....,x_n'] sorted in descending order. The 90th percentile value is x_[x*0.9]. For information about the calculation function used, see approx_percentile.
  Median Execution Time	The median of the execution time, which is calculated based on the replay time. The unit is milliseconds, and the value is accurate to three decimal places.
  Average Execution Time	The average execution time, which is calculated based on the replay time. The unit is milliseconds, and the value is accurate to three decimal places.

  The migration assessment service provides the source execution time and target execution time metrics for each SQL statement assessed. The 90th percentile of execution time, media of execution time, and average execution time are aggregated based on the two metrics. The time collection sources are described as follows:

  • Source execution time: directly collected from Alibaba Cloud Simple Log Service (SLS). ApsaraDB for OceanBase and the migration assessment service cannot ensure the correctness of the source execution time, nor can they determine the statistic collection algorithm.

  • Target execution time: obtained from the oceanbase.v$sql_audit view of ApsaraDB for OceanBase.

    Important

    Affected by the view eviction mechanism and the view collection speed of ApsaraDB for OceanBase, the target execution time data may be incomplete.