clickhouse两种jdbc对接ELB测试

陈方业 发表于 2021/05/28 11:48:17 2021/05/28
【摘要】 ELB上tcp协议只能做连接级别的负载均衡,http才可以做请求级别的负载均衡。

摘要

clickhouse目前有两种jdbc,包括clickhouse官网提供的ru.yandex.clickhouse.clickhouse-jdbc和第三方clickhouse-native-jdbc,.clickhouse-jdbc使用的是http协议,对接clickhouse的8123端口,clickhouse-native-jdbc使用的是tcp协议,对接clickhouse的9000端口。当前配置两个ELB监听来测试clickhouse两种端口的负载情况。

环境

在ELB上配置两个监听器:9000和8123。9000端口配置tcp转发,8123配置http转发。每个监听器两台后端服务器,后端服务器端口和监听器端口一致。


20210528-102056(WeLinkPC).png

后端服务器配置:


20210528-102640(WeLinkPC).png

负载均衡测试

pom文件配置

<!--&lt;!&ndash;        9000端口&ndash;&gt;-->
<!--        <dependency>-->
<!--            <groupId>com.github.housepower</groupId>-->
<!--            <artifactId>clickhouse-native-jdbc-shaded</artifactId>-->
<!--            <version>2.4.2</version>-->
<!--        </dependency>-->

<!--        8123端口-->
        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.2.6</version>
        </dependency>
        <dependency>
<!--            涉及到ru.yandex.clickhouse驱动的批量插入setString-->
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1-jre</version>
        </dependency>

测试代码

package com.huawei.cbu.ei.core.clickhouse;

import java.sql.*;

/*如果需要更换不同的clickhouse jdbc,只需要在pom文件中进行修改即可*/
/*clickhouse-native-jdbc :9000端口, TCP连接*/
/*clickhouse-jdbc        :8123端口,HTTP连接*/

public class ClickhouseJDBC {
    private static void resultSetPrint(ResultSet rs) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnsNumber = rsmd.getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= columnsNumber; i++) {
                if (i > 1) System.out.print(",  ");
                String columnValue = rs.getString(i);
                System.out.print(columnValue + " " + rsmd.getColumnName(i));
            }
            System.out.println("");
        }
    }

    public static ResultSet clickhouseQuery(Connection conn, String sql) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(sql);
        return stmt.executeQuery();
    }

    public static void clickhouseInsertBatch(Connection conn, String sql) throws SQLException {
        int count=100;
            while (count >= 0) {
                PreparedStatement stmt = conn.prepareStatement(sql);

                stmt.setString(1, "name_" + count);
                stmt.setInt(2, count);

                stmt.addBatch();
                stmt.executeBatch();

                System.out.println("insert " + count + " ok!");

                count -= 1;
            }
    }

    public static void clickhouseInsertBatch_multiConn(String url, String sql) throws SQLException {
        // ELB的TCP负载均衡:链路负载
        int count=100;
        while (count >= 0) {
            Connection insertConn = DriverManager.getConnection(url);
            PreparedStatement stmt = insertConn.prepareStatement(sql);

            stmt.setString(1, "name_" + count);
            stmt.setInt(2, count);

            stmt.addBatch();
            stmt.executeBatch();

            System.out.println("insert " + count + " ok!");

            count -= 1;

            insertConn.close();
        }
    }

    public static void main(String[] args) throws Exception {

        int testPort=9000;
        String url = "jdbc:clickhouse://127.0.0.1:" + testPort;

        String singleConnTable="elb_" + testPort + "_single";
        String multiConnTable="elb_" + testPort + "_multi";

        String createSingleTableSql="CREATE TABLE IF NOT EXISTS " + singleConnTable + " on cluster default_cluster " +
                                    "(`name` String, `id` UInt32) ENGINE = MergeTree PARTITION BY id ORDER BY id";
        String createMultiTableSql="CREATE TABLE IF NOT EXISTS " + multiConnTable + " on cluster default_cluster " +
                                   "(`name` String, `id` UInt32) ENGINE = MergeTree PARTITION BY id ORDER BY id";

        String insertSingleTableSql="insert into " + singleConnTable + "(name,id) values(?,?)";
        String insertMultiTableSql="insert into " + multiConnTable + "(name,id) values(?,?)";;

        String dropSingleTableSql="DROP TABLE IF EXISTS " + singleConnTable + " on cluster default_cluster";
        String dropMultiTableSql="DROP TABLE IF EXISTS " + multiConnTable + " on cluster default_cluster";

        // 建表
        Connection connection_table = DriverManager.getConnection(url);
        PreparedStatement table_stmt = connection_table.prepareStatement(createSingleTableSql);
        table_stmt.executeUpdate();
        table_stmt = connection_table.prepareStatement(createMultiTableSql);
        table_stmt.executeUpdate();
        connection_table.close();


        System.out.println("多连接测试");
        try  {
            clickhouseInsertBatch_multiConn(url,insertMultiTableSql);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }

        System.out.println("单连接测试");
        Connection connection_native = DriverManager.getConnection(url);
        try  {
            clickhouseInsertBatch(connection_native, insertSingleTableSql);
        } catch (SQLException throwable) {
            connection_native.close();
            throwable.printStackTrace();
        }
        connection_native.close();

        // 清除表
        Connection clearTableConn = DriverManager.getConnection(url);
        PreparedStatement dropStmt = clearTableConn.prepareStatement(dropSingleTableSql);
        dropStmt.executeUpdate();
        dropStmt = clearTableConn.prepareStatement(dropMultiTableSql);
        dropStmt.executeUpdate();
    }
}

测试结果

9000端口测试单条连接结果:单连接下,sql全部发到一台clickhouse上执行

ClickHousejJNB :) select count(*) from system.query_log where query like 'insert into elb_9000_single%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_9000_single%') AND (type = 'QueryFinish')

┌─count()─┐
│     101 │
└─────────┘
1 rows in set. Elapsed: 0.015 sec. Processed 305.12 thousand rows, 20.51 MB (20.33 million rows/s., 1.37 GB/s.) 

ClickHousejJTa :) select count(*) from system.query_log where query like 'insert into elb_9000_single%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_9000_single%') AND (type = 'QueryFinish')

┌─count()─┐
│       0 │
└─────────┘

1 rows in set. Elapsed: 0.019 sec. Processed 339.75 thousand rows, 22.92 MB (18.26 million rows/s., 1.23 GB/s.) 

9000端口测试多连接结果:多连接下,sql均衡发送到两天clickhouse上执行

ClickHousejJNB :) select count(*) from system.query_log where query like 'insert into elb_9000_multi%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_9000_multi%') AND (type = 'QueryFinish')

┌─count()─┐
│      50 │
└─────────┘
1 rows in set. Elapsed: 0.012 sec. Processed 305.23 thousand rows, 20.52 MB (24.85 million rows/s., 1.67 GB/s.) 

ClickHousejJTa :) select count(*) from system.query_log where query like 'insert into elb_9000_multi%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_9000_multi%') AND (type = 'QueryFinish')

┌─count()─┐
│      51 │
└─────────┘
1 rows in set. Elapsed: 0.020 sec. Processed 339.87 thousand rows, 22.93 MB (17.35 million rows/s., 1.17 GB/s.)

8123端口测试单条连接结果:单连接下,sql均衡发送到两天clickhouse上执行

ClickHousejJNB :) select count(*) from system.query_log where query like 'insert into elb_8123_single%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_8123_single%') AND (type = 'QueryFinish')


┌─count()─┐
│      51 │
└─────────┘
1 rows in set. Elapsed: 0.015 sec. Processed 305.60 thousand rows, 20.55 MB (19.86 million rows/s., 1.34 GB/s.) 


ClickHousejJTa :) select count(*) from system.query_log where query like 'insert into elb_8123_single%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_8123_single%') AND (type = 'QueryFinish')

┌─count()─┐
│      50 │
└─────────┘
1 rows in set. Elapsed: 0.019 sec. Processed 340.28 thousand rows, 22.96 MB (17.54 million rows/s., 1.18 GB/s.) 

8123端口测试多条连接结果:多连接下,sql均衡发送到两天clickhouse上执行

ClickHousejJNB :) select count(*) from system.query_log where query like 'insert into elb_8123_multi%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_8123_multi%') AND (type = 'QueryFinish')

┌─count()─┐
│      54 │
└─────────┘
1 rows in set. Elapsed: 0.015 sec. Processed 305.62 thousand rows, 20.56 MB (20.83 million rows/s., 1.40 GB/s.) 


ClickHousejJTa :) select count(*) from system.query_log where query like 'insert into elb_8123_multi%' and type='QueryFinish';

SELECT count(*)
FROM system.query_log
WHERE (query LIKE 'insert into elb_8123_multi%') AND (type = 'QueryFinish')

┌─count()─┐
│      47 │
└─────────┘
1 rows in set. Elapsed: 0.021 sec. Processed 340.30 thousand rows, 22.96 MB (16.45 million rows/s., 1.11 GB/s.) 

测试结果分析

ELB上tcp协议只能做连接级别的负载均衡,http才可以做请求级别的负载均衡。

从tcpdump中来看,一次sql执行(一次批量插入)相对于一个post请求:

20210528-114742(WeLinkPC).png

ELB使用建议

  1. 如果客户端和ELB建立单个连接,通过ELB往多台clickhouse插入数据,如果想达到负载均衡,请使用ru.yandex.clickhouse.clickhouse-jdbc并且配置ELB的8123端口http协议转发;
  2. 如果客户端和ELB建立多个连接,同时通过ELB往多台clickhouse插入数据,可以使用ru.yandex.clickhouse.clickhouse-jdbc或者clickhouse-native-jdbc,并且配置ELB的8123端口http协议或者9000端口tcp协议,都能达到负载均衡效果。当然,由于tcp协议只能做连接级别的负载均衡,如果配置ELB的9000端口tcp协议转发,但是每个连接中请求数差别较大,也无法达到负载均衡的效果。相对而言,推荐使用官方的ru.yandex.clickhouse.clickhouse-jdbc。


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区),文章链接,文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:cloudbbs@huaweicloud.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。