clickhouse数据维护

# clickhouse 数据维护

# 系统使用情况

SELECT
    query_id,
    query,
    elapsed,
    rows_read,
    bytes_read,
    result_rows,
    total_rows_to_read, 
    total_rows_to_insert, 
    total_rows_written, 
    total_bytes_written, 
    created_at, 
    event_time, 
    event_date 
FROM system.query_log 
WHERE event_date = today() 
ORDER BY elapsed DESC 
LIMIT 2;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

查询慢日志

SELECT * FROM system.query_log WHERE duration_ms > 2000 ORDER BY duration_ms DESC LIMIT 10; 

SELECT *  FROM system.query_log WHERE elapsed < 1000 ORDER BY elapsed DESC;

SELECT * FROM system.asynchronous_metrics WHERE name = 'QueryProcessingTimeMax' ORDER BY value DESC LIMIT 10;

1
2
3
4
5

# 查看有哪些sql进程

select query_id,user,read_rows,elapsed,memory_usage,peak_memory_usage,query from system.processes;

# 查看数据库、表存储情况

# 查看库数据情况(库：dispatcher)

SELECT
    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, 0) AS `压缩率`,
    `table` AS `表名`
    FROM system.parts where database = 'dispatcher' group by `table`;

1
2
3
4
5
6
7

# 查看单表数据情况(库：dispatcher,表：gnss)

SELECT
    column AS `字段名`,
    any(type) AS `类型`,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`,
    round(sum(column_data_uncompressed_bytes) / sum(column_data_compressed_bytes), 2) AS `压缩率`,
    sum(rows) AS `行数`
    FROM system.parts_columns
    WHERE (database = 'dispatcher') AND (table = 'gnss')
    GROUP BY column
    ORDER BY column ASC;

1
2
3
4
5
6
7
8
9
10
11

# 查看表信息(库：dispatcher、表：gnss)

select
    database,
    table,
    formatReadableSize(size) as size,
    formatReadableSize(bytes_on_disk) as bytes_on_disk,
    formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
    formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
    compress_rate,
    rows,
    days,
    formatReadableSize(avgDaySize) as avgDaySize
from
(
    select
        database,
        table,
        sum(bytes) as size,
        sum(rows) as rows,
        min(min_date) as min_date,
        max(max_date) as max_date,
        sum(bytes_on_disk) as bytes_on_disk,
        sum(data_uncompressed_bytes) as data_uncompressed_bytes,
        sum(data_compressed_bytes) as data_compressed_bytes,
        (data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
        max_date - min_date as days,
        size / (max_date - min_date) as avgDaySize
    from system.parts
    where active 
     and database = 'dispatcher'
     and table = 'gnss'
    group by
        database,
        table
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# clickhouse库分区

# 查看库有哪些分区(库：dispatcher、表：gnss、)

SELECT
    partition AS `分区`,
    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, 0) AS `压缩率`
    FROM system.parts
    WHERE (database IN ('dispatcher')) AND (table IN ('gnss'))
    GROUP BY partition
    ORDER BY partition ASC;

1
2
3
4
5
6
7
8
9
10

# 查看库有哪些分区(库：dispatcher、表：gnss、分区LIKE：2024%)

SELECT
    partition AS `分区`,
    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, 0) AS `压缩率`
    FROM system.parts
    WHERE (database IN ('dispatcher')) AND (table IN ('gnss')) AND (partition LIKE '2024%')
    GROUP BY partition
    ORDER BY partition ASC;

1
2
3
4
5
6
7
8
9
10

# 删除分区(库：dispatcher、表：gnss、分区：202407)

分区clickhouse设置ttl (opens new window)

alter table dispatcher.gnss drop partition '202407';

# 设置分区过期删除(注意：不要在生产高峰期执行)

按表字段时间来进行删除：timestamp

ALTER TABLE dispatcher.gnss MODIFY TTL timestamp + INTERVAL 5 month;

ALTER TABLE dispatcher.gnss MODIFY TTL 0;

1
2
3

# 查看单表字段信息(库：dispatcher、表：gnss)

SELECT
    column AS `字段名`,
    any(type) AS `类型`,
    formatReadableSize(sum(column_data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(column_data_compressed_bytes)) AS `压缩大小`,
    round(sum(column_data_uncompressed_bytes) / sum(column_data_compressed_bytes), 2) AS `压缩率`,
    sum(rows) AS `行数`
FROM system.parts_columns
WHERE (database = 'dispatcher') AND (table = 'gnss')
GROUP BY column
ORDER BY column ASC;

1
2
3
4
5
6
7
8
9
10
11

如果此文章对您有帮助，点击 -->> 请博主喝咖啡

上次更新: 2025/03/28, 13:42:54

← clickhouse-backup使用 Sqlserver 2017安装问题记录→