clickhouse-(05)-之DBA运维宝典

当前连接数

SELECT * FROM system.metrics WHERE metric LIKE '%Connection';

当前正在执行的查询

SELECT query_id, user, address, query  FROM system.processes ORDER BY query_id;

终止查询

KILL QUERY WHERE query_id = 'query_id'

存储空间统计

 SELECT name,path,formatReadableSize(free_space) AS free,formatReadableSize(total_space) AS total,formatReadableSize(keep_free_space) AS reserved FROM system.disks

各数据库占用空间统计

SELECT database, formatReadableSize(sum(bytes_on_disk)) on_disk FROM system.parts GROUP BY database;

每个列字段占用空间统计

SELECT 
    database, 
    table, 
    column, 
    any(type), 
    sum(column_data_compressed_bytes) AS compressed, 
    sum(column_data_uncompressed_bytes) AS uncompressed, 
    round(uncompressed / compressed, 2) AS ratio, 
    compressed / sum(rows) AS bpr, 
    sum(rows)
FROM system.parts_columns
WHERE active AND database != 'system'
GROUP BY 
    database, 
    table, 
    column
ORDER BY 
    database ASC, 
    table ASC, 
    column ASC
 

慢查询

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
ORDER BY query_duration_ms DESC
LIMIT 10

副本预警监控

SELECT database, table, is_leader, total_replicas, active_replicas 
  FROM system.replicas 
 WHERE is_readonly 
    OR is_session_expired 
    OR future_parts > 30 
    OR parts_to_check > 20 
    OR queue_size > 30 
    OR inserts_in_queue > 20 
    OR log_max_index - log_pointer > 20 
    OR total_replicas < 2 
    OR active_replicas < total_replicas

转载: https://blog.csdn.net/huzechen/article/details/107527346