常用ClickHouse性能监控SQL汇总

-- 查询所有分区的大小
SELECT 
    database, 
    table, 
    partition, 
    sum(bytes_on_disk) AS partition_size 
FROM system.parts 
GROUP BY database, table, partition 
ORDER BY partition_size DESC;
 
-- 查询每个表的大小
SELECT 
    database, 
    table, 
    sum(bytes_on_disk) AS table_size 
FROM system.parts 
GROUP BY database, table 
ORDER BY table_size DESC;
 
-- 查询整个集群的大小
SELECT 
    database, 
    sum(bytes_on_disk) AS database_size 
FROM system.parts 
GROUP BY database 
ORDER BY database_size DESC;
 
-- 查询所有节点的CPU和内存使用情况
SELECT 
    hostname, 
    cpu_usage, 
    memory_usage_percent 
FROM system.metrics 
WHERE (event_date >= today() - 1) AND (event_date <= today());
 
-- 查询最慢的查询
SELECT 
    query, 
    round(elapsed, 2) AS duration, 
    type 
FROM system.query_log 
WHERE (type = 'Query' AND event_date >= today() - 1 AND event_date <= today()) 
ORDER BY duration DESC 
LIMIT 10;
 
-- 查询最频繁的查询
SELECT 
    query, 
    count() AS num_executions 
FROM system.query_log 
WHERE (type = 'Query' AND event_date >= today() - 1 AND event_date <= today()) 
GROUP BY query 
ORDER BY num_executions DESC 
LIMIT 10;

这些SQL语句可以帮助你监控ClickHouse的性能，包括分区大小、表大小、集群大小，以及节点的CPU和内存使用情况。同时，它们还提供了最慢查询和最频繁查询的相关信息。这些信息对于调优ClickHouse集群性能至关重要。