ClickHouse and Elasticsearch Stress Testing Practice

1 Demand analysis
1.1 Analyze the stress test object
1) What is ClickHouse and Elasticsearch
ClickHouse is a true columnar database management system (DBMS). In ClickHouse, data is always stored in columns, including vector (vector or column block) execution. Whenever possible, operations are dispatched based on vectors rather than individual values, this is called «vectorized query execution», and it helps to reduce the actual data processing overhead.

Elasticsearch is an open source distributed, RESTful style search and data analysis engine, and its underlying layer is the open source library Apache Lucene. It can be accurately described like this:

A distributed real-time document storage, each field can be indexed and searched A distributed real-time analysis search engine capable of scaling hundreds of service nodes and supporting PB-level structured or unstructured data

2) Why stress test them As we all know, ClickHouse performs very well in basic scenarios and its performance is better than ES, but many of our actual business queries are complex business query scenarios, even a large number of queries. Before the peak of the Double Eleven business, we can ensure the stability of the peak business of the big promotion activities. Regarding whether ClickHouse and Elasticsearch have excellent pressure resistance in our actual business scenario, through this performance pressure test, we can detect the performance bottleneck in the system. , carry out targeted optimization to improve system performance.

1.2 Formulating pressure measurement goals

Why choose this (queryOBBacklogData) interface?

1) From the perspective of complexity, the interface (queryOBBacklogData) is queried 5 times, and the code is as follows:

 /**
 * 切ck-queryOBBacklogData
 * @param queryBO
 * @return
 */
public OutboundBacklogRespBO queryOBBacklogDataCKNew(OutboundBacklogQueryBO queryBO) {
    log.info(" queryOBBacklogDataCK入参：{}", JSON.toJSONString(queryBO));
    // 公共条件-卡最近十天时间
    String commonStartTime = DateUtils.getTime(DateUtil.format(new Date(), DateUtil.FORMAT_DATE), DateUtils.ELEVEN_AM, 1, -10);
    String commonEndTime = DateUtils.getTime(DateUtil.format(new Date(), DateUtil.FORMAT_DATE), DateUtils.ELEVEN_AM, 1, 1);
    // 越库信息-待越库件数&待越库任务数
    WmsObCrossDockQueryBo wmsObCrossDockQueryBo = wmsObCrossDockQueryBoBuilder(queryBO,commonStartTime, commonEndTime);
    log.info("queryOBBacklogDataCK-wmsObCrossDockQueryBo: {}", JSON.toJSONString(wmsObCrossDockQueryBo));
    CompletableFuture<OutboundBacklogRespBO> preCrossDockInfoCF = CompletableFuture.supplyAsync(
            () -> wmsObCrossDockMapper.preCrossDockInfo(wmsObCrossDockQueryBo), executor);
    // 集合任务信息-待分配订单
    WmsObAssignOrderQueryBo wmsObAssignOrderQueryBo = wmsObAssignOrderQueryBoBuilder(queryBO, commonStartTime, commonEndTime);
    log.info("queryOBBacklogDataCK-wmsObAssignOrderQueryBo: {}", JSON.toJSONString(wmsObAssignOrderQueryBo));
    CompletableFuture<Integer> preAssignOrderQtyCF = CompletableFuture.supplyAsync(
            () -> wmsObAssignOrderMapper.preAssignOrderInfo(wmsObAssignOrderQueryBo), executor);
    // 拣货信息-待拣货件数&待拣货任务数
    WmsPickTaskQueryBo wmsPickTaskQueryBo = wmsPickTaskQueryBoBuilder(queryBO, commonStartTime, commonEndTime);
    log.info("queryOBBacklogDataCK-wmsPickTaskQueryBo: {}", JSON.toJSONString(wmsPickTaskQueryBo));
    CompletableFuture<OutboundBacklogRespBO> prePickingInfoCF = CompletableFuture.supplyAsync(
            () -> wmsPickTaskMapper.pickTaskInfo(wmsPickTaskQueryBo), executor);
    // 分播信息-待分播件数&待分播任务
    WmsCheckTaskDetailQueryBo wmsCheckTaskDetailQueryBo = wmsCheckTaskDetailQueryBoBuilder(queryBO, commonStartTime, commonEndTime);
    log.info("queryOBBacklogDataCK-wmsCheckTaskDetailQueryBo: {}", JSON.toJSONString(wmsCheckTaskDetailQueryBo));
    CompletableFuture<OutboundBacklogRespBO> preSowInfoCF = CompletableFuture.supplyAsync(
            () -> wmsCheckTaskDetailMapper.checkTaskDetailInfo(wmsCheckTaskDetailQueryBo), executor);
    // 发货信息-待发货件数
    WmsOrderSkuQueryBo wmsOrderSkuQueryBo = wmsOrderSkuQueryBoBuilder(queryBO, commonStartTime, commonEndTime);
    log.info("queryOBBacklogDataCK-wmsOrderSkuQueryBo: {}", JSON.toJSONString(wmsOrderSkuQueryBo));
    CompletableFuture<Integer> preDispatchCF = CompletableFuture.supplyAsync(
            () -> wmsOrderSkuMapper.preDispatchInfo(wmsOrderSkuQueryBo), executor);
    return processResult(preCrossDockInfoCF, preAssignOrderQtyCF, prePickingInfoCF, preSowInfoCF, preDispatchCF);
}

2) Interface (queryOBBacklogData), a total of 5 tables are queried, as follows:

 wms.wms_ob_cross_dock
wms.wms_ob_assign_order
wms.wms_picking_task.
wms.wms_check_task_detail
wms.wms_order_sku

3) The amount of data to be queried is as follows:

 select
   (ifnull(sum(m.shouldBeCrossedDockQty),
   0) -
        ifnull(sum(m.satisfiedCrossedDockQty),
   0)) as preCrossStockSkuQty,
   count(m.docId) as preCrossStockTaskQty
from
   wms.wms_ob_cross_dock m final
    prewhere
        m.createTime >= '2021-12-03 11:00:00'
   and m.createTime <= '2021-12-14 11:00:00'
   and m.warehouseNo = '279_1'
   and m.orderType = '10'
   and tenantCode = 'TC90230202'
where
   m.deleted = 0
   and m.deliveryDestination = '2'
   and m.shipmentOrderDeleted = 0
   and m.status = 0

As can be seen from the above SQL screenshot, a total of 720,817 rows of data were read for querying the number of pending cross-docking & the number of pending cross-docking tasks.

 select count(distinct m.orderNo) as preAssignedOrderQty
from wms.wms_ob_assign_order m final
prewhere
m.createTime >= '2021-12-03 11:00:00'
and m.createTime <= '2021-12-14 11:00:00'
and m.warehouseNo = '361_0'
and tenantCode = 'TC90230202'
where m.taskassignStatus = 0
and m.deliveryDestination = 2
and m.stopProductionFlag = 0
and m.deleted = 0
and m.orderType = 10

As can be seen from the above SQL screenshot, a total of 153118 rows of data were read for querying the collection task information - orders to be allocated

 select minus(toInt32(ifnull(sum(m.locateQty), toDecimal64(0, 4))),
toInt32(ifnull(sum(m.pickedQty), toDecimal64(0, 4)))) as prePickingSkuQty,
count(distinct m.taskNo) as prePickingTaskQty
from wms.wms_picking_task m final
prewhere
m.shipmentOrderCreateTime >= '2021-12-03 11:00:00'
and m.shipmentOrderCreateTime <= '2021-12-14 11:00:00'
and m.warehouseNo = '286_1'
and tenantCode = 'TC90230202'
where m.pickingTaskDeleted = 0
and m.deliveryDestination = 2
and m.pickLocalDetailDeleted = 0
and m.shipmentOrderDeleted = 0
and m.orderType = 10
and (m.operateStatus = 0 or m.operateStatus = 1)

As can be seen from the above SQL screenshot, a total of 2,673,536 rows of data have been read to query the picking information - the number of items to be picked & the number of tasks to be picked

 select minus(toInt32(ifnull(sum(m.locateQty), toDecimal64(0, 4))),
toInt32(ifnull(sum(m.pickedQty), toDecimal64(0, 4)))) as prePickingSkuQty,
count(distinct m.taskNo) as prePickingTaskQty
from wms.wms_picking_task m final
prewhere
m.shipmentOrderCreateTime >= '2021-12-03 11:00:00'
and m.shipmentOrderCreateTime <= '2021-12-14 11:00:00'
and m.warehouseNo = '279_1'
and tenantCode = 'TC90230202'
where m.pickingTaskDeleted = 0
and m.deliveryDestination = 2
and m.pickLocalDetailDeleted = 0
and m.shipmentOrderDeleted = 0
and m.orderType = 10
and (m.operateStatus = 0 or m.operateStatus = 1)

As can be seen from the above SQL screenshot, a total of 1,448,149 rows of data were read for querying distribution information - number of pieces to be distributed & tasks to be distributed

 select ifnull(sum(m.unTrackQty), 0) as unTrackQty
from wms.wms_order_sku m final
    prewhere
        m.shipmentOrderCreateTime >= '2021-12-03 11:00:00'
        and m.shipmentOrderCreateTime <= '2021-12-14 11:00:00'
        and m.warehouseNo = '280_1'
        and m.orderType = '10'
        and m.deliveryDestination = '2'
        and tenantCode = 'TC90230202'
where m.shipmentOrderDeleted <> '1'
  and m.ckDeliveryTaskDeleted <> '1'
  and m.ckDeliveryTaskDetailDeleted <> '1'
  and m.ckDeliveryTaskStatus in ('1','0','2')

As can be seen from the above SQL screenshot, a total of 99591 rows of data have been read to query the shipping information - the number of pieces to be shipped

2 Preparation of the test environment In order to maximize the performance stress test, the performance stress test environment should be as consistent as possible with the online environment, so we use the same environment as the online environment

3 Collection tools prepare monitoring tools
http://origin.jd.com/ : monitor JVM, method-level monitoring (provide second-level support)
http://console.jex.jd.com/ : Provides exception stack monitoring, flame graph monitoring, thread stack analysis
http://x.devops.jdcloud.com/ : Supports viewing the cpu usage of each node of the clickhouse/Elasticsearch database service
http://dashboard.fireeye.jdl.cn/ : Monitor application server cpu usage and memory usage
4 Stress test execution and result analysis
4.1 Writing a stress test script tool
Forcebot ( http://forcebot.jd.com ) is a performance testing platform specially provided for developers and testers, through a series of operations such as writing scripts, configuring monitoring, setting scenarios, starting tasks, real-time monitoring, log positioning, and exporting reports Process to complete the performance test, flexible script configuration to meet the synchronization, asynchronous, rendezvous and other pressure mode.

Help documentation ( http://doc.jd.com/forcebot/helper/ )

4.2 Design pressure measurement data
4.2.1 Explanation of terms in the previous stress test
DBCP: database connection pool, is a Java connection pool project on apache. DBCP establishes some connections with the database in advance through the connection pool and puts them in the memory (that is, in the connection pool). When the application needs to establish a database connection, it directly applies for a connection from the connection pool. To achieve the purpose of connection multiplexing and reducing resource consumption.
maxTotal: is the maximum number of total connections in the connection pool, the default value is 8
max_thread: The underlying configuration in clickhouse, the maximum number of threads used when processing SQL requests. The default is the number of cores for the clickhouse server.
coordinating: the number of coordinating nodes, mainly used for request forwarding, request response processing and other lightweight operations. Data nodes: mainly nodes that store index data, and mainly perform addition, deletion, modification, and query operations on documents, aggregation operations, etc. Data nodes have high requirements for cpu, memory, and io. During optimization, you need to monitor the status of data nodes. When resources are not enough, you need to add new nodes to the cluster.

4.2.2 Pressure measurement data
clickhouse data service: 32C128G6 node 2 replica application server: 4 cores 8G2
maxTotal=16

Note: Before each stress test, be sure to observe the CPU usage of each data node

Note: It can be seen from the sequence numbers 6-12 in the above stress test process that the number of concurrent users is increasing, but there is no significant change in tps. It is found that the maximum number of threads in the bigdata dbcp database link pool is not configured, and the default maximum number of threads is 8. After the number of users increased to 8, the clickhouse cpu remained stable between 40% and 50%, and the application server CPU remained stable at around 25%.

After that, we adjusted maxTotal=50, and by adjusting the different values of max_thread, the CPU usage of database nodes was kept at about 50% to view the corresponding monitoring data indicators: application service CPU usage, TPS, TP99, and number of concurrent users.

clickhouse data node, CPU usage:

Elasticsearch Data Service: 32C128G6 Node 2 Replicas Application Server: 4 Cores 8G2
Elasticsearch also keeps the database service CPU usage up to (about 50%), and then monitors the data indicators tps and tp99
The adjustment indicators are as follows: coordinating number of coordinator nodes, data nodes, poolSize

Metric 1: coordinating=2, datanode=4, poolSize=400

Note: During the stress test, it was found that the CPU utilization rate of the coordinating node reached 51.69%, and the role of load balancing was limited. Therefore, the coordinating node needs to be expanded by 2 nodes.

Metric 2: coordinating=4, datanode=5, poolSize=800

Note: During the stress test, it was found that when the CPU usage (database) of the ES data node was about 40%, it could not go up. Check the log and found that the activeCount has reached 797, and the poolSize value needs to be increased.

Indicator 3: coordinating=4, datanode=5, poolSize=1200

Note: During the stress test, it was found that the coordinating node still needs to be expanded, which cannot support the current cpu usage of the data node reaching 50%.
Elasticsearch data nodes and coordination nodes, CPU usage:

In the process of stress testing, we found some problems that were not found in the development process before. First, the number of bigdata application servers in bdcp and the maximum number of threads in the thread pool used were 8, which became the bottleneck. After the number of users increased to 8, the cpu of clickhouse It is stable between 40% and 50% and does not increase. The CPU of the application server is stable at about 25%. Secondly, the configuration of the warehouse cluster coordination node is low, and the CPU usage of the coordination node is high. Finally, clickhouse-jdbc JavaCC is inefficient in parsing SQL.

4.3 Analysis of results
4.3.1 Test conclusion
1) Clickhouse has certain support for concurrency, but it does not support high concurrency. You can improve concurrency by adjusting max_thread

When max_thread=32, the maximum supported TPS is 37, and the corresponding TP99 is 122
When max_thread=2, the maximum supported TPS is 66, and the corresponding TP99 is 155
When max_thread=1, the maximum supported TPS is 86, and the corresponding TP99 is 206
2) Elasticsearch supports better than clickhouse in terms of concurrency, but the corresponding response speed is much slower

Elasticsearch: The corresponding TPS is 192, TP99 is 3050
clickhouse: The corresponding TPS is 86, TP99 is 206
Comprehensive consideration, we believe that clickhouse is enough to support our business demands

4.3.2 Optimization suggestion to expand the ES coordination node
The maximum number of threads in the bigdata application thread pool is increased to 200
Bigdata application dbcp thread pool maxTotal is set to 50
Read configuration file tool class to increase memory cache

Author: Pan Xueyan