Clickhouse的使用记录

2020-04-06 磨延城 1426 字 ~3分钟
clickhouse  database  ssh隧道  tidb 

[文章作者：磨延城 转载请注明原文出处： http://notes.mo2g.com/database/clickhouse/experience-sharing/ ]

为什么使用 Clickhouse

如果你用 Mysql 做过 OLAP 之类的报表统计，应该会明白复杂查询在关系型数据库中运行会有多慢。我曾经拿 300G 的真实数据来做过基准测试，你们可以参考一下我司真实业务中 OLAP 耗时的复杂子查询分别在 Mysql Clickhouse TiDB 中的测试结果。

  1. Mysql 耗时 15 min 22.93 sec，够时间去喝下午茶了 🤣
  2. Clickhouse 耗时 3.761 sec，令人印象深刻 👍
  3. TiDB 耗时 10.11 sec，不负众望 👍

从 2016 年我就开始内推 TiDB，历时 4 年，TiDB 终于用到了核心系统中

不要纠结 TiDB 使用了几台服务器，测试使用的服务器配置如何这些问题，我只关注业务优化后的效果，使用场景并不通用，只能作为参考。

由于 Clickhouse 的查询性能非常牛逼，后来我把部分业务中的 Mysql 换成了 Clickhouse，优化后性能提升接近上述的测试结果。

接下来内容是我使用 Clickhouse 的一些记录，供大家参考。

yum 安装 Clickhouse

1
2
3
4

sudo yum install yum-utils
sudo rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
sudo yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/stable/x86_64
sudo yum install clickhouse-server clickhouse-client

启动服务

1

systemctl start clickhouse-server

Clickhouse 主要目录结构

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

/etc/clickhouse-server   clickhouse服务的配置文件目录，包括：config.xml和users.xml
/etc/clickhouse-client    clickhouse客户端的配置文件目录，里面只有一个config.xml并且默认为空
/var/lib/clickhouse     clickhouse默认数据目录
/var/log/clickhouse-server    clickhouse默认日志目录
/etc/init.d/clickhouse-server   clickhouse启动shell脚本，用来方便启动服务的.
/etc/security/limits.d/clickhouse.conf   最大文件打开数的配置，这个在config.xml也可以配置
/etc/cron.d/clickhouse-server    clickhouse定时任务配置，默认没有任务，但是如果文件不存在启动会报错.
/usr/bin    clickhouse编译好的可执行文件目录，主要有下面几个：
    clickhouse     clickhouse主程序可执行文件
    clickhouse-compressor
    clickhouse-client      是一个软链指向clickhouse，主要是客户端连接操作使用
    clickhouse-server     是一个软链接指向clickhouse，主要是服务操作使用

Clickhouse 使用记录

我们可以很方便的把 Mysql 中的数据导入刚装好的 Clickhouse，Clickhouse 的压缩率很高 30G 的 mysql 数据导入 Clickhouse 只占用 2.6G 的磁盘空间。

分享一个 TiDB 使用案例，10 T 的 MySQL 数据，三个副本 30T，乘 70% 的压缩率，大概 20 T 数据，加上一定 buffer， 如果每个 SSD 1T 的，可能需要 30 个盘，一般的 X86 机器的话，可以考虑三实例部署，TiKV 的话，也就是大概 10 台机器 + 30 个 SSD 盘。

连接 clickhouse 数据库

-m 添加参数后，Enter 只会换行，除非遇上分号才会执行

1

clickhouse-client -m

创建数据库

1
2

create database bigdata;
use bigdata;

从 Mysql 中导入数据+自动建表

1
2
3
4

CREATE TABLE IF NOT EXISTS bigdata.users
ENGINE = MergeTree
ORDER BY id AS
SELECT * FROM mysql('127.0.0.1', 'mysql数据库名', 'mysql表名', '用户名', '密码');

添加索引

索引不会影响历史数据，所以得事先建好索引，才会对之后插入的数据生效

1

ALTER TABLE bigdata.users ADD INDEX bigdata_date (channel_id,date) TYPE minmax GRANULARITY 8192;

删除索引

1

ALTER TABLE bigdata.users drop index bigdata_date

导入增量数据

可以写脚本定时同步 Mysql 的数据到 clickhouse,使用顺序的自增 ID 来做增量同步每秒能处理 20 万的数据。

1
2
3

insert into bigdata.users
SELECT * FROM mysql('127.0.0.1', 'mysql数据库名', 'mysql表名', '用户名', '密码')
where id > (select max(id) from bigdata.users);

命令行写法

1

clickhouse-client -q "SQL语句";

导出 clickhouse 数据

1

clickhouse-client -h  127.0.0.1 --port 9001 -m -d bigdata -q "select * from users where id > 1942467" | gzip > bigdata_users.sql.gz

导出的 clickhouse 数据默认分割符是 tab

导入 clickhouse 数据

1

gunzip < bigdata_users.sql.gz | clickhouse-client --min_insert_block_size_rows 1000 --format=TabSeparated -q "INSERT INTO bigdata.users FORMAT TabSeparated"

参数说明

  1. –min_insert_block_size_rows 限制每次插入的数据量，避免爆内存
  2. –format 指定默认分隔符

其他 clickhouse 使用经验分享

  1. select 语句避免使用*。
  2. 针对大表的关联查询，可以通过减少查询字段来降低内存消耗，避免爆内存导致 OOM。
  3. https://www.altinity.com/ 同事分享的这个网站有很多使用经验。
  4. 其他想起来再补充 😂。