clickhouse两种jdbc对接ELB测试
【摘要】 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转发。每个监听器两台后端服务器，后端服务器端口和监听器端口一致。
后端服务器配置：
负载均衡测试
pom文件配置
<!--<!– 9000端口–>-->
<!-- <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请求：
ELB使用建议
- 如果客户端和ELB建立单个连接，通过ELB往多台clickhouse插入数据，如果想达到负载均衡，请使用ru.yandex.clickhouse.clickhouse-jdbc并且配置ELB的8123端口http协议转发；
- 如果客户端和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）