users.xml log_queries 1 


查看磁盘空间
SELECT 
name, path, 
formatReadableSize(free_space) AS free_space, 
formatReadableSize(total_space) AS total_space, type 
FROM system.disks


查看表大小
SELECT
table,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size ,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_bytes
FROM system.parts
WHERE active AND (table LIKE 'data_%') --and partition like '(21,%)'
GROUP BY table 
order by uncompressed_bytes desc ;

查看列大小
select column,any(type),    
formatReadableSize(sum(column_data_compressed_bytes)) compressed_size ,     
formatReadableSize(sum(column_data_uncompressed_bytes)) uncompressed_bytes,     
sum(rows) 
from system.parts_columns 
where table ='data_report_local' and active --and partition like '(21,%)'
GROUP BY column
ORDER BY uncompressed_bytes desc ;


查看总连接数
SELECT * FROM system.metrics WHERE metric LIKE '%Connection';

查看正在执行的查询语句
SELECT query_id, user, address, elapsed, query
FROM system.processes
ORDER BY query_id ASC


kill 指定的查询语句
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

kill 指定的更新语句
KILL MUTATION mutation_id = 'mutation_id';

手动合并parts
OPTIMIZE TABLE table [PARTITION partition] [FINAL]

当天前十的慢查询
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 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;