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.