--查询 SELECT query_id, user, address, elapsed, query FROM system.processes ORDER BY query_id ASC  --慢慢杀死执行SQL KILL QUERY WHERE query_id='query_id'; 

--查询 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  --慢慢杀死执行SQL KILL MUTATION WHERE 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 type,concat(substr(query,1,100),'...') as query,read_rows,query_duration_ms,memory_usage,read_bytes,written_bytes from system.query_log limit 10

--统计执行频繁的SQL
select concat(substr(query,1,100),'...') as sql,count(*) as total from system.query_log
where event_time>'2021-12-01 00:00:00' and event_time<'2021-12-02 00:00:00' and is_initial_query=1 and lower(query) like '%%select%%'
group by sql order by total desc

如何开启查询日志： query_log记录了所有clickhouse服务中所有已经执行的查询记录

 	<!--全局定义-->
 	<!-- Query log. Used only for queries with setting log_queries = 1. -->
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <!-- Interval of flushing data. -->
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>

	<!--如果只需要为某些用户单独开启query_log,在users.xml的profile中配置-->
	<log_queries>1</log_queries>

remote(‘addresses_expr’, db, table[, ‘user’[, ‘password’]]) 允许访问远程服务器而不创建分布式表。

• 按节点按天统计慢SQL数
• 查询耗时大于指定时间的慢SQL
• 按节点统计正在运行的SQL数
• 查询正在运行的SQL

select * from
(         
select 'node1' as server,address,toStartOfDay(event_time) as event_day,count(1) as selectCount from remote('x.x.x.x1','system','query_log') where event_time>'2021-12-01 10:00:00' and event_time<'2021-12-01 11:00:00' and query_duration_ms>10000 and is_initial_query=1 and lower(query) like '%%select%%'  group by address,toStartOfDay(event_time) 
union all select 'node2' as server,address,toStartOfDay(event_time) as event_day,count(1) as selectCount from remote('x.x.x.x2','system','query_log') where event_time>'2021-12-01 10:00:00' and event_time<'2021-12-01 11:00:00' and  query_duration_ms>10000 and is_initial_query=1 and lower(query) like '%%select%%'  group by address,toStartOfDay(event_time) 
union all select 'node3' as server,address,toStartOfDay(event_time) as event_day,count(1) as selectCount from remote('x.x.x.x3','system','query_log') where event_time>'2021-12-01 10:00:00' and event_time<'2021-12-01 11:00:00' and query_duration_ms>10000 and is_initial_query=1 and lower(query) like '%%select%%'  group by address,toStartOfDay(event_time) 
) t order by server,event_day

select 'node1' as server,address,event_time,type,query_duration_ms,query from remote('x.x.x.x1','system','query_log','default') where event_time>'2021-12-01 10:00:00' and event_time<'2021-12-01 11:00:00' and query_duration_ms>10000 and is_initial_query=1 
union all select 'yjdsj03' as server,address,event_time,type,query_duration_ms,query from remote('x.x.x.x2','system','query_log','default') where event_time>'2021-12-01 10:00:00' and event_time<'2021-12-01 11:00:00' and  query_duration_ms>10000 and is_initial_query=1 
union all select 'yjdsj04' as server,address,event_time,type,query_duration_ms,query from remote('x.x.x.x3','system','query_log','default') where event_time>'2021-12-01 10:00:00' and event_time<'2021-12-01 11:00:00' and query_duration_ms>10000 and is_initial_query=1 


select * from
(select 'node1' as server,count(1) as cc from remote('x.x.x.x1','system','processes') 
union all select 'node2' as server,count(1) as cc from remote('x.x.x.x2','system','processes') 
union all select 'node3' as server,count(1) as cc from remote('x.x.x.x3','system','processes')
) t order by server;

select * from
(select 'node1' as server,query_id, user, address, elapsed, query from remote('x.x.x.x1','system','processes')   
union all select 'node2' as server,query_id, user, address, elapsed, query from remote('x.x.x.x2','system','processes') 
union all select 'node3' as server,query_id, user, address, elapsed, query from remote('x.x.x.x3','system','processes')
) t order by server;

SELECT table,partition,formatReadableSize(sum(data_compressed_bytes)) AS compressed_size ,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_bytes
FROM system.parts
WHERE active AND (table LIKE 'vehicle_warning_%%') 
GROUP BY table,partition
order by partition desc 

select column as colName,any(type) as colType, 	
sum(column_data_compressed_bytes) compressed_size , 	
sum(column_data_uncompressed_bytes) uncompressed_bytes, 	
sum(rows) as rowNum
from system.parts_columns 
where active AND table like 'vehicle_warning_LOCAL'
GROUP BY column
ORDER BY uncompressed_bytes desc ;

统计指定时间范围内，表的分区、分区文件数及占用空间大小

SELECT partition, count() AS number_of_parts, formatReadableSize(sum(bytes)) AS sum_size
FROM system.parts 
WHERE active AND (table = 'vehicle_warning_new_LOCAL') and partition between '2021-11-01 00:00:00' and '2021-11-30 00:00:00'
GROUP BY partition
ORDER BY partition ASC

SELECT database, table, is_leader, total_replicas, active_replicas, zookeeper_exception
is_session_expired,future_parts, parts_to_check,queue_size,inserts_in_queue,log_max_index,log_pointer
FROM system.replicas where table = 'vehicle_warning_LOCAL'

--查看异常的副本,各个预警的变量可以根据自身情况调整。
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

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

SELECT name,path,formatReadableSize(free_space) AS free_space,
formatReadableSize(total_space) AS total_space, type
FROM system.disks

select cluster,shard_num,shard_weight,replica_num,host_name,host_address,port,user,errors_count,estimated_recovery_time  from system.clusters where replica_num=1

查看目前正在处理MergeTree族表的合并和分区变化的信息

select database ,table,elapsed ,progress,num_parts ,result_part_name ,is_mutation ,total_size_bytes_compressed ,rows_read ,rows_written   
from system.merges

系统表介绍详见官网

import time
def yieldDF(df):
    for index, row in df.iteritems():
        for i in range(len(row)):
            yield row[i],index

def printDF(df,pos=slice(1,2)):
    for row in df.itertuples():
        print(row[pos])
def executeSQL(sql):
    start =time.process_time()
    df = pd.read_sql(sql,con=engine)
    end = time.process_time()
    print('Running time: %s Seconds'%(end-start))
    return df

sql='''select cluster,shard_num,shard_weight,replica_num,host_name,host_address,port,user,errors_count,estimated_recovery_time from system.clusters where replica_num=1 '''
executeSQL(sql)

for d,i in yieldDF(executeSQL(sql).head(1)):
    print(i,'==>',d)

printDF(executeSQL(sql),slice(1,3))