clickhouse-jdbc性能排查

我们项目准备用clickhouse来做数据统计，当前(2021-04-18)使用的是clickhouse官方最新发布的0.3.0版jdbc驱动，使用过程中碰到了几个问题：

1、javacc解析器导致大文本sql语句的解析性能损耗严重

2、ClickHouseConnection#getMetaData获取元数据时使用反射，导致JdbcTemplate.batchUpdate性能损耗严重。

3、对java8的LocalDateTime支持有问题

JavaCC解析器的性能问题

按照clickhouse官方文档中给的性能建议：使用批量插入单个请求里至少1000行。官方给的性能数据是CSV插入MergeTree表，可以达到50～200M/S。

根据这个建议，项目中将kafka的数据按批次读取出来，直接拼成一条条sql去执行，但是线上性能和预期差距特别大：一秒不到一万条数据，一万条trade_info大概5M，也就是不超过5M/s。

clickhouse-server性能

首先想的是测试一下clickhouse-server性能，是不是言过其实。

我在开发环境测试过，由于开发环境带宽影响也没达到预期：开发环境是公司内网，机器部署在15楼，7楼访问带宽只能达到4M/s，网络影响很大。

所以干脆直接起了个docker，写了个脚本在docker里直接测了下性能：

rows	curl	clickhouse-client sql	clickhouse-client csv
10000 5.2M	295.195ms 17.6M/s	178.915ms 29.0M/s	131.204ms 39.69M/s

这里使用clickhouse自带的clickhouse-client插入csv能达到将近40M/s，所以基本排除了clickhouse-server的问题。

JavaCC解析器

鉴于此，将我们的测试代码跑了一下profiler，发现大部分cpu时间都花在了ClickHouseParser.parse方法上：

arthas

看clickhouse-jdbc源码发现ClickHouseSqlParser是使用JavaCC文法分析器自动生成的代码。从github上的issue来看JavaCC解析器是0.2.6版本新引入的，用于替换之前基于正则表达式的解析器。

我提了个issue给clickhouse-jdbc项目，得到回复说可以用use_new_parser=false禁用JavaCC解析器。

我用了一下发现Connection都无法创建，原因是Connection初始化的时候会执行一条查询clickhouse-server时区的sql。而且由于原来基于正则的解析器bug特别多，它将在0.3.0被移除，为了保证向后兼容没有使用use_new_parser=false的方式。

JdbcTemplate.batchUpdate的问题

clickhouse-driver的开发人员在issue中提到，可以直接用PreparedStatement.addBatch来批量插入数据。

所以我们又转向用JdbcTemplate.batchUpdate的方式来执行批量插入，发现性能离预期还是有很大差距。

SqlExecutor vs. BatchUpdater vs. HttpClientExecutor

最后我写了份测试代码，直接用HttpClient调用clickhouse-server的Http接口。

得到如下的测试结果(纵坐标是执行耗时，单位ms)：

SqlExecutor是jdbcTemplate.execute直接执行大文本sql
BatchUpdater是jdbcTemplate.batchUpdate批量插入
HttpClientExecutor是使用httpclient直接调用clickhouse-server的Http接口执行大文本sql。

用httpclient差不多1秒钟可以插入3W条数据，大约15M/s，和直接在docker里用curl插入性能相差不多。

看了一下jdbcTemplate.batchUpdate的消耗的火炬图，发现大部分CPU时间都花在StatementCreateorUtils.setNull：

从Spring源码看来，是因为对于字段类型未知的null字段，Spring会调用Connection.getMetaData去获取数据库的类型，从而在setNull的时候兼容不同的数据库。

package org.springframework.jdbc.core;

public abstract class StatementCreatorUtils {
  // ...
		if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER && typeName == null)) {
			boolean useSetObject = false;
			Integer sqlTypeToUse = null;
			if (!shouldIgnoreGetParameterType) {
				try {
					sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
				}
				catch (SQLException ex) {
					if (logger.isDebugEnabled()) {
						logger.debug("JDBC getParameterType call failed - using fallback method instead: " + ex);
					}
				}
			}
			if (sqlTypeToUse == null) {
				// Proceed with database-specific checks
				sqlTypeToUse = Types.NULL;
				DatabaseMetaData dbmd = ps.getConnection().getMetaData();
				String jdbcDriverName = dbmd.getDriverName();
				String databaseProductName = dbmd.getDatabaseProductName();
				if (databaseProductName.startsWith("Informix") ||
						(jdbcDriverName.startsWith("Microsoft") && jdbcDriverName.contains("SQL Server"))) {
						// "Microsoft SQL Server JDBC Driver 3.0" versus "Microsoft JDBC Driver 4.0 for SQL Server"
					useSetObject = true;
				}
				else if (databaseProductName.startsWith("DB2") ||
						jdbcDriverName.startsWith("jConnect") ||
						jdbcDriverName.startsWith("SQLServer")||
						jdbcDriverName.startsWith("Apache Derby")) {
					sqlTypeToUse = Types.VARCHAR;
				}
			}
			if (useSetObject) {
				ps.setObject(paramIndex, null);
			}
			else {
				ps.setNull(paramIndex, sqlTypeToUse);
			}
		}
		else if (typeName != null) {
			ps.setNull(paramIndex, sqlType, typeName);
		}
		else {
			ps.setNull(paramIndex, sqlType);
		}
  //...
}

而ClickHouseConnection在getMetaData时会通过反射来记录trace日志。

package ru.yandex.clickhouse;

public class ClickHouseConnectionImpl implements ClickHouseConnection {
  //...
		@Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return LogProxy.wrap(DatabaseMetaData.class, new ClickHouseDatabaseMetadata(url, this));
    }
	//...
}

原生Jdbc批量插入性能

最后我在测试中加入了原生Jdbc的Batch方式：

public int batchInsert(String batchSql, List<Object[]> args) {
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(batchSql)) {
        Preconditions.checkArgument(JdbcUtils.supportsBatchUpdates(conn));
        for (Object[] arg : args) {
            for (int i = 0; i < arg.length; i++) {
                int paramIndex = i + 1;
                Object o = arg[i];
                if (o instanceof CharSequence) {
                    ps.setString(paramIndex, o.toString());
                } else {
                    // ClickHouse-Jdbc考虑了o为null的情况，不用做特殊处理
                    ps.setObject(paramIndex, o);
                }
            }
            ps.addBatch();
        }
        int[] ints = ps.executeBatch();
        return Arrays.stream(ints).sum();
    } catch (SQLException e) {
        return ExceptionUtils.rethrow(e);
    }
}

测试结果发现，原生Jdbc批量插入和直接用HttpClient基本没什么性能差距。

上线后Kafka消费速度从原来的不到5M/s(均速3M/s)提升到了15M/s

LocalDateTime问题

我们项目直接把Kafka中同步的json拼成sql写入到clickhouse，没有先转换成Java对象再交给ORM框架处理。

我们kafka中存储的时间类型都是yyyy-MM-dd HH:mm:ss标准格式的字符串，好在clickhouse-jdbc也是使用ClickHouseValueFormatter直接将所有类型转换成字符串，并且时间的格式也是yyyy-MM-dd HH:mm:ss。

但如果是直接使用JdbcTemplate，插入LocalDateTime类型可能就会有问题，因为clickhouse-jdbc 2.6版本不支持LocalDateTime类型，PreparedStatement.setObject()会直接调用LocalDateTime.toString()方法，toString方法格式是uuuu-MM-dd'T'HH:mm:ss，服务端执行时将无法识别。

clickhouse-jdbc3.0添加了对java.time的支持: ClickHousePreparedStatementImpl、ClickHouseResultSet。

但是在返回值解析时，被统一转成了UTC时区的LocalDateTime:

package ru.yandex.clickhouse.response.parser;

abstract class ClickHouseDateValueParser<T> extends ClickHouseValueParser<T> {
// ...
    protected LocalDateTime dateTimeToLocalDateTime(String value, ClickHouseColumnInfo columnInfo, TimeZone timeZone) {
        TimeZone serverTimeZone = columnInfo.getTimeZone();
        LocalDateTime localDateTime = parseAsLocalDateTime(value);
        if (serverTimeZone != null
            && (serverTimeZone.useDaylightTime() || serverTimeZone.getRawOffset() > 0)) { // non-UTC
            localDateTime = localDateTime.atZone(columnInfo.getTimeZone().toZoneId())
                .withZoneSameInstant(java.time.ZoneId.of("UTC")).toLocalDateTime();
        }

        return localDateTime;
    }
//...
}