【JMeter-ClickHouse】使用JMeter对ClickHouse的查询性能进行压测

r囧r小猫 2023-10-08 19:20 34阅读 0赞

#### 【JMeter-ClickHouse】使用JMeter对ClickHouse的查询性能进行压测 ####

*  1）生成测试数据
 *   *  1.1.下载数据
     *  1.2.建表（共4张）
     *  1.3.导入数据
     *  1.4.合并多张表为宽表
 *  2）读取压测
 *   *  2.1.创建线程用户并指定参数配置
     *  2.2.创建JDBC Connection Configuration并配置连接信息
     *  2.3.导入ClickHouse相关jar包
     *  2.4.创建JDBC Request取样器并添加需要测试的SQL
     *  2.5.添加所需要的监听器
     *  2.6.开始测试

## 1）生成测试数据 ##

### 1.1.下载数据 ###

*  编译 dbgen

$ git clone git@github.com:vadimtk/ssb-dbgen.git
    $ cd ssb-dbgen
    $ make

*  生成数据

$ ./dbgen -s 100 -T c
    $ ./dbgen -s 100 -T l
    $ ./dbgen -s 100 -T p

### 1.2.建表（共4张） ###

`customer`

CREATE TABLE customer
    (
    C_CUSTKEY UInt32,
    C_NAME String,
    C_ADDRESS String,
    C_CITY LowCardinality(String),
    C_NATION LowCardinality(String),
    C_REGION LowCardinality(String),
    C_PHONE String,
    C_MKTSEGMENT LowCardinality(String)
    )
    ENGINE = MergeTree ORDER BY (C_CUSTKEY);

`lineorder`

CREATE TABLE lineorder
    (
    LO_ORDERKEY UInt32,
    LO_LINENUMBER UInt8,
    LO_CUSTKEY UInt32,
    LO_PARTKEY UInt32,
    LO_SUPPKEY UInt32,
    LO_ORDERDATE Date,
    LO_ORDERPRIORITY LowCardinality(String),
    LO_SHIPPRIORITY UInt8,
    LO_QUANTITY UInt8,
    LO_EXTENDEDPRICE UInt32,
    LO_ORDTOTALPRICE UInt32,
    LO_DISCOUNT UInt8,
    LO_REVENUE UInt32,
    LO_SUPPLYCOST UInt32,
    LO_TAX UInt8,
    LO_COMMITDATE Date,
    LO_SHIPMODE LowCardinality(String)
    )
    ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

`part`

CREATE TABLE part
    (
    P_PARTKEY UInt32,
    P_NAME String,
    P_MFGR LowCardinality(String),
    P_CATEGORY LowCardinality(String),
    P_BRAND LowCardinality(String),
    P_COLOR LowCardinality(String),
    P_TYPE LowCardinality(String),
    P_SIZE UInt8,
    P_CONTAINER LowCardinality(String)
    )
    ENGINE = MergeTree ORDER BY P_PARTKEY;

`supplier`

CREATE TABLE supplier
    (
    S_SUPPKEY UInt32,
    S_NAME String,
    S_ADDRESS String,
    S_CITY LowCardinality(String),
    S_NATION LowCardinality(String),
    S_REGION LowCardinality(String),
    S_PHONE String
    )
    ENGINE = MergeTree ORDER BY S_SUPPKEY;

### 1.3.导入数据 ###

$ clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl
    $ clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl
    $ clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
    $ clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < l
    
    ineorder.tbl

### 1.4.合并多张表为宽表 ###

将star schema转换为flat schema，为后面查询性能压测做准备。

SET max_memory_usage = 20000000000;

CREATE TABLE lineorder_flat
    ENGINE = MergeTree ORDER BY (LO_ORDERDATE, LO_ORDERKEY)
    AS SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER
    FROM lineorder AS l
    INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
    INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
    INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

## 2）读取压测 ##

测试工具JMeter的安装链接：[【JMeter-JMeter的安装】压测工具Apache-JMeter的安装：Windows & Linux][JMeter-JMeter_Apache-JMeter_Windows _ Linux]

### 2.1.创建线程用户并指定参数配置 ###

参数说明：

*  立即停止测试：调度器内达到规定时间立即停止测试
 *  线程数：开启多少并发（如：20、50、100，依次增大）
 *  循环次数：规定时间内循环次数
 *  调度器：本次测试任务的时间调度
 *  持续时间：测试时间

![在这里插入图片描述][29aec0bbb9664eeab406d251d7cc45ed.png]

![在这里插入图片描述][e99b3abbec1e46bf9cba34a62f311d54.png]

### 2.2.创建JDBC Connection Configuration并配置连接信息 ###

参数说明：

*  Variable Name for created pool：创建的连接池名称，需要与后面JDBC Request中保持一致
 *  Database URL：ClickHouse数据库连接URL
 *  JDBC Driver class：ClickHouse连接驱动，使用`ru.yandex.clickhouse.ClickHouseDriver`即可
 *  Username：ClickHouse数据库用户名
 *  Password：ClickHouse数据库密码

![在这里插入图片描述][bdd2ae81241a4c12bde65c3f5bdfd547.png]

![在这里插入图片描述][3de239e38e2f431d9670c10c1f29b497.png]

### 2.3.导入ClickHouse相关jar包 ###

注意：

ClickHouse的连接驱动JMeter自身没有集成，需要我们自行添加，否则就会出现找不到驱动的异常。

提供下载链接：[驱动所需jar包地址][jar]

提取码：dhc8

将所有jar包放入到`JMeter`的`lib`路径下，如果有重复的就跳过添加。

### 2.4.创建JDBC Request取样器并添加需要测试的SQL ###

![在这里插入图片描述][e0401cbc3ee14cb08778bdd14b46a2c6.png]  
![在这里插入图片描述][8ba71f5307d842ac9acb32d32645235a.png]

### 2.5.添加所需要的监听器 ###

![在这里插入图片描述][a89fab608113407d9ad06ffddc757e89.png]

这里选取几个常用的监听器进行举例，其中响应时间单位均为`毫秒`

*  查询结果树：
    
    ![在这里插入图片描述][c1bef3d3f81d4ee48da001d27a450a54.png]
 *  汇总报告：
    
    ![在这里插入图片描述][a44f22b5c73249759f97f9f67e04f60a.png]
 *  聚合报告：
    
    ![在这里插入图片描述][7ad54593748c4b0db725c74b8ff0388d.png]

### 2.6.开始测试 ###

![在这里插入图片描述][0d7ba6740ba24b17bd4851adc519d7de.png]

[JMeter-JMeter_Apache-JMeter_Windows _ Linux]: https://blog.csdn.net/weixin_53543905/article/details/129408329?spm=1001.2014.3001.5502
[29aec0bbb9664eeab406d251d7cc45ed.png]: https://img-blog.csdnimg.cn/29aec0bbb9664eeab406d251d7cc45ed.png
[e99b3abbec1e46bf9cba34a62f311d54.png]: https://img-blog.csdnimg.cn/e99b3abbec1e46bf9cba34a62f311d54.png
[bdd2ae81241a4c12bde65c3f5bdfd547.png]: https://img-blog.csdnimg.cn/bdd2ae81241a4c12bde65c3f5bdfd547.png
[3de239e38e2f431d9670c10c1f29b497.png]: https://img-blog.csdnimg.cn/3de239e38e2f431d9670c10c1f29b497.png
[jar]: https://pan.baidu.com/s/1UjEDvDSQSTkxW01SEb3oZw
[e0401cbc3ee14cb08778bdd14b46a2c6.png]: https://img-blog.csdnimg.cn/e0401cbc3ee14cb08778bdd14b46a2c6.png
[8ba71f5307d842ac9acb32d32645235a.png]: https://img-blog.csdnimg.cn/8ba71f5307d842ac9acb32d32645235a.png
[a89fab608113407d9ad06ffddc757e89.png]: https://img-blog.csdnimg.cn/a89fab608113407d9ad06ffddc757e89.png
[c1bef3d3f81d4ee48da001d27a450a54.png]: https://img-blog.csdnimg.cn/c1bef3d3f81d4ee48da001d27a450a54.png
[a44f22b5c73249759f97f9f67e04f60a.png]: https://img-blog.csdnimg.cn/a44f22b5c73249759f97f9f67e04f60a.png
[7ad54593748c4b0db725c74b8ff0388d.png]: https://img-blog.csdnimg.cn/7ad54593748c4b0db725c74b8ff0388d.png
[0d7ba6740ba24b17bd4851adc519d7de.png]: https://img-blog.csdnimg.cn/0d7ba6740ba24b17bd4851adc519d7de.png