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.