ODP performance analysis

Basic theories

Performance tuning

Performance tuning aims to make full use of the software and hardware capabilities to achieve the best system performance and improve the cost performance of the software and hardware in production environments, thereby lowering the costs for customers. You can run the perf stat command to collect the hardware performance metrics within a period of time, as shown in the following example:

Performance counter stats for process id '3260':

   182250.331423      task-clock (msec)         #   18.221 CPUs utilized
      3,259,379      context-switches          #    0.018 M/sec
      1,628,966      cpu-migrations            #    0.009 M/sec
               51      page-faults               #    0.000 K/sec
442,995,845,816      cycles                    #    2.431 GHz
226,812,458,841      instructions              #    0.51  insn per cycle
   45,556,284,037      branches                  #  249.965 M/sec
      662,510,643      branch-misses             #    1.45% of all branches

   10.002402740 seconds time elapsed

Performance tuning procedure

The performance tuning procedure is as follows:

1. Determine the optimization objectives.

   1. Throughput: Pay attention to the CPU information and component bottlenecks. At present, both OceanBase Database Proxy (ODP) and OceanBase Database can exhaust CPU resources.

   2. Latency: Pay attention to the number of components on the access link and the time consumption on each segment. The network latency must also be considered.

   3. Experience: From a global perspective, performance tuning performed nearer to the business system achieves better effects.

2. Perform stress testing and collect required information: Besides the target data, you must also collect other data such as the CPU utilization and network latency of each component.

3. Analyze the stress and identify the performance bottleneck: Based on the collected data, identify the bottleneck component and then perform detailed analysis.

4. Implement optimization: Adjust parameters and deployments. In special cases, you may need to modify the code and release a new version.

5. Confirm the optimization effect: Observe whether the throughput and latency meet the customer requirements or test results.

ODP performance

Data summary

The performance data is summarized as follows:

• Queries per second (QPS) = Concurrency × (1000 ms/Response time [RT])

• RT = 1000 ms/(QPS/Concurrency)

• The network latency within an IDC is within 100 μs.

• The forwarding efficiency of OceanBase Database is lower than 50% of the forwarding efficiency of ODP.

The ODP performance data is summarized as follows:

• In the case of exclusive use, the QPS is between 20,000 and 35,000 and affected by factors such as the CPU model. The QPS will be higher if the stress is low and hyper-threading (HT) is not used.

• If ODP is deployed on the same physical server as OceanBase Database, resource preemption occurs. In this case, the performance will be higher if load balancing is configured for ODP.

• Compared with other deployment modes, deploying ODP on the application client achieves the best performance.

• The advanced RISC machine (ARM) platform is supported but its performance is lower than that of the x86 platform.

Troubleshooting

The issues of ODP are mainly related to the QPS and RT.

• QPS: You can improve the QPS by scaling out ODP. If scale-out is not supported, you can improve the performance of individual ODP instances. If the CPU resources are sufficient but the QPS is low, you must analyze the issue based on the actual situation.

• RT: You can view the execution time on ODP in slow query logs. The execution time on ODP is at the μs level. The RT is mostly affected by the overall architecture.

When you handle issues, you must be clear with your objectives. For example, if data migration by using OceanBase Migration Service (OMS) is slow because ODP has become a bottleneck due to full CPU utilization, you can increase the number of ODP instances.

Thread model

You can add the -H parameter to the top command to check the thread status. The worker threads of ODP are obproxy and the threads whose name starts with ET NET. In ODP, threads basically share nothing with each other and each thread can work independently. Requests can be evenly distributed to the worker threads.

Parameter adjustment

Parameters involved in performance tuning are as follows:

alter proxyconfig set enable_compression_protocol=false; -- Disable compression to reduce the CPU utilization.
alter proxyconfig set proxy_mem_limited='16G'; --You can dynamically adjust the value as needed to prevent out-of-memory (OOM) errors.
alter proxyconfig set enable_prometheus=false;
alter proxyconfig set enable_metadb_used=false;
alter proxyconfig set enable_standby=false;
alter proxyconfig set enable_strict_stat_time=false;
alter proxyconfig set use_local_dbconfig=true; This parameter is available in ODP V3.x and later.
alter proxyconfig set work_thread_num= xxx; --xxx must be smaller than or equal to the actual number of cores.

Routing accuracy

The forwarding efficiency of OceanBase Database is lower than that of ODP. Therefore, the routing accuracy of ODP is essential in core scenarios, for which local plans are recommended. This is not required in non-core scenarios. At present, ODP supports routing based on regular tables, partitioned tables, logical data centers (LDCs), and primary zones. For more information, see Data routing.

Process performance analysis

Thread model data

The following table describes the CPU utilization under the assumption that the QPS is 100,000.

View performance data by using tools

You can view performance data by using the following tools.

1. top command

   1. You can use the top command to view the CPU utilization of ODP in real time. If the CPU utilization of ODP exceeds that of OceanBase Database, ODP performance is abnormal and further analysis is required.

   2. You can use the top -H command to view the CPU utilization of threads, which is steady in normal cases.

2. perf command

   1. You can use the perf top command to view hotspot functions. At present, no functions in ODP have a CPU utilization of more than 5%.

   2. You can use the perf stat command to view hardware metrics, which are helpful in analyzing the performance of CPUs of different models.

3. Log printing

   Logging severely compromises performance. You can check the ODP log generation interval. If a log is generated every 1 to 2 seconds, the performance will be significantly downgraded.