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;
最后修改于 2021-11-25 18:04:27
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付

