clickhouse 查询正在执行的语句

-- 如果进程太多，可以查看正在执行的查询
SELECT query_id, user, address, query  FROM system.processes ORDER BY query_id;
 
-- 杀死不用的正在执行的查询
KILL QUERY WHERE query_id='2-857d-4a57-9ee0-327da5d60a90' 
KILL QUERY WHERE user='default'

-- 如果查询遇到 timeout，可以设置jdbc参数
socket_timeout=600000&max_bytes_before_external_group_by=20000000

-- 查看clickhouse整体数据量
SELECT
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts;


-- 根据分区查询系统日志情况
select  
database,
	table, 
    sum(rows) as row, 
    formatReadableSize(sum(bytes_on_disk)) as used_disk, 
    formatReadableSize(sum(data_uncompressed_bytes)) as before_compress, 
    formatReadableSize(sum(data_compressed_bytes)) as after_compress, 
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) compress_rate 
from system.parts 
where partition = '202111' 
group by table,database
order by row desc 


-- 查看磁盘使用情况
SELECT
    name,
    path,
    formatReadableSize(free_space) AS free_space,
    formatReadableSize(total_space) AS total_space,
    type
FROM system.disks 

-- 查看表大小
SELECT
table,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size ,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_bytes
FROM system.parts
WHERE active 
--AND (table LIKE 'data_%')
GROUP BY table 
order by uncompressed_bytes desc ; 

-- 查看正在后台执行的更新语句
SELECT database,table,mutation_id,command,create_time,parts_to_do_names,parts_to_do,latest_fail_reason 
FROM system.mutations
where is_done<>1
 
-- kill 指定的更新语句
KILL MUTATION mutation_id = 'mutation_id';

-- 今天前十的慢查询
SELECT
    user,
    formatDateTime(query_start_time, '%Y%m%d %T') AS start_time,
    query_duration_ms / 1000 AS query_duration_s,
    formatReadableSize(memory_usage ) AS memory_usage,
    result_rows ,
    formatReadableSize(result_bytes) AS result_bytes,
    read_rows ,
    formatReadableSize(read_bytes) AS read_bytes,
    written_rows ,
    formatReadableSize(written_bytes) AS written_bytes,
    query
FROM system.query_log
WHERE type = 2
and query_start_time>=today()
ORDER BY query_duration_s DESC
LIMIT 10 

--查看副本表是否异常
SELECT database, table, is_leader, total_replicas, active_replicas, zookeeper_exception 
FROM system.replicas 
WHERE is_readonly 
OR is_session_expired 
OR future_parts > 20 
OR parts_to_check > 10 
OR queue_size > 20 
OR inserts_in_queue > 10 
OR log_max_index - log_pointer > 10 
OR total_replicas < 2 
OR active_replicas < total_replicas;