Performance report

Version information

Performance reports vary by the OCP version. This topic uses OCP V3.3.0 as an example.

Architecture

OceanBase Analyze Report (OBAR) provides database performance analysis to help you comprehensively diagnose OceanBase Database performance issues. OBAR aims to keep users informed of cluster and tenant loads and performance to help users execute general inspections and learn about the inspection results in the form of reports. These reports are easy to operate on. You can aggregate the system status information in multiple dimensions and learn about the cluster status with a few clicks.

The following figure shows the OCP task framework for generating a performance report.

• The OCP monitoring data collection module regularly collects the required monitoring data and writes the data to MonitorDB.

  • The OCP server collects data every hour and stores the data in the ob_hist * table.
  • The ocp_monagent collects data every 30 seconds and stores the data to MonitorDB.

• The OCP task framework collects snapshot statistics as scheduled. The scheduled task is called snapshot_producer. The snapshot_producer task reads required monitoring information from the monitoring data, processes the information into snapshots, and writes the snapshots to the ob_hist_* table. To accelerate snapshot statistics processing, the system splits tasks based on the monitoring data type and collects diagnostic information concurrently by using subtasks in the task framework.

• The OCP task framework provides interfaces to support the following operations.

  • Generating reports

    • You can specify the starting and ending snapshot points to generate a report.
    • Before generating a report, the system checks the report data. If a snapshot between the starting snapshot point and the ending snapshot point has an error, the system rejects to generate the report. In addition, the system will reject to generate a report if the server goes online or offline.
    • After the report data passes the check, the system writes the current report metadata to ob_workload_report and changes the report status to "Generating". After the task succeeds or fails, the system updates the report status to "Successful" or "Failed".
    • The system generates a report based on local resource files and snapshot data and stores successfully generated reports in ObjectStorageService.

  • Querying and downloading reports

    • The system queries the list of report descriptions from ob_workload_report and returns it to the frontend.
    • The system queries the corresponding report description from ob_workload_report, obtains the report content from ObjectStorageService, and then returns the report content to the frontend.

Report description

A performance report contains the following sections: Basic Information, Workload Statistics, SQL Statistics, Lock Statistics, Cache Statistics, RPC Statistics, and Parameter Changes.

Basic information

The following table lists all the basic information about the resources, nodes, and tenants in the cluster.

Workload statistics

Workload statistics include the statistics of data operation load-related metrics for clusters and tenants, such as the cumulative values, start values, end values, maximum values, maximum value occurrence time, minimum values, minimum value occurrence time, and average values.

You can obtain the following information from the table:

• db_time: the workload of the database.

• db_cpu: the CPU time consumed by the database. Time consumed for non-idle waits = db_time- db_cpu.

• Database request statistics, including statistics on SELECT, INSET, DELETE, and UPDATE statements, which help you learn more about the business.

• Optimization space for the execution of SQL statements. If a large number of SQL statements are executed remotely or in a distributed manner, you can bind a table group or modify partitions to optimize the table definition.

• The status of database transactions. If too many transactions are rolled back, the database may not be properly running and will consume database resources improperly. In this case, you must identify the cause.

• Network communication. A large number of network requests received and sent will consume a lot of network bandwidth resources and result in additional costs.

The specific metrics include:

SQL statistics

The following table lists the statistic metrics in different dimensions on top SQL statements to help you learn about resource consumption by different SQL statements.

Lock statistics

Cache statistics

This module provides the average key-value cache hit rate of tenants. The statistic data source is the gv$systat view in OceanBase Database. The cache hit rate is accurate to two decimal places. You can view the statistics of seven types of caches: Block Cache, Block Index Cache, Clog Cache, Clog Index Cache, BloomFilter Cache, Location Cache, and Row Cache.

Different caches are used in different stages of SQL statement execution. If a certain cache has a significantly low hit rate, it undermines the SQL execution efficiency. Therefore, the information is key to performance optimization.

Statistical information includes the following:

RPC statistics

The finer-granule statistics on RPC communication packets. The information helps you learn about the network bandwidth resource consumption and thereby quickly locate problems caused by insufficient network resources.

Parameter Changes

This module provides information about the changes of the parameters collected in dimensions of cluster, zone, server, and tenant in the snapshot period.

Parameter changes before and after a fault can help us more quickly understand changes in the cluster and troubleshoot faults caused by parameter changes.

Partition management

Performance data are sorted by day. Each partition records data for one day. By default, a partition is created 8 days (specified in ocp.perf.sql.prepare-partition-ahead) in advance. Outdated data is cleared by partition based on the retention period specified in the table.