clickhouse 系统表 --查看连接和进程

标签：rows 查看 database -- sum bytes system query clickhouse

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