使用YCSB对Clickhouse进行压测

使用YCSB对Clickhouse进行压测

一、前置条件

成功安装jdk及maven。

二、实现Clickhouse压测客户端

目前YCSB没有实现Clickhouse客户端，因此需要我们自己实现。
添加新database客户端的wiki：Adding a Database

拉取项目

git clone https://github.com/brianfrankcooper/YCSB.git

或者直接下载压缩包解压。

添加新module

项目根目录右键添加module。

ArtifactId为待测试客户端名称，这里为

clickhouse-binding

。

模块创建完成后，可以在根项目的pom文件中看到新加模块。

Clickhouse客户端实现

添加相关依赖。

<?xml version="1.0" encoding="UTF-8"?><projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>site.ycsb</groupId><artifactId>binding-parent</artifactId><version>0.18.0-SNAPSHOT</version><relativePath>../binding-parent</relativePath></parent><artifactId>clickhouse-binding</artifactId><name>Clickhouse Binding</name><packaging>jar</packaging><dependencies><dependency><groupId>ru.yandex.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.1.52</version></dependency><dependency><groupId>site.ycsb</groupId><artifactId>core</artifactId><version>${project.version}</version><scope>provided</scope></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>1.7.13</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-simple</artifactId><version>1.7.13</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency></dependencies></project>

客户端实现，由于这里我只需要测试查询性能，且在现有数据集上测试，因此只实现了

scan()

方法。另外，我测试的sql只有一个返回值，因此只用了一个

key

表示，若需多个返回值，可以通过传入字段数组的方式对其进行修改。

packagesite.ycsb.db.clickhouse;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importru.yandex.clickhouse.ClickHouseConnection;importru.yandex.clickhouse.ClickHouseDataSource;importru.yandex.clickhouse.settings.ClickHouseProperties;importsite.ycsb.ByteIterator;importsite.ycsb.DBException;importsite.ycsb.Status;importsite.ycsb.StringByteIterator;importjava.sql.*;importjava.util.*;importjava.util.concurrent.atomic.AtomicInteger;/**
 * Clickhouse client for YCSB framework.
 */publicclassClickhouseClientextends site.ycsb.DB {privatestaticfinalLogger LOGGER =LoggerFactory.getLogger(ClickhouseClient.class);/** Count the number of times initialized to teardown on the last. */privatestaticfinalAtomicInteger INIT_COUNT =newAtomicInteger(0);privatestaticClickHouseConnection connection;// clickhouse urlprivatestaticfinalString CONNECTION_URL ="clickhouse.url";// clickhouse portprivatestaticfinalString CONNECTION_PORT ="clickhouse.port";// clickhouse database nameprivatestaticfinalString CONNECTION_DB ="clickhouse.db";// clickhouse usernameprivatestaticfinalString CONNECTION_USER ="clickhouse.username";// clickhouse passwordprivatestaticfinalString CONNECTION_PASSWORD ="clickhouse.password";// sql for testprivatestaticfinalString SCAN_SQL ="clickhouse.scan_sql";// sql resultprivatestaticfinalString RES_KEY ="clickhouse.key";privatestaticfinalString DEFAULT_PROP ="";privateString sql;privateString key;privatePreparedStatement statement;@Overridepublicvoidinit()throwsDBException{
    INIT_COUNT.incrementAndGet();synchronized(ClickhouseClient.class){Properties props =getProperties();String url = props.getProperty(CONNECTION_URL, DEFAULT_PROP);String port = props.getProperty(CONNECTION_PORT, DEFAULT_PROP);String db = props.getProperty(CONNECTION_DB, DEFAULT_PROP);String username = props.getProperty(CONNECTION_USER, DEFAULT_PROP);String password = props.getProperty(CONNECTION_PASSWORD, DEFAULT_PROP);
      sql = props.getProperty(SCAN_SQL, DEFAULT_PROP);
      key = props.getProperty(RES_KEY, DEFAULT_PROP);ClickHouseProperties properties =newClickHouseProperties();
      properties.setUser(username);
      properties.setPassword(password);
      properties.setDatabase(db);
      properties.setSocketTimeout(60000000);ClickHouseDataSource clickHouseDataSource =newClickHouseDataSource("jdbc:clickhouse://"+ url +":"+ port, properties);try{
        connection = clickHouseDataSource.getConnection();System.out.println(connection);
        statement = connection.prepareStatement(sql);}catch(SQLException e){
        LOGGER.error(e.getMessage());}}}@Overridepublicvoidcleanup()throwsDBException{if(INIT_COUNT.decrementAndGet()==0){try{if(!connection.getAutoCommit()){
          connection.commit();}
        connection.close();}catch(SQLException e){System.err.println("Error in cleanup execution. "+ e);}}}@OverridepublicStatusread(String table,String key,Set<String> fields,Map<String,ByteIterator> result){returnnull;}@OverridepublicStatusscan(String table,String startkey,int recordcount,Set<String> fields,Vector<HashMap<String,ByteIterator>> result){try{ResultSet resultSet = statement.executeQuery();if(!resultSet.next()){
        resultSet.close();returnStatus.NOT_FOUND;}HashMap<String,ByteIterator> values =newHashMap<String,ByteIterator>();Integer value = resultSet.getInt(key);
      values.put(key,newStringByteIterator(value.toString()));
      result.add(values);
      resultSet.close();}catch(SQLException e){
      LOGGER.error(e.getMessage());returnStatus.ERROR;}returnStatus.OK;}@OverridepublicStatusupdate(String table,String key,Map<String,ByteIterator> values){returnnull;}@OverridepublicStatusinsert(String table,String key,Map<String,ByteIterator> values){returnnull;}@OverridepublicStatusdelete(String table,String key){returnnull;}}

实现单测。

packagesite.ycsb.db.clickhouse;importorg.junit.BeforeClass;importorg.junit.Test;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importru.yandex.clickhouse.ClickHouseConnection;importru.yandex.clickhouse.ClickHouseDataSource;importru.yandex.clickhouse.settings.ClickHouseProperties;importsite.ycsb.ByteIterator;importsite.ycsb.DBException;importjava.io.IOException;importjava.net.Socket;importjava.sql.SQLException;importjava.util.HashMap;importjava.util.Properties;importjava.util.Vector;importstaticorg.hamcrest.CoreMatchers.not;importstaticorg.junit.Assert.assertThat;importstaticorg.junit.Assume.assumeNoException;publicclassClickhouseClientTest{privatestaticfinalLogger LOGGER =LoggerFactory.getLogger(ClickhouseClientTest.class);privatestaticClickhouseClient clickhouseClient;privatestaticClickHouseConnection connection;privatestaticfinalString CONNECTION_URL ="clickhouse.url";// your clickhouse ipprivatestaticfinalString CONNECTION_URL_INPUT ="your clickhouse ip";privatestaticfinalString CONNECTION_PORT ="clickhouse.port";// your clickhouse portprivatestaticfinalString CONNECTION_PORT_INPUT ="your clickhouse port";privatestaticfinalString CONNECTION_DB ="clickhouse.db";// your database nameprivatestaticfinalString CONNECTION_DB_INPUT ="database name";privatestaticfinalString CONNECTION_USER ="clickhouse.username";// your usernameprivatestaticfinalString CONNECTION_USER_INPUT ="your username";privatestaticfinalString CONNECTION_PASSWORD ="clickhouse.password";// your passwordprivatestaticfinalString CONNECTION_PASSWORD_INPUT ="your password";privatestaticfinalString SCAN_SQL ="clickhouse.scan_sql";// your test sqlprivatestaticfinalString SCAN_SQL_INPUT ="select uniqExact(user_id) as count from tb";privatestaticfinalString RES_KEY ="clickhouse.key";// the field returned by sqlprivatestaticfinalString RES_KEY_INPUT ="count";@BeforeClasspublicstaticvoidsetUp(){try(Socket socket =newSocket(CONNECTION_URL_INPUT,Integer.valueOf(CONNECTION_PORT_INPUT))){assertThat("Socket is not bound.", socket.getLocalPort(),not(-1));}catch(IOException connectFailed){assumeNoException("PostgreSQL is not running. Skipping tests.", connectFailed);}Properties props =newProperties();
    props.setProperty(CONNECTION_URL, CONNECTION_URL_INPUT);
    props.setProperty(CONNECTION_PORT, CONNECTION_PORT_INPUT);
    props.setProperty(CONNECTION_DB, CONNECTION_DB_INPUT);
    props.setProperty(CONNECTION_USER, CONNECTION_USER_INPUT);
    props.setProperty(CONNECTION_PASSWORD, CONNECTION_PASSWORD_INPUT);
    props.setProperty(SCAN_SQL, SCAN_SQL_INPUT);
    props.setProperty(RES_KEY, RES_KEY_INPUT);try{ClickHouseProperties properties =newClickHouseProperties();
      properties.setUser(CONNECTION_USER_INPUT);
      properties.setPassword(CONNECTION_PASSWORD_INPUT);
      properties.setDatabase(CONNECTION_DB_INPUT);
      properties.setSocketTimeout(600000);ClickHouseDataSource clickHouseDataSource =newClickHouseDataSource("jdbc:clickhouse://"+ CONNECTION_URL_INPUT +":"+ CONNECTION_PORT_INPUT, properties);
      connection = clickHouseDataSource.getConnection();boolean tableExists = connection.getMetaData().getTables(null,null,"tb_user_action_3",null).next();assertThat("Table does not exist.", tableExists,not(false));

      clickhouseClient =newClickhouseClient();
      clickhouseClient.setProperties(props);
      clickhouseClient.init();}catch(SQLException|DBException e){
      LOGGER.error(e.toString());}}@TestpublicvoidscanTest(){Vector<HashMap<String,ByteIterator>> results =newVector<>();
    clickhouseClient.scan("","",1,null, results);
    LOGGER.info(results.toString());}}

压测前准备

YCSB的

bin

目录下事先准备好了执行脚本，但为了可以正确使用它，需要做一些准备。
首先修改

bin

目录下

bindings.properties

文件，添加clickhouse标识，使脚本可以识别clickhouse，并映射到clickhouse的客户端。

如果使用python脚本

ycsb

，则还需修改脚本，在

DATABASES

字典中添加clickhouse的映射。

为了使测试方便，我还在脚本中添加了打包时跳过代码风格检查的flag。

别的脚本同理，修改

mvn

命令即可。

打包

如果嫌麻烦，可以直接在项目根目录下：

mvn clean
mvn install

也可以单独给模块打包，在

core

、

binding-parent

等自定义数据库客户端依赖的包目录以及

clickhouse

包目录下执行：

mvn clean
mvn install

实现测试配置文件

在

workloads

目录下复制一份配置文件模板。

cp workload_template workload_clickhouse

添加并修改文件内容。

...# The name of the workload class to use
workload=site.ycsb.workloads.CoreWorkload

# The inputs of clickhouse# 完善信息
clickhouse.url=
clickhouse.port=
clickhouse.db=
clickhouse.username=
clickhouse.password=
clickhouse.scan_sql=
clickhouse.key=

# There is no default setting for operationcount but it is# required to be set.# The number of operations to use during the run phase.# 总操作数
operationcount=30

...# What proportion of operations are reads
readproportion=0

# What proportion of operations are updates
updateproportion=0

# What proportion of operations are inserts
insertproportion=0

# What proportion of operations read then modify a record
readmodifywriteproportion=0

# scan操作占比100%# What proportion of operations are scans
scanproportion=1

...

执行脚本进行测试

在项目根目录下：

./bin/ycsb run clickhouse -threads 2 -target 10 -P ./workloads/workload_clickhouse

即可看到压测结果。