Distributed | BenchmarkSQL Stress Test dble Performance Tuning Guide

Author: Cai Wei

The middleware dble test members are mainly responsible for the daily testing of dble, and are keen to explore and learn new technologies.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Background introduction

BenchmarkSQL is a benchmark tool that supports many relational databases. By using BenchmarkSQL to perform TPC-C standard tests on the database, it simulates multiple transaction processing: new order, payment operation, order status query, shipment, inventory status query, etc., Thereby obtaining the final pressure measurement value. Compared with the single Sysbench, it can more closely simulate the real application scenarios, so more and more customers choose to use BenchmarkSQL when stress testing the database.

As a database middleware, dble can also use BenchmarkSQL for stress testing. However, during the stress testing process, how to tune it so that the maximum stress testing value of the product can be measured is more worthy of attention. From an observable perspective, bypassing the internal logic, we introduce some of our internal tuning experience and observation methods when using BenchmarkSQL to stress test dble for your reference. This tuning method mainly focuses on the configuration and the number of internal threads in the dble.

Configuration Recommendations

This article is mainly aimed at tuning and reference. I will not introduce the BenchmarkSQL installation and dble installation process too much here, and will not describe the data preparation and pressure measurement steps.

But it is necessary to make some brief descriptions of necessary and recommended values for some necessary configuration information.

Number of backend connections

It is necessary to ensure that the number of dble user connections > the number of concurrent pressure tests
The maximum number of connections to the storage node in dble = the maximum number of connections to the corresponding backend MySQL >> (the number of concurrent / the number of storage nodes)

log level

It is recommended to set the log level of dble to WARN, and the corresponding configuration file to be set is dble/conf/log4j2.xml

isolation level

The isolation level of dble and the back-end database is set to the RC isolation level. If it is the RR isolation level, a lock waiting timeout error will occur during the stress test. For the reason, please refer to the historical official account article: https://mp.weixin.qq. com/s/-2I39hukyUb8qpzMmftfFA

Turn off dble parameter switches that may affect performance

It is recommended to turn off some parameter switches in dble that may affect performance, and other switches can be turned off if not necessary, such as: useSqlStat=0, enableSlowLog=0

dble configuration

As for the configuration information of dble, it is necessary to pay extra attention to the fact that the config and item tables need to be set as global tables during the pressure test, and all other tables are set as sharded tables. The data of the slice table is evenly distributed on the back-end database. It is recommended to use the modulo algorithm to achieve the purpose.

MySQL configuration

As for the performance parameter configuration information of MySQL, it needs to be self-tuned according to the machine configuration, which will not be repeated here.

means of observation

System Resource Observation Tool

When using a stress testing tool for stress testing, it is sometimes important to choose a simple and easy-to-use system observation tool, so that we can observe the bottleneck and perform some possible tuning. It is recommended to use the dstat tool, which can see all system resources in real time, such as CPU usage, disk IO and network IO.

dstat can also input the observed results into a csv file for subsequent analysis such as plotting.

The following is the real-time output of dstat. For details, please refer to https://linux.cn/article-3215-1.html

dble side observation means

Here are two observation methods that can be used on the dble side:

• To enable the query time-consuming system, you need to enable useCostTimeStat=1 in dble bootstrap.cnf, and restart dble to take effect. The following is an observation sample:
  

DBLE divides the query into six stages:

1) Start combing

2) Complete the parsing

3) Complete routing assignment

4) Retrieve results from database

5) Post-processing

6) Feedback processing

We only need to focus on WallTime.Avg , which refers to the average time spent in each stage, so that we can know at which stage of the middleware the pressure is slowing down. Then adjust the number of threads inside dble or check the backend mysql according to the time-consuming anomalies in different stages. For details, please refer to: https://actiontech.github.io/dble-docs-cn/2.Function/2.18_performance_observation.html

• View thread usage

dble provides a command to view the usage of various threads: show @@thread_used . With this command, you can view the usage of various threads during the stress test, and then adjust the number of threads accordingly. The following is a sample image of the command.

Pressure testing experience

When we first started to use BenchmarkSQL to test dble, we were also not very clear on the positive and negative effects of the number of threads of various types on performance. We only gradually got preliminary conclusions after exploring step by step. Here we will introduce the relevant situation to you.

About dstat

Since dstat is mainly used for resource occupancy observation, it is always open as an auxiliary tool to observe resource usage during the test. not used as a basis for adjustment.

About query time-consuming statistics

We first used the time-consuming statistics function [useCostTimeStat=1] inside dble to test the time-consuming situation of different stages, and then adjusted the number of threads according to the time-consuming situation of different stages of SQL execution. However, during the test, it was found that adjusting the number of threads could not effectively solve the observed time-consuming anomalies, indicating that these time-consuming anomalies were not suitable for solving the problem by adjusting the number of threads. One of the problems found, we also recorded this, and will continue to follow up the investigation.

About the show command

Adjusting the number of threads of dble based on the query time-consuming statistics has not achieved good results. Therefore, in the stress test process, the management-side command show @@thread_used to observe the thread usage has become the main basis for us to adjust the number of threads.

According to the experience of testing dble with other stress testing tools, at the beginning of the stress testing, we also wanted to ensure that the usage rate of various threads is below 80%, so as to ensure that the threads are not so busy. After the utilization rate of various threads drops to 80%, sometimes the tpmC value of the stress test does not rise but falls. I guess, is it because there are too many threads and frequent thread switching affects performance, and vice versa?

So the next step is to divide the threads inside dble into three categories according to their types:

• Front-end and back-end IO threads: processors, backendProcessors
• Front-end and back-end business processing threads: processorExecutor, backendProcessorExecutor
• Other threads: complexExecutor, writeToBackendExecutor

Refer to the thread usage rate queried by show @@thread_used, and adjust the number up and down according to different types of threads.

During the adjustment process, we found that adjusting the number of different types of threads up and down has different effects on the stress test results. After several rounds of testing, the following conclusions were drawn:

• The number of front-end and back-end IO threads and complexExecutor and writeToBackendExecutor have no significant impact on the value of tpmC under pressure measurement
• The greatest impact on the tpmC value is the number of front-end and back-end business processing threads, and the usage rate of front-end and back-end business processing threads is positively correlated with the value of tpmC, that is, the higher the thread usage rate of front-end and back-end business processing threads, the higher the value of tpmC.
• The network IO of the machine where dble is located is positively correlated with the value of tpmC, that is, the higher the network IO, the higher the value of tpmC [observed by observing the results of dstat]
• The adjustment of the number of front-end and back-end business processing threads has the same effect on the network IO of the machine where the dble is located and the usage rate of the front-end and back-end business processing threads, that is, the number of front-end and back-end business processing threads is reduced, the network IO increases, the thread usage rate increases, and the higher the tpmC value is.

Summarize

Through the above adjustment methods, under the condition that the configuration remains unchanged, based on the dble 3.20.10.0 version, we obtain the pressure test results internally during the pressure test. ]: MySQL [250 data warehouses, 250 concurrent threads] = 2.8:1 or so.

Of course, in an ideal situation, the value of tpmC of the two should reach about 4:1. It can be seen that there should still be room for improvement. In addition to the necessary network loss, our team is still working hard to make other optimizations in other places that can be optimized within dble, and you can look forward to the performance of subsequent versions.

At this point, it is recommended that when you use BenchmarkSQL to stress test dble and use observable methods for tuning, you can mainly adjust (reduce) the number of front-end and back-end business processing threads. The basis for the adjustment is:

• The usage rate of the front-end and back-end business processing threads should be kept at a relatively high level, and it is recommended to be around 95%
• Through the dstat command, the higher the network IO value of the machine where dble is located, the better

In addition, the number of front-end and back-end IO threads and the number of other types of threads can be adapted according to the machine configuration according to the official recommended value of dble. In addition, it should be noted that if the pressure test is performed multiple times, it is recommended to delete the historical data and prepare the data again for each pressure test, so as to avoid data pollution after the first pressure test, resulting in the subsequent pressure test results being different from the first one. The problem of large deviation of pressure test results.