ClickHouse问题排查心得

腾讯云后台只能看整体情况


其他指标也是断崖式下降


查看查询日志中的历史查询:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
query_id,
user,
query,
query_duration_ms,
read_rows,
read_bytes,
memory_usage,
ProfileEvents.Names,
ProfileEvents.Values
FROM system.query_log
WHERE event_time >= now() - INTERVAL 1 HOUR
AND type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

查看CPU使用情况相关的指标:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
query_id,
user,
query,
query_duration_ms,
ProfileEvents['UserTimeMicroseconds'] as user_cpu_time,
ProfileEvents['SystemTimeMicroseconds'] as system_cpu_time,
ProfileEvents['OSCPUVirtualTimeMicroseconds'] as virtual_cpu_time
FROM system.query_log
WHERE event_time >= now() - INTERVAL 1 HOUR
AND type = 'QueryFinish'
AND ProfileEvents['UserTimeMicroseconds'] > 0
ORDER BY ProfileEvents['UserTimeMicroseconds'] DESC
LIMIT 10;

查看当前正在执行的查询 (意义不太大)

1
2
3
4
5
6
7
8
9
10
11
SELECT 
query_id,
user,
query,
elapsed,
read_rows,
read_bytes,
memory_usage,
thread_ids
FROM system.processes
ORDER BY elapsed DESC;

文章目录