0. 前言

最近项目上线前遇到一个性能问题,需求很简单,对一张有300万行数据Table 的多个Column 进行模糊查询,类似这样子:

select * from documen where name like '%abc%' or code like '%abc' or typeName like '%abc%'

由于在mysql 数据库中,对全模糊匹配的查询是不能利用上索引的,且Table的数据库量(约300万行)及Column 数量 (约200多个Column)都比较大,在mysql 下经过了很多优化尝试均没能达到性能要求,而mysql 下的full-text 索引在中、英文、数字混合的场景下 检索的效果也不如人意。 这时候项目组想到了ClickHouse, 将数据导入到ClickHouse 后,在没有做任何索引优化的情况下,相同的查询,执行时间从 29秒降低到 500毫秒,满足了项目的性能要求。

由于是一个企业级的项目,客户要求系统需要有高可用机制,所以我们搭建了3个节点组成的群集,下面我们将群集安装配置的主要步骤分享出来,供大家参考。

1. ClickHouse 簡介

ClickHouse 是一个开源的用于联机查询分析(OLAP)的列式数据库管理系统。 基于列式存储、向量化处理、MPP等技术,使ClickHouse 有优秀的性能,适合用于 OLAP、实时BI、统计分析、报表等应用场景。 可以在Github 上下载。

https://github.com/ClickHouse/ClickHouse

2. 安裝包下載

访问下面地址,下载三个安装包

Release Release v22.6.9.11-stable · ClickHouse/ClickHouse (github.com)

clickhouse-client

https://github.com/ClickHouse/ClickHouse/releases/download/v22.6.9.11-stable/clickhouse-client-22.6.9.11.x86_64.rpm

clickhouse-common

https://github.com/ClickHouse/ClickHouse/releases/download/v22.6.9.11-stable/clickhouse-common-static-22.6.9.11.x86_64.rpm

clickhouse-server

https://github.com/ClickHouse/ClickHouse/releases/download/v22.6.9.11-stable/clickhouse-server-22.6.9.11.x86_64.rpm

将安装包拷贝到服务器上。

3. 单机安装

登录服务器

运行下面命令

sudo rpm -i clickhouse-common-static-22.6.9.11.x86_64.rpm
sudo rpm -i clickhouse-cli··ent-22.6.9.11.x86_64.rpm
sudo rpm -i clickhouse-server-22.6.9.11.x86_64.rpm

在提示 Enter password for default user 时 输入缺省用户的密码

启动Clickhouse 服务

sudo systemctl start clickhouse-server

验证客户端连接

clickhouse-client --host 127.0.0.1 --user default --password

输入密码后显示 如下,表明已经连接到Clickhouse 服务器

servername :) 

输入兼容MySQL的 命令查看数据库,输入exit; 可以退出客户端

show databases;

修改配置文件的owner 和 mode ,使我们可以对配置文件进行编辑。config.xml 缺省是属于clickhouse 用户,且是只读的,要修改配置文件需要修改文件的owner 和 mode。

sudo chown root:root  /etc/clickhouse-server/config.xml
sudo chmod 600 /etc/clickhouse-server/config.xml

clickhouse 缺省只监听localhost, 外部机器不能通过网络连接。需要使用文本编辑工具, 编辑配置文件, 允许监听所有IP地址。

sudo vi /etc/clickhouse-server/config.xml

找到下面这行,将XML 注析 去掉,修改好后如下:

<listen_host>::</listen_host>

该配置使clickhouse 监听IPV4, IPV6的所有地址。

修改完成后,还原配置文件的owner 和 mode

sudo chown clickhouse:clickhouse  /etc/clickhouse-server/config.xml
sudo chmod 400 /etc/clickhouse-server/config.xml

重启 clickhouse-server

sudo systemctl restart clickhouse-server

用netstat -l 确认 clickhouse-server 是否已经监听到所有IP地址了

netstat -l |grep 8123

4. 群集安装

4.1 安装及配置zookeeper

clickhouse 群集需要zookeeper 支撑,在配置群集时,先要安装zookeeper。

zookeeper 群集需要至少三台机器,本次安装我们准备了下面三台机器安装 zookeeper

机器名 IP 地址
node-1 192.168.100.101
node-2 192.168.100.102
node-3 192.168.100.103

zookeeper 需要 JDK 1.8 及以上版本,请在安装zookeeper 前先在确定三台服务器上已经安装好JDK。

在下面地址下载zookeeper 的安装包,目前最新的稳定版本是 3.7.1

https://www.apache.org/dyn/closer.lua/zookeeper/zookeeper-3.7.1/apache-zookeeper-3.7.1-bin.tar.gz

将 apache-zookeeper-3.7.1-bin.tar.gz 文件 拷贝三台服务器

依次登录 三台服务器,执行下面命令将zookeeper 安装到 /usr/local/ 目录下

tar -xvf apache-zookeeper-3.7.1-bin.tar.gz
sudo mv apache-zookeeper-3.7.1-bin /usr/local/apache-zookeeper-3.7.1
sudo chown -R root:root /usr/local/apache-zookeeper-3.7.1

修改每台服务器的 /etc/profile ,增加下面内容,用于设置zookeeper 环境变量及路径

ZK_HOME=/usr/local/apache-zookeeper-3.7.1
export PATH=$PATH:$ZK_HOME/bin

修改每台服务器的zookeeper 配置文件 /usr/local/apache-zookeeper-3.7.1/conf/zoo.cfg , 内容如下

tickTime=2000
initLimit=10
syncLimit=5
dataDir=/data/zkdata
clientPort=2181
server.1=node-1:2888:3888
server.2=node-2:2888:3888
server.3=node-3:2888:3888

在每台服务器创建zookeeper 的数据目录 /data/zkdata。 在目录下面创建一个myid文件,写入一个0~255之间的整数,对应到我们的环境:

node-1 服务器的 /data/zkdata/myid 文件内容为

1

node-2 服务器的 /data/zkdata/myid 文件内容为

2

node-3 服务器的 /data/zkdata/myid 文件内容为

3

4.2 规划clickhouse 群集

clickhouse 群集由多台服务器组成,是一个无中心的群集结构,每个节点均可以访问数据及插入数据。 每个节点承载的数据有下面两种功能:

  • 数据分片 shard: 用于支持大数据量情况下的扩展,对一个大数据集,可以由多个节点分片存储,比如3千万数据行的表,三个节点分片存储,每个节点存储1千万行。
  • 数据复制 replica: 用于支持高可用,通过数据复制,一份数据将的多个副本将存储在多个节点上,当一个节点故障时,其它节点仍然能够提供服务。

在进行clickhouse 群集进行配置前,我们需要对 群集组成的节点、数据分片 和 数据复制 进行规划。

群集名称: my_cluster_01

节点 数据分片shard 数据复制 replica
node-1 s1 node-1-s1-r1
node-2 s1 node-2-s1-r2
node-3 s1 node-3-s1-r3

如上,我们的群集有三个节点组成。群集只有一个分片,该分片有3个复制副本,分别由三个节点承载。

4.3 安装clickhouse 单机环境

按本文档 2. 单机安装的步骤,在构成群集的每个服务器节点上安装clickhouse。

4.4 准备clickhouse 群集配置文件

在构成群集的每个服务器节点上,修改clickhouse 配置文件 /etc/clickhouse-server/config.xml

config.xml 文件中找到 clickhouse_remote_servers 节点,修改如下,password 中请修改为default 账号的密码

<clickhouse_remote_servers>
    <!-- 集群名称 -->
    <my_cluster_01>
        <!-- 配置1个分片,1个分片对应3台机器,为每个分片配置3个副本 -->
        <shard>
             <internal_replication>true</internal_replication>
            <replica>
                <host>node-1</host>
                <port>9000</port>
                <password>test123</password>
            </replica>
            <replica>
                <host>node-2</host>
                <port>9000</port>
                <password>test123</password>
            </replica>
            <replica>
                <host>node-3</host>
                <port>9000</port>
                <password>test123</password>
            </replica>
        </shard>
    </dyw_cluster_01>
</clickhouse_remote_servers>

config.xml 文件中找到 zookeeper-servers 节点,修改为如下内容,host 及 port 填入 4.1 安装及配置zookeeper 中 zookeeper 群集的每个节点的主机名及 端口。

<zookeeper-servers>
  <node index="1">
    <host>node-1</host>
    <port>2181</port>
  </node>
  <node index="2">
    <host>node-2</host>
    <port>2181</port>
  </node>
  <node index="3">
    <host>node-3</host>
    <port>2181</port>
  </node>
</zookeeper-servers>

config.xml 文件中找到 节点,修改为如下内容,该部分每个服务器节点上的内容不一样:

node-1 节点

<macros>
    <share>s1</share>
    <replica>node-1-s1-r1</replica>
</macros>

node-2 节点

<macros>
    <share>s1</share>
    <replica>node-2-s1-r2</replica>
</macros>

node-3 节点

<macros>
    <share>s1</share>
    <replica>node-3-s1-r3</replica>
</macros>

4.5 启动群集

4.5.1 启动zookeeper 群集

登录zookeeper 的每台服务器,执行下面命令

zkServer.sh start

4.5.2 重启clickhouse 群集

重新启动每台服务器上的clickhouse server, 使修改后的 config.xml 文件生效。

登录clickhouse 群集的每台服务器,执行下面命令

sudo systemctl restart clickhouse-server

4.5.3 检查clickhouse 群集的情况

用clickhouse 客户端连接 群集中的任何一台clickhouse-server, 执行下面SQL

select * from system.clusters;

4.6 在群集上创建表及验证数据复制

可以通过 ReplicatedMergeTree 引擎创建 在群集中自动复制的表,实现高可用需求。

4.6.1 创建用于测试的数据库

用clickhouse 客户端连接 群集中的每个节点,执行下面的SQL, 创建一个名称为 testdb 数据库用于测试

create database testdb;

4.6.2 创建 ReplicatedMergeTree 引擎的表

用clickhouse 客户端连接 群集中的一个节点,执行下面的SQL,创建一张用于测试的ReplicatedMergeTree 引擎的表

CREATE TABLE testdb.test_table ON CLUSTER my_cluster_01
(
		`ID` String,
    `Name` String,
		`TenantID` Int32
)
PRIMARY KEY (ID, TenantID)
ORDER BY (ID, TenantID)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/s1/testdb/test_table', '{replica}');

ON CLUSTER my_cluster_01 表明在群集 my_cluster_01 上创建这张表,这个注意不要遗漏。

ENGINE = ReplicatedMergeTree 表明 使用 ReplicatedMergeTree 引擎

'/clickhouse/tables/s1/testdb/test_table' 表明s1分片、testdb 库、test_table 表

执行完成后,我们在群集的每个节点的testdb 数据库下都可以看到 test_table 这张表。

4.6.3 验证数据同步

4.6.3.1 插入数据

用clickhouse 客户端连接 群集中的一个节点,执行下面的SQL,向表 test_table 插入数据

INSERT INTO testdb.test_table VALUES('001','Name001','101');
INSERT INTO testdb.test_table VALUES('002','Name001','101');
INSERT INTO testdb.test_table VALUES('003','Name001','101');

执行完后,用clickhouse 客户端连接 群集中的每个节点,执行下面的SQL,确定数据已经同步到每个节点

SELECT * FROM testdb.test_table;

4.6.3.2 修改数据

用clickhouse 客户端连接 群集中的一个节点,执行下面的SQL,更新DM_Document_RMT中的一条数据

ALTER TABLE testdb.test_table UPDATE Name='Name001-XXX'  WHERE ID='001';

执行完后,用clickhouse 客户端连接 群集中的每个节点,执行下面的SQL,确定数据已经同步到每个节点

SELECT * FROM testdb.test_table WHERE ID='001';

4.6.3.3 删除数据

用clickhouse 客户端连接 群集中的一个节点,执行下面的SQL,删除DM_Document_RMT中的一条数据

ALTER TABLE testdb.test_table DELETE  WHERE ID='001';

执行完后,用clickhouse 客户端连接 群集中的每个节点,执行下面的SQL,确定数据已经同步到每个节点

SELECT * FROM testdb.test_table WHERE ID='001';