clickhouse小工具&小技巧收集汇总

0x01. 排查clickhouse问题

• 找到问题发生的时间点，可以对照grafana中的资源监控来看时间点
• 找/var/log/clickhouse-server/clickhouse-server[-err].log对应时间点的日志
• 查看system.query_log/processes具体的一些动作，定位问题

0x02. 琐碎的小技巧和系统表

1. 改名

rename table ods_data.single_point_log_2 to ods_data.single_point_log on cluster cluster_2shard_5_6_2replicas_5_6;

2. 更改或者删除表数据

ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr

clickhouse中这些语句是异步执行的，查询这些语句执行情况，可以查看system.mutations表，找到执行语句对应的记录，is_done为1表示执行完成

3. system.query_log与慢查询查询
   query_log系统表记录clickhouse中sql执行的历史，可以查看执行时间，影响的数据行等信息，可以为优化SQL提供一些关键性的信息。当然基于此我们也可以做一些监控，例如下面的慢查询语句

--查询执行超过1000秒的语句
select  type,query_start_time,query_duration_ms ,query,`exception` 
from system.query_log
where query_start_time >='2020-11-09 08:11:00' and query_duration_ms >1000000 
order by query_start_time ;

4. 表强制去重

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE]  

If you specify FINAL, optimization is performed even when all the data is already in one part.
If you specify DEDUPLICATE, then completely identical rows will be deduplicated (all columns are compared), it makes sense only for the MergeTree engine.

5. kill查询/更新
   在cli中执行sql会返回一个query_id，基于该ID我们强制停止该查询

KILL QUERY [ON CLUSTER cluster]
  WHERE <where expression to SELECT FROM system.processes query>
  [SYNC|ASYNC|TEST]
  [FORMAT format]

-- Forcibly terminates all queries with the specified query_id:
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90'

-- Synchronously terminates all queries run by 'username':
KILL QUERY WHERE user='username' SYNC

KILL MUTATION [ON CLUSTER cluster]
  WHERE <where expression to SELECT FROM system.mutations query>
  [TEST]
  [FORMAT format]

-- Cancel and remove all mutations of the single table:
KILL MUTATION WHERE database = 'default' AND table = 'table'

-- Cancel the specific mutation:
KILL MUTATION WHERE database = 'default' AND table = 'table' AND mutation_id = 'mutation_3.txt'

0x03. 磁盘损坏clickhouse的恢复

如果表是多副本集，那么磁盘损坏是可以快速恢复的

1. 直接更换损坏的盘
2. 重启clickhouse的服务
3. 重启zookeeper的服务