clickhouse两种jdbc对接ELB测试

华为云博客 技术博客 17029 22

摘要

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。


（完）