Clickhouse日常维护

1.查询正在执行的sql

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

终止查询:
kill query where query_id = 'query_id'

2.查询mutations
SELECT 和 INSERT 之外，在 ClickHouse 中还存在一类被称作 Mutation 的操作，也就是 ALTER DELETE 和 ALTER UPDATE

SELECT database, table, mutation_id, command, create_time, is_done FROM system.mutations;

终止mutation
kill mutation where mutation_id = 'mutation_id';

3.各数据库占用空间统计

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

4.查看字段压缩比

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 Query id: afcc3d43-73cb-4be6-a2c4-b0de1d0d88d4 ┌─database────┬─table──┬─column────────┬─any(type)────────┬──compressed─┬─uncompressed─┬──ratio─┬──────────────────bpr─┬──sum(rows)─┐ │ db_sentinel │ metric │ app │ String │ 756331102 │ 37651779156 │ 49.78 │ 0.5223987357655436 │ 1447804235 │ │ db_sentinel │ metric │ block_qps │ Int64 │ 51523505 │ 11582153888 │ 224.79 │ 0.03558734237298318 │ 1447804235 │ │ db_sentinel │ metric │ count │ Int64 │ 204264903 │ 11582153888 │ 56.7 │ 0.14108599633982974 │ 1447804235 │ │ db_sentinel │ metric │ exception_qps │ Int64 │ 54114898 │ 11582153888 │ 214.03 │ 0.03737722040853127 │ 1447804235 │ │ db_sentinel │ metric │ gmt_create │ DateTime │ 1461204548 │ 5791076944 │ 3.96 │ 1.0092556111358522 │ 1447804235 │ │ db_sentinel │ metric │ gmt_modified │ DateTime │ 1461204548 │ 5791076944 │ 3.96 │ 1.0092556111358522 │ 1447804235 │ │ db_sentinel │ metric │ id │ Int64 │ 5983378896 │ 11582153888 │ 1.94 │ 4.132726477347264 │ 1447804235 │ │ db_sentinel │ metric │ machine_ip │ Nullable(String) │ 2310772922 │ 21136005999 │ 9.15 │ 1.5960534346689488 │ 1447804235 │ │ db_sentinel │ metric │ month │ Date │ 12930487 │ 2895538472 │ 223.93 │ 0.008931101793606785 │ 1447804235 │ │ db_sentinel │ metric │ pass_qps │ Int64 │ 3362071882 │ 11582153888 │ 3.44 │ 2.32218679896319 │ 1447804235 │ │ db_sentinel │ metric │ resource │ String │ 13376802422 │ 116862047689 │ 8.74 │ 9.239372353403843 │ 1447804235 │ │ db_sentinel │ metric │ resource_code │ Int64 │ 3054311246 │ 11582153888 │ 3.79 │ 2.1096161844007866 │ 1447804235 │ │ db_sentinel │ metric │ rt │ Float64 │ 3896826770 │ 11582153888 │ 2.97 │ 2.691542596572112 │ 1447804235 │ │ db_sentinel │ metric │ success_qps │ Int64 │ 3361088814 │ 11582153888 │ 3.45 │ 2.3215077927990726 │ 1447804235 │ │ db_sentinel │ metric │ timestamp │ DateTime │ 31010619 │ 5791076944 │ 186.74 │ 0.021419069132644165 │ 1447804235 │ └─────────────┴────────┴───────────────┴──────────────────┴─────────────┴──────────────┴────────┴──────────────────────┴────────────┘ 15 rows in set. Elapsed: 0.057 sec.

5.慢查询

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;

6.表压缩大小

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 ASC Query id: d3c88c77-590b-40e7-9328-03406704c2c3 ┌─database────┬─table──┬─────总行数─┬─原始大小───┬─压缩大小──┬─压缩率/%─┐ │ db_sentinel │ metric │ 1447804235 │ 268.76 GiB │ 36.67 GiB │ 13.65 │ └─────────────┴────────┴────────────┴────────────┴───────────┴──────────┘ 1 rows in set. Elapsed: 0.024 sec.