【clickhouse】一个性能问题，把一个中间件从头到位翻了个遍

现象

团队在前期引入了clickhouse，业务上端口进行审计，每天的数据量很大，测试小伙伴在测试的时候，使用pcap回放，将千兆带宽几乎跑满，出现每天大概一亿的数据量，导致界面实时查询很慢，因为是一台物理机部署多个服务，存在其他服务争抢cpu资源，当数据量大的时候 出现查询超时错误。之前没有玩过clickhouse，借着这次机会把clickhouse把玩了一把，算是有了个入门了，这里做个记录，从索引 分区、投影、cpu、内存、并发数、几个方面来优化。

准备数据

登录Linux clickhouse-client --password 'password'

• 导入数据
  1. clickhouse-client --password 'password' -d default -q "insert into table format csv" --format_csv_delimiter='|' <  /home/a_port.txt
  2. insert  into a_port_adit（id,start_time,end_time,create_time,partition_time）select id,start_time,end_time,create_time,toDate(end_time) from a_port_audit10;
• 导出数据
  1. clickhouse-client --password 'password' -d default -q "select * from table (where 条件) format csv" --format_csv_delimiter='|' > /home/a_port.txt
• 删除TTL 
  1. ALTER TABLE TABLE REMOVE TTL
  2. SHOW CREATE TABLE a_port_adit;
• pcap包流量回放
  1. nohup tcpreplay -i eth2 -M 600 -l 0 bilbi.pcap > /dev/null 2>&1 &
  2. tcpdump -i lo port 8080 -v -w 8080.pcap

优化方向

• 分区
  • create table a_port_adit（id uuid，patition_time Date）engine = MergeTree PARTITION BY toYYYYMMDD(partition_time) order by(end_time) TTL create_time + toIntervalDay(7) SETTINGS index_granularity=8192; 
• 索引
• 投影
• 字段压缩方式
  • smac LowCardinality(String)
  • create_time DateTime codec(DoubleDelta)

是否使用索引

我们可以使用 EXPLAIN indexes = 1 来验证是否使用了索引：

实例：

EXPLAIN indexes = 1
SELECT * from docs WHERE doc LIKE '%wind%';
┌─explain─────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│   ReadFromMergeTree (default.docs)          │
│   Indexes:                                  │
│     PrimaryKey                              │
│       Condition: true                       │
│       Parts: 1/1                            │
│       Granules: 1/1                         │
│     Skip                                    │
│       Name: inv_idx                         │
│       Description: inverted GRANULARITY 1   │
│       Parts: 1/1                            │
│       Granules: 1/1                         │
└─────────────────────────────────────────────┘
12 rows in set. Elapsed: 0.006 sec.

优化结果