ClickHouse自带用于记录系统信息的系统库system,通过processes表,我们可以查看当前连接的进程信息,也就是正在运行的sql的信息。
SELECT query_id, read_rows, total_rows_approx, memory_usage, initial_user, initial_address, elapsed, query, client_hostname FROM system.processes;
# 字段含义
# query_id 查询id,
# read_rows 从表中读取的行数,
# total_rows_approx 应读取的行总数的近似值,
# memory_usage 请求使用的内存量
# initial_user 进行查询的用户
# initial_address 请求的 IP 地址
# elapsed 求执行开始以来的秒数
# query 查询语句
1:查询当前clickhouse的查看当前实时连接数
select * from metrics where metric like '%Connection'
2.杀死进程
KILL QUERY WHERE query_id='e9395abd-9367-4796-a6ec-a4e8a639aaea';
3:查看各个数据库占用空间统计
SELECT database, formatReadableSize(sum(bytes_on_disk)) AS on_disk FROM system.parts GROUP BY database
4:查询执行完的日志,这个可以看每个执行的sql的耗时,客户端是什么等。
SELECT user, client_hostname AS host, client_name AS client, formatDateTime(query_start_time, '%T') AS started, query_duration_ms / 1000 AS sec, round(memory_usage / 1048576) AS MEM_MB, result_rows AS RES_CNT, result_bytes / 1048576 AS RES_MB, read_rows AS R_CNT, round(read_bytes / 1048576) AS R_MB, written_rows AS W_CNT, round(written_bytes / 1048576) AS W_MB, query FROM system.query_log WHERE (type = 2) AND (query LIKE '%insert%') ORDER BY query_duration_ms DESC LIMIT 10
5:查询当前库表资源占用情况:
select database, table, 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., 2 ) AS "压缩率/%" from system.parts group by database, table order by database
标签:rows,查看,database,--,sum,bytes,system,query,clickhouse From: https://www.cnblogs.com/luckyfruit/p/16649864.html