Posted in运维

CentOS7安装配置ClickHouse

8月 13, 2020No Comments

业务需求

公司内网mysql服务器使用32核32G内存12T机械硬盘
当前遇到了查询瓶颈，磁盘I/O无法跟上系统，mysql表结构如下地

CREATE TABLE `detaillog20200213` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键，自增长',
  `wuserId` int(11) NOT NULL,
  `tradeno` varchar(80) NOT NULL COMMENT 
  `type` int(11) NOT NULL DEFAULT '0' COMMENT 
  `tradedec` varchar(255) DEFAULT NULL COMMENT 
  `orderstatus` int(11) NOT NULL DEFAULT '0' COMMENT ,
  `balance` decimal(14,4) NOT NULL DEFAULT '0.0000' COMMENT ,
  `operatetype` smallint(2) NOT NULL DEFAULT '1' COMMENT ,
  `overagebefore` decimal(14,2) DEFAULT '0.00' COMMENT ,
  `overageafter` decimal(14,2) DEFAULT '0.00' COMMENT ,
  `isshow` int(11) NOT NULL DEFAULT '0' COMMENT ,
  `createtime` datetime DEFAULT NULL,
  `updatetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY ```wuserId``` (`wuserId`) USING BTREE,
  KEY ```tradeno``` (`tradeno`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查询语句很简单，根据wuserid查询到用户，然后根据查询到的tradeno查询该订单关联的用户信息，当前业务就这两步
但是由于数据量特别大，当前存储了一年的数据，每天一张表，每张表平均一亿条数据，导致I/O遇到严重瓶颈，现在考虑迁移到ClickHouse看看效果
用了一台新的服务器dell R740XD，40TB的机械硬盘

安装

我们的系统是CentOS7.6，按照官网上的安装方法即可，非常简单https://ClickHouse.tech/#quick-start

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

sudo /etc/init.d/ClickHouse-server start
ClickHouse-client

创建数据库和表

我们的数据在mysql，所以直接从mysql导入的方法
首先创建数据库

CREATE DATABASE cxdetaillogs

然后创建表

CREATE TABLE detaillog20190306
ENGINE = MergeTree
ORDER BY id AS
SELECT * FROM mysql('192.168.1.x:3306','cxdetaillogs','detaillog20190306', 'uname', 'pwd');

运行结束如下
↓ Progress: 19.47 million rows, 2.79 GB (56.21 thousand rows/s., 8.05 MB/s.) Ok.

0 rows in set. Elapsed: 346.350 sec. Processed 19.47 million rows, 2.79 GB (56.21 thousand rows/s., 8.05 MB/s.)

发现数据目录没有东西，非常蛋疼，忘记use数据库了，但是我导入的时候看到磁盘在变化的呀，数据写到哪里去了呢？
解答：ClickHouse有一个default数据库，进入命令行默认就在default数据库，所以创建的表和数据都在default数据库下面了，这点不太习惯，ClickHouse命令行居然可以按Tab补全命令，比mysql优秀

use cxdetaillogs

然后重新从mysql导入表，运行结束如下
← Progress: 19.47 million rows, 2.79 GB (294.86 thousand rows/s., 42.22 MB/s.) Ok.

0 rows in set. Elapsed: 66.031 sec. Processed 19.47 million rows, 2.79 GB (294.86 thousand rows/s., 42.22 MB/s.)

[root@localhost cxdetaillogs]# ls -lh detaillog20190306.ibd
-rw-rw----. 1 mysql mysql 6.1G Oct 28 09:59 detaillog20190306.ibd

mysql占用了6个G，可能跟压缩和索引都有关系？
解答：ClickHouse压缩比特别高，上面看导入的时候是2.79G，导入完成后ClickHouse底层会不断的做异步的数据合并,合并完成后最终这个表占用了621M空间，和mysql比起来，磁盘占用只有1/10(可能是由于mysql索引占用比较大的原因，所以这么悬殊，网上介绍应该是没这么大的)

先查一下看看吧，不知道导成功没

SELECT COUNT(*)
FROM cxdetaillogs.detaillog20190306

┌──COUNT()─┐
│ 19469753 │
└──────────┘
1 rows in set. Elapsed: 0.009 sec.

在myslq数据库也查了一下148秒，等的无聊还抽了支烟，头发都不够用了。小激动了一下，现在看看表结构

localhost :) desc detaillog20190306;

DESCRIBE TABLE detaillog20190306

┌─name──────────┬─type───────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int32 │ │ │ │ │ │
│ wuserId │ Int32 │ │ │ │ │ │
│ tradeno │ String │ │ │ │ │ │
│ type │ Int32 │ │ │ │ │ │
│ tradedec │ Nullable(String) │ │ │ │ │ │
│ orderstatus │ Int32 │ │ │ │ │ │
│ balance │ String │ │ │ │ │ │
│ operatetype │ Int16 │ │ │ │ │ │
│ overagebefore │ Nullable(String) │ │ │ │ │ │
│ overageafter │ Nullable(String) │ │ │ │ │ │
│ isshow │ Int32 │ │ │ │ │ │
│ createtime │ Nullable(DateTime) │ │ │ │ │ │
│ updatetime │ Nullable(DateTime) │ │ │ │ │ │
└───────────────┴────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

看文档MergeTree结构需要指定主键和date什么的，现在这样算是指定了吗，我再研究研究

数据目录如下
刚建好的表partition如下

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'detaillog20190306'

┌─partition─┬─name────────┬─active─┐
│ tuple() │ all_1_1_0 │ 0 │
│ tuple() │ all_1_6_1 │ 1 │
│ tuple() │ all_2_2_0 │ 0 │
│ tuple() │ all_3_3_0 │ 0 │
│ tuple() │ all_4_4_0 │ 0 │
│ tuple() │ all_5_5_0 │ 0 │
│ tuple() │ all_6_6_0 │ 0 │
│ tuple() │ all_7_7_0 │ 0 │
│ tuple() │ all_7_12_1 │ 1 │
│ tuple() │ all_8_8_0 │ 0 │
│ tuple() │ all_9_9_0 │ 0 │
│ tuple() │ all_10_10_0 │ 0 │
│ tuple() │ all_11_11_0 │ 0 │
│ tuple() │ all_12_12_0 │ 0 │
│ tuple() │ all_13_13_0 │ 0 │
│ tuple() │ all_13_18_1 │ 1 │
│ tuple() │ all_14_14_0 │ 0 │
│ tuple() │ all_15_15_0 │ 0 │
│ tuple() │ all_16_16_0 │ 0 │
│ tuple() │ all_17_17_0 │ 0 │
│ tuple() │ all_18_18_0 │ 0 │
│ tuple() │ all_19_19_0 │ 1 │
│ tuple() │ all_1_6_1 │ 1 │
│ tuple() │ all_7_12_1 │ 1 │
│ tuple() │ all_13_18_1 │ 1 │
│ tuple() │ all_19_19_0 │ 1 │
└───────────┴─────────────┴────────┘

过了一段时间数据合并完后，如下：

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'detaillog20190306'

┌─partition─┬─name────────┬─active─┐
│ tuple() │ all_1_6_1 │ 1 │
│ tuple() │ all_7_12_1 │ 1 │
│ tuple() │ all_13_18_1 │ 1 │
│ tuple() │ all_19_19_0 │ 1 │
│ tuple() │ all_1_6_1 │ 1 │
│ tuple() │ all_7_12_1 │ 1 │
│ tuple() │ all_13_18_1 │ 1 │
│ tuple() │ all_19_19_0 │ 1 │
└───────────┴─────────────┴────────┘

ClickHouse底层会不断的做异步的数据合并，所以数据也会逐渐压缩，分区也会合并
和分区是能对应上的，一个文件夹，有两个相同名称的分区
靠什么规则分区的呢，这个暂时不知道

ClickHouse时区设置

sudo vim /etc/ClickHouse-server/config.xml
<timezone>Asia/Shanghai</timezone>

sudo service ClickHouse-server restart

由于我的数据盘在/home目录下面，所以要修改ClickHouse的数据目录

service ClickHouse-server stop
mkdir -p /home/ClickHouse/data /home/ClickHouse/log
mv /var/lib/ClickHouse /home/ClickHouse/data/
mv /var/log/ClickHouse-server /home/ClickHouse/log/
cd /var/lib/
ln -s /home/ClickHouse/data/ClickHouse .
cd /var/log/
ln -s /home/ClickHouse/log/ClickHouse-server .
service ClickHouse-server start

之前的索引用的id，由于查询中并未使用，根据业务，将索引改为wuserId和tradeno的组合重新建表

CREATE TABLE detaillog20190306 ENGINE = MergeTree ORDER BY (wuserId, tradeno) AS SELECT * FROM mysql('192.168.1.x:3306','cxdetaillogs','detaillog20190306', 'uname', 'pwd');

命令行导入需要手动一个表一个表导入，太麻烦，python2导入脚本如下

#-- coding:UTF-8 --

from ClickHouse_driver import Client
import sys
import time
import datetime

reload(sys)
sys.setdefaultencoding('utf8')

TNAME_PRE = 'detaillog'


def dateRange(beginDate, endDate):
    dates = []
    dt = datetime.datetime.strptime(beginDate, "%Y%m%d")
    date = beginDate[:]
    while date <= endDate:
        dates.append(date)
        dt = dt + datetime.timedelta(1)
        date = dt.strftime("%Y%m%d")
    return dates

if __name__ == '__main__':
    for ymd in dateRange('20190909', '20191231'):
        table_name = TNAME_PRE + ymd
        try:
            print table_name
            client = Client('localhost')
            sql = "CREATE TABLE cxdetaillogs."+ table_name +" ENGINE = MergeTree ORDER BY (wuserId, tradeno) AS SELECT * FROM mysql('192.168.1.x:3306','cxdetaillogs','" + table_name + "', 'username', 'password')"
            client.execute(sql)
            time.sleep(60)
        except Exception, e:
            break

参数设置：
在查询过程中出现了ClickHouse Max query size exceeded的报错
修改/etc/ClickHouse-server/users.xml
在profiles->defaul下面添加了10M
重启ClickHouse-server
select * from system.settings where name like ‘max_%’
就可以看到设置后的参数了，默认是256k，改成了10M，暂时没出现问题

备注：
1、ClickHouse查询字符串不能用双引号，只能用单引号
2、尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作，因为ClickHouse底层会不断的做异步的数据合并，会影响查询性能，这个在做实时数据写入的时候要尽量避开

Tags:

Comments

No comments yet. Why don’t you start the discussion?

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注